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

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