Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2008
    Posts
    36

    Question Unanswered: MYSQL : Country + State list .sql

    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)

  2. #2
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    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
    Good, fast, cheap...Pick 2.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Mar 2008
    Posts
    36
    If not all, can I expect atleast UK countries and their states/county?


    Thanks

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    Last edited by healdem; 12-29-10 at 13:15.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Sep 2011
    Posts
    1

    Countries, US States, and CA Provinces

    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

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •