Software Developer

GeoNames Data Dump Files – Usage Example

This is an example of how to use the GeoNames data dump files on your own server. This gives you the steps to import the GeoNames data into your own MySQL database.

This example will only use the allCountries.txt data file from the GeoNames data download page. Go ahead and download that file, now. You will need to download the .zip file (allCountries.zip), then extract it to find the .txt file.

Create a new MySQL database for the data. In this example, the database will be named geonames. On the command line:

mysql -u USERNAME -p
CREATE DATABASE geonames;

Next, create the table to hold all of the data from the allCountries.txt file.

USE geonames;

This will create the table with the necessary structure. This is only one line of code:

bigint(20) unsigned NOT NULL UNIQUE

CREATE TABLE allcountries (geonameid BIGINT(20) unsigned NOT NULL PRIMARY KEY, name VARCHAR(200), asciiname VARCHAR(200), altnames VARCHAR(15000), latitude DECIMAL(10,7), longitude DECIMAL(10,7), fclass CHAR(1), fcode VARCHAR(10), countrycode CHAR(2), cc2 CHAR(200), admin1 VARCHAR(20), admin2 VARCHAR(80), admin3 VARCHAR(20),admin4 VARCHAR(20),population BIGINT, elevation INT, gtopo30 INT, timezone VARCHAR(40), moddate DATE);

Next, import that data file into the database. You must edit “/home/user/Downloads/allCountries.txt” to use the correct path for where you have downloaded/extracted the file to.

LOAD DATA INFILE '/home/user/Downloads/allCountries.txt' INTO TABLE geonames.allcountries (geonameid, name, asciiname, altnames, latitude, longitude, fclass, fcode, countrycode, cc2, admin1, admin2, admin3, admin4, population, @elevation, gtopo30, timezone, moddate) SET elevation = if(@elevation='', null, @elevation);

If you get an error like this:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Then enter this into the command line:

SHOW VARIABLES LIKE "secure_file_priv";

That should give a path to a folder that is allowed for MySQL. Move your allCountries.txt file into that folder.

To do that, first exit from MySQL:

exit

Then move the file (replace /var/lib/mysql-files with your own allowed path which you got in the previous step):

sudo cp /home/user/Downloads/allCountries.txt /var/lib/mysql-files/allCountries.txt

Now, try to import the data again, but with the new path (again, replace /var/lib/mysql-files with your own allowed path):

LOAD DATA INFILE '/var/lib/mysql-files/allCountries.txt' INTO TABLE geonames.allcountries (geonameid, name, asciiname, altnames, latitude, longitude, fclass, fcode, countrycode, cc2, admin1, admin2, admin3, admin4, population, @elevation, gtopo30, timezone, moddate) SET elevation = if(@elevation='', null, @elevation);

NOTE: THIS LAST COMMAND WILL TAKE A LONG TIME. In my case, it took 15 minutes and 43 seconds. You’ll finally get a response like this:

Query OK, 11540556 rows affected (15 min 43.23 sec)
Records: 11540556  Deleted: 0  Skipped: 0  Warnings: 0

Now, you must add the indexes that you need for your queries. Here is how to add at least an index for the city name:

CREATE INDEX city_name ON allcountries (name)

Here is a sample query that returns a list of cities whose names begin with “Miami.” It will return the region, country name, latitude, longitude, and timezone ID for each city.

SELECT name,admin1,countrycode,latitude,longitude,timezone FROM allcountries WHERE fclass = "P" AND fcode = "PPL" AND name LIKE 'Miami%';

You can create indexes as needed. You can use the other GeoNames data dump files, as needed, to map the region codes (admin1_code) and country codes to their actual names.

By

Questions and Comments are Welcome

Your email address will not be published. All comments will be moderated.

Please wrap code in "code" bracket tags like this:

[code]

YOUR CODE HERE 

[/code]