Calculate Distance in Miles from Latitude and Longitude

Question

Calculate Distance in Miles from Latitude and Longitude

Viewing 15 posts - 31 through 45 (of 58 total)

You must be logged in to reply to this topic. Login to reply

Aristides (Ari) Smith SSC Rookie Points: 38 More actions · Answer 1

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 Hall of Fame Points: 3299 More actions · Answer 2

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 SSC-Insane Points: 24198 More actions · Answer 3

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[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"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 SSC Rookie Points: 29 More actions · Answer 4

Is there some reason you didn't simply use the sql radians() function instead of doing the division with your constant?

FargoUT SSC Eights! Points: 853 More actions · Answer 5

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 SSC-Insane Points: 24198 More actions · Answer 6

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[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"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 SSC Eights! Points: 853 More actions · Answer 7

@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 Hall of Fame Points: 3299 More actions · Answer 8

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 SSC Journeyman Points: 87 More actions · Answer 9

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 SSC-Insane Points: 24198 More actions · Answer 10

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[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"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."

scott.bernstein SSC Journeyman Points: 87 More actions · Answer 11

I saw that there was talk about the earth not being a perfect sphere. I didn't see anything mentioned about Sedonos though... 🙂

FargoUT SSC Eights! Points: 853 More actions · Answer 12

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

That will only apply to those who need exact coordinates. I work in the health care industry, and the minute measurements of the earth's circumference is not going to matter quite so much. This function works for those who need inexact-but-close enough coordinates.

alastair-804470 Old Hand Points: 331 More actions · Answer 13

You don't need Sedonos equations. Just use SQL Server's own STDistance() method to accurately calculate the distance on the ellipsoid model of the spatial reference system in which your geography data is defined.

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 14

alastair-804470 (4/15/2011)
You don't need Sedonos equations. Just use SQL Server's own STDistance() method to accurately calculate the distance on the ellipsoid model of the spatial reference system in which your geography data is defined.

Unless you're on something before 2008 😉

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

"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."

alastair-804470 Old Hand Points: 331 More actions · Answer 15

Well, yes... but in that case you've probably got other problems. Like no support 😉