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!
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