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:
It returns the correct closest geom and associated row, and
Include the distance between the points with the result.
Any help is much appreciated!