August 22, 2010 at 12:41 am
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.
Try Kansas City...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 31, 2010 at 8:25 am
Yes, you can find contiguous municipalities of the same name in two different States, even two different Countries, but they are legally two different entities with two different municipal governments.
Point being, you can make Towns, Cities, etc. children of States, but not of a county or a 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]
March 11, 2011 at 9:34 am
I need Sql tables for all countries States and Cities[/quote]
June 14, 2011 at 3:01 am
As some people have already said, not all Countries have the concept of State. San Merino, Vatican, Andorra are all internationally recognised countries, but have a very limited sub-division of land within them. There are doubtless other examples in other parts of the world.
There are also countries that have multiple hierarchies. The entire EU is divided into Regions, some of which cross Country boundaries. Many EU countries have their own internal divisions (Counties in UK, Lander in Germany), and contain some cities that are contained within a County or Land and can be considered a child of the parent, but also contain some cities that are entities in their own right (eg London in the UK, and multiple types of 'Free City' in Germany) and are a 'child' only of the country.
You also need to define what you mean by 'City'. Are you concerned by population or legal title? Germany in particular has some very large towns in terms of population that do not have the legal title City, and some places with the legal title of City that have a population under 30,000.
IMHO you need to consider what you want this information for. Personally I would be inclined to look at the real world and maybe end up with of City and define this by population. All other attributes (Country, State, Region, Legal Status, etc) are children of City. This allows a City to span multiple States or belong to none, and also allows you to select all Cities that exist in a given State.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 14, 2011 at 11:46 am
Kansas City and Texarkana immediately come to mind as spanning States.
June 29, 2011 at 12:56 am
you should consider the purpose why you need the database first.
having the database with as many cities as possible is completely different from having a database that you are going to use to populate multi-level location selector scripts.
For the former, you would probably need a database with several million entries.
For the latter, you should not consider anything with over 100,000 entries, because otherwise you run the risk that the script will either freeze the browser dead or simply take too long to feed all of the data.
Have a look at my post on the second page. I gave a link to a $4.99 database that is perfect for the latter situation.
June 30, 2011 at 7:39 am
From http://www.idescat.cat/territ/BasicTerr?TC=9 you can extract all cities in Catalonia 🙂 . For the rest of the world I can't help you.
July 2, 2011 at 3:59 pm
info 57591 (6/29/2011)
For the latter, you should not consider anything with over 100,000 entries, because otherwise you run the risk that the script will either freeze the browser dead or simply take too long to feed all of the data.
Gosh... if the data is correctly structured and the code is properly written, that's just not true... not even with the likes of Canadian Postal Code proximity searches.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 7, 2012 at 3:01 pm
spam reported
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 7, 2012 at 3:03 pm
Uripedes Pants (6/14/2011)
Kansas City and Texarkana immediately come to mind as spanning States.
Actually the cities do NOT cross state lines. The metropolitan areas cross state lines but they have separate governing bodies for the portions in each state. Kansas City, KS and Kansas City, MO. Two city governments, two mayors, etc.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply