Re: Geographic data sources, queries and questions

* Chuck D. <pgsql-list(at)nullmx(dot)com> [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