SQL query to calculate coordinate proximity

Question

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. JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

Answer 1

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 => , :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