Hi

Does anyone here have the DDL + the data for country list along with state for those countries?

If yes, can you please share the same with me?



Thanks

(Database = mySQL)

I've got a comma delineated text list for the US that has latitude, longitude, city, state, county, and zip for the US (should have one somewhere for Canada as well), or an MS Access DB with that data broken out into tables minus the lat/long for the US.
If either of those would be helpful let me know and I'll be glad to throw them up here when I get to my dev PC.

Sam

Countries list is easy enough, I'd suggest you use the ISO 2 or 3 digit definitions
Google
Google
many years ago I used to use the international dialling code for the telephone, until I started to encounter North American dialling codes sharing the same international code.

A state list for countries is a wee bit harder, it may be terminology but not every country is comprised of states, although most countries do have some form of lower regional administration
ferinstance
German has the Lander
France, Departments
the UK, countries followed by counties
Ireland counties
Canada has provinces

for modelling purposes you may be better off called the subdivision say regions or subregions

If not all, can I expect atleast UK countries and their states/county?


Thanks

Countries
ISO2Digit
ISO3Digit (PK)
EnglishCountryName
OwnLanguageName

Regions
ISO3Digit (PK)
Code (PK)
EnglishName
OwnLanguageName


SubRegions
ISO3Digit (PK)
RegionCode (PK)
Code (PK)
EnglishName
OwnLanguageName

you could flatten the design by making it two tables and make region have a self referencing FK to identify its parent region, arguably you could flatten the whole thing by havign one table and treat the whole thing as a single table
geographicalentities
Entity
Name
ParentEntity
that way round you could map any countries divisional approach no matter how large or how small
you could make the top level (with a null parententity as say continents), and then those with first level (ie only have a parententity whose own parent entity is null).

might be worthwhile doing a google to see if such a list exists
on the face of it geonames may be able to help you
GeoNames webservice and data download


one thing to bear in mind is that country names can be a source of conflict and passion. take for example the UK
the official country is the United Kingdom, which means the countries such as England, Northern Ireland, Scotland and Wales are not recognised, yet try telling that to some people.

some from the Republic of Ireland may not recognise Northern Ireland.

Oddly enough Jersey, Guernsey and the Isle of Man are seen as separate states.

similar 'bones of contention' exist elsewhere for example some people in Northern Spain and Southern France see themselves as Basque not Spanish of French

Hi,

If anyone else finds this post through Google, I posted this DDL and data:

Countries and Regions | Shattered Code

Anyone have an idea where I can find UK regions as cancer10 asked for?

--Andreas

if you want British regions then you have to decide what level you want to go to.
traditionally the counties were the sub dividisions in the UK. These had been the fixed adminstrative areas for hundreds of years. possibly dating back to before the time of the Normans. in 1974 these administrative areas were modified, with new areas created out of existing counties
ferinstance Lancashire lost part of its territory to the new counties of Greater Manchester, Merseyside & Cumbria.

since then the politicians have been meddling again, hiving off different bits of the historic countres into new administrative areas.
ferinstance Cheshire has now been split into two entites East & West Cheshire.

Im guessing for your purposes the 1974 reorgansiation is probably the most relevant to what you want. as this is the more commonly used notation these days. however unlike the US these boundaries are not set in stone and different counties have different 'acceptance' in the population. some counties are still very clear in their indentity (eg Yorkshire (even though as a county Yorkshire ceasaed to exist in 1974 when its constituent parts (the ridings there broken up and reallocated), Cornwall and so on. not many identify themsleves as 'belonging to the 1974 metropolitan counties of Greater London, Greater Manchester, Merseyside, West Midlands and so on

you could probably codge together a list based on List of counties of the United Kingdom - Wikipedia, the free encyclopedia

however since 200 the Post Office / Royal Mail have announced that they no longer see the need for the county to be put in an address as their internal postcode system is sufficient to identify an address and therefore no longer need the county to localise a specific 'main street'.
http://en.wikipedia.org/wiki/Postal_..._United_Kingdo

the (UK) post code as 4 elements
the first being the nearest main sorting office,
the next being an area the delivery office
the next being the delivery round
the last being a specific geographical area that should target upto 10..20 addresses.