5

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
5

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
  • 1
    @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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.