3

I have a coordinate (longitude, latitude) which I get from my map (I'm using OSM with OpenLayers) so I get something like this lon: -46.502541777468984 lat: -23.474943422638063.

What I need? I need to know if there is some function in PostGIS that I can use to search on my database which will return the nearest result that I have in DB.

I know I can do this using a select, but it take too much time.

1
  • See my answer for a minor correction. Nov 27 '17 at 15:42
3

If it is just from one table then you could do:

SELECT * FROM table
ORDER BY geom <-> 'SRID=4326;POINT(-46.502541777468984 -23.474943422638063)'::geometry
LIMIT 1;
3
  • @AdenirSouza use that method instead. see the update. Nov 24 '17 at 23:29
  • @EvanCarroll if you have a better answer feel free to add it. I'm happy with my query. Nov 27 '17 at 10:09
  • 1
    glad to, done. =) Nov 27 '17 at 15:44
2

Don't use the chosen method. It's relying on unstandardized text-to-point conversion and is otherwise subject to rounding errors. They won't be a problem because of the precision, they will be a problem because of equality-testing. ST_MakePoint is typed is and standardized OGC/SQL-MM 3: 8.1.3, 9.5.4.

SELECT * FROM table
ORDER BY geom <-> ST_MakePoint(-46.502541777468984,-23.474943422638063)::geography
LIMIT 1;

EKWT(with the SRID in the WKT) is a PostGIS thing, and if OGC or ISO standardizes it then that behavior may change.

In my example above, I'm using the geography type because it's probably what you want anyway and it defaults to srid 4326.

1
  • I've tested your method, but besides it worked when I tested directly on pgAdmin it did not work when I applied in my application. But thank you for your help! Nov 27 '17 at 15:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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