Continent, Country, State/Province, City Data Source

Continent, Country, State/Province, City Data Source

bamccaig

Member #7,536

July 2006

Well I'm planning to do a Forum system for fun and to flex my skills during my free time. I'm sure I'll learn a lot from the experience... It'll also be nice to work on a project done right for a change (you know, you can't fix WTF systems you're stuck working on). My plan for extreme user-friendliness is to offer AJAX loaded controls for as much as possible.

I'm planning to do the system with Apache/PHP/MySQL + AJAX/XHTML. I'm learning AJAX techniques as best I can (limited useful tutorials, apparently) and hopefully it turns out well.

I'm storing the City of residence of users so I want a drop down hierarchy of continents -> countries -> states/provinces -> cities to make it rather easy (and also more accurate than individual users typing these in).

I'm curious if there is an easy button to get and insert the data for cities and above. Obviously there are hundreds of countries... The states/provinces will increase exponentially; and cities will increase exponentially from there. That is a lot of SQL to write. Does anybody know of a reliable way to get this information and insert it into your database?

:-/

--
I mean the best with what I say. It doesn't always sound that way.

Matthew Leverton

Supreme Loser

January 1999

Find the information via Google and write a script to populate the database.

For example: http://www.geodatasource.com/cities-free.html

Their free database only has countries and names of cities. If you need more, look elsewhere.

Or there's this one:

http://www.maxmind.com/download/worldcities/

It has more information. (See this for the region codes.)

--
RTFM | Follow Me on Google+ | I know 10 people

bamccaig

Member #7,536

July 2006

Thanks, Matthew.

So far I've got a list of continents and countries that seems reliable. I'm turning it into SQL now.

Australia is grouped in a continent called Oceania... Is that right? I was taught North America, South America, Europe, Africa, Asia, Australia, and Antarctica.

In any case, it's going to take me at least another hour to turn just the continents and countries into SQL (which means a half hour tonight and finish tomorrow)... Unless I relied on my basic TCL skills to try to automate the SQL writing... ::)

There really should be a source of this information... It's 2007 people! Maybe when I'm done I should register a domain and offer one?

It occurred to me that I might want to separate the regional data into a separate database...?

--
I mean the best with what I say. It doesn't always sound that way.

Matthew Leverton

Supreme Loser

January 1999

Quote:

Australia is grouped in a continent called Oceania... Is that right? I was taught North America, South America, Europe, Africa, Asia, Australia, and Antarctica.

The American school system divides the world into those seven continents, but there are several other standard ways to do it.

You should also take a look at the link I added to my previous post.

--
RTFM | Follow Me on Google+ | I know 10 people

Rampage

Member #3,035

December 2002

Quote:

If you want to have all that information for every country in the world, you won't find it. If you limit your scope to the developed countries it might work, because in other countries there's no trustworthy record of all the cities and towns.

-R

LennyLen

Member #5,313

December 2004

Quote:

Australia is grouped in a continent called Oceania... Is that right? I was taught North America, South America, Europe, Africa, Asia, Australia, and Antarctica.

When it comes to regional listings, Oceania is used the most times online in my experience, otherwise sites generally use Australasia. I dont ever recall coming across a site that lists the South Pacific region as Australia.

And it's definitely better that way. Nobody else in the area wants to be considered part of Australia.

bamccaig

Member #7,536

July 2006

Rampage said:

Yeah, it's slowly becoming clear how incredibly difficult it would be to get all of this information... Perhaps I will start with US/Canada since I know the most about those... Besides, if this system gets to a point where it's in use by other continents I will be amazed...

Then again, I would probably ask Allegro.cc to review/critic it and most users do seem to be from Europe/Asia... 8-)

Maybe I can have a "Your city not listed? Click here to add it!" section... But then I'm sure I would end up with a ton of garbage data... :-/

I guess I could add a flag, UserDefined, to the region-related tables and allow other users to call "BS!" on the data.

--
I mean the best with what I say. It doesn't always sound that way.