Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Sql tables for all countries States and Cities Expand / Collapse
Author
Message
Posted Monday, June 9, 2008 11:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, August 23, 2008 11:59 PM
Points: 8, Visits: 41
I need Sql tables for all countries States and Cities
Post #514155
Posted Monday, June 9, 2008 11:39 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 1, 2014 6:55 AM
Points: 2,366, Visits: 1,845
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"
Post #514158
Posted Tuesday, June 10, 2008 12:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, August 23, 2008 11:59 PM
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.
Post #514188
Posted Wednesday, June 11, 2008 12:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 9, 2010 2:32 AM
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.
Post #514951
Posted Wednesday, June 11, 2008 12:41 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 29, 2010 8:03 PM
Points: 54, Visits: 1,497
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.


Post #514959
Posted Thursday, April 9, 2009 12:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 15, 2011 9:17 PM
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?
Post #693758
Posted Saturday, April 11, 2009 11:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 10:12 AM
Points: 60, Visits: 903
Free download at the US Census Bureau web site

http://www.census.gov/tiger/tms/gazetteer/places2k.txt
Post #695281
Posted Sunday, April 12, 2009 5:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 15, 2011 9:17 PM
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'.
Post #695513
Posted Monday, April 13, 2009 6:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 15, 2011 9:17 PM
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
Post #696258
Posted Tuesday, July 14, 2009 11:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 14, 2009 11:50 PM
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.
Post #753171
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse