SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Longitude & latitude based on distance
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rekha
Starting Member

14 Posts

Posted - 06/21/2007 :  03:29:44  Show Profile  Reply with Quote
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

583 Posts

Posted - 06/21/2007 :  06:05:40  Show Profile  Reply with Quote
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
Go to Top of Page

Rekha
Starting Member

14 Posts

Posted - 06/21/2007 :  06:35:00  Show Profile  Reply with Quote
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?
Go to Top of Page

Ifor
Aged Yak Warrior

583 Posts

Posted - 06/21/2007 :  07:07:07  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 06/21/2007 :  08:24:48  Show Profile  Reply with Quote
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,Postcodes

Kristen
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 06/21/2007 :  09:04:22  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 06/21/2007 :  11:20:42  Show Profile  Reply with Quote
"The problem of finding the limits of the square around the circle are not as simple as it seems"

Didn't know that ...
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 06/21/2007 :  13:49:24  Show Profile  Reply with Quote
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
Go to Top of Page

dmbware
Starting Member

4 Posts

Posted - 12/01/2008 :  23:33:55  Show Profile  Reply with Quote
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?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000