Re: Geographic data sources, queries and questions - Mailing list pgsql-general

On Wednesday 23 May 2007 17:56, you wrote:
> 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.

No, a city state combination belongs to one country as far as I know.  I
really just left the country id in the city table as an easy means of getting
around while I was trying to understand the different data sources.  I was
doing a lot of typing at the console and it was easier than joins :)  It
would be dropped for sure as it adds another 4 MEGS to the table not
including indexes, and it violates BC normal form.

>
> 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!)

Ya, about 2 million city entries total.

>
> 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.

OK, cool.  I *prefer* the id as that is what I've been used to, but when I
used oracle in school we would have been taught to use the CHAR.  I just
don't like using the CHAR because some data sets use FIPS and some use ISO
and both are bound to change.  After all those are just abbreviations.  Seems
like something to be referenced.  I think I'll stick with the ID's instead of
CHARs then as they seem more comfortable.

Speaking of which, if you are using a similar database what source did you use
for geographic data?  I'm having troubles with a reliable set.

>
> > -----Original Message-----
> > From: pgsql-general-owner@postgresql.org
> > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Chuck D.
> > Sent: Wednesday, May 23, 2007 4:22 PM
> > To: pgsql-general@postgresql.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.