> -----Mensaje original-----
> From: Mark Stosberg
>
> 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?
>
You absolutely need zipcode as a primary key? If you must enforce non
duplicate entries use country + state + county + city_name instead. You
might still need to throw zipcode into the PK for certain cities
(worldwide).
Otherwise, latitud & longitude provide a better natural key, or simply use a
non data related sequential bigint.
Regards.