Selecting closest points from a given point on another table

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.

• Please spend some more time researching on how to do a "self join". Keep in mind that you only want to match featues with a higher unique key. – Vince May 18 '17 at 10:59
• I had thought about that; collecting all distances and then returning the lowest one with min(), but it wasn't a very clean way. That's why I asked here. – Newwt May 18 '17 at 12:00
• @Newwt can you update the question with SELECT version() UNION ALL SELECT postgis_version(); – Evan Carroll May 19 '17 at 6:39

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.

• Works perfect! The only thing I'd suggest to change is ST_DistanceSphere for ST_Distance_Sphere. Thank you very much! – Newwt May 18 '17 at 11:56
• @Newwt No, tinlyx did it right. ST_DistanceSphere is the 2.x name (upgraded to match the spec), ST_Distance_Sphere is the 1.5 name. If you were on 1.5, you would not have <->. This answer uses knn (index) to determine the closest house. That's great. It's a great query. You can't use it in 1.5x. – Evan Carroll May 19 '17 at 0:41
• @EvanCarroll: imgur.com/QkxXsns. The second one doesn't return any result because the ids don't exist, but whatevs. – Newwt May 19 '17 at 6:36