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. ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
