Re: Geographic data sources, queries and questions

Tilmann Singer wrote:

> We are using this data which seems to be fairly extensive and
> accurate, and is free:
>
> http://earth-info.nga.mil/gns/html/gis_countryfiles.htm

We use that, but it is only non-US, so we combine it with this:

http://geonames.usgs.gov/domestic/download_data.htm

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 D. Burger
MITRE