On May 24, 2007 01:02:42 pm John D. Burger wrote:
> Tilmann Singer wrote:
> > We are using this data which seems to be fairly extensive and
> > accurate, and is free:
> >
> > http://earth-info.nga.mil/gns/html/gis_countryfiles.htm
>
> We use that, but it is only non-US, so we combine it with this:
On May 30, 2007, at 11:51 , Gregory Stark wrote:
Firstly trying to update such a key you'll immediately bump into
the practical
reasons why it doesn't work well. You have to update every record
everywhere
in the database that references that key which represents a lot of
potential
work.
On 05/30/07 11:01, John D. Burger wrote:
Even ISO country codes are not guaranteed to be stable
I'm not sure where the idea that primary keys must be stable comes
from. There's nothing necessarily wrong with updating a primary key.
All a primary key does is uniquely identify a row in a table.
In short, you have to update every instance of the key, not only in the
database, but in every application and even in every other
representation in
the real world. That could include changing people's bookmarks, notes in
PDAs,
even paper reports sitting on people's desks -- a tall order fo
>>> I'm not sure where the idea that primary keys must be stable comes from.
>>> There's nothing necessarily wrong with updating a primary key. All a
>>> primary key does is uniquely identify a row in a table. If that id changes
>>> over time, that's fine, as long as the primary key columns con
Even ISO country codes are not guaranteed to be stable
I'm not sure where the idea that primary keys must be stable comes
from. There's nothing necessarily wrong with updating a primary
key. All a primary key does is uniquely identify a row in a table.
If that id changes over time, that's
On 05/29/07 17:46, Michael Glaesemann wrote:
On May 29, 2007, at 15:28 , John D. Burger wrote:
Even ISO country codes are not guaranteed to be stable
I'm not sure where the idea that primary keys must be stable comes from.
There's nothing necessarily wrong with updating a primary key. All a
On May 29, 2007, at 15:28 , John D. Burger wrote:
Even ISO country codes are not guaranteed to be stable
I'm not sure where the idea that primary keys must be stable comes
from. There's nothing necessarily wrong with updating a primary key.
All a primary key does is uniquely identify a ro
On May 29, 2007, at 14:50 , Oliver Elphick wrote:
On Tue, 2007-05-29 at 13:49 -0500, Michael Glaesemann wrote:
If you're handling more than one country, you'll most likely want to
associate the states with their respective countries.
-- Listing 4
CREATE TABLE countries
(
country_id INTE
Oliver Elphick wrote:
You have assumed that state codes are unique integers, but for a
worldwide database that is probably a bad design. The USA knows its
states by two-letter codes, as does India and one should surely not
invent a new set of codes for them. I would make this field a
VARCHAR(3
On Tue, 2007-05-29 at 13:49 -0500, Michael Glaesemann wrote:
>
> If you're handling more than one country, you'll most likely want to
> associate the states with their respective countries.
>
> -- Listing 4
> CREATE TABLE countries
> (
> country_id INTEGER PRIMARY KEY
> );
>
> CREATE TABL
On May 24, 2007, at 8:57 , btober wrote:
I'm not sure it is a bad design. Country has a country_id.
That's the primary key. State has a state_id, and exactly
one country, so really state has a compound primary key,
namely (country_id, state_id).
While each state may have a single state_id and
You can try the free sample database from http://www.geodatasource.com
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Off-topic, but the US Virgin Islands are an "unincorporated United
States insular area" ("territory"--note the lowercase t). The Dept.
Of the Interrior addresses this in their FAQ:
http://www.doi.gov/oia/FAQ/FAQindex.htm#4
You'll be surprised and amazed at the number of US "terms of
sovere
John D. Burger wrote:
For instance, does the US have 50 states - what about the US Virgin Islands,
etc?
Off-topic, but the US Virgin Islands are an "unincorporated United
States insular area" ("territory"--note the lowercase t). The Dept. Of
the Interrior addresses this in their FAQ:
http
Chuck D. wrote:
I decided to put together the USGS stuff, the maxmind free stuff
and the
GeoNames project files and in the end I had countries with no
states, states
with no cities and cities with no states. Some data sources said a
country
had 40 states, another said it had 50. It was di
On Thursday 24 May 2007 13:02, John D. Burger wrote:
>
> We also have a hodge-podge of other sources, but those are the main
> ones. (By the way, we have found USGS to very amenable to dumping
> their data in arbitrary ways. Those state files essentially try to
> fit everything into a single CSV
Tilmann Singer wrote:
We are using this data which seems to be fairly extensive and
accurate, and is free:
http://earth-info.nga.mil/gns/html/gis_countryfiles.htm
We use that, but it is only non-US, so we combine it with this:
http://geonames.usgs.gov/domestic/download_data.htm
We also ha
>
> >
> > I don't believe this is good design. You'll have to
have a trigger or
> > something to verify that the country_id+state_id on the
city table are
> > exactly equal to the country_id+state_id on the state
table. If you
> > don't, you might have something like (using US city
names...) "cou
* Chuck D. <[EMAIL PROTECTED]> [20070524 01:26]:
> 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 r
On Wednesday 23 May 2007 18:59, you wrote:
>
> I don't believe this is good design. You'll have to have a trigger or
> something to verify that the country_id+state_id on the city table are
> exactly equal to the country_id+state_id on the state table. If you
> don't, you might have something li
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: [EMAIL PROTECTED]
> > [mailto:[EMAIL PROTECTED] On Behalf Of Chuck D.
> > Sent: Wednesday, May 23, 2
"Chuck D." <[EMAIL PROTECTED]> writes:
> 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 i
ood 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 question
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
25 matches
Mail list logo
