PostgreSQL/PostGIS: ST_distance query should only return "nearest" result

Question

I would like to query the distance for every point (id) in table A to every polgygon (id) in table B. For the distance calculation I am using ST_Distance. However, ST_Distance returns (obviously) the distance to every polygon. But I only need the "closest" result for each point. So far, I tried the following query which returns the correct result but (of course) for exactly one point.

SELECT polygons.id, points.id, ST_Distance(points.geom, polygons.geom) 
FROM table_A AS points, table_B AS polygons 
ORDER BY st_distance ASC LIMIT 1

The result should be something like this:

polygon_id | point_id | min(distance)
-------------------------------------
1234       |   876    |   54.32
...        |   ...    |    ...
-------------------------------------

Do you have any hints? Thank you very much.

UPDATE 1

WITH CTE AS
       (SELECT polygons.id as poly_id, points.id as point_id, 
        ST_Distance(points.geom, polygons.geom) as thedistance , 
        row_number() OVER
              (PARTITION BY points.id ORDER BY ST_Distance(points.geom, polygons.geom)) 
    FROM 
        table_A AS points
    INNER JOIN table_B AS polygons
    ON ST_DWithin(points.geom, polygons.geom, 100)) SELECT * FROM CTE WHERE row_number = 1

After running the above query (3h 24m) there was empty result returned. However, there should a result. Is it possible that there is a problem with parentheses?

UPDATE 2

The SRID is 4326 (WGS84) and the polygons are OSM building polygons and the points arbitrary points in the same city.

mlinth mlinth · Accepted Answer · 2020-03-23T15:25:25

You can use a window function and a CTE. Something like this:

WITH CTE AS
   (select polygons.id as poly_id, points.id as point_id, 
    ST_Distance(points.geom, polygons.geom) as thedistance , 
    row_number() OVER
          (PARTITION BY points.id ORDER by ST_Distance(points.geom, polygons.geom) ) 
FROM 
 table_A AS points, table_B AS polygons )
 select poly_id, point_id, thedistance from CTE where row_number = 1

However, this might be slow if you have lots of points. You can speed it up by using st_dwithin in a join that uses an index, if you know roughly how far apart your points are from your polygons. Just set the distance parameter so you catch every point:

WITH CTE AS
   (select polygons.id as poly_id, points.id as point_id, 
    ST_Distance(points.geom, polygons.geom) as thedistance , 
    row_number() OVER
          (PARTITION BY points.id ORDER by ST_Distance(points.geom, polygons.geom) ) 
FROM 
    table_A AS points
INNER JOINT table_B AS polygons
ON st_Dwithin(points.geom, polygons.geom, 5000 )) -- assumes you have a metres projection, limit to 5KM
select * from CTE where row_number = 1

Be sure you have GIST indexes on both your GEOM columns

PostgreSQL/PostGIS: ST_distance query should only return "nearest" result

1 Answers