[SOLVED] Importing data from geonames.org database into MySQL DB

Importing data from geonames.org database into MySQL DB

Does anyone how to import a geonames.org data into my database? The one i'm trying to import is http://download.geonames.org/export/dump/DO.zip, and my DB its a MySQL db.

Solution

I found the following by looking in the readme file included in the zip file you linked to in the section called "The main 'GeoName' table has the following fields:"

First create the database and table on your MySQL instance. The type of fields are given in each row of the section I just quoted the title of above.

CREATE DATABASE DO_test;
CREATE TABLE `DO_test`.`DO_table` (
  `geonameid` INT,
  `name` varchar(200),
  `asciiname` varchar(200),
  `alternatenames` varchar(5000),
  `latitude` DECIMAL(10,7),
  `longitude` DECIMAL(10,7),
  `feature class` char(1),
  `feature code` varchar(10),
  `country code` char(2),
  `cc2` char(60),
  `admin1 code` varchar(20),
  `admin2 code` varchar(80),
  `admin3 code` varchar(20),
  `admin4 code` varchar(20),
  `population` bigint,
  `elevation` INT,
  `gtopo30` INT,
  `timezone` varchar(100),
  `modification date` date
)
CHARACTER SET utf8;

After the table is created you can import the data from the file. The fields are delimited by tabs, rows as newlines:

LOAD DATA INFILE '/path/to/your/file/DO.txt' INTO TABLE `DO_test`.`DO_table`;