From: | johnf <jfabiani(at)yolo(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Best practices for geo-spatial city name searches? |
Date: | 2009-02-24 19:42:29 |
Message-ID: | 200902241142.29231.jfabiani@yolo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(at)summersault(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Fernando Hevia | 2009-02-24 19:59:01 | Re: Best practices for geo-spatial city name searches? |
Previous Message | Mark Stosberg | 2009-02-24 16:19:56 | Best practices for geo-spatial city name searches? |