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.
