Ordering the Nearest - PostGIS, GeoRuby, spaces_adapter

Question

I am trying to execute an order request that finds records closest to current_user.

I know the distance between two points: current_location.euclidean_distance(@record.position)

How can I work with this PostGIS request (or active_record / spaces_adapter)?

Answer 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 large data set and you know that you do not want to search further, say, 1 km, the query will be more efficient if you run:

 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; 

/ Niklas

Answer 2

Just in case, someone stumbles upon this problem in rails 4. I use rgeo gem and it works for me.

 scope :closest, ->(point) { order("ST_Distance(lonlat, ST_GeomFromText('# {point.as_text}', #{SRID}))").limit(5) } 

Answer 3

To wrap this up, with every help I have the job I wanted:

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

Answer 4

If you really want to find literally 5 entries closest to current_user, consider the neighborhood search, which is supported by the KNN index in PostGIS 2.0 (see the "↔" operator):

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

Yours, S.

Answer 5

See the ST_Distance documentation in PostGIS.

Answer 6

If you DO NOT USE PostGIS, the geo-kit does it fine using google or yahoo (I just used Google) and in your queries you can sort by distance, this is awesome ..