Order by nearest - PostGIS, GeoRuby, spatial_adapt

问题:

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?

回答1:

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

回答2:

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) }

回答3:

To wrap this up, with everyone's help I've got it working how I wanted:

order("ST_Distance(items.position, ST_GeomFromText('POINT (#{current_location.y} #{current_location.x})', #{SRID}))")

回答4:

If you really want to find literally the 5 records nearest to the current_user, consider neighborhood search, which is supported by KNN index in PostGIS 2.0 (see '<->' operator):

SELECT * FROM your_table ORDER BY your_table.geom <-> ST_Geomfromtext(your point as wkt, 1000) LIMIT 5

回答5:

Look at the ST_Distance documentation in PostGIS.

回答6:

If you dont NEED to use PostGIS, geo-kit does this perfectly using google or yahoo (I've only used Google) and in your queries you can sort by distance, its awesome..