4

I am trying to write a query with PostGIS that returns one row where the geometry of that row is the closest to an input point. I would also like to include the distance between these points in the result. And of course, I want it to run as quickly as possible. I have scoured the internets, but haven't quite found the solution to this specific problem.

Here is what I have so far, the query runs just fine, but the result is definitely not the closest record. (Note that geom is the name of the geometry column)

SELECT * FROM table 
ORDER BY geom <-> st_setsrid(st_makepoint(-112,33),3857)
LIMIT 1;

I have also tried to use the following ORDER BY clause, but I get the same incorrect result:

ORDER BY ST_Distance(geom,ST_GeomFromText('POINT(-112 33)', 3857));

I need to fix this query so:

  1. It returns the correct closest geom and associated row, and

  2. Include the distance between the points with the result.

Any help is much appreciated!

4
  • 1
    have you tried select *, ST_Distance(geom,ST_GeomFromText('POINT(-112 33)', 3857)) as Distance from table where ... order by .... limit 1; I have feeling that since the function is not at the select section is not computated? Just a hunch
    – nickves
    Dec 7, 2012 at 16:03
  • 1
    Assuming -112,33 is in Arizona, have you tried changing 3857 to 4326, and then doing a ST_ Transform to 3857 if the layer is in web Mercator?
    – mkennedy
    Dec 7, 2012 at 17:09
  • @mkennedy That did it! Not sure why this works, but it does. Do you mind making an answer out of your comment, so I can close this one out?
    – Gady
    Dec 7, 2012 at 17:12
  • Done! Glad to hear that it worked.
    – mkennedy
    Dec 7, 2012 at 17:23

1 Answer 1

3

The input coordinates are longitude,latitude values and must be identified as such so use 4326. Then use ST_Transform to convert to 3857 (web Mercator) to match the layer.

Note: I don't know SQL well enough to provide updated commands. I'd most likely make a mistake!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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