Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sql tables for all countries States and Cities


Sql tables for all countries States and Cities

Author
Message
manaprime
manaprime
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 41
I need Sql tables for all countries States and Cities
ChiragNS
ChiragNS
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2409 Visits: 1865
If you mean designing the tables then you may want 3 tables
one each for country,state and city. Try designing them and then come to us for doubts/suggestions.

"Keep Trying"
manaprime
manaprime
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 41
I need data for all countries and their states and then their cities. I want to build a web form where in dropdown i give user option to select a country then states for that country are loaded then by selecting state he can select the city. I can do that all if i got the data.

List of all countries , states and cities.
peter-775355
peter-775355
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 5
manaprime (6/10/2008)
I need data for all countries and their states and then their cities. I want to build a web form where in dropdown i give user option to select a country then states for that country are loaded then by selecting state he can select the city. I can do that all if i got the data.


Best to have a read about ISO 3166-2 and buy the database.
http://www.iso.org/iso/country_codes/iso_3166_databases/iso_3166-2_database.htm
and consider which languages these dropdowns will be displayed in for non-english counties.

Also “State” is not the correct heading – they are called subdivisions. Eg: Province, County, island, state, etc

Cities is elsewhere on the web, but I can't remember the link.
Peter Lavelle
Peter Lavelle
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 1497
See also another link http://www.dak-tek.dakotta.com/Country_City_State_List.htm to buy from.

Not all countries use the concept of 'State'. If you are trying to capture an address then you may also need to allow for a ZIP/Postcode column in your database and the format of this varies widely Something like NVARCHAR(20) will probably be ok.

This information also changes from time to time. How will you know when it changes?
You might need to think about DateValidFrom and DateValidTo columns to preserve old values.
gazmanic
gazmanic
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 31
http://27.org/isocountrylist/

download the http://27.org/isocountrylist/iso_country_list.sql
load into vim editor and save
fire this at your sql database and your off

currently looking for regions (subdivisions) and cities sql tables?
Edward Boyle-478467
Edward Boyle-478467
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 1133
Free download at the US Census Bureau web site

http://www.census.gov/tiger/tms/gazetteer/places2k.txt
gazmanic
gazmanic
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 31
Thanks for reply. So we have a list of all cities and positional data (guessing) which is helpful.

Not wanting to redo a wheel that must have been done many times before; ER diagramaticaly this is what I am after.

country -< region -< city

http://www.gefeg.com/edifact/d03a/s3/codes/cl1h.htm
I now have the ISO regions as per this link with the matching ISO country 2 char code. Which is easy to translate (using vim or your favourite editor) into an SQL table.

Understandably city is a big job and may not exist in 'ISO land'.
gazmanic
gazmanic
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 31
Even better we have ISO related codes down to city level ... and download-able in different formats.
http://www.unece.org/cefact/locode/

Same and as MySQL download but regions in 2 char alpha-numerics.
http://www.iplocationtools.com/sql_database.php

MySQL download but country -< cities only.
http://drupal.org/node/19983
hexahow
hexahow
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 0
Maybe this http://www.geodatasource.com/world-major-cities.html will help. It even has sample codes in ASP, .NET and PHP.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search