GeoNames Forum

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.

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/

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/

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)

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);

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

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

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?

you have to treat '' as null.

Marc

Thanks for the reply
How should I change my db to make it treat '' as null?

And what about the cities5000.txt database and so on? what tables do i need to create?

Hi,
I'm try for months but without success.
I utilise Mysql with Xampp on Windows 7 and I seen many commands run under Linux system.
It's possibile to have a little how-to which helps to import alla datas?
Another question: I seen, for example, in italy nation more cities and regions are in english language. How is possible to have every city in the correct language spoken by the associated nation?
Thanks.

the other languages are in the file 'alternateNames.zip'.

Marc

And How I must to do do assign every language to its nation?

This is impossible. There is no 1:1 relation between languages and countries.
The file countryInfo.txt contains a list of languages spoken in each country.

Marc

First up, thanks to everyone in this thread... what i post is what took me a while to figure out (character set in load data and some other errors in the original script (like capital I in countryInfo etc).

save as import.sql (update)

Code:

 CREATE DATABASE geonames;
 USE geonames;
 
 CREATE TABLE geoname (
 geonameid int PRIMARY KEY,
 name varchar(200),
 asciiname varchar(200),
 alternatenames varchar(4000),
 latitude decimal(10,7),
 longitude decimal(10,7),
 fclass char(1),
 fcode varchar(10),
 country varchar(2),
 cc2 varchar(60),
 admin1 varchar(20),
 admin2 varchar(80),
 admin3 varchar(20),
 admin4 varchar(20),
 population int,
 elevation int,
 gtopo30 int,
 timezone varchar(40),
 moddate date
 ) CHARACTER SET utf8;
 
 
 CREATE TABLE alternatename (
 alternatenameId int PRIMARY KEY,
 geonameid int,
 isoLanguage varchar(7),
 alternateName varchar(200),
 isPreferredName boolean,
 isShortName boolean,
 isColloquial boolean,
 isHistoric boolean
 ) CHARACTER SET utf8;
 
 
 CREATE TABLE countryinfo (
 iso_alpha2 char(2),
 iso_alpha3 char(3),
 iso_numeric integer,
 fips_code varchar(3),
 name varchar(200),
 capital varchar(200),
 areainsqkm double,
 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,
 languages varchar(200),
 neighbours char(20),
 equivalentFipsCode char(10)
 ) CHARACTER SET utf8; 
 
 
 CREATE TABLE iso_languagecodes(
 iso_639_3 CHAR(4),
 iso_639_2 VARCHAR(50),
 iso_639_1 VARCHAR(50),
 language_name VARCHAR(200)
 ) CHARACTER SET utf8;
 
 
 CREATE TABLE admin1Codes (
 code CHAR(6),
 name TEXT
 ) CHARACTER SET utf8;
 
 
 CREATE TABLE admin1CodesAscii (
 code CHAR(6),
 name TEXT,
 nameAscii TEXT,
 geonameid int
 ) CHARACTER SET utf8;
 
 
 CREATE TABLE featureCodes (
 code CHAR(7),
 name VARCHAR(200),
 description TEXT
 ) CHARACTER SET utf8;
 
 
 CREATE TABLE timeZones (
 timeZoneId VARCHAR(200),
 GMT_offset DECIMAL(3,1),
 DST_offset DECIMAL(3,1)
 ) CHARACTER SET utf8;
 
 
 CREATE TABLE continentCodes (
 code CHAR(2),
 name VARCHAR(20),
 geonameid INT
 ) CHARACTER SET utf8;
 
 
 LOAD DATA LOCAL INFILE 'allCountries.txt' INTO TABLE geoname CHARACTER SET 'UTF8' (geonameid,name,asciiname,alternatenames,latitude,longitude,fclass,fcode,country,cc2, admin1,admin2,admin3,admin4,population,elevation,gtopo30,timezone,moddate);
 
 LOAD DATA LOCAL INFILE 'alternateNames.txt' INTO TABLE alternatename CHARACTER SET 'UTF8' (alternatenameid,geonameid,isoLanguage,alternateName,isPreferredName,isShortName,isColloquial,isHistoric);
 
 LOAD DATA LOCAL INFILE 'iso-languagecodes.txt' INTO TABLE iso_languagecodes CHARACTER SET 'UTF8' (iso_639_3, iso_639_2, iso_639_1, language_name);
 
 LOAD DATA LOCAL INFILE 'admin1Codes.txt' INTO TABLE admin1Codes CHARACTER SET 'UTF8' (code, name);
 
 LOAD DATA LOCAL INFILE 'admin1CodesAscii.txt' INTO TABLE admin1CodesAscii CHARACTER SET 'UTF8' (code, name, nameAscii, geonameid);
 
 LOAD DATA LOCAL INFILE 'featureCodes.txt' INTO TABLE featureCodes CHARACTER SET 'UTF8' (code, name, description);
 
 LOAD DATA LOCAL INFILE 'timeZones.txt' INTO TABLE timeZones CHARACTER SET 'UTF8' IGNORE 1 LINES  (timeZoneId, GMT_offset, DST_offset);
 
 LOAD DATA LOCAL INFILE 'countryInfo-n.txt' INTO TABLE countryInfo CHARACTER SET 'UTF8' IGNORE 1 LINES  (iso_alpha2,iso_alpha3,iso_numeric,fips_code,name,capital,areaInSqKm,population,continent,languages,currency,geonameId);
 
 LOAD DATA LOCAL INFILE 'continentCodes.txt' INTO TABLE continentCodes CHARACTER SET 'UTF8' FIELDS TERMINATED BY ',' (code, name, geonameId);

run locally via ssh or console from the folder with the .txt files

> mysql -u root < import.sql -p
(change root accordingly) (-p is only if you have a password set)

Good luck!

giorgio79 wrote:

Here is the structure for admin2codes

Code:

 CREATE TABLE `geonames_admin2codes` (
   `code` char(10) default NULL,
   `name_local` text,
   `name` text NOT NULL,
   `geonameid` int(11) NOT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Some codes become truncated with char(10). We suggest char(40) or more

To correctly load the data as utf8 data, I had to add the character set specification to the load data file command as indicated below.

LOAD DATA INFILE 'data/geonames.org/allCountries.txt' INTO TABLE geoname CHARACTER SET UTF8 (geoname_id,name,ascii_name,alternate_names,lat,lon,fclass,fcode,country,cc2,admin1,admin2,admin3,admin4,population,alt,gtopo30,timezone,mod_date);

When i tried tried to dump the countryInfo.txt file, something went wrong. The table creation as described above here, is not the right table. It seems the countryInfo.txt file has been updated, so this won't work anymore.

Is anyone able to recreate this table? I am absolutely not an sql expert....

The countryInfo is structured differently from the contents of countryInfo.txt. The column positions for 'languages' and 'geonameId' need to be swapped around.

Also, the file 'countryInfo.txt' has a whole lot of comments that MySql is happy to turn into corrupted rows. My advice is to get rid of all but the last of the leading commented lines, so that the file starts thus:

Code:

 #ISO	ISO3	ISO-Numeric	fips	Country	Capital	Area(in sq km)	Population	Continent	tld	CurrencyCode	CurrencyName	Phone	Postal Code Format	Postal Code Regex	Languages	geonameid	neighbours	EquivalentFipsCode
 AD	AND	020	AN	Andorra	Andorra la Vella	468	84000	EU	.ad	EUR	Euro	376	AD###	^(?:AD)*(\d{3})$	ca	3041565	ES,FR

Following, are the corrected TABLE & LOAD DATA commands (as of the date of this posting):

Code:

 CREATE TABLE countryinfo (     
  iso_alpha2 char(2),           
  iso_alpha3 char(3),           
  iso_numeric integer,          
  fips_code varchar(3),         
  name varchar(200),            
  capital varchar(200),         
  areainsqkm double,            
  population integer,           
  continent char(2),            
  tld char(3),                  
  currency char(3),             
  currencyName char(20),        
  Phone char(10),               
  postalCodeFormat char(20),    
  postalCodeRegex char(20),     
  languages varchar(200),,      
  geonameId int                 
  neighbours char(20),          
  equivalentFipsCode char(10)   
 ) CHARACTER SET utf8;         
 
 LOAD DATA LOCAL INFILE 'tableData/countryInfo.txt' INTO TABLE countryInfo IGNORE 1 LINES (iso_alpha2,iso_alpha3,iso_numeric,fips_code,name,capital,areainsqkm,population,continent,tld,currency,currencyName,Phone,postalCodeFormat,postalCodeRegex,languages,geonameId,neighbours,equivalentFipsCode)