SQL: script to create country, state tables

Question

Consider writing an application that requires registration for an entity, and the schema has been defined to require the country, state/prov/county data to be normalized. This is fairly typical stuff here. Naming also is important to reflect. Each country has a different name for this entity:

• USA = states
• Australia = states + territories
• Canada = provinces + territories
• Mexico = states
• Brazil = states
• Sweden = provinces
• UK = counties, principalities, and perhaps more!

Most times when approaching this problem, I have to scratch together a list of good countries, and the states/prov/counties of each. The app may be concerned with a few countries and not others.

The process is full of pain. It typically involves one of two approaches:

• opening up some previous DB and creating a CREATE script based on those tables. Run that script in the context of the new system.
• creating a DTS package from database1 to database2, with all the DDL and data included in the transfer.

My goal now is to script the creation and insert of the countries that I'd be concerned with in the app of the day. When I want to roll out Countries X/Y/Z, I'll open CountryX.sql, and load its states into the ProvState table.

Question: do you have a set of scripts in your toolset to create schema and data for countries and state/province/county? If so, would you share your scripts here?

(U.K. citizens, please feel free to correct me by way of a comment in the use of counties.)

Answer 1

NOTE: This post does not cover the Licensing of such information.

If you use a search engine like Krugle getting the required information is fairly easy. See note above about using results in your application though.

Try this: http://www.krugle.org/kse/entfiles/phpnuke/sourceforge.net/head/nukeaddon/html/modules/Weather/sql/ALBANIA.sql#2

Answer 2

I'm not sure if you're creating redistributable or centralised software or whether it's online versus offline but this might be of interest; have you considered centralising this data into a service which is then consumed as required as opposed to replicating then redistributing? If the environment allows, this would solve the issue of recreating the schema for each new app and also allow centralised management when records change (which they do every now and then for geography data).

Answer 3

You want to use the Geonames Datadump. The export files are located at that site as well. Data is free. The stuff is broken down by countries, just as you asked for. Converting them to SQL should be simple, or you should just load the files in using LOAD DATA INFILE syntax. Best of luck.

Answer 4

This blog post has a collection of create scripts for Countries, Provinces, States, etc.

It's easy to copy/paste and modify for any other country that you might need.

Answer 5

see

http://java.sg/sql-script-for-country-table-ddl-and-dml/

It gives you the SQL DML and DDL Script for your database.