[GIS] Selecting closest points from a given point on another table

postgispostgresql

I have two tables, one with "houses", and the other with "points". I know how to get the distance individually, like this:

gisdb=# SELECT round(ST_Distance_Sphere(
(SELECT coordinates FROM houses WHERE id = 1),
(SELECT coordinates FROM points WHERE id = 2)))
as Distance
;
 distance
----------
      174
(1 row)

The thing is, if on the table "points" I have about 50 points, how can I make it return the distance of the closest one?

I've seen the function ST_ClosestPoint but I don't know how to apply it to my query.

Best Answer

To find the closest points to houses, you can replace your second subquery (SELECT coordinates FROM points WHERE id = 2) with one that specifically finds the closest points, e.g. by sorting:

SELECT coordinates FROM points ORDER BY coordinates <->
    (select coordinates from houses where id = 1) LIMIT 1 )

This selects the point whose distance (<->) to the house with id=1 is the smallest (ORDER BY and LIMIT 1). The entire SQL can be something like this:

SELECT round(ST_DistanceSphere(
 (SELECT coordinates FROM houses WHERE id = 1),
 (SELECT coordinates FROM points ORDER BY coordinates <->
    (select coordinates from houses where id = 1) LIMIT 1 )
 ))
as Distance;

By the way, ST_ClosestPoint is probably not what you are looking for. It selects the closest vertex in one geometry (e.g. polyline) with respect to a query geometry (e.g. a point). In your case, the points table contains points, not multipoints.

Related Question