* Chuck D. <[EMAIL PROTECTED]> [20070524 01:26]: > 2) I've spent an accumulated total of around a month and a half trying to > consolidate geographic name data from several free sources on the net and > realize this isn't the best use of my time and errors will be had. Does > anyone know of a reliable source of geo data that isn't costly? Most want to > charge a server license, annual rate, etc. I'm not sure about the free > sources because one I used actually had mixed values in a column and drove me > nuts. I primarily need: > > country > state > county if applicable > city > latitude > longitude > > This is primarily input from an HTML form to calculate distances between > users. > > Anyone who has any experience with geo name data I would appreciate hearing > your solution.
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 I haven't fully understood the meaning of all the fiels in there however, we're using it only to compute alternative spellings for city and country names and came up with these conditions that seem to return the desired results: For city alternatives: select lower(full_name) as full_name from geo_names gn1 where gn1.ufi in (select ufi from geo_names gn2 where lower(gn2.full_name)=:city and gn2.fc='P') For country alternatives: select lower(full_name) as full_name from geo_names gn1 where gn1.ufi in (select ufi from geo_names gn2 where lower(gn2.full_name)=:country and gn2.fc='A' and gn2.dsg='PCLI') Til ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
