How to return nearest point to input lat/long with distance?

Question

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!

• nickves: 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
• mkennedy: 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?
• Gady: @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?
• mkennedy: Done! Glad to hear that it worked.

Answer 1

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!