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: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] 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. ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
