Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Calculate Distance in Miles from Latitude and Longitude Expand / Collapse
Author
Message
Posted Friday, April 15, 2011 7:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 7, 2014 11:06 AM
Points: 1, Visits: 18
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
Post #1094134
Posted Friday, April 15, 2011 7:34 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 5, 2014 12:21 PM
Points: 78, Visits: 193
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."
Post #1094139
Posted Friday, April 15, 2011 8:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 7:42 PM
Points: 2,677, Visits: 19,269
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."
Post #1094170
Posted Friday, April 15, 2011 11:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 21, 2011 11:12 AM
Points: 1, Visits: 9
Is there some reason you didn't simply use the sql radians() function instead of doing the division with your constant?
Post #1094298
Posted Friday, April 15, 2011 11:16 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 11, 2011 1:51 PM
Points: 341, Visits: 311
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.
Post #1094307
Posted Friday, April 15, 2011 11:30 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 7:42 PM
Points: 2,677, Visits: 19,269
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."
Post #1094311
Posted Friday, April 15, 2011 12:05 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 11, 2011 1:51 PM
Points: 341, Visits: 311
@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.
Post #1094326
Posted Friday, April 15, 2011 12:13 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 5, 2014 12:21 PM
Points: 78, Visits: 193
Yea, I can use that. Except I'll probably convert it to feet. Thanx!







Wallace Houston
Sunnyland Farms, Inc.

"We must endeavor to persevere."
Post #1094330
Posted Friday, April 15, 2011 12:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 10, 2011 8:16 AM
Points: 29, 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
Post #1094348
Posted Friday, April 15, 2011 1:43 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 7:42 PM
Points: 2,677, Visits: 19,269
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."
Post #1094372
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse