World City locations database

For one of my projects, I need the geographical location (latitude, longitude, altitude) of all main cities of the world. It was a adobe air project to determine worldwide salat timetable automatically.

I couldn’t find a complete list. Then I found this site, Aneki World Cities. Then I designed a scrapper using PHP, and scrapped all the countries first, then their respective cities list, then respective geographical location. Then end result was a table in the database with all countries, respective cities and locations

As I have only compiled the database, the information aren’t mine, so I have decided to make the table publicly available. You can download the zip file here: World Cities Locations (The zip file contains 4 types of file for same content. SQL, CSV, CSV for MS EXCEL, MS EXCEL 2000. All of them are exported from MySQL using phpMyAdmin)

The sql file contains the schema to reproduce the location table in your designated database (assuming you are using mysql database engine. If you aren’t, you can easily convert the sql schema ).

The table has in 10,567 rows (means 10,567 unique cities, their locations and 170 countries).

The table has 5 fields, id, country, city, latitude, longitude, altitude. From their name, I hope you can understand their significance.

The table can be used in different ways:

• Getting all countries of the world (170 countries): SELECT DISTINCT(country) FROM location
• Getting all cities of a country: SELECT city FROM location WHERE country = 'YOUR_COUNTRY_NAME_HERE'
• Getting the location of a specific city of a specific country:
  SELECT latitude,longitude,altitude FROM location WHERE country='COUNTRY_NAME' AND city = 'CITY_NAME'

I hope this small contribution to the open world helps all. You can use the content in location based applications, finding a city easily in map services etc.