SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calculate Distance in Miles from Latitude and Longitude


Calculate Distance in Miles from Latitude and Longitude

Author
Message
Aristides (Ari) Smith
Aristides (Ari) Smith
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 19
Barry,
How does this formula compare in acurracy to the road mileage programs like ALK-PC Miler and Rand McNally IntelleRoute Software? How does your formula apply road infastructure to the calculation?
Thanks,
Ari Smith
Caruncles
Caruncles
Say Hey Kid
Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)

Group: General Forum Members
Points: 687 Visits: 244
Actually I meant both, but realized right after I posted that the example (test) was commented right there in the code. Sorry for being such a rookie, but your answer was also helpful. I'm guessing the Google Maps and TerraServers of the world would use code like this.

Wallace Houston
Sunnyland Farms, Inc.

"We must endeavor to persevere."
jcrawf02
jcrawf02
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5080 Visits: 19324
For Ari and Caruncles both, this is point to point data, "as the crow flies", so road miles are not considered at all. That might mean you're only 2 miles away with this calculation, but there's a mountain, so it's a forty-mile drive to get there. Solution? Don't live near mountains.

---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
mbarraclough
mbarraclough
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 9
Is there some reason you didn't simply use the sql radians() function instead of doing the division with your constant?
FargoUT
FargoUT
SSC-Addicted
SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)

Group: General Forum Members
Points: 401 Visits: 312
For US developers, this would be helpful in combination with a dataset of zip codes and their lat/long coordinates (this is one I've found useful: http://federalgovernmentzipcodes.us/).

Additionally, you could adjust the function a bit and compare the distances between zip codes. Microsoft even provides a stored procedure for this functionality: http://msdn.microsoft.com/en-us/library/ms980211.aspx.

I use a modified version of this whenever I want to find the zip codes within N number of miles of a zip code. When we open a new hospital, we will often use this to determine what zip codes will be best served by the hospital.
jcrawf02
jcrawf02
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5080 Visits: 19324
FargoUT, you're making me realize that perhaps what Caruncles is looking for requires a little more info.

If you don't have geocoding software to turn addresses into lat/long, then it might be a little stickier, but you can get that data online too, http://stevemorse.org/jcal/latlonbatch.html being a good example of that.

Then use Barry's function (or use it inline if you want) to get the distance between two addresses.

**Edit (oops, Caruncles, not Carbuncles lol)

---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
FargoUT
FargoUT
SSC-Addicted
SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)

Group: General Forum Members
Points: 401 Visits: 312
@jcrawf02: I'm not sure that's what Caruncle wants, but I thought I would just post a tangential use for such a function. I use it for determining location of doctors in the vicinity of a hospital, which helps for reporting purposes. :-)
Caruncles
Caruncles
Say Hey Kid
Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)

Group: General Forum Members
Points: 687 Visits: 244
Yea, I can use that. Except I'll probably convert it to feet. Thanx!

Wallace Houston
Sunnyland Farms, Inc.

"We must endeavor to persevere."
scott.bernstein
scott.bernstein
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 47
I believe you are computing the length of arc on the surface of a sphere - which is cool... But the earth is not a perfect sphere - it's an "oblate spheroid". (it bulges around the equator and is not perfectly symetric) There is an algorithm used in the aerospace industry to compute the distance between two points on the earths surface. Do a google search on "Sedonos Equations". It gets very complicated... Some aerospace companies have patented their implementations of it.

SMB
jcrawf02
jcrawf02
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5080 Visits: 19324
scott.bernstein (4/15/2011)
I believe you are computing the length of arc on the surface of a sphere - which is cool... But the earth is not a perfect sphere - it's an "oblate spheroid". (it bulges around the equator and is not perfectly symetric) There is an algorithm used in the aerospace industry to compute the distance between two points on the earths surface. Do a google search on "Sedonos Equations". It gets very complicated... Some aerospace companies have patented their implementations of it.

SMB
Read back a page or two, think that topic got beat to death already. :-)

---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search