Geo databases

July 2, 2012 • 9 minutes read

Sometimes tasks related to geographical locations appear in development process. It can be development of LBS, construction of the drop-down list with a countries and cities, detecting location by ip, etc.. For this purpose it is necessary to have geo database.

The most often tasks:

• definition of the geographical location by ip address
• definition of the geographical location by geographical longitude and latitude
• get a list of countries, regions and cities
• search for objects in a given geographic range (geo radius)

Of course, you can use third-party services, but these services in the project has a lot of disadvantages:

• dependence on service
• inability to control the service
• velocity of the service is less than the local database
• payment for service

I would like to allocate two free-of-charge geo databases among founded databases for this tasks. This is MaxMind and GeoNames. Evgeniy Kuzminov advised me to pay attention on Geonames and I am very thankful to him for that

MaxMind

To determine the location by ip address it will be better to use database from MaxMind. There are ability to determine location by IPv4 and IPv6.

If you are PHP developer than you can use databases from MaxMind in two variants:

1. install php5-geoip and download necessary databases in .dat format and after that you will be able to use a lot of GeoIP Functions. Among which there is an opportunity to determine city, country, state, longitude, latitude, type of internet connection, etc. by user's ip address
2. parse the database from a text file into MySQL. A set of my shell scripts geo-db-to-mysql-parser can help you for these purposes. They are easy to use and can help to automate the updating of databases.

The set includes the following scripts:

• maxmind_geoip_country.sh - IPv4 ranges for countries
• maxmind_geoipv6.sh - IPv6 ranges for countries
• maxmind_geolitecity.sh - cities database with relation to the country
• maxmind_worldcitiespop.sh - cities database with relation to the country and population for some cities
• maxmind_region.sh - FIPS 10-4 region codes for countries

Example of usage:

# make executable script
chmod +x maxmind_geoip_country.sh
# maxmind_geoip_country.sh connection options to mysql
./maxmind_geoip_country.sh -uroot -ppassword -hlocalhost

As a result we will get maxmind database with geoipcountry table. Similarly, you can use the other scripts

GeoNames

There are databases with lists of countries and cities in MaxMind's arsenal, but there is more fully database - GeoNames. This database is free to download and distributed on Creative Commons Attribution 3.0 License. Also GeoNames have daily updates. Database contains an impressive list of different geographical objects, among them, not just cities, villages and other settlements, but also rivers, lakes, parks, beaches, hotels, etc. A complete list of supported geographical objects in the database can be viewed here.

Database available to download in text format. And a set of shell scripts geo-db-to-mysql-parser will help to parse it again

The set includes the following scripts:

• geonames_allcountries.sh - all countries in one table
• geonames_country.sh - geographical objects for specific country. After script starts you will be prompted by message "input 2 digits country code in UPPER CASE and press ENTER" and after that you should input country code in upper case
• geonames_cities1000.sh - all cities with a population > 1000 or seats of adm div (ca 80.000)
• geonames_cities5000.sh - all cities with a population > 5000 or PPLA (ca 40.000)
• geonames_cities15000.sh - all cities with a population > 15000 or capitals (ca 20.000)
• geonames_admin1_codes.sh - names of admin divisions in ascii

• geonames_alternate_names.sh - alternate names with language codes Example of usage:

  make executable script

  chmod +x geonames_allcountries.sh

  geonames_allcountries.sh connection options to mysql

  ./geonames_allcountries.sh -uroot -ppassword -hlocalhost

As a result we will get geonames database with all_countries table. Similarly, you can use the other scripts

Conclusion:

1. If you need database to determine user's country, city by ip you can use MaxMind
2. If you need database to get list of countries, cities and other settlements in country, geolocation selections by geographical coordinates, searching for nearest geographical objects - GeoNames