10

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?

DanS
  • 17,550
  • 9
  • 53
  • 47

6 Answers6

17

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

Thilo
  • 17,565
  • 5
  • 68
  • 84
Nicklas Avén
  • 4,706
  • 1
  • 18
  • 15
  • I have my position in a helper method current_location. Do you know if it's possible to insert this into the query (rails)? Something like: `.order('ST_Distance(items.position, #{current_location})')` – DanS Feb 25 '11 at 08:34
  • Problem was I needed double quoates. `.order("#current_location")` – DanS Feb 25 '11 at 09:00
  • 1
    There is a bracket missing on the WHERE line after 'your point as wkt' – DanS Feb 25 '11 at 10:19
  • Why pass it in as wkt? – boulder_ruby May 14 '14 at 04:31
  • For other newbies like me, it might be helpful to know that `items` in the call to `ST_Distance` refers to the name of the DB table that has the geo-coded elements. – sameers Sep 19 '15 at 18:07
6

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) }
dc10
  • 2,160
  • 6
  • 29
  • 46
  • 1
    you should add to the answer how you define the `lonlat` in Rails / rgeo – Tilo Aug 24 '14 at 22:38
  • 3
    For other newbies like me, it might be helpful to know that the value of `SRID` should be an integer that points to the universally-known ID of the coordinate system you are using to compute distances. You are _most probably_ looking to use the [Geodesic coordinate system](https://en.wikipedia.org/wiki/World_Geodetic_System#WGS84) of the planet Earth which is ID **4326**. – sameers Sep 19 '15 at 18:09
2

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

Francisco
  • 10,918
  • 6
  • 34
  • 45
Stefan
  • 1,036
  • 1
  • 10
  • 14
2

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}))")
DanS
  • 17,550
  • 9
  • 53
  • 47
1

Look at the ST_Distance documentation in PostGIS.

Sarge
  • 2,367
  • 2
  • 23
  • 36
0

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..

Rabbott
  • 4,282
  • 1
  • 30
  • 53
  • 1
    you are right but you are restricted by google's request limit for their API. in this case it is 2500 request / 24 hrs – dc10 Apr 16 '14 at 21:14