From: | "Chuck D(dot)" <pgsql-list(at)nullmx(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org, "John D(dot) Burger" <john(at)mitre(dot)org> |
Subject: | Re: Geographic data sources, queries and questions |
Date: | 2007-05-28 04:34:21 |
Message-ID: | 200705272234.21865.pgsql-list@nullmx.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thursday 24 May 2007 13:02, John D. Burger wrote:
>
> We also have a hodge-podge of other sources, but those are the main
> ones. (By the way, we have found USGS to very amenable to dumping
> their data in arbitrary ways. Those state files essentially try to
> fit everything into a single CSV format, but they have given us other
> custom dumps.)
>
> Note that both of these main sources have multiple names for the same
> location, so our schema is highly normalized - we have a separate
> table for names (so the string "Springfield" occurs in only one
> place :). Because we are interested in all sorts of geographic
> entities, not just city/state/country, we have only a single table
> for these, with fields for type, lat/long, primary name, and a few
> other things. All other relationships are represented in separate
> linking tables, using our internal IDs for locations and names, e.g.,
> location_has_name, location_contained_in_location, etc. As far as
> FIPS and ISO codes are concerned, we have a separate table mapping
> (locationID, standards body) to codes.
>
> We are interested in sharing this stuff, so I'd be happy to pass
> along the schema and/or the data, although all of it is kind of beta.
John,
I'd be happy to take a look at the schema and data.
I decided to put together the USGS stuff, the maxmind free stuff and the
GeoNames project files and in the end I had countries with no states, states
with no cities and cities with no states. Some data sources said a country
had 40 states, another said it had 50. It was difficult to try and figure
out because I don't know geo stuff enough to verify it.
How can I go about getting a peek at the schema and the sources at least?
From | Date | Subject | |
---|---|---|---|
Next Message | Louis-David Mitterrand | 2007-05-28 09:26:00 | table referencing several others |
Previous Message | Rodrigo De León | 2007-05-28 01:57:44 | Re: How to create trigger if it does not exist |