Calculating Distance in Miles from Latitude and Longitude

January 18, 2007

The amount of data out there via API's is increadible these days. For instance you can take an address, and get the latitude and longitude using Google's GeoCoding API.

I am using this API along with some others to build a pretty some interesting stuff (more on that when its public).

Today I needed to calculate the distance between two points, I found a bunch of formulas here to convert two lats and longs into miles. They had some more complicated formulas, but I went with an easier one because approximate accuracy was sufficent. Here's how the formula translated into SQL (tested on MySQL):

SELECT id, place_name,
ROUND( SQRT( POW((69.1 * (#Val(arguments.latitude)# - latitude)), 2) + POW((53 * (#Val(arguments.longitude)# - longitude)), 2)), 1) AS distance
FROM places
ORDER BY distance ASC

I am also rounding it to one decimal place, which you can remove if you want.

Speaking of mileage, if you are looking for a Web Application to track business mileage checkout mileagepad.com

Permalink | Add Comment |

add to del.icio.us | Tags: maps, latitude, longitude, geo, gps, geocoding, sql, mysql, google

Related Entries

Google's JavaScript API Loader - November 26, 2008
Cheat Sheet Roundup - Over 30 Cheatsheets for developers - September 1, 2005
Top 10 Reserved SQL Keywords - October 28, 2008
INFORMATION_SCHEMA Support in MySQL, PostgreSQL - February 18, 2008
Backwards LIKE Statements - January 10, 2007

16 people found this page useful, what do you think?

Trackbacks

Trackback Address: 622/A217F665272CA5CE2F671563168BFC40

Comments

On 01/20/2007 at 4:59:28 PM EST Kae Verens wrote:

does that not make the assumption that each longitude line is always 69.1 miles apart?

if you remember that the lines are drawn on a globe, you'll realise 69.1 is only correct at two exact latitudes, north and south of the equator.

On 01/20/2007 at 8:06:22 PM EST anon wrote:

dude... "increadible"? Come on, man, at least do a spell check.

On 01/24/2007 at 10:09:25 AM EST David Stockton wrote:

A more accurate yet still reasonably simple implementation would be the haversine method. This takes into account the average curvature of the Earth's surface. I recently combined this into a CF app alone with converting GRE/GRN to Lat/Long so I could do a distance calculation between a GRE/GRN co-ord and a Lat/Long co-ord.

(Greets to bazmod!)

On 01/26/2007 at 8:17:05 AM EST Adam Howitt wrote:

Hey Pete, I built WalkJogRun.net with ColdFusion and MySql and have tons of Geo-related stuff if you need anything. The JavaScript does a lot of the Google Maps API stuff but there is some CF and MySql code I can share.

On 03/30/2007 at 10:19:06 PM EDT John Caron wrote:

looks like you're missing the cos term:

dy = radius * (lat1 - lat0); dx = radius * cos(lat1) * (lon1 - lon0); distance = sqrt(dy*dy + dx*dx)

radius = earth radius lat,lon in radians

On 06/14/2007 at 6:14:12 PM EDT ryan wrote:

You can receive up-to-date zipcodes, along with latitude and longitude, county, and country information from www.meridianworlddata.com. You can also download complete geographical databases for cities, land features, water features, and man made features around the world. You can then use this data within your calculations to make sure you have the most up-to-date data.

On 08/11/2007 at 10:01:17 AM EDT Tom Nunamaker wrote:

Of course you don't want to run a calc like this for all data points in your database. You can setup a box around your point using pretty simple math and apply the floating point calcs to all points within the box. 1 degree of arc at the earth's surface = 60nautical miles. I've been meaning to write an article about this but just haven't had the time.

On 01/28/2008 at 10:03:54 AM EST Bill Blaettler wrote:

I did just as Tom suggested recently with a mySQL stored proceedure to expand a box out from the point, several iterations, until I have the desired number of locations in a temp table or until i've reached my maximum box size for the data to still be meaningful, then I calced the distance using the law of haversines on only the subset of data points. It runs extremely efficiently as compared to just calcing the law of haversines in a WHERE clause against all data points.

On 01/25/2011 at 6:11:35 AM EST Bimal Poudel wrote:

My version:

# Your lat/long of preferences SET @arguments_latitude = 51.355887; SET @arguments_longitude = -2.949691;

# Make a distance query SELECT p.postcode_id, p.postcode, ROUND( SQRT( POW((69.1 * (@arguments_latitude - p.latitude)), 2) + POW((53 * (@arguments_longitude - p.longitude)), 2) ) , 1) AS distance FROM postcodes p ORDER BY distance DESC;

On 02/28/2011 at 7:16:53 AM EST Anonymous wrote:

Calculating Distance in Miles from Latitude and Longitude

Related Entries

Trackbacks

Comments

Post a Comment

Recent Entries