[GIS] How to return nearest point to input lat/long with distance

nearest neighborpostgissql

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!

Best Answer

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!

Related Question