Hi folks,
I wish to do the following pseduo code in Sql without the use of a CURSOR
, if possible.
for each zipcode
{
-- What city is this zipcode in? A zipcode can be in multiple cities,
-- but one city area is always way greater that the others.
-- Eg. 90210 is 96% in the city of Beverly Hills.
-- The remaining 4% is that it s in some other fringe cties ..
--but only just (some mismatch mapping errors).
** grab the city largest shape area which this zip is a part of **
}
Now, I have some SQL to help us out.
-- All zipcodes and each boundary/shapefile.
SELECT ZipCodeId, Boundary FROM ZipCodes -- Boundary is a GEOGRAPHY field type.
To determine if a zipcode boundary is in a city....
SELECT CityId, CityName,
City.Boundary.Intersection(@someZipCodeBoundary).STArea() AS Area
FROM Cities
WHERE City.Boundary.Intersects(@someZipCodeBoundary) = 1
and to get the area of intersection (because we want the highest area of intersection... ie. TOP(1) ORDER BY Area DESC
or a DISTINCT with an ORDER BY
.. sorta thing... .. we use the Intersection
sql method.
NOTE: Intersects
and Intersection
are two different Sql methods.
So.. can anyone help me out? I suck at all this SET
stuff :(