How to put all countries/cities/states of the world into my database?

I have a difficult architectural and web problem. I am trying to make a page about hiking for every city in the world for http://www.comehike.com

I have开发者_JAVA百科 to somehow import all the cities in the world and structure them by state, which is in turn structured by country.

I already have a list of countries which I added by hand into the db, then added all the cities of California (there are 300+ if you consider small places), and realized there will be no way for me to do this by hand with accuracy for all the cities in the world.

Is there a way to do this in an automated fashion? Where do I get the reliable data from? Ideally I would have their lat/lng as well.

---

You can download free geographical data from GeoNames -- this comes in a text file from which the pertinent information can easily be parsed. If the free data is not reliable enough for your needs, they also offer verified data with higher quality control for a fee.

---

I answered a question very similar a few months back. I think my answer would be helpful to you: City Country State Database

I have a cities of the world CSV dataset compiled from the USGS GNIS Server (US) and US NGA GNS Server (non-US), that I have placed into the public domain. Below is a link and metadata of the layout.

Column 1: ISO 3166-1 alpha-2 country code.
Column 2: US FIPS 5-2 1st level administrative division code (e.g., state/province).
Column 3: NGA GNS Feature Description (DSG) code.
Column 4: NGA GNS Unique Feature Identifier (UFI).
Column 5: ISO 639-1 alpha-2/3 code for language corresponding to the feature name.
Column 6: Language script (e.g., latin, arabic, chinese, etc) corresponding to the feature name.
Column 7: Feature name.
Column 8: Latitude coordinate of the area centroid. Column 9: Longitude coordinate of the area centroid.

http://www.opengeocode.org/download.php#cities

---

I've recently did the exact same job. I used ansi SQL so that any relational database system can use the scripts.

You can find scripts over here: https://github.com/MehmetKaplan/SQL_for_Country_State_Cities

PS: Original scripts from which I derived data were here.