Re: Geographic data sources, queries and questions

On May 24, 2007 01:02:42 pm John D. Burger wrote:
> 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

This has been a while since I've written discussing this but I am looking for
some help.

Has anyone successfully imported any of the newer
http://earth-info.nga.mil/gns/html/gis_countryfiles.htm world cities files?

I keep getting problems with the import using COPY. Just when I thought I'd
solved the problem of the ^M$ in some fields (notably China's adm2's) I've
come across a problem with cc1=SG in which there appears to be extra ^I tabs.

Anyone have success with these? What preprocessing needs to be done to these
files to get them to COPY correctly?