GeoNames Forum

CREATE TABLE `geonames` (
`geonameid` int(10) unsigned NOT NULL default '0',
`name` varchar(200) NOT NULL default '',
`ansiname` varchar(200) NOT NULL default '',
`alternames` varchar(2000) NOT NULL default '',
`latitude` double NOT NULL default '0',
`longitude` double NOT NULL default '0',
`feature_class` char(1) ,
`feature_code` varchar(10) ,
`country_code` char(2),
`cc2` varchar(60),
`admin1_code` varchar(20) default '',
`admin2_code` varchar(80) default '',
`admin3_code` varchar(20) default '',
`admin4_code` varchar(20) default '',
`population` int(11) default '0',
`elevation` int(11) default '0',
`gtopo30` int(11) default '0',
`timezone` varchar(40),
`modification_date` date default '0000-00-00',
PRIMARY KEY (`geonameid`)
) CHARACTER SET utf8 ;

You will need to index the columns needed by your application in queries.

Edit 2006.07.19 : added cc2
Edit 2006.08.08 : added timezone
Edit 2007.03.24 : added admin2
Edit 2007.07.25 : added admin3 and admin4

How to load all tables is described here :
http://forum.geonames.org/gforum/posts/list/732.page

If you want to save some disk space:

geonameid - can be mediumint (at least I think so, this allows for 8 million ids, or 16 million if you make it positive, whereas geonames is more around 4.5 million)

population - mediumint
altitude - smallint
gtopo30 - smallint

latitude/longitude - probably can be float for most uses (the geonames database isn't that precise)

Hi everyone!

And which table format would you use for best search performance? MyISAM or InnoDB?

Thank you,
Eddie

Hi Eddie

We are using postgres at geonames. This said I guess MyISAM could be the better choice as it is supposed to be faster than InnoDB and I don't think you need transactions or foreign keys for your geonames table.

Cheers

Marc

after creating the table its quite simple
Code:

 matt@matt-laptop:~/Desktop/IN$ mysql -uroot
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 81 to server version: 5.0.21-Debian_3ubuntu1-log
 
 Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
 mysql> use geonames;
 Reading table information for completion of table and column names
 You can turn off this feature to get a quicker startup with -A
 
 Database changed
 mysql> load data infile '/home/matt/Desktop/allCountries/allCountries.txt' INTO TABLE geonames;
 Query OK, 6313518 rows affected, 65535 warnings (5 min 21.73 sec)
 Records: 6313518  Deleted: 0  Skipped: 0  Warnings: 5734251

When I try the approach above with the table structure from the first post of this thread I get the following error:

ERROR 1264 (22003): Out of range value adjusted for column 'elevation' at row 1

Any ideas what I have to do to fix this?

For the first row the elevation value is empty, but since the column don't have the NOT NULL flag set, I don't see how that should be a problem?

I had much better luck using the mysqlimport tool.

mysqlimport -h host -u username -ppassword --fields-terminated-by='\t' --lines-terminated-by='\n' --columns='geonameid, name, ansiname, alternames, latitude, longitude, feature_class, feature_code, country_code, cc2, admin1_code, admin2_code, population, elevation, gtopo30, timezone, modification_date' --local database /path/geonames.txt

Use ftp or wget on the server to grab the dump file, upload it to your server, uncompress and rename to match your table.

Replace the following with your info:
host
username
password (no space between the -p option and password)
database
/path/geonames.txt (path to your file on server)

Important: double check via phpmyadmin that both the collation of the whole database and the table is set to "utf8_general_ci" via the "operations" tab before you import.

matthieu wrote:

after creating the table its quite simple
Code:

 matt@matt-laptop:~/Desktop/IN$ mysql -uroot
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 81 to server version: 5.0.21-Debian_3ubuntu1-log
 
 Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
 mysql> use geonames;
 Reading table information for completion of table and column names
 You can turn off this feature to get a quicker startup with -A
 
 Database changed
 mysql> load data infile '/home/matt/Desktop/allCountries/allCountries.txt' INTO TABLE geonames;
 Query OK, 6313518 rows affected, 65535 warnings (5 min 21.73 sec)
 Records: 6313518  Deleted: 0  Skipped: 0  Warnings: 5734251

mysql> load data infile '/home/luuk/Downloads/geonames/allCountries.txt' into table geonames;
Query OK, 6530255 rows affected, 65535 warnings (1 min 39.69 sec)
Records: 6530255 Deleted: 0 Skipped: 0 Warnings: 4628033

more records, less warnings.... and quicker
but still the same way loaded....

are there also scripts for other tables like
- admin1Codes
- admin2Codes
- countryInfo
- featureCodes
- iso-languagecodes
- timeZones

Hi there,

these posts did help a lot in order to mySQLize thegeonames. BUT i still have a problem:

When i imported the file into the geonames table which i created with the CREATE from the start of this post, there are broken string entries like in "Zwölfing" or other german "umlaute".

So i did save the txt file as UTF8 encoded file on my limux development machine again but it didn't help.

any hints what i forgot??
thanks

dag

Hi RaggaDee

Our files are in UTF8. If you are using the create statement above you should be ok. It could be a problem with the way you look at the data. You will have to set some display parameter to tell your display tool that you want to look at UTF8.

Marc

Hi Marc, thnaks a lot for your answer. I still watched them in phpMyAdmin... maybe i find there a display param, too.

RgD

hello there,
i lost couple of hours before i found out why i got this
Code:

  error ERROR 1366 (HY000): Incorrect integer value: '' for column 'elevation' at row 1.

Well it means what it says!!! my integer cant have this value '' in fact when you are under mysql5. So under mysql5 read this link it might help.
http://bugs.mysql.com/bug.php?id=18551

Cheers.

The fields have changed since the last (helpful) post on how to do a mysqlimport, so now this will work:

Code:

mysqlimport -h <yourhost> -u <yourusername>--default-character-set=utf8 
 --fields-terminated-by='\t' --lines-terminated-by='\n' 
 --columns='geonameid, name, ansiname, alternames, latitude, longitude, 
 feature_class, feature_code, country_code, cc2, admin1_code, 
 admin2_code, admin3_code,admin4_code,population, elevation, gtopo30, 
 timezone, modification_date' --local <your_geonames_db> allcountries.txt

I found that varchar(200) is not sufficient for the "alternames" field in some cases, such as Brussels, Belgium. So you might want to change that to varchar(500) or something like that.

Hi,

i've tried to load dump but it throw me this error and i don't know what is it.

Code:

Error
 
 SQL query:
 
 LOAD DATA INFILE '/data/allCountries.txt' INTO TABLE geoname(
 geonameid, name, asciiname, alternatenames, latitude, longitude, fclass, fcode, country, cc2, admin1, admin2, admin3, admin4, population, elevation, gtopo30, timezone, moddate
 );
 
 MySQL said: Documentation
 #13 - Can't get stat of '/data/allCountries.txt' (Errcode: 2)

can you help me?