Re: Best practices for geo-spatial city name searches?

 

> -----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.