On Tuesday 24 February 2009 08:19:56 am Mark Stosberg wrote:
> 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/ . . . . . . . .
I don't know if this is any help. I recently used google to obtain the
longitude and latitude and then used simple math to determine the distance
between the locations to determine proximity searches. Like finding the
closes store.
--
John Fabiani