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

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Zip Code Function in SQL Server

Author  Topic 

westmich
Starting Member

35 Posts

Posted - 2004-03-26 : 08:19:04
This post relates to one I've posted on another board on zip code proximity - http://www.sitepoint.com/forums/showthread.php?t=157759

I was able to get this working correctly so that a user can plug in a zip code and a radius and see all the zip codes that fall in the radius, which in turn can be feed into another query. But what I would love to have is a custom function that when I run the query I also get the distance between the zip code I am looking up and the ones it returns.


Select Zipcode, ZipDistance('49415') as Distance
From Zipcodes
Where Latitude = 'somenumber' And Longitude = 'somenumber'
Order By Distance


The Where clause I know ho to do - it's the function I've been beating my head against the wall on.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2004-03-26 : 08:45:22
Books online is your friend.

CREATE FUNCTION fn_CustomerNamesInRegion
( @RegionParameter nvarchar(30) )
RETURNS table
AS
RETURN (
SELECT CustomerID, CompanyName
FROM Northwind.dbo.Customers
WHERE Region = @RegionParameter
)
GO
-- Example of calling the function for a specific region
SELECT *
FROM fn_CustomerNamesInRegion(N'WA')
GO

Inline user
Go to Top of Page

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-26 : 09:54:22
Have a look at the following link and see if it is useful for your situation.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30843&SearchTerms=zipcode
Go to Top of Page

westmich
Starting Member

35 Posts

Posted - 2004-03-26 : 14:05:31
Thanks, Valter, I should have been more clear, though. I know how to create a function in general. I don't know how to do the trigonometry to determine the distance between two zip codes based on longitude and latitude.

The link, at first glance, looks it should contain my answer but I am not putting two and two together. I have the calculation to figure out other zip codes based on one zip code and a radius, what I don't have is the function to return distance based on a single zip.

Here is what I do have:


'returns zip codes within proximity range
Public Shared Function GetNearByZips(zipcode as String,zipprox as Integer) as String
Dim sSQL1,sSQL2 as String
Dim iStartLong,iStartLat,iLongVary,iLatVary as Decimal
Dim dblMiles as Double
'look up lat and long on passed zip
sSQL1 = "Select * From ZipSource Where ZipCode = '" & zipcode & "';"
Dim oConn as New SQLConnection(Client.sAltConnStr)
Try
oConn.Open()
Dim oComm as New SQLCommand(sSQL1, oConn)
Dim dr as SQLDataReader = oComm.ExecuteReader(CommandBehavior.CloseConnection)
If dr.Read() Then
iStartLat = dr("Latitude")
iStartLong = dr("Longitude")
Else
'return error if no zip code was found
Return "Sorry, no matching zip code was found."
End If
oConn.Close()
dblMiles = Convert.ToDouble(zipprox)
'determine longitude/latitude variance
iLatVary = dblMiles/((6076/5280) * 60)
iLongVary = dblMiles/(((Math.Cos(Convert.ToDouble(iStartLat * Math.PI/180)) * 6076)/5280) * 60)
'query zip codes that fall with variance
sSQL2 += "Select ZipCode From ZipSource "
sSQL2 += "Where Longitude Between " & (iStartLong - iLongVary) & " And " & (iStartLong + iLongVary) & " "
sSQL2 += "And Latitude Between " & (iStartLat - iLatVary) & " And " & (iStartLat + iLatVary) & ";"
Dim oConn2 as New SQLConnection(Client.sAltConnStr)
oConn2.Open()
Dim oComm2 as New SQLCommand(sSQL2, oConn2)
Dim dr2 as SQLDataReader = oComm2.ExecuteReader(CommandBehavior.CloseConnection)
'concat results as single string
Dim sZipcodeList as String
Do While dr2.Read()
sZipcodeList += "'" & dr2("ZipCode") & "',"
Loop
'remove last comma
Return Util.CutLeft(sZipcodeList,sZipcodeList.LastIndexOf(","))
Catch ex as Exception
Return ex.Message
End Try
End Function
Go to Top of Page

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-26 : 14:27:06
Hi there,

Check this out:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33357

It may seem a bit confusing but similar trig to what you require is being used here.


Duane.
Go to Top of Page

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-26 : 14:32:25
In fact, this one might be more usefull:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33465



Duane.
Go to Top of Page

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-26 : 14:38:41
[code]CREATE PROC up_FindZipCodesWithinRadius

@ZipCode char(5) ,
@GivenMileRadius int
AS
SET NOCOUNT ON

DECLARE @lat1 float,
@long1 float

SELECT @lat1= latitude,
@long1 = longitude
FROM ZipSource
WHERE zipcode = @ZipCode

SELECT ZipCode ,DistanceInMiles
FROM
(
SELECT ZipCode,3958.75 * ( Atan(Sqrt(1 - power(((Sin(@Lat1/57.2958) * Sin(latitude/57.2958)) +
(Cos(@Lat1/57.2958) * Cos(latitude/57.2958) * Cos((longitude/57.2958) - (@Long1/57.2958)))), 2)) /
((Sin(@Lat1/57.2958) * Sin(latitude/57.2958)) + (Cos(@Lat1/57.2958) * Cos(latitude/57.2958) *
Cos((longitude/57.2958) - (@Long1/57.2958)))))) DistanceInMiles
FROM ZipSource
) a
WHERE a.DistanceInMiles <= @GivenMileRadius
--AND ZipCode <> @ZipCode
ORDER BY DistanceInMiles

GO

EXEC up_FindZipCodesWithinRadius '35085',20
GO

DROP PROC up_FindZipCodesWithinRadius[/code]
Go to Top of Page

westmich
Starting Member

35 Posts

Posted - 2004-03-26 : 16:23:21
Thanks a lot!

Ehorn, so your stored procedure would basically take the place of needing a query and a function, i.e. you combined them, correct?

Quick question - should the last line of the inner select read '(@Long1/57.2958)))))) AS DistanceInMiles'?
Go to Top of Page

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-26 : 17:16:12
quote:
Quick question - should the last line of the inner select read '(@Long1/57.2958)))))) AS DistanceInMiles'


The AS Clause is implied. I am just lazy...
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-30 : 11:27:04
Ahhhh...the great circle...

Did this in Access once...had to build all the trig functions from sin and cosine, because access didn't have them...

http://gc.kls2.com/



Brett

8-)
Go to Top of Page

codezilla94
Starting Member

6 Posts

Posted - 2007-11-12 : 20:04:49
(spam deleted -graz)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 21:46:21
It cost $20 and you can get it for free here!
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81360



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
   

- Advertisement -