We have the need to determine surrounding counties of a particular county.
For example how can I determine, if I entered a zip code for Contra Costa and the property is also right on the line of alameda that Alameda is a neighboring county of Contra Costa
Could it be determined by the longitude and latitude of the zip code in comparison with the counties?
Hello,
Although I'm sure a geospacial co-ordinate system could be used to determine this, most people think it overkill and just use a proximity table :)
tCounty (CountyID int, CountyName varchar)
tCountyAdjoined(CountyID int FK to tCounty.CountyID, AdjoiningCountyID int FK to tCounty.CountyID)
So if Contra Costa was near Alameda, tCountry might contain rows:
1, Contra Costa
2, Alameda
And tCountyAdjoined would contain:
1, 2
Obviously, a simple join then returns all counties that adjoin a particular County.
Unfortunatly, you'll need to manually determine and input the proximity values (unless your postal service supplies these details as in Australia.)
Cheers,
Rob
