UPDATE: We decided to use geonames API 😉 so no need to read this boring post
Long time not writting int he blog, I’ve been really busy developing on Open Classifieds and Yclas, releasing a new version of Open eShop on Codecanyon etc…
I want to develop and API to get Countries->Regions->Cities for users to use it on Open-Classifieds.com and Yclas.com for free. So they can import in their site directly locations witohut filling much information.
After 2 days literally non sleep testing DB…. None of them its good, or good enough.
Challenges
- Multilingual, its not the same Catalonia in English than Cataluña in Spanish. for example
- Updated, it’s not data that changes a lot but new regions or subdivisions of countries appear.
- Longitude and latitude, nice to have information
- Regions, subregions, provinces….there’s not any standard rule for all the countries so it’s difficult to apply this to many countries, therefore giving not realistic information
- Huge amounts of data. I will like to have only the capitals of provinces, or bigger cities or more important cities. No need of super little towns.
I’ve got an sponsorship from Vultr.com to host the project for free and I have already started with the project at github will have the curated DB and the PHP project for the API.
A bit desperated, any help?? thanks!
This are the DB I have tried. Sorry typos, wrote late at night.
Databases
http://download.geonames.org/export/dump/
Huge and updated, seems the most reliable.
Will be difficult to extract the names in different locales. To extract it and normalize it will take a while but not crazy. Translated.
Huge issue the http://download.geonames.org/export/dump/cities15000.zip (should contain only cities > 15K) includes neighbous of barcelona!! 🙁 sucks
http://openmundi.github.io/
Not sure If I am doing something wroong but seems really limited, see spain:
https://github.com/openmundi/world.db/tree/master/europe/es-spain quite strange
http://www.opengeocode.org/download.php#cities
Without other languages. Only citis, we will need to extract countries and regions from there. Only english. Lat and long available.
http://www.sergiquinonero.net/base-de-datos-con-paises-provincias-y-localidades-de-todo-el-mundo.html
Complete, and translated, not sure if its Open Source. Names in HTML. Mexico seemed to be terrible. Spain was quite good using provinces instead of states. Will need to do lot of work to normalize it
https://code.google.com/p/worlddb/
Theres 2 downloads available:
https://code.google.com/p/worlddb/downloads/detail?name=lokasyon.sql.gz&can=2&q=
In a single Table, easy to use. Only English. Has ISO code for countries but no idea where he got it from. In spain uses states as regions. Quite accurate data and easy to manipulate
https://code.google.com/p/worlddb/downloads/detail?name=worlddb.7z&can=2&q=
It has Countries, Regions, Cities. And then a table with translations for each. Its outdated 2009.
I was trying to merge these 2 for many hours and then realize that the translations are extremely bad, example: Catalonia is Catalonia in spanish….
Spent more than 12 hours meging data and built the API based on this. :`(
http://dev.maxmind.com/geoip/geoip2/geolite2/
Really updated and in different languages! Doesnt include the Lat, Long but since uses the geoname_id we could merge it from another DB. Also we could extract it form the blocks of that is included in the zip. Will take few hours to extract the countries, regions and normalize the data. Only after that process we will know if we can use it.