to begin with.
UPDATE ZipCodeTable as m
SET m.CityID = (SELECT CityID
FROM (SELECT TOP 1 CityID,
DistanceAssistant(m.latitude,m.longitude,c.Latitude,c.Longitude) as Distance
FROM CitiesTable as c
ORDER BY Distance))
I've inserted a Function (DinstanceAssistant) in SQL Server which calculates
the distance between two points (m, c).
Should this work? It's not for me...
Ben
_____
From: [EMAIL PROTECTED] [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: October 1, 2003 4:36 PM
To: CF-Talk
Subject: Re:SQL - Zip Code/Nearest City
OK, here's what I'm seeing; please correct me -
-- The Zip table does not have the corners (need four corners) of counties
or whatever specified, it has ONE SET of lat/longs, correct?
-- This single set of data says where the center of the Zip is.
If I'm correct (see below), it's either a trig problem or conversion to
polar coordinates.
If I'm wrong - if all four corners of the Zip areas are delineated, it
becomes a rather straight-forward UPDATE (with Select) statement. One query
and you're done.
What is the actual case? I'm curious.
>Thanks for the response Ed.
>
>The Cities table has just the 400 major cities in the US and Canada, the
>ZipCode table has every zip code and postal code for every corner of the
two
>countries.
>
>The ZipCode table has Longitude and Latitude coordinates for each zip code
>(basically each city). The Cities table has Longitude and Latitude
>coordinates for each City (or CityID).
>
>What I need to do is match a CityID with a ZipCode. I'd need to select a
>zipcode record, then loop through and calculate the distance of each of the
>400 cities based on the lat/lon of selected zip code, take the nearest/top
>CityID and update the ZipCode table.
>
>For example: (irrelevant data excluded)
>
>ZipCodes Table
>ZipCode Latitude Longitude CityID
>
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
