GeoNames Forum

akreider wrote:

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 - bigint
altitude - smallint
gtopo30 - smallint

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

MySQL PROCEDURE ANALYSE advises an MEDIUMINT(7) for geonameid

Also, ENUM is faster then char for index search.

http://www.mysqlperformanceblog.com/2008/01/24/enum-fields-vs-varchar-vs-int-joined-table-what-is-faster/

Has anyone done this with MySQL using the geometry column type for the latitude and longitude? It'd make indexing on it much faster.

luuk34 wrote:

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

I find valuable info in this thread and the quoted links, but it is a little bit less to for me. Before I ask for details, I ask generally.

Hasn't anyone tried to write a full working script to create an own local database or maybe with a web-access to a local webserver?

Since the server is most times unreachable in the last time, I would like to install it locally with my Linux server (Opensuse 11.1), but it looks like this is not so easy.

Just succeeded in importing geonames into MySQL, however I found a problem with the import.sql file truncating population values.

Population fields should be bigint, not mediumint. This affects the tables: geonames and country_info.

BTW: thanks to all contributors. This is a great resource.

Thanks, I have updated the script. We have recently increased the population column to be large enough for continents and the entire planet.

Best

Marc

For those of you, who want to make a clean import into mysql while preserving NULL, maybe this little code will help. Without preserving NULL, all fields with no data ('') will end up as 0 in any numeric field. And for strings you have to do something like UPDATE table SET name=NULL WHERE name='' to make them really NULL for queries like WHERE IS NOT NULL (that's the job of NULL: to know if there is data or not). This is a bunch of work, if you want to do that for all fields manually (you want, if you want clean data).

Just do it like jbarbede explained and after that, run the little php-script attached as CLI (php fix.php) in the directory where all your files are stored. Make sure that all fields in the mysql database have standard "NULL" (without that, the NULL info will be lost). Besides that, I attached modified tables (changed fields to be more efficient) from jbarbede. Besides you wouldn't get a single warning exclude table geonames (because I decided to reduce the latitude & longitude to 6 decimals after comma, which is a precision of 11 centimeter at equador, which is more than enough) in MYSQL 5.0.

For those of you, who have trouble while import data into mysql with LOAD DATA: try LOAD DATA INFILE with GRANT-privileges. Another tip: set a correct line-terminator! Otherwise you'll get some spacing-chars in the last field of a line. Carriage-return for example, because mysql automatically choose newline as divisor. Something like this will do it:

Code:

TRUNCATE TABLE country_info;
 LOAD DATA INFILE 'countryInfo-fix.txt' INTO TABLE country_info LINES TERMINATED BY '\r\n'
 (iso, iso3, iso_numeric, fips, country, capital, area_in_sq_km, population,
  continent, tld, currency_code, currency_name, phone, postal_code_format,
 postal_code_regex, languages, geoname_id, neighbours, equivalent_fips_code);

Hopes that helps anybody like this thread helped me.

Best regards, T.

I imported everything. But in CountryInfo table all the values in the column geonamesid are 0.

I recommend the Geonames-MysqlImport project on Github. Simplifies everything quite nicely.

https://github.com/codigofuerte/GeoNames-MySQL-DataImport

(This should probably be sticky...)

marc wrote:

CREATE TABLE `geonames` (
`geonameid` int(10) unsigned NOT NULL default '0',
`name` varchar(200) NOT NULL default '',
`ansiname` varchar(200) NOT NULL default '',
`alternatenames` 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` bigint(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
Edit 2009.04.13 : alter column population from int to bigint

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

Hi everyone!

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

Thank you,