Haversine SQL trouble - Distance between zip codes

[csumner]

I am trying to use the haversine function to find the distance between
two points on a sphere, specifically two zip codes in my database. I'm
neither horribly familiar with SQL syntax nor math equations :), so I
was hoping I could get some help. Below is what I'm using and it is,
as best as I can figure, the correct formula. It is not however,
giving me correct results. Some are close, others don't seem right at
all. Any ideas?


SET @lat1 = RADIANS(@lat1)
SET @log1 = RADIANS(@log1)
SET @lat2 = RADIANS(@lat2)
SET @log2 = RADIANS(@log2)
SET @Dlat = ABS(@lat2 - @lat1)
SET @Dlog = ABS(@log2 - @log1)
SET @R = 3956 /*Approximate radius of earth in miles*/
SET @A = SQUARE(SIN(@Dla t/2)) + COS(@lat1) * COS(@lat2) *
SQUARE(SIN(@Dlo g/2))
SET @C = 2 * ATN2(SQRT(@A), SQRT(1 - @A))
/*SET @C = 2 * ASIN(min(SQRT(@ A))) Alternative calculation*/

SET @distance = @R * @C


thnx,
cjrsumner

[Simon Hayes]

Re: Haversine SQL trouble - Distance between zip codes

christian@admin console.com (csumner) wrote in message news:<32458ea2. 0402261803.1e72 c1ef@posting.go ogle.com>...[color=blue]
> I am trying to use the haversine function to find the distance between
> two points on a sphere, specifically two zip codes in my database. I'm
> neither horribly familiar with SQL syntax nor math equations :), so I
> was hoping I could get some help. Below is what I'm using and it is,
> as best as I can figure, the correct formula. It is not however,
> giving me correct results. Some are close, others don't seem right at
> all. Any ideas?
>
>
> SET @lat1 = RADIANS(@lat1)
> SET @log1 = RADIANS(@log1)
> SET @lat2 = RADIANS(@lat2)
> SET @log2 = RADIANS(@log2)
> SET @Dlat = ABS(@lat2 - @lat1)
> SET @Dlog = ABS(@log2 - @log1)
> SET @R = 3956 /*Approximate radius of earth in miles*/
> SET @A = SQUARE(SIN(@Dla t/2)) + COS(@lat1) * COS(@lat2) *
> SQUARE(SIN(@Dlo g/2))
> SET @C = 2 * ATN2(SQRT(@A), SQRT(1 - @A))
> /*SET @C = 2 * ASIN(min(SQRT(@ A))) Alternative calculation*/
>
> SET @distance = @R * @C
>
>
> thnx,
> cjrsumner[/color]

It would help if you could post your DECLARE statments (different data
types can affect calculations in various ways), as well as some sample
data for cases which give the results you want and for cases which
don't.

Simon

[--CELKO--]

Re: Haversine SQL trouble - Distance between zip codes

>> I am trying to use the haversine function to find the distance
between two points on a sphere, specifically two zip codes in my
database. <<

Do not re-invent (and have to maintain!!!) the wheel. KJL Software
(www.kjlsoftware.com) gives you 5-digit ZIP Code, City, State,USPS
Status Code, LATEST Area Code(s) from NANPA, Time Zone,Latitude, and
Longitude for all valid US Postal Service 5 digit ZIP Codes/City/State
combinations and a Distance Calculator.

[--CELKO--]

Re: Haversine SQL trouble - Distance between zip codes

Just for the record and in SQL/PSM:

CREATE FUNCTION Distance
(IN latitude1 REAL, IN longitude1 REAL,
IN latitude2 REAL, IN longitude2 REAL)
RETURNS REAL
AS
BEGIN
DECLARE r REAL;
DECLARE lat REAL;
DECLARE lon REAL;
DECLARE a REAL;
DECLARE c REAL;
SET r = 6367.00 * 0.6214;

-- calculate the Deltas...
SET lon = longitude2 - longitude1;
SET lat = latitude2 - latitude1;

--Intermediate values...
SET a = SIN(lat / 2) + COS(latitude1)
* COS(latitude2) * SIN(lon / 2)

--Intermediate result c is the great circle distance in radians...
SET c = 2 * ARCSIN(LEAST(1. 00, SQRT(a)))

--Multiply the radians by the radius to get the distance
RETURN (r * c)
END;

LEAST() function protects against possible roundoff errors that could
sabotage computation of the ARCSIN() if the two points are very nearly
antipodal. It exists as a vendor extension in Oracle, but can be
written with a CASE expression in Standard SQL.

[Christian Sumner]

Re: Haversine SQL trouble - Distance between zip codes

Okay, below is the whole function. Here are some values and their
results compared to zipfind.net. Note the strange behavior for 32610 and
32611, and others had this same value too.

Search for 10 mile radius of 32601 and get distance between zip codes
(This post is only concerned with the distance function part)

my results (sample):
zip code miles latitude longitude
32601 0.0 29.68040999998 -82.345738999999 995
32602 4.6333652 29.629887 -82.396567000000 005
32604 8.0310783 29.573293 -82.397903999999 997
32610 0.49070904 29.68131199998 -82.353862000000 007
32611 0.49070904 29.68131199998 -82.353862000000 007
...

zipfind.com results:
zip code miles
32601 0.0
32602 5.5
32604 1.5
32610 1.3
32611 0.2
...

CREATE FUNCTION dbo.GetDistance (
@lat1 Float(8),
@log1 Float(8),
@lat2 Float(8),
@log2 Float(8)
)
RETURNS Float(8)
AS
BEGIN

DECLARE @distance Float(8)
DECLARE @R int
DECLARE @Dlog Float(8)
DECLARE @Dlat Float(8)
DECLARE @A Float(8)
DECLARE @C FLoat(8)

SET @lat1 = RADIANS(@lat1)
SET @lat2 = RADIANS(@lat2)
SET @log1 = RADIANS(@log1)
SET @log2 = RADIANS(@log2)
SET @Dlat = ABS(@lat2 - @lat1)
SET @Dlog = ABS(@log2 - @log1)
SET @R = 3956 /*Approximate radius of earth in miles*/
SET @A = SQUARE(SIN(@Dla t/2)) + COS(@lat1) * COS(@lat2) *
SQUARE(SIN(@Dlo g/2))
SET @C = 2 * ATN2(SQRT(@A), SQRT(1 - @A))
/*SET @C = 2 * ASIN(min(SQRT(@ A))) Alternative calculation*/

SET @distance = @R * @C


RETURN @distance
END
GO

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Christian Sumner]

Re: Haversine SQL trouble - Distance between zip codes

I dont' understand this: How can these three zip code finding website
have such drastically different results???:
Are their databases so different? This first one doesn't quite match up
with my lat/log values.

https://www.kjlsoftware.com

32601 GAINESVILLE FL AD 352 -5 29.653195 -82.3244 0
32602 GAINESVILLE FL AD 352 -5 29.665245 -82.336097 1.0884
32603 GAINESVILLE FL AD 352 -5 29.653145 -82.346901 1.3501
32604 GAINESVILLE FL AD 352 -5 29.665245 -82.336097 1.0884
32605 GAINESVILLE FL AD 352 -5 29.676006 -82.368897 3.0994
32606 GAINESVILLE FL AD 352 -5 29.681426 -82.415022 5.7754
32607 GAINESVILLE FL AD 352 -5 29.646189 -82.396588 4.3583
32608 GAINESVILLE FL AD 352 -5 29.611545 -82.394108 5.0763

http://zipfind.net

1 32601 Gainesville FL 17,760 0.0 Alachua 352 Eastern
2 32602 Gainesville FL 0 5.5 Alachua 352 Eastern
3 32603 Gainesville FL 10,034 1.4 Alachua 352 Eastern
4 32604 Gainesville FL 0 1.5 Alachua 352 Eastern
5 32605 Gainesville FL 21,539 3.3 Alachua 352 Eastern
6 32606 Gainesville FL 19,662 6.3 Alachua 352 Eastern
7 32607 Gainesville FL 26,666 4.8 Alachua 352 Eastern
8 32608 Gainesville FL 39,781 4.9 Alachua 352 Eastern


and http://www.cryptnet.net/fsp/zipdy/ gives different results too.

These results seem to be off too far to be accounted for with simple
rounding errors and the like. Anyone know what's going on here?


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Christian Sumner]

Re: Haversine SQL trouble - Distance between zip codes


thnx, ill try this out

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Joe Celko]

Re: Haversine SQL trouble - Distance between zip codes

>> These results seem to be off too far to be accounted for with simple
rounding errors and the like. Anyone know what's going on here? <<

Nope, but perhaps one uses the location of the post office that serves
the zipcode and the other uses a map with the centroid of the territory?

Another thought is that if we want the for mailing purposes, you can get
a table of zones for each zip code.

--CELKO--
=============== ============
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!