Sort relative distance on the fly


#1

Hi I am quite a newbie in Ruby. I want to sort a list of locations by
relative distance (shortest to farthest relative to my location) on the
fly. For example, let say I am at x and y coordinate, I have other
points in the map around me. I want to be able to list the other points
in terms of the shortest distance from me to the farthest. I store
longitude and latitude information on the database. However, I cannot
store the relative distance permanently, as (i) number of points change
in real time; and (ii) I don’t have relationship with the other points,
which means the many-to-many tables are still empty.

Any ideas?


#2

On Apr 16, 2009, at 10:21 PM, Milton Wong wrote:

change
in real time; and (ii) I don’t have relationship with the other
points,
which means the many-to-many tables are still empty.

Any ideas?

Google for “earth distance sql”… or trust that the below is right :slight_smile:

This assumes that some_table has columns for latitude and longitude
and that you’ve set variables for latitude, longitude and radius (in
miles) to search on.

SELECT * FROM some_table WHERE
(3960 * 2 * ASIN( SQRT( (1 - COS(RADIANS(latitude - #{latitude})))
/ 2 + COS(RADIANS(#{latitude}))

  • COS(RADIANS(latitude))
  • (1 - COS(RADIANS(longitude - #{longitude}))) / 2))) <= #{radius}

#3

Michael W. wrote:

Hi I am quite a newbie in Ruby. I want to sort a list of locations by
relative distance (shortest to farthest relative to my location) on the
fly.
[…]

No need to implement this yourself. Use PostgreSQL, PostGIS, and (if
necessary) GeoRuby. This will make the task almost trivial.

Best,

Marnen Laibow-Koser
http://www.marnen.org
removed_email_address@domain.invalid


#4

Philip H. wrote:

On Apr 16, 2009, at 10:21 PM, Milton Wong wrote:
SELECT * FROM some_table WHERE
(3960 * 2 * ASIN( SQRT( (1 - COS(RADIANS(latitude - #{latitude})))
/ 2 + COS(RADIANS(#{latitude}))

  • COS(RADIANS(latitude))
  • (1 - COS(RADIANS(longitude - #{longitude}))) / 2))) <= #{radius}

Thanks Phillip, Michael. I tried the formula and it works!