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 <some city>.
We should be able to calculate which long/lat values would fall in this range.
Please help


Ifor
Aged Yak Warrior
697 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 response but 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
697 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
22688 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 furtherprocess 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,Postcodes
Kristen 


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 circle
Great Circle Distance Function  Haversine Formula http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81360
CODO ERGO SUM 


Kristen
Test
United Kingdom
22688 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 
