Need help: Designing query on zip codes

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

SQL Server Development (2000)

Need help: Designing query on zip codes

Author

Topic

xzibited
Starting Member

5 Posts

Posted - 2008-12-04 : 16:18:05

I could use some assistance designing a query. I have two separate tables right now that I need to pull data off of. One of them is a listing of all US Zip Codes, and the other is a database of people with their associated zip code reflecting their location.

The end goal is to run the database of people against the zip code database, and have it return the five people that are closest to each zip code in the database. Both tables contain the latitude and longitude that cooresponds with the zip, and I'm using the great circle equation to do the actual mathematics.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-12-04 : 21:23:16

Is SQL Server 2008 an option? It has support for what you need built in.

xzibited
Starting Member

5 Posts

Posted - 2008-12-04 : 22:48:00

quote:
Originally posted by snSQL

Is SQL Server 2008 an option? It has support for what you need built in.

Unfortunately it is not.

xzibited
Starting Member

5 Posts

Posted - 2008-12-05 : 13:02:41

If this helps, here is the query now running it based off of one zip code only. I just need to be able to adapt it to recursively search through a table of zip codes.

Select top 5 l.techname, maxdrivingdistance as [Max Distance], DEGREES(ACOS(SIN(RADIANS(z.Latitude1)) * SIN( RADIANS(t.Lat))
                        + COS(RADIANS(z.Latitude1)) * COS(RADIANS(t.Lat)) 
                        * COS(RADIANS(convert(float, z.Longitude1) - convert(float, t.Long)))))
                        *60*1.1515 AS 'Distance' from ZipCodes z
Left Outer Join techziplatlong t
on t.Zip > 0
left outer join distechname l
on l.techname = t.techname
WHERE z.Zip = '97005'
order by distance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources