Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

 SQL Server Forums Profile | Active Topics | Members | Search | Forum FAQ Register Now and get your question answered!
 All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  Longitude & latitude based on distance Reply to Topic  Printer Friendly
Author  Topic

Rekha
Starting Member

14 Posts

 Posted - 06/21/2007 :  03:29:44 There's a table companysite which will have longitude and latitude info based on city.We will have a new web portal, I would like the business to do a query like this: Give me all of the companysites within a 25 mile radius of .We should be able to calculate which long/lat values would fall in this range. Please help

Ifor
Aged Yak Warrior

700 Posts

 Posted - 06/21/2007 :  06:05:40 The great circle distance should be accurate enough. Here is my function. You may want to slightly alter the radius of the sphere depending on where you live.```SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE FUNCTION dbo.GreatCircleDistance ( @Latitude1 float = NULL, @Longitude1 float = NULL, @Latitude2 float = NULL, @Longitude2 float = NULL ) RETURNS float AS BEGIN IF @Latitude1 IS NULL RETURN 0.0 IF @Longitude1 IS NULL RETURN 0.0 IF @Latitude2 IS NULL RETURN 0.0 IF @Longitude2 IS NULL RETURN 0.0 DECLARE @sin1 float ,@sin2 float ,@sind float ,@cos1 float ,@cos2 float ,@cosd float SELECT @sin1 = SIN(RADIANS(@Latitude1)) ,@sin2 = SIN(RADIANS(@Latitude2)) ,@sinD = SIN(RADIANS(@Longitude2 - @Longitude1)) ,@cos1 = COS(RADIANS(@Latitude1)) ,@cos2 = COS(RADIANS(@Latitude2)) ,@cosD = COS(RADIANS(@Longitude2 - @Longitude1)) RETURN ATN2 (SQRT(SQUARE(@cos2 * @sinD) + SQUARE(@cos1 * @sin2 - @sin1 * @cos2 * @cosD)) ,@sin1 * @sin2 + @cos1 * @cos2 * @cosD ) * 3959.871 END GO``` Edited by - Ifor on 06/21/2007 06:06:50

Rekha
Starting Member

14 Posts

 Posted - 06/21/2007 :  06:35:00 Hi,Thanx for the responsebut why 2 latitudes & longitudes.If the user selects city "California" for eg.It will have latitude & longitude stoder in DB which can be retreived now i need to know other cities which comes within 25 miles of California's lat & long.How do I do it?

Ifor
Aged Yak Warrior

700 Posts

 Posted - 06/21/2007 :  07:07:07 quote:but why 2 latitudes & longitudes?Distance is between two points!Something like the following should give you sites within 25 miles of any city in California.```SELECT * ,dbo.GreatCircleDistance(C.Latitude, C.Longitude, S.Latitude, S.Longitude) AS Miles FROM City C JOIN CompanySite S ON dbo.GreatCircleDistance(C.Latitude, C.Longitude, S.Latitude, S.Longitude) <= 25.0 WHERE C.State = 'CA'``` Edited by - Ifor on 06/21/2007 07:09:58

Kristen
Test

United Kingdom
22859 Posts

 Posted - 06/21/2007 :  08:24:48 I recommend that you don't use the Great Circle algorithm as part of your FROM/JOIN, its too slow for any reasonably sized database, and precludes use of indexes.First imagine a SQUARE that has a centre on your Target City, and has sides 2 x 25 miles.Calculate the Lat / Long for the corners.You now need to find all companysites within that square. It is best to have the Lat / Long columns of the table indexed to make this fast:``` SELEFT companysite, Lat, Long FROM MyTable WHERE Lat >= @MinLat AND Lat <= @MaxLat AND Long >= @MinLong AND Long <= @MaxLong ```If a "square" is good enough you are done. If you need a CIRCLE based on the centre of your Target City then further-process THESE RESULTS ONLY through the Great Circle algorithm. But frankly for a granularity of 1 mile Pythagoras is just fine, and faster than Great Circle as there are no Trigonometric functions involved.See also http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Distance+of+Zip,PostcodesKristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

 Posted - 06/21/2007 :  09:04:22 This link has a function for the Great Circle Distance using the Haversine Formula, which has less trouble with shorter distances. It returns kilometers, but you can just change the radius to a value for miles to get results in miles.The thread also has considerable discussion of the other issues, including squaring the circle as discussed below. The problem of finding the limits of the square around the circle are not as simple as it seems, so you have to take care to get limits that actually include the entire search circleGreat Circle Distance Function - Haversine Formulahttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81360CODO ERGO SUM

Kristen
Test

United Kingdom
22859 Posts

 Posted - 06/21/2007 :  11:20:42 "The problem of finding the limits of the square around the circle are not as simple as it seems"Didn't know that ...

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

 Posted - 06/21/2007 :  13:49:24 quote:Originally posted by Kristen"The problem of finding the limits of the square around the circle are not as simple as it seems"Didn't know that ...The basic issue is this:Suppose you want to search inside a 50 mile diameter circle. You want to find a square that has borders 50 miles north/south/east/west of your position. If you measure the distance directly along the Latitude line for 50 miles, using the assumption that the Earth is a flat plane, the Great Circle Distance to that point will actually be slightly less than 50 miles unless you are on the equator, because the shortest distance to that point is not along the Latitude line. So you have to find points that are actually 50 mile east and west of your position using the Great Circle Distance.The larger the search circle, the greater the difference between the Great Circle Distance and the distance along the Latitude line, so the problem becomes even larger with larger search circles.CODO ERGO SUM

dmbware
Starting Member

4 Posts

 Posted - 12/01/2008 :  23:33:55 I don't know if this will work but If I have a DB of addresses and coordinates. Is there a way for me to compare one address (coordinate) against a DB and return all coordinates that fall within one square mile?
Topic
 Reply to Topic  Printer Friendly Jump To: Select Forum General SQL Server Forums       New to SQL Server Programming       New to SQL Server Administration       Script Library       Data Corruption Issues       Database Design and Application Architecture SQL Server 2012 Forums       Transact-SQL (2012)       SQL Server Administration (2012)       SSIS and Import/Export (2012)       Analysis Server and Reporting Services (2012)       Replication (2012)       Availability Groups and DR (2012)       Other SQL Server 2012 Topics SQL Server 2008 Forums       Transact-SQL (2008)       SQL Server Administration (2008)       SSIS and Import/Export (2008)       High Availability (2008)       Replication (2008)       Analysis Server and Reporting Services (2008)       Other SQL Server 2008 Topics SQL Server 2005 Forums       Transact-SQL (2005)       SQL Server Administration (2005)       .NET Inside SQL Server (2005)       SSIS and Import/Export (2005)       Service Broker (2005)       Replication (2005)       High Availability (2005)       Analysis Server and Reporting Services (2005)       Express Edition and Compact Edition (2005)       Other SQL Server Topics (2005) SQL Server 2000 Forums       SQL Server Development (2000)       SQL Server Administration (2000)       Import/Export (DTS) and Replication (2000)       Transact-SQL (2000)       Analysis Services (2000)       MSDE (2000) Development Tools       ASP.NET       Reporting Services Development       Other Development Tools Site Related Forums       Site Related Discussions       Article Discussion       Poll Discussion       The Yak Corral Other Forums       SQL Server 6.5 \ SQL Server 7.0       Other Topics       MS Access       ClearTrace Support Forum Old Forums       CLOSED - General SQL Server       CLOSED - SQL Server 2005/Yukon  -------------------- Home Active Topics Frequently Asked Questions Member Information Search Page
 SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC