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?
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
