Geographic data sources, queries and questions

Поиск
Список
Период
Сортировка
От Chuck D.
Тема Geographic data sources, queries and questions
Дата
Msg-id 200705231722.16691.pgsql-list@nullmx.com
обсуждение исходный текст
Ответ на Re: Searching data across tables, some large  ("Ericson Smith" <esconsult1@gmail.com>)
Ответы Re: Geographic data sources, queries and questions  ("George Pavlov" <gpavlov@mynewplace.com>)
Re: Geographic data sources, queries and questions  (Jorge Godoy <jgodoy@gmail.com>)
Re: Geographic data sources, queries and questions  (Tilmann Singer <tils-pgsql@tils.net>)
Список pgsql-general
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.

В списке pgsql-general по дате отправления:

Предыдущее
От: Francisco Reyes
Дата:
Сообщение: Delete with subquery deleting all records
Следующее
От: "Joris Dobbelsteen"
Дата:
Сообщение: Re: Delete with subquery deleting all records