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? ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly
