I'm trying to do an order query that finds records nearest to the current_user.
I know the distance between the two points is: current_location.euclidean_distance(@record.position)
How can I work this into a PostGIS (or active_record/spatial_adapter) query?
To get the 5 closest:
SELECT * FROM your_table
ORDER BY ST_Distance(your_table.geom, ST_Geomfromtext(your point as wkt))
limit 5;
If you have a big dataset and know that you don't want to search further than , say 1 km, the query will be more efficient if you do:
SELECT * FROM your_table
WHERE ST_DWithin(your_table.geom, ST_Geomfromtext(your point as wkt, 1000)
ORDER BY ST_Distance(your_table.geom, ST_Geomfromtext(your point as wkt))
limit 5;
/Nicklas
Just in case somebody stumbles upon this issue in rails 4. I am using rgeo gem and this works for me
scope :closest, ->(point) { order("ST_Distance(lonlat, ST_GeomFromText('# {point.as_text}', #{SRID}))").limit(5) }
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With