Re: Geographic data sources, queries and questions

seems hard to enforce integrity in your model. how are you going to
ensure that the user's city-state-country combo a valid one? (well, you
can, but it is a pain). ask yourself: can a city be in more than one
country? probably not (even if the name is the same it is not the same
city!). can a state be in more than one country? etc., etc.

seems much cleaner to have cities have a key to states, states to
countries. otherwise might as well just have a big denormalized table
and skip the whole relational thing...

numeric ids vs chars, when properly indexed, should perform about the
same (even if there is a small difference this is not something one
should really worry about; hey, there aren't even that many cities in
the world!)

i would go with a unique internal id (in fact that IS what i do) you can
store the FIPS/ISO code in a neighboring field, but i am not sure it is
good enough for a primary key.

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Chuck D.
> Sent: Wednesday, May 23, 2007 4:22 PM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] Geographic data sources, queries and questions
>
> Greetings all,
>
> I have a couple issues regarding geographic names databases.
>
> 1) The first is this. I have 3 tables. Country, state and
> city. Country has
> a country_id to identify a country, state has a state_id and
> country_id to
> identify a state, and city has a city_id, state_id and
> country_id (for easy
> reference) to identify it. I then have a table for users
> that stores their
> city, state and country ID's along with other info about them.
>
> My problem came recently when I questioned the integrity of
> the data and
> needed to make some changes. I thought to myself that maybe
> storing the ID
> wasn't as good as storing the ISO or FIPS 2 letter
> abbreviation. The only
> problem the abbreviation could changed at some point by the
> regulating bodies
> and all rows in all tables would need to be updated.
>
> The question is, for the purposes of querying or searching is
> it better to
> store and search a 2 byte integer that is indexed for country
> or state ID's,
> or is it better to store and search a 2 byte CHAR abbreviation?
>
> 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.