Hello,
I use PostgreSQL and the "cube" type to perform geo-spatial zipcode proximity
searches. I'm wondering about the best practices also supporting a geo-spatial
distance search based on a city name rather than zipcode.
In our original data model, we used a 'zipcodes' table, with the zipcode as the
primary key. This can of course contain a "City Name" column, but there is a
problem with this, illustrated a "Nome, Alaska" case. Nome's zipcode is 99762.
It maps to multiple cities including Diomede, Alaska and Nome, Alaska.
In the data model described, only the "Diomede" row is imported, and the other
rows, including the "Nome, Alaska" row are dropped. So if you try to search
for Nome, Alaska, you won't find anything.
One solution would be to have a "cities" table, with the city/state as the
primary key, and a zipcode as an additional column. Then, by joining on the
zipcodes table, the coordinates for a city could be found.
Is there any other way I should be considering data modelling to support
searches on zipcodes and cities?
Thanks!
Mark
-- . . . . . . . . . . . . . . . . . . . . . . . . . . . Mark Stosberg Principal Developer
mark@summersault.com Summersault, LLC 765-939-9301 ext 202 database driven websites. . . . .
http://www.summersault.com/. . . . . . . .
