Re: Geographic data sources, queries and questions

["John D. Burger" <john(at)mitre(dot)org>]

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) with an upper-case constraint.

In fact, the US postal codes are not what most govt. data sources use  
- they are mandated to use FIPS codes, which are numeric and are not  
guaranteed to be stable!!!

> Furthermore, these codes are
> not going to be unique.  For instance MH is the US abbreviation for  
> the
> Marshall Islands [US Post Office] and also the Indian abbreviation for
> Maharashtra [Wikipedia].  In such a case I would always make the  
> country
> code part of the primary key and not just an attribute.  Again this
> saves your having to invent a new set of codes when one exists  
> already.

Even ISO country codes are not guaranteed to be stable - I think  
Yugoslavia is one example where a code has been recycled recently.   
As I said, we found the simplest approach was to use our own internal  
IDs for these things, and have a table mapping these to the codes  
used in various standards.

- John D. Burger
  MITRE