CODEWITHSUNDEEP
sqlgeocoding
deceze
deceze

Reputation: 522016

SQL query to calculate coordinate proximity

I'm using this formula to calculate the distance between entries in my (My)SQL database which have latitude and longitude fields in decimal format:

6371 * ACOS(SIN(RADIANS( %lat1% )) * SIN(RADIANS( %lat2% )) + 
COS(RADIANS( %lat1% )) * COS(RADIANS( %lat2% )) * COS(RADIANS( %lon2% ) - 
RADIANS( %lon1% )))

Substituting %lat1% and %lat2% appropriately it can be used in the WHERE clause to find entries within a certain radius of another entry, using it in the ORDER BY clause together with LIMIT will find the nearest x entries etc.

I'm writing this mostly as a note for myself, but improvements are always welcome. :)

Note: As mentioned by Valerion below, this calculates in kilometers. Substitute 6371 by an appropriate alternative number to use meters, miles etc.

Upvotes: 7

Views: 6694

Answers (4)

David
David

Reputation: 239

For databases (such as SQLite) that don't support trigonometric functions you can use the Pythagorean theorem.

This is a faster method, even if your database does support trigonometric functions, with the following caveats:

  • you need to store coords in x,y grid instead of (or as well as) lat,lng;
  • the calculation assumes 'flat earth', but this is fine for relatively local searches.

Here's an example from a Rails project I'm working on (the important bit is the SQL in the middle):

class User < ActiveRecord::Base
  ...
  # has integer x & y coordinates
  ...

  # Returns array of {:user => <User>, :distance => <distance>}, sorted by distance (in metres).
  # Distance is rounded to nearest integer.
  # point is a Geo::LatLng.
  # radius is in metres.
  # limit specifies the maximum number of records to return (default 100).
  def self.find_within_radius(point, radius, limit = 100)

    sql = <<-SQL
      select id, lat, lng, (#{point.x} - x) * (#{point.x} - x) + (#{point.y} - y) * (#{point.y} - y) d 
      from users where #{(radius ** 2)} >= d 
      order by d limit #{limit}
    SQL
    
    users = User.find_by_sql(sql)
    users.each {|user| user.d = Math.sqrt(user.d.to_f).round}
    return users
  end

Upvotes: 7

2pha
2pha

Reputation: 11

I have been using this, forget where I got it though.

SELECT n, SQRT(POW((69.1 * (n.field_geofield_lat - :lat)) , 2 ) + POW((53 * (n.field_geofield_lon - :lon)), 2)) AS distance FROM field_revision_field_geofield n ORDER BY distance ASC

Upvotes: 1

Valerion
Valerion

Reputation: 831

I use the exact same method on a vehicle-tracking application and have done for years. It works perfectly well. A quick check of some old code shows that I multiply the result by 6378137 which if memory serves converts to meters, but I haven't touched it for a very long time.

I believe SQL 2008 has a new spatial datatype that I imagine allows these kinds of comparisons without knowing this formula, and also allows spatial indexes which might be interesting, but I've not looked into it.

Upvotes: 1

Adam Hopkinson
Adam Hopkinson

Reputation: 28795

Am i right in thinking this is the Haversine formula?

Upvotes: 2

Related Questions