2

Right now I have a data base with PostGIS. Several of the data sets contain different locations at latitude longitude points. I will be doing several queries based at different latitude longitude points, I need to find the closest locations at each point as well as all the points within a certain number of meters. Right now I was just sorting all the data by closest to the lat/lon point that I was querying, so:

SELECT 
     rest.station_id
FROM 
     rest
ORDER BY rest.geom <-> ST_SetSRID(ST_MakePoint('%(lat)s',' (lon)s')::geometry, 4326)
LIMIT 1

where the lat lon variables are what I am querying.

Is there a way to do this better so that I don't have to sort each time I do a query?

  • With a spatial index on gages.geom, the ordering should be very fast. On a side note, you have swapped lat/long when creating the points (it should be longitude first, then latitude) – JGH Feb 11 at 12:37
1

Based on an example here, you could do something like this:

SELECT 
a.station_id,
ST_Distance(a.geom, 'POINT(your_lon your_lat)'::geography) dist 
FROM rest a 
WHERE ST_DWithin(a.geom, 'POINT(your_lon your_lat)'::geography, your_dist_in_meters)
ORDER BY a.geom <-> 'POINT(your_lon your_lat)'::geography;
| improve this answer | |

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.