Author |
Message |
![[Post New]](/gforum/templates/default/images/icon_minipost_new.gif) 23/01/2008 20:25:11
|
marc
Joined: 08/12/2005 07:39:47
Messages: 4486
Offline
|
Is the file path correct? Is the file readable? Is it really in /data/ and not in data/?
Cheers
Marc
|
 |
|
 |
![[Post New]](/gforum/templates/default/images/icon_minipost_new.gif) 28/01/2008 15:28:34
|
Q
Joined: 23/01/2008 14:26:58
Messages: 3
Offline
|
...maybe you right.
|
|
 |
![[Post New]](/gforum/templates/default/images/icon_minipost_new.gif) 30/08/2008 18:35:40
|
jbarbede
Joined: 30/08/2008 18:15:04
Messages: 1
Offline
|
Like it seems the method to insert the geonames database in MySQL was not updated, I propose an actualized version to insert the geonames database in UTF8. I made it with the geonames files downloaded the 21 November 2008 and the MySQL version used is the 5.0.67. All comments are welcome.
1. Create a temporary folder on your hard disk
2. Download the following elements and save them in your temporary folder:
- http://download.geonames.org/export/dump/allCountries.zip
- http://download.geonames.org/export/dump/alternateNames.zip
- http://download.geonames.org/export/dump/admin1Codes.txt
- http://download.geonames.org/export/dump/admin1CodesASCII.txt
- http://download.geonames.org/export/dump/admin2Codes.txt
- http://download.geonames.org/export/dump/featureCodes_en.txt
- http://download.geonames.org/export/dump/timeZones.txt
- http://download.geonames.org/export/dump/countryInfo.txt
3. Unzip allCountries.zip in your temporary folder
4. Unzip alternateNames.zip in your temporary folder. There are two files: iso-languagecodes.txt and alternateNames.txt
5. Check all files to remove commented and/or useless lines. There are commented and/or useless lines at least in countryInfo.txt, iso-languagecodes.txt and timeZones.txt to the beginning of each file.
6. Create the continentCodes.txt file with the following content in your temporary folder:
AF,Africa,6255146
AS,Asia,6255147
EU,Europe,6255148
NA,North America,6255149
OC,Oceania,6255151
SA,South America,6255150
AN,Antarctica,6255152
7. Download the import.sql file attached to this post and save it in your temporary folder. Create the database, tables and import data due to the import.sql file. The database and inserted data will be in UTF8.
mysql -u user -p < import.sql (of course replace "user" by an existing MySQL user in your system and you need to have your temporary folder like current folder to execute this command).
10 tables will be created.
10 text files will be used to load 9 049 392 lines:
- allCountries.txt: 6,674,517 lines
- alternateNames.txt: 2,351,166 lines
- admin1Codes.txt: 4,578 lines
- admin1CodesASCII.txt: 3,540 lines
- admin2Codes.txt: 11,396 lines
- continentCodes.txt: 7 lines
- countryInfo.txt: 247 lines
- featureCodes.txt: 664 lines
- iso-languagecodes.txt: 7600 lines
- timeZones.txt: 399 lines
It represents approximatively 800 Mo.
8. Remember to add the indexes you need.
9. You have your database created and ready to use.
TODO: geoname.cc2 : alternate country codes, comma separated, ISO-3166 2-letter country code, 60 characters --> Move this information to a separate table. It's a todo in the initial version.
Edit 18. May 2009, Marc Wick: changed population to bigint.
Description |
The import.sql file to create the database and load data. |
Download
|
Filesize |
4 Kbytes
|
Downloaded: |
69002 time(s) |
|
|
 |
![[Post New]](/gforum/templates/default/images/icon_minipost_new.gif) 05/12/2008 05:17:43
|
garaki
Joined: 19/12/2007 23:57:44
Messages: 5
Offline
|
Alright all the tables are created.. now how do i query it to get a zip code for city called 'Dari' in India
thanks
|
|
 |
![[Post New]](/gforum/templates/default/images/icon_minipost_new.gif) 20/12/2008 18:32:32
|
SEO
Joined: 14/10/2008 18:58:20
Messages: 18
Offline
|
marc wrote:
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
Hi there!
Your message is from 2006, is this still the case for the city database? Will MyIsam work better then InnoDB?
As it seems from test results on for example the MySQL performance blog the innodb has become much (30% in general) faster then MyIsam, especially when selecting on geonameid (primary id) or sorting by latitude longitude (<> ) .
http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/
What should we choose? Does anyone have experience with innodb as storage engine for the city database?
|
|
 |
![[Post New]](/gforum/templates/default/images/icon_minipost_new.gif) 24/12/2008 20:53:19
|
SEO
Joined: 14/10/2008 18:58:20
Messages: 18
Offline
|
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/
|
|
 |
![[Post New]](/gforum/templates/default/images/icon_minipost_new.gif) 21/01/2009 07:59:05
|
tburch
Joined: 21/01/2009 07:57:49
Messages: 1
Offline
|
Has anyone done this with MySQL using the geometry column type for the latitude and longitude? It'd make indexing on it much faster.
|
|
 |
![[Post New]](/gforum/templates/default/images/icon_minipost_new.gif) 29/01/2009 17:03:20
|
bognerart
Joined: 28/05/2007 23:18:02
Messages: 15
Offline
|
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.
|
|
 |
![[Post New]](/gforum/templates/default/images/icon_minipost_new.gif) 17/05/2009 18:56:20
|
l8rm8e
![[Avatar]](/gforum/images/avatar/facf9f743b083008a894eee7baa16469.png)
Joined: 13/05/2009 18:03:02
Messages: 1
Location: Tasmania
Offline
|
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.
|
|
 |
![[Post New]](/gforum/templates/default/images/icon_minipost_new.gif) 18/05/2009 07:44:35
|
marc
Joined: 08/12/2005 07:39:47
Messages: 4486
Offline
|
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
|
 |
|
 |
![[Post New]](/gforum/templates/default/images/icon_minipost_new.gif) 16/07/2009 14:59:40
|
Tomahawk
Joined: 16/07/2009 13:28:07
Messages: 1
Offline
|
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.
Description |
geoname_tables.txt |
Download
|
Filesize |
5 Kbytes
|
Downloaded: |
51067 time(s) |
Description |
fix.php |
Download
|
Filesize |
844 bytes
|
Downloaded: |
41850 time(s) |
|
|
 |
![[Post New]](/gforum/templates/default/images/icon_minipost_new.gif) 02/12/2009 16:09:59
|
filangi
Joined: 02/12/2009 16:08:29
Messages: 1
Offline
|
I imported everything. But in CountryInfo table all the values in the column geonamesid are 0.
|
|
 |
![[Post New]](/gforum/templates/default/images/icon_minipost_new.gif) 27/10/2013 08:33:11
|
rfay
Joined: 14/10/2007 01:00:50
Messages: 8
Offline
|
I recommend the Geonames-MysqlImport project on Github. Simplifies everything quite nicely.
https://github.com/codigofuerte/GeoNames-MySQL-DataImport
(This should probably be sticky...)
|
|
 |
![[Post New]](/gforum/templates/default/images/icon_minipost_new.gif) 03/10/2015 17:48:27
|
dungnvph03125
![[Avatar]](/gforum/images/avatar/7e183bb3e56405bbc499a1c706f71a06.jpg)
Joined: 17/09/2015 15:46:32
Messages: 2
Offline
|
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,
|
|
 |
|