GeoNames Home | Postal Codes | Download / Webservice | About 

GeoNames Forum
  [Search] Search   [Recent Topics] Recent Topics   [Groups] Back to home page 
[Register] Register / 
[Login] Login 
Importing *all* geonames tables to mysql  XML
Forum Index -> FAQ - frequently asked questions Go to Page: Previous  1, 2, 3, 4
Author Message
lexiz



Joined: 15/12/2015 18:58:03
Messages: 1
Offline

dportabella wrote:

wget http://download.geonames.org/export/dump/admin1Codes.txt
wget http://download.geonames.org/export/dump/admin1CodesASCII.txt 


These two didn't work for me initially, they gave me a 404 error. It seems that admin1Codes.txt is no longer on the data download page here, but admin2Codes.txt is, so I tried that and it worked. Does anyone know if this is the same? I'm pretty sure since there isn't any other admin#Codes.txt available there.

I'm not sure why but after I got admin2Codes.txt to download, then admin1CodesASCII.txt worked.
psalvaro



Joined: 05/02/2016 06:25:30
Messages: 1
Offline

New allCountries.txt and alternateNames.txt files uses full Unicode (4-Byte UTF-8 Unicode encoding). MySQL utf8 charset only supports 3-Byte UTF-8 Unicode encoding. MySQL version 5.5.3 introduced a new encoding called utf8mb4 which maps to proper UTF-8 and thus fully supports Unicode.

Set table charset and collation to "CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci" and use CHARACTER SET 'utf8mb4' to LOAD DATA and the "invalid utf8 character string" error goes away.
xezen



Joined: 03/03/2016 19:30:40
Messages: 1
Offline

keep getting this error MySQL Database Error: Incorrect integer value: '' for column 'equivalent' at row 1

no matter what i try is there now new dump or .sql file avalable

as the msaccess database is also not avalable for download
hillrivers



Joined: 19/04/2016 15:41:16
Messages: 1
Offline

How to solve this error?

[SQL] LOAD DATA INFILE 'data/allCountries.txt' INTO TABLE geoname CHARACTER SET 'utf8mb4' (geonameid,name,asciiname,alternatenames,latitude,longitude,fclass,fcode,country,cc2, admin1,admin2,admin3,admin4,population,elevation,gtopo30,timezone,moddate);
[Err] 1062 - Duplicate entry '0' for key 'PRIMARY'
jonny512379



Joined: 11/02/2017 20:41:32
Messages: 10
Offline

Can anyone please help

i am trying to evaluate the geo data. we intend to use it on a large travel based website (we have tried other products but to no success, as they lacked too many tourist destinations...).
We will upgrade to the premium data, but i would first like to know is this project still active (we gained no reply from an email we sent)?

the problem is :http://download.geonames.org/export/dump/admin1Codes.txt is missing (gives a 404 page), so we can not properly evaluate the data. Can admin, or anyone else send or link to a copy please ASAP?
[Email]
marc



Joined: 08/12/2005 07:39:47
Messages: 4406
Offline

the file admin1Codes.txt no longer exists since it was redundant in the active part. In contrast to the other files it also included some names of no longer used admin divisions which led to unnecessary confusion and troubles.

[WWW]
jonny512379



Joined: 11/02/2017 20:41:32
Messages: 10
Offline

marc wrote:
the file admin1Codes.txt no longer exists since it was redundant in the active part. In contrast to the other files it also included some names of no longer used admin divisions which led to unnecessary confusion and troubles. 


OK thank you for the information!
[Email]
BillHeaton



Joined: 29/09/2019 04:00:28
Messages: 2
Offline

Here is an updated mysql import file GeoNames databases as of 9/25/19. I've also attached an UML diagram for the same.
 Description UML diagram for GeoNames as of 9/25/19. [Disk] Download
 Filesize 222 Kbytes
 Downloaded:  7026 time(s)

 Description mysql file to import GeoNames database. [Disk] Download
 Filesize 14 Kbytes
 Downloaded:  7909 time(s)


Semi-retired programmer since the 70s. Database driven Website designer using Yii2.
BillHeaton



Joined: 29/09/2019 04:00:28
Messages: 2
Offline

I believe that URL is now:
https://www.techrepublic.com/article/save-disk-space-by-compressing-mysql-tables/


giorgio79 wrote:
The main geonames and alternate tables are huge once I load them.

geonames is almost 700 MB s and alternate names is sg like 80 MBs

Given I am only doing SELECTs on the tables, I compressed them with mysql tools to half their original size. Now geonames is 300 MB and alternatenames is 30

Here is the artcile how
http://articles.techrepublic.com.com/5100-10878_11-5852557.html 

Semi-retired programmer since the 70s. Database driven Website designer using Yii2.
brayanrodbajo



Joined: 16/05/2020 18:28:46
Messages: 1
Offline

BillHeaton wrote:
Here is an updated mysql import file GeoNames databases as of 9/25/19. I've also attached an UML diagram for the same. 


I had to add set sql_mode = ''; in the first line.
 
Forum Index -> FAQ - frequently asked questions Go to Page: Previous  1, 2, 3, 4
Go to:   
Powered by JForum 2.1.5 © JForum Team