From: | "Fernando Hevia" <fhevia(at)ip-tel(dot)com(dot)ar> |
---|---|
To: | "'Mark Stosberg'" <mark(at)summersault(dot)com>, <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Best practices for geo-spatial city name searches? |
Date: | 2009-02-24 19:59:01 |
Message-ID: | 175E5B6233FD4244B9A52AB1D19BF120@iptel.com.ar |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
> -----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.
From | Date | Subject | |
---|---|---|---|
Next Message | John Zhang | 2009-02-25 03:37:54 | Add column by using SELECT statement |
Previous Message | johnf | 2009-02-24 19:42:29 | Re: Best practices for geo-spatial city name searches? |