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