<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by RBarryYoung
»
Calculate Distance in Miles from Latitude and...
48 posts, Page 5 of 5
««
«
1
2
3
4
5
Calculate Distance in Miles from Latitude and Longitude
Rate Topic
Display Mode
Topic Options
Author
Message
scott.bernstein
scott.bernstein
Posted Friday, April 15, 2011 1:58 PM
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, May 10, 2011 8:16 AM
Points: 29,
Visits: 47
I saw that there was talk about the earth not being a perfect sphere. I didn't see anything mentioned about Sedonos though...
Post #1094383
FargoUT
FargoUT
Posted Friday, April 15, 2011 2:01 PM
Old Hand
Group: General Forum Members
Last Login: Tuesday, October 11, 2011 1:51 PM
Points: 341,
Visits: 311
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.
Post #1094384
alastair-804470
alastair-804470
Posted Friday, April 15, 2011 2:11 PM
Grasshopper
Group: General Forum Members
Last Login: Tuesday, May 01, 2012 10:31 AM
Points: 17,
Visits: 105
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.
Post #1094390
jcrawf02
jcrawf02
Posted Friday, April 15, 2011 2:18 PM
SSCrazy
Group: General Forum Members
Last Login: Today @ 9:29 AM
Points: 2,551,
Visits: 18,884
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
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 #1094393
alastair-804470
alastair-804470
Posted Friday, April 15, 2011 2:42 PM
Grasshopper
Group: General Forum Members
Last Login: Tuesday, May 01, 2012 10:31 AM
Points: 17,
Visits: 105
Well, yes... but in that case you've probably got other problems. Like no support ;)
Post #1094407
FargoUT
FargoUT
Posted Friday, April 15, 2011 3:16 PM
Old Hand
Group: General Forum Members
Last Login: Tuesday, October 11, 2011 1:51 PM
Points: 341,
Visits: 311
alastair-804470 (4/15/2011)
Well, yes... but in that case you've probably got other problems. Like no support ;)
And no budget...
Post #1094424
alastair-804470
alastair-804470
Posted Friday, April 15, 2011 3:36 PM
Grasshopper
Group: General Forum Members
Last Login: Tuesday, May 01, 2012 10:31 AM
Points: 17,
Visits: 105
You work in the healthcare industry and you've got no budget? You surprise me ;)
Post #1094434
Nadrek
Nadrek
Posted Thursday, April 21, 2011 9:39 AM
Say Hey Kid
Group: General Forum Members
Last Login: Monday, May 20, 2013 12:28 PM
Points: 675,
Visits: 2,031
Be careful; on at least SQL Server 2005 SP3 with a very similar formula, I've seen a very interesting issue where the internal SQL Server rounding results in the ACOS receiving a value very slightly higher than 1, and exploding (with an error or a NULL output of ACOS, depending on ANSI_WARNINGS and ARITHABORT).
Tracking this down was interesting, as the difference is extremely subtle; SQL Server would not show the difference between a working center point and a failing center point in DECIMAL or FLOAT data types. Only a conversion to a (VAR)BINARY type actually showed that the two values were, in fact, different.
To make a long story short, we were operating in a batch environment on a limited set of tens of thousands of points, so the simplest (if not very elegant) answer was at the end of each batch update, set ANSI_WARNINGS and ARITHABORT off, run each point through a trial with the precise math we were using as the center point of a radius, and any points that return a NULL from the ACOS math get moved by 1/100000th of a degree latitude (about 11 cm, per
http://www.csgnetwork.com/degreelenllavcalc.html
)and retried; there's often a handful that need to be moved by 2/100000th or 3/100000ths of a degree latitude as they turn up NULL on the retest. In all but surveying and specialist applications, even moving a point by a few meters isn't going to be significant compared to the error already present in the calculation (think: elevation + inaccuracies in measuring/recording).
Essentially: whatever lat/long math you're trying to do in SQL, please do test every single point you're going to use as a center to see if it's going to return NULL results for any of them. Postal code tables usually have less than a few million entries in them (for the US, usually less than 50,000 5 digit zip codes are listed), so a full test only takes a few seconds.
Post #1097083
« Prev Topic
|
Next Topic »
48 posts, Page 5 of 5
««
«
1
2
3
4
5
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
