All countries/states/cities database or text file

Discussion in 'Programming & Software Development' started by Azsen, Aug 27, 2010.

  1. Azsen

    Azsen Member

    Joined:
    Oct 15, 2004
    Messages:
    1,174
    Location:
    Sydney
    Hi,

    I'm sure one of you has come across this problem sometime in your programming career. It seems to be an absolute pain trying to find anything on the net though.

    Basically I'm after 3 tables containing all the country/state/city data in the world:
    countries (country_id, country_name)
    states (state_id, state_name, country_id)
    major cities (city_id, city_name, country_id, state_id)

    Which I would then put into 3 combo boxes and have them dependent on each other:
    1. The user selects a country, I use ajax to grab the states for that country.
    2. User selects a state, I use ajax to populate the city combo box with the cities in the state.
    3. User selects the city, then submits the form.

    Ok so I've got a rough idea what the code will look like from here however I'm missing all the data to make this happen.

    So far I've got a list of all the timezones and countries with international dialing code prefixes i.e. +61 for Australia (I had to format myself from some pdf file). Some sites have a list of the countries and cities, but no state data.

    Is there a free web service you can use to query for this information? From my readings so far it looks like a database doing this could be upwards of 80MB. Could slow down my web server quite a bit... I'm using PHP and MySQL by the way.

    Many thanks!
     
    Last edited: Aug 27, 2010
  2. Mikos

    Mikos Member

    Joined:
    Mar 12, 2004
    Messages:
    2,881
    Location:
    Cydonia
    Googling "free world city state country database" brings up this page, will that do what you are after? You can easily delete the unnecessary columns.

    Are you worried about server storage space or the speed of the queries? The queries will be instant on a database containing this information, you will not have to worry about lag. 80MB may seem like a big database but it is peanuts so don't worry about it.

    If you don't have the storage to put the database on your own server, I think you are going to have a lot more headaches trying to find a free online service. And then you have to put up with the problem that if the online service goes down, so does your website. Much better to have a local copy of this IMO, considering how static the data is.
     
  3. OP
    OP
    Azsen

    Azsen Member

    Joined:
    Oct 15, 2004
    Messages:
    1,174
    Location:
    Sydney
    Thanks Mikos! I'm kinda getting there with the data from that site.

    So far I think the matching pattern between the data is the 2 digit country code and the region code. You can use that to link the country, region and city data together.

    I've got the country table into the database by importing the csv file with SQLYog. Then the region table I had to manually parse the file with PHP and insert the rows from there because it wouldn't accept the region code for some reason. The code is 2chars and ranges from 00 - 99 and AA - ZZ but SQL just sets the cell to null if it encounters the code with letters. I've set the column type to varchar but it doesn't like it either.

    For the cities file I imported that in and put a where clause on the population to be > 40000 so I'm only getting the main cities. Cut the database right down to 8700 rows (252KB) instead of 2.3million rows (130MB). Also some of the region codes are null in this one so it looks like I'll have to parse the big file with PHP. :shock:

    Hell of a mission I tell you.
     
  4. OP
    OP
    Azsen

    Azsen Member

    Joined:
    Oct 15, 2004
    Messages:
    1,174
    Location:
    Sydney
    Well finally sorted it out after two days of importing/parsing the data. :wired: Got all the countries, regions/states and cities with population > 45K in the database, now in relational format.

    Database schema looks like this:
    [​IMG]

    SQL file here if anyone needs it.

    Query all the data like so:
    Code:
    SELECT countries.country_id, country_code, country_name, country_call_code, regions.region_id, region_name, city_id, city_name
    FROM countries
    INNER JOIN regions ON regions.country_id = countries.country_id
    INNER JOIN cities ON cities.region_id = regions.region_id
    Very quick to query, only 7600 rows.
     
    Last edited: Aug 30, 2010
  5. btwong

    btwong Member

    Joined:
    Jun 28, 2004
    Messages:
    359
    Location:
    just over there ->
    dude... nice. i think i will get a copy of this... might come in handy someday.
     
  6. Foliage

    Foliage Member

    Joined:
    Jan 22, 2002
    Messages:
    32,093
    Location:
    Sleepwithyourdadelaide
    If I ever did any web programming I imagine that would come in handy. I might save it just in case.
     
  7. grs1961

    grs1961 Member

    Joined:
    Jan 21, 2005
    Messages:
    519
    Location:
    Melbourne
    Now, a program to do all that automagically would be good. Did you keep notes?
     

Share This Page

Advertisement: