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 Next 
Author Message
codebreaker



Joined: 19/12/2008 07:34:30
Messages: 1
Offline

the countryinfo.txt have 18 column ,but the table countryinfo's sql just have 12 column,this makes some mistake,why?thanks.
redah75



Joined: 03/02/2009 13:36:51
Messages: 3
Offline

Hi, i would like to thank you for this amazing and useful database.
however, i have a simple question, what is the diference between admin1Codes.txt and admin1CodesASCII.txt because the number of recods is not the same!!

thanks for your help!
marc



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

It is in the readme. The larger file contains obsolete codes for reference.

Marc

[WWW]
redah75



Joined: 03/02/2009 13:36:51
Messages: 3
Offline

OK, thank you very much, and also thank you very much for this amazing database

2 last questions please:
1- is it normal the 'geonameid' field in the admin1CodesASCII table has no correspondance in the geoname table?

2- i'm trying to redesign our clients/members database, but i'm a little lost regarding addresses. i don't know if i should always use the content of admin1Codes or if the internet user should enter his region.
however i know i have to use it for the US states.
do you have a list of countries that require a state or area information??

Thank you so much
marc



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

1. all entries have a geonameid
2. no, we don't have a list with countries that require state information

Marc

[WWW]
redah75



Joined: 03/02/2009 13:36:51
Messages: 3
Offline

Thank you!
bye
ajmas



Joined: 09/02/2009 01:01:21
Messages: 18
Offline

It would be nice if the provided files included the latest mysql table structure, excluding the data. This can be achieved using this command, if your database is called geonames:

Code:
 mysqldump5  -u root --no-data geonames > geonames.sql
I have attached a dump of my database (excluding data) using this command. Note that the table structures include the corrections submitted through-out this thread. Hopefully this should help avoid people needing to piece together the instructions and corrections to create the empty database.

Edit: I have just tried importing the latest countryinfo data and I am seeing a difference in data structure, with regards to what was posted at the start of this thread. I will revise the table creation data, unless someone does so before me.
 Description result of mysqldump5 -u root --no-data geonames > geonames.sql [Disk] Download
 Filesize 7 Kbytes
 Downloaded:  13788 time(s)

ajmas



Joined: 09/02/2009 01:01:21
Messages: 18
Offline

I have just spent the last half hour reshaping the square peg so it would fit in the round hole. The updated load statements are:
Code:
 LOAD DATA INFILE '/data/geonames.org/allCountries.txt' INTO TABLE geoname (geonameid,name,asciiname,alternatenames,latitude,longitude,fclass,fcode,country,cc2, admin1,admin2,admin3,admin4,population,elevation,gtopo30,timezone,moddate);
 
 LOAD DATA INFILE '/data/geonames.org/alternateNames.txt' INTO TABLE alternatename (alternatenameid,geonameid,isoLanguage,alternateName,isPreferredName,isShortName,isColloquial,isHistoric);
 
 LOAD DATA INFILE '/data/geonames.org/iso-languagecodes.txt' INTO TABLE iso_languagecodes (iso_639_3, iso_639_2, iso_639_1, language_name);
 
 LOAD DATA INFILE '/data/geonames.org/admin1Codes.txt' INTO TABLE admin1Codes (code, name);
 
 LOAD DATA INFILE '/data/geonames.org/admin1CodesAscii.txt' INTO TABLE admin1CodesAscii (code, name, nameAscii, geonameid);
 
 LOAD DATA INFILE '/data/geonames.org/featureCodes.txt' INTO TABLE featureCodes (code, name, description);
 
 LOAD DATA INFILE '/data/geonames.org/timeZones.txt' INTO TABLE timeZones IGNORE 1 LINES (timeZoneId, GMT_offset, DST_offset);
 
 LOAD DATA INFILE '/data/geonames.org/countryInfo-n.txt' INTO TABLE countryInfo IGNORE 1 LINES (iso_alpha2,iso_alpha3,iso_numeric,fips_code,name,capital,areaInSqKm,population,continent,tld,currency_code,currency_name,phone,postal_code_format,postal_code_regex,languages,geonameId,neighbours,equivalent_fips_code);
 
 LOAD DATA INFILE '/data/geonames.org/continentCodes.txt' INTO TABLE continentCodes FIELDS TERMINATED BY ',' (code, name, geonameId); 
 


I have also updated the database creation script to reflect the adjusted table format. At this point I haven't tested this with any existing applications, so I can't say whether it will break anything.

Don't forget the continent codes at the start of this thread.
 Description updated result from mysqldump5 -u root --no-data geonames > geonames.sql [Disk] Download
 Filesize 8 Kbytes
 Downloaded:  13995 time(s)

shafique



Joined: 12/03/2009 21:28:22
Messages: 1
Offline

Hello all,

I tried using the code given (with the modification that I made all tables MyISAM, and in the geoname table added FULLTEXY (name) ), but get the following errors when loading the data into the tables (I'm using Mac OSX running on macbook pro, with mysql 5):



Query OK, 6903952 rows affected, 65535 warnings (10 min 32.11 sec)
Records: 6903952 Deleted: 0 Skipped: 0 Warnings: 167637

Query OK, 2609470 rows affected, 65535 warnings (27.01 sec)
Records: 2609470 Deleted: 0 Skipped: 0 Warnings: 1

Query OK, 7601 rows affected, 1 warning (0.14 sec)
Records: 7601 Deleted: 0 Skipped: 0 Warnings: 1

Query OK, 4505 rows affected, 21 warnings (0.16 sec)
Records: 4505 Deleted: 0 Skipped: 0 Warnings: 21

Query OK, 3558 rows affected, 21 warnings (0.02 sec)
Records: 3558 Deleted: 0 Skipped: 0 Warnings: 21

Query OK, 664 rows affected (0.02 sec)
Records: 664 Deleted: 0 Skipped: 0 Warnings: 0

Query OK, 400 rows affected, 6 warnings (0.00 sec)
Records: 400 Deleted: 0 Skipped: 0 Warnings: 6

Query OK, 247 rows affected, 496 warnings (0.04 sec)
Records: 247 Deleted: 0 Skipped: 0 Warnings: 249

Query OK, 7 rows affected (0.02 sec)
 


Can anyone offer some advice?

(I would just like to perform searches using the database locally because using ws.geonames.org quite often times out.)

Thanks,
stanimir



Joined: 18/04/2009 14:01:10
Messages: 1
Offline

simple update script for crontab this download and create table for separatel country and insert or update changes to geonames all countries table.

[code]#!/bin/bash
for i in 'AD' 'AE' 'AF' 'AG' 'AI' 'AL' 'AM' 'AN' 'AO' 'AQ' 'AR' 'AS' 'AT' 'AU' 'AW' 'AZ' 'BA' 'BB' 'BD' 'BE' 'BF' 'BG' 'BH' 'BI' 'BJ' 'BL' 'BM' 'BN' 'BO' 'BR' 'BS' 'BT' 'BV' 'BW' 'BY' 'BZ' 'CA' 'CC' 'CD' 'CF' 'CG' 'CH' 'CI' 'CK' 'CL' 'CM' 'CN' 'CO' 'CR' 'CU' 'CV' 'CX' 'CY' 'CZ' 'DE' 'DJ' 'DK' 'DM' 'DO' 'DZ' 'EC' 'EE' 'EG' 'EH' 'ER' 'ES' 'ET' 'FI' 'FJ' 'FK' 'FM' 'FO' 'FR' 'GA' 'GB' 'GD' 'GE' 'GF' 'GG' 'GH' 'GI' 'GL' 'GM' 'GN' 'GP' 'GQ' 'GR' 'GS' 'GT' 'GU' 'GW' 'GY' 'HK' 'HM' 'HN' 'HR' 'HT' 'HU' 'ID' 'IE' 'IL' 'IM' 'IN' 'IO' 'IQ' 'IR' 'IS' 'IT' 'JE' 'JM' 'JO' 'JP' 'KE' 'KG' 'KH' 'KI' 'KM' 'KN' 'KP' 'KR' 'KW' 'KY' 'KZ' 'LA' 'LB' 'LC' 'LI' 'LK' 'LR' 'LS' 'LT' 'LU' 'LV' 'LY' 'MA' 'MC' 'MD' 'ME' 'MF' 'MG' 'MH' 'MK' 'ML' 'MM' 'MN' 'MO' 'MP' 'MQ' 'MR' 'MS' 'MT' 'MU' 'MV' 'MW' 'MX' 'MY' 'MZ' 'NA' 'NC' 'NE' 'NF' 'NG' 'NI' 'NL' 'NO' 'NP' 'NR' 'NU' 'NZ' 'OM' 'PA' 'PE' 'PF' 'PG' 'PH' 'PK' 'PL' 'PM' 'PN' 'PR' 'PS' 'PT' 'PW' 'PY' 'QA' 'RE' 'RO' 'RS' 'RU' 'RW' 'SA' 'SB' 'SC' 'SD' 'SE' 'SG' 'SH' 'SI' 'SJ' 'SK' 'SL' 'SM' 'SN' 'SO' 'SR' 'ST' 'SV' 'SY' 'SZ' 'TC' 'TD' 'TF' 'TG' 'TH' 'TJ' 'TK' 'TL' 'TM' 'TN' 'TO' 'TR' 'TT' 'TV' 'TW' 'TZ' 'UA' 'UG' 'UM' 'US' 'UY' 'UZ' 'VA' 'VC' 'VE' 'VG' 'VI' 'VN' 'VU' 'WF' 'WS' 'YE' 'YT' 'ZA' 'ZM' 'ZW'
do
fl=`wget -r http://download.geonames.org/export/dump/$i.zip`
if [ "$?" = "0" ];
then
unzip -o /home/user/Desktop/geonames.script/download.geonames.org/export/dump/$i.zip
mysql --user mysqldbuser --password=mysqlpassword -e "USE denbg_geonames;
CREATE TABLE IF NOT EXISTS $i (
intID int(11) unsigned NOT NULL AUTO_INCREMENT,
strName varchar(200) COLLATE utf8_unicode_ci NOT NULL,
strAsciiName varchar(200) COLLATE utf8_unicode_ci NOT NULL,
strAlternateNames varchar(4000) COLLATE utf8_unicode_ci NOT NULL,
fltLatitude float NOT NULL,
fltLongitude float NOT NULL,
strFeatureClass char(1) COLLATE utf8_unicode_ci NOT NULL,
strFeatureCode varchar(10) COLLATE utf8_unicode_ci NOT NULL,
strCountryCode varchar(2) COLLATE utf8_unicode_ci NOT NULL,
strCC2 varchar(60) COLLATE utf8_unicode_ci NOT NULL,
strAdmin1Code varchar(20) COLLATE utf8_unicode_ci NOT NULL,
strAdmin2Code varchar(80) COLLATE utf8_unicode_ci NOT NULL,
strAdmin3Code varchar(20) COLLATE utf8_unicode_ci NOT NULL,
strAdmin4Code varchar(20) COLLATE utf8_unicode_ci NOT NULL,
intPopulation int(11) NOT NULL,
intElevation int(11) NOT NULL,
intGtopo30 int(11) NOT NULL,
strTimeZone varchar(100) COLLATE utf8_unicode_ci NOT NULL,
dtaModification date NOT NULL,
PRIMARY KEY (intID),
KEY intID (intID,strFeatureClass,strFeatureCode,strCountryCode),
KEY intPopulation (intPopulation),
FULLTEXT KEY strAlternateNames (strName,strAsciiName,strAlternateNames)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
TRUNCATE TABLE $i;
LOAD DATA INFILE '/home/stanimir/Desktop/geonames.script/$i.txt' INTO TABLE $i (intID,strName,strAsciiName,strAlternateNames,fltLatitude,fltLongitude,strFeatureClass,strFeatureCode,strCountryCode,strCC2,strAdmin1Code,strAdmin2Code,strAdmin3Code,strAdmin4Code,intPopulation,intElevation,intGtopo30,strTimeZone,dtaModification);
INSERT INTO denbg_denbg.geonames (intID,strName,strAsciiName,strAlternateNames,fltLatitude,fltLongitude,strFeatureClass,strFeatureCode,strCountryCode,strCC2,strAdmin1Code,strAdmin2Code,strAdmin3Code,strAdmin4Code,intPopulation,intElevation,intGtopo30,strTimeZone,dtaModification) SELECT * FROM denbg_geonames.bg ON DUPLICATE KEY UPDATE strName=VALUES(strName),strAsciiName=VALUES(strAsciiName),strAlternateNames=VALUES(strAlternateNames),fltLatitude=VALUES(fltLatitude),fltLongitude=VALUES(fltLongitude),strFeatureClass=VALUES(strFeatureClass),strFeatureCode=VALUES(strFeatureCode),strCountryCode=VALUES(strCountryCode),strCC2=VALUES(strCC2),strAdmin1Code=VALUES(strAdmin1Code),strAdmin2Code=VALUES(strAdmin2Code),strAdmin3Code=VALUES(strAdmin3Code),strAdmin4Code=VALUES(strAdmin4Code),intPopulation=VALUES(intPopulation),intElevation=VALUES(intElevation),intGtopo30=VALUES(intGtopo30),strTimeZone=VALUES(strTimeZone),dtaModification=VALUES(dtaModification);"
else
echo "geonames aborted."
fi
done[/code]
nasserahmed



Joined: 14/08/2009 11:35:37
Messages: 1
Offline

Hi,

Great work! I also love the update script but not tried it yet though.

Once you have all the DB tables imported how do you set up the web services access like ws.geonames.org? What is this written in?

Namely:
http://my.server/findNearby?lat=48.865618158309374&lng=2.344207763671875&fclass=P&fcode=PPLA&fcode=PPL&fcode=PPLC&style=full

Thanks.
kbechtold



Joined: 29/05/2007 19:21:00
Messages: 4
Offline

Hi folks,

does anyone has a new updated MySQL script to create the geonames database tables? Like an empty dump as written in the message from ajmas (see above, 09/02/2009)?

And - does anyone has also the updated load statements?

Thanks a lot!

Cheers, Klaus
http://www.gpsies.com/



Klaus Bechtold
http://www.gpsies.com
kbechtold



Joined: 29/05/2007 19:21:00
Messages: 4
Offline

Hi folks,

me again

I found out the problem with the uft8 problem (I had the same problem, see some posts above). You must add the "CHARACTER SET utf8" into the LOAD DATA command.

Here is my complete example (with a geo spatial search within a bounding box)


# open mysql command line
mysql --default-character-set=utf8 -u root -p

# set character-set to utf8
SET NAMES 'utf8';

# create the database
CREATE DATABASE `geonames`;

# use the database
USE `geonames`;

# create the allCountries table for example
CREATE TABLE `geonames`.`allCountries` (
`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',
`featureClass` char(1) ,
`featureCode` varchar(10) ,
`countryCode` char(2),
`cc2` varchar(60),
`admin1Code` varchar(20) default '',
`admin2Code` varchar(80) default '',
`admin3Code` varchar(20) default '',
`admin4Code` varchar(20) default '',
`population` bigint(11) default '0',
`elevation` int(11) default '0',
`gtopo30` int(11) default '0',
`timezone` varchar(40),
`modificationDate` date default '0000-00-00',
PRIMARY KEY (`geonameid`)
) CHARACTER SET utf8 ;

# here I inserted the character-set property that works for me
LOAD DATA INFILE '/tmp/geonames/allCountries.txt' INTO TABLE `geonames`.`allCountries` CHARACTER SET utf8 (geonameId,name,ansiName,alternateNames,latitude,longitude,featureClass,featureCode,countryCode,cc2,admin1Code,admin2Code,admin3Code,admin4Code,population,elevation,gtopo30,timezone,modificationDate);

# shoud be over 7 million entries (2010, January)
SELECT count(*) FROM `geonames`.`allCountries`;

# now you could use the MySQL geo spatial extensions and add the new (geo) column "coordinate"
ALTER TABLE `geonames`.`allCountries` ADD COLUMN `coordinate` POINT NOT NULL AFTER `longitude`;

# fill the new column "coordinate" with data (lat, lon) from the allCountries table
UPDATE `geonames`.`allCountries` SET coordinate = PointFromText(CONCAT('POINT(',latitude,' ',longitude,')'));

# set an index to the new geo point "coordinate". It MUST be MyISAM, see MySQL documentation
ALTER TABLE `geonames`.`allCountries` ADD SPATIAL INDEX `coordinate`(`coordinate`), ENGINE = MyISAM;

# set some MySQL statement variables, e.g. a small bounding box of part of Berlin, Germany
SET @NE_Latitude=52.45904246505317, @NE_Longitude=13.354050815105438; # northEast coords Berlin-Stegitz, Germany
SET @SW_Latitude=52.44811060147279, @SW_Longitude=13.321183025836945; # southWest coords Berlin-Stegitz, Germany

select * from `geonames`.`allCountries` where
MBRContains(
GeomFromText(CONCAT('Polygon((',@SW_Latitude,' ',@SW_Longitude,', ',@NE_Latitude,' ',@SW_Longitude,', ',@NE_Latitude,' ',@NE_Longitude,', ',@SW_Latitude,' ',@NE_Longitude,', ',@SW_Latitude,' ',@SW_Longitude,'))')),
coordinate
);

# here is same example, if you're using it in your code
select * from `geonames`.`allCountries` where
MBRContains(
GeomFromText('Polygon((52.44811060147279 13.321183025836945, 52.45904246505317 13.321183025836945, 52.45904246505317 13.354050815105438, 52.44811060147279 13.354050815105438, 52.44811060147279 13.321183025836945))'),
coordinate
);
 


I hope, that helps

So, I do have also a question. I want to get the closest/nearest point to a point in that table. Yes, I could build a bounding box with a rectangle of e.g. 1 mile. But, is there not an more easier way to find the closest point by a coordinate?

Thanks!

Best regards, Klaus
http://www.gpsies.com/

Klaus Bechtold
http://www.gpsies.com
surajddk



Joined: 03/06/2010 11:42:46
Messages: 1
Offline

GREAT WORK!!!
But it is need to inser one Comma in Here,

CREATE TABLE countryinfo (
iso_alpha2 char(2),
iso_alpha3 char(3),
iso_numeric integer,
fips_code character varying(3),
name character varying(200),
capital character varying(200),
areainsqkm double precision,
population integer,
continent char(2),
tld char(3),
currency char(3),
currencyName char(20),
Phone char(10),
postalCodeFormat char(20),
postalCodeRegex char(20),
geonameId int<<<<<<<<<<<<<<<<<<<<-----------Here
languages character varying(200),
neighbours char(20),
equivalentFipsCode char(10)
) CHARACTER SET utf8;


Then it is working fine,
Regards,
Suraj from Sri Lanka (Colombo)
drzraf



Joined: 10/06/2010 11:27:15
Messages: 19
Offline

Some corrections/updates :

wget http://download.geonames.org/export/dump/featureCodes.txt 

wget http://download.geonames.org/export/dump/featureCodes_en.txt 


Code:
cat countryInfo.txt | grep -v "^#" >countryInfo-n.txt 

Code:
cat countryInfo.txt | grep -v "^#"|cut -f1-11,17 >countryInfo-n.txt


Code:
postalCodeRegex char(20),
 geonameId int
 languages character varying(200), 

Code:
postalCodeRegex char(20),
 geonameId int,
 languages character varying(200), 



Code:
LOAD DATA INFILE '/data/admin1CodesAscii.txt' INTO TABLE admin1CodesAscii (code, name, nameAscii, geonameid); 

Code:
LOAD DATA INFILE '/data/admin1CodesASCII.txt' INTO TABLE admin1CodesAscii (code, name, nameAscii, geonameid); 


Code:
LOAD DATA INFILE '/data/featureCodes.txt' INTO TABLE featureCodes (code, name, description);

Code:
LOAD DATA INFILE '/data/featureCodes_en.txt' INTO TABLE featureCodes (code, name, description);
drzraf



Joined: 10/06/2010 11:27:15
Messages: 19
Offline

hierarchy.txt :

Code:
CREATE TABLE IF NOT EXISTS hierarchy (
 parentId int(11),
 childId int(11),
 relationType varchar(20),
 PRIMARY KEY (parentId, childId)
 ) CHARACTER SET utf8;


but :
ERROR 1062 (23000): Duplicate entry '6255146-3370751' for key 1 

$ grep 3370751 hierarchy.txt :
6255146 3370751 ADM
6255146 3370751 ADM
6255146 3370751 ADM 
marc



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

the duplicates will be fixed with the next dump. The cause is that Saint Helena, Ascension and Tristan da Cunha have equal status since 2009.

Marc

[WWW]
Rowena84



Joined: 28/09/2010 13:53:41
Messages: 2
Offline

Hi,
I try to import the dumps on my db but I always get errors like:

Code:
ERROR 1366 (HY000) at line 3: Incorrect integer value: '' for column 'isPreferredName' at row 1

My tables are MYISAM and CHARACTER SET utf8, so what's the problem?
marc



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

you have to treat '' as null.

Marc

[WWW]
Rowena84



Joined: 28/09/2010 13:53:41
Messages: 2
Offline

Thanks for the reply
How should I change my db to make it treat '' as null?
 
Forum Index -> FAQ - frequently asked questions Go to Page: Previous  1, 2, 3, 4 Next 
Go to:   
Powered by JForum 2.1.5 © JForum Team