# 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