> -----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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
