Sql tables for all countries States and Cities

Question

Post reply

Sql tables for all countries States and Cities

manaprime

SSChasing Mays

Points: 650
More actions
June 9, 2008 at 11:29 pm

#121891

I need Sql tables for all countries States and Cities

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

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 SSChasing Mays Points: 650 More actions · Answer 2

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 Valued Member Points: 69 More actions · Answer 3

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 Mr or Mrs. 500 Points: 526 More actions · Answer 4

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 SSC Veteran Points: 243 More actions · Answer 5

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 Ten Centuries Points: 1165 More actions · Answer 6

Free download at the US Census Bureau web site

http://www.census.gov/tiger/tms/gazetteer/places2k.txt

gazmanic SSC Veteran Points: 243 More actions · Answer 7

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 SSC Veteran Points: 243 More actions · Answer 8

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 Valued Member Points: 65 More actions · Answer 9

Maybe this http://www.geodatasource.com/world-major-cities.html will help. It even has sample codes in ASP, .NET and PHP.

lullabee SSC Journeyman Points: 79 More actions · Answer 10

You can also take a look at GeoDataSource's World Cities Database:

http://www.geodatasource.com/cities.html

They have free and paid editions of world cities database.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

You should be aware that although no City in the U.S. that I know of crosses state lines, some may effectively cross county lines, and many cover more than one zip code.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

rock on dude Ten Centuries Points: 1236 More actions · Answer 12

Hi, I bought this one... it come with a nice jquery integration to load states and cities. http://www.jaxiter.com/scripts/signup

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 13

RBarryYoung (3/8/2010)
You should be aware that although no City in the U.S. that I know of crosses state lines, some may effectively cross county lines, and many cover more than one zip code.

I remember the problem of ZipCodes and LATA's that cross state lines. I'd expect that cities and even counties could somehow do the same thing. What the heck... there's a city that's half in Canada and half in the US (although I can't remember the name).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WayneS SSC Guru Points: 95460 More actions · Answer 14

RBarryYoung (3/8/2010)
You should be aware that although no City in the U.S. that I know of crosses state lines, some may effectively cross county lines, and many cover more than one zip code.

Here's a few that come pretty darn close, and there might even be one (Delmar) that actually qualifies. Though it seems that all are legally two different cities, with different governments, which seems to exclude them.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2