GeoNames Forum

but, in geoname table has many rows that hasn't codes of admin3 and admin4

to catch the results i need to make 2 updates

//original 30
UPDATE `geoname` SET `admin1_code` = '19' WHERE `geoname`.`geonameid` =3392268 LIMIT 1 ;

//original 29
UPDATE `geoname` SET `admin1_code` = '10' WHERE `geoname`.`geonameid` =3462372 LIMIT 1 ;

Brazil has 27 division,

in admin1codes Brazil has 30.

two duplicate state

BR.19 Pernambuco
BR.30 Pernambuco
BR.10 Goias
BR.29 GoiÃ¡s

i.e with Pernambuco:

i think that the geonameid 3392268 with admin1_code = 30 is incorrect,
the correct admin1_code is 19. because your cities is refence from 19 e not 30.

debtor for the attention

Nei

i need this files, It's available?

becuse this query no give me correctly all results.

SELECT * FROM `admincodes1` t1 LEFT JOIN admincodes2 t2 ON (t1.code = LEFT(t2.code, 5) ) WHERE t1.code like "FR.%" ;

it's right?

thank's

Nei

marc wrote:

We did not invent these names. It is normal that villages have the same name in different areas and regions.
Brazil is large, it could make sense for you to also include ADM2 in the hierarchy.

Hi Mark,

You're right on large countries having multiple cities with the same name, but it is not the case here. The cities I listed are within the same state and if you notice the geonameid , they are pretty much sequencial.

Just to make sure, I did some tests with France, which is much smaller than Brazil. I found repeated entries as well. For instance:

L PPL 3033366 Bergouey ( population: 0 - alternatenames: 40250)
L PPL 3033367 Bergouey ( population: 120 - alternatenames: Bergouey,Burgue)

Note that their geonameid is sequencial and one of them has a popúlation of zero and a strange alternate name...

Do you think there might be a problem with the database?

The problem about using ADM2 is that different countries have different ADM levels. In our project we only need a second level hierarchy and we list all the cities right in. No need for extensive subdivisions.

I really appreciate your comments and help on this.

Nei

i need your help to improve this code
http://201.86.7.131/geonames/lista.php

Thank's

Nei Rauni Santos

look this http://201.86.7.131/geonames/lista.php

look this

http://201.86.7.131/geonames/lista.php

no update?

why it has as much repeated?

(geonameid is different)
i.e: (geonameid, name)

L 3452016 Quilombo
L 3452018 Quilombo
L 3452019 Quilombo
L 3452020 Quilombo
L 3452021 Quilombo
L 3452022 Quilombo
L 3452023 Quilombo

L 3451582 Retiro
L 3451583 Retiro
L 3451584 Retiro
L 3451585 Retiro
L 3451586 Retiro
L 3451588 Retiro
L 3451589 Retiro
L 3451590 Retiro
L 3451591 Retiro
L 3451599 Retiro

L 3451374 Ribeirão
L 3451375 Ribeirão
L 3451376 Ribeirão
L 3451377 Ribeirão
L 3451378 Ribeirão
L 3451379 Ribeirão
L 3451380 Ribeirão
L 3451353 Ribeirão

L 3450317 Santa Cruz
L 3450318 Santa Cruz
L 3450321 Santa Cruz
L 3450324 Santa Cruz
L 3450326 Santa Cruz
L 3450327 Santa Cruz
L 3450331 Santa Cruz
L 3450332 Santa Cruz
L 3450333 Santa Cruz
L 3450336 Santa Cruz
L 3450337 Santa Cruz

L 3450143 Santa Luzia
L 3450144 Santa Luzia
L 3450145 Santa Luzia
L 3450147 Santa Luzia
L 3450148 Santa Luzia
L 3450150 Santa Luzia
L 3450151 Santa Luzia
L 3450152 Santa Luzia
L 3476917 Santa Luzia

L 3449888 Santa Rita
L 3449892 Santa Rita
L 3449893 Santa Rita
L 3449894 Santa Rita
L 3449896 Santa Rita
L 3449897 Santa Rita
L 3449899 Santa Rita
L 3449901 Santa Rita
L 3449904 Santa Rita

L 3449638 Santo Antônio
L 3449639 Santo Antônio
L 3449647 Santo Antônio
L 3449649 Santo Antônio
L 3449650 Santo Antônio
L 3449651 Santo Antônio
L 3449653 Santo Antônio
L 3449654 Santo Antônio
L 3449655 Santo Antônio
L 3449656 Santo Antônio
L 3449658 Santo Antônio
L 3449659 Santo Antônio
L 3449662 Santo Antônio
L 3449663 Santo Antônio
L 3449664 Santo Antônio
L 3449665 Santo Antônio
L 3476922 Santo Antônio

filename: import.sh

#!/bin/bash
# http://download.geonames.org/export/dump/

clear

echo "--- inicialization of importation"

rm -rf admin1Codes.txt
echo "Downloading admin1Codes.txt from geonames"
wget -c http://download.geonames.org/export/dump/admin1Codes.txt

rm -rf admin2Codes.txt
echo "Downloading admin2Codes.txt from geonames"
wget -c http://download.geonames.org/export/dump/admin2Codes.txt

rm -rf countryInfo.txt
echo "Downloading countryInfo.txt from geonames"
wget -c http://download.geonames.org/export/dump/countryInfo.txt

rm -rf featureCodes.txt
echo "Downloading featureCodes.txt from geonames"
wget -c http://download.geonames.org/export/dump/featureCodes.txt

mv -f alternateNames.zip alternateNames-bkp.zip
echo "Downloading alternateNames.zip from geonames"
wget -c http://download.geonames.org/export/dump/alternateNames.zip
unzip -o alternateNames.zip

mv -f allCountries.zip allCountries-bkp.zip
echo "Downloading allCountries.zip from geonames"
wget -c http://download.geonames.org/export/dump/allCountries.zip
unzip -o allCountries.zip

#import mysql

echo "start importation of tables"

mysql -u root geoname < sql-import.sql

echo "--- finalizado script de importação"

#3 beeps
echo -e "\a"
echo -e "\a"
echo -e "\a"

------------------- OTHER FILES

filename: sql-import.sql

-- Author: Nei Rauni Santos nrauni@yahoo.com.br
-- execute this file with this command
-- mysql -u root geoname < sql-import.sql

SET character_set_client = 'utf8';
SET character_set_results = 'utf8';

-- criação da tabela main do geoname

CREATE TABLE IF NOT EXISTS geoname (
geonameid INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'integer id of record in geonames database',
name VARCHAR (200) NULL COMMENT 'name of geographical point',
asciiname VARCHAR(200) NULL COMMENT 'name of geographical point in plain ascii characters',
alternatenames TEXT NULL COMMENT 'alternatenames',
latitude DECIMAL( 20, 15 ) NULL COMMENT 'latitude in decimal degrees (wgs84)',
longitude DECIMAL( 20, 15 ) NULL COMMENT 'longitude in decimal degrees (wgs84)',
feature_class CHAR(1) NULL COMMENT 'see http://www.geonames.org/export/codes.html',
feature_code VARCHAR(10) NULL COMMENT 'see http://www.geonames.org/export/codes.html',
country_code CHAR(2) NULL COMMENT 'ISO-3166 2-letter country code, 2 characters',
cc2 VARCHAR(60) NULL COMMENT 'alternate country codes, comma separated, ISO-3166 2-letter country code',
admin1_code VARCHAR(20) NULL COMMENT 'fipscode (subject to change to iso code), isocode for the us and ch, see file admin1Codes.txt for display names of this code',
admin2_code VARCHAR(80) NULL COMMENT 'code for the second administrative division, a county in the US, see file admin2Codes.txt',
population INT NULL,
elevation INT NULL COMMENT 'in meters',
gtopo30 INT NULL COMMENT 'average elevation of 30x30 (ca 900mx900m) area in meters',
timezone VARCHAR(40) NOT NULL COMMENT 'the timezone id (see file timeZone.txt)',
modification DATE NULL COMMENT 'date : date of last modification'
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT = 'geoname table main';

-- criaçao da tabela alternatename

CREATE TABLE IF NOT EXISTS alternatename (
alternateNameId INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'the id of this alternate name',
geonameid INT(10) NULL COMMENT 'geonameId referring to id in table geoname',
isolanguage VARCHAR(4) NULL COMMENT 'iso 693 language code 2- or 3-characters; 4-characters post for postal codes and iata or icao for airport codes',
alternate_name VARCHAR(200) NULL COMMENT 'alternate name or name variant',
isPreferredName INT(1) NULL COMMENT '1 if this alternate name is an official/preferred name',
isShortName INT(1) NULL COMMENT '1, if this is a short name like California for (State of California)'
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT = 'alternate names';

-- criaçao da tabela continentcode

CREATE TABLE IF NOT EXISTS continentcode (
geonameid INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'the id of continents in geoname table',
name VARCHAR(100) NULL COMMENT 'name of continent',
code VARCHAR(2) NULL COMMENT 'code of continent'
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;

-- inserção/atualização dos continentes

REPLACE INTO `continentcode` ( `geonameid` , `name` , `code` ) VALUES ('6255146', 'Africa', 'AF');
REPLACE INTO `continentcode` ( `geonameid` , `name` , `code` ) VALUES ('6255147', 'Asia', 'AS');
REPLACE INTO `continentcode` ( `geonameid` , `name` , `code` ) VALUES ('6255148', 'Europa', 'EU');
REPLACE INTO `continentcode` ( `geonameid` , `name` , `code` ) VALUES ('6255149', 'North America', 'NA');
REPLACE INTO `continentcode` ( `geonameid` , `name` , `code` ) VALUES ('6255151', 'Oceania', 'OC');
REPLACE INTO `continentcode` ( `geonameid` , `name` , `code` ) VALUES ('6255150', 'South America', 'SA');
REPLACE INTO `continentcode` ( `geonameid` , `name` , `code` ) VALUES ('6255152', 'Antarctica', 'AN');

-- criaçao da tabela continentcode

CREATE TABLE IF NOT EXISTS admincodes1 (
code VARCHAR(15) NOT NULL PRIMARY KEY,
name VARCHAR(100) NULL
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE IF NOT EXISTS admincodes2 (
code VARCHAR(15) NOT NULL PRIMARY KEY,
name VARCHAR(100) NULL,
name2 VARCHAR(100) NULL,
geonameid INT(10) NULL
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;

-- criaçao da tabela continentcode
-- BR|BRA|076|BR|Brazil|Brasília|8511965.0|186112794|SA|pt-BR|BRL|3469034
-- 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

CREATE TABLE IF NOT EXISTS countryinfo (
iso_alpha2 CHAR(2) NULL,
iso_alpha3 CHAR(3) NULL,
iso_numeric INT(10) NULL,
fips CHAR(3) NULL,
name VARCHAR(200) NULL,
capital VARCHAR(200) NULL,
areainsqkm DECIMAL(20,1) NULL,
population INT(20) NULL,
continent CHAR(2) NULL,
languages VARCHAR(200) NULL,
other1 VARCHAR(3) NULL,
other2 INT(20) NULL
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Remark : the field 'alternatenames' in the table 'geoname' is a short version of the 'alternatenames' table. You probably don't need both.
-- If you don't need to know the language of a name variant, the field 'alternatenames' will be sufficient. If you need to know the language
-- of a name variant, then you will need to load the table 'alternatenames' and you can drop the column in the geoname table.

LOAD DATA INFILE '/home/nei/projects/geoname/geonames/countryInfo.txt' REPLACE INTO TABLE countryinfo FIELDS TERMINATED BY '\t';

LOAD DATA INFILE '/home/nei/projects/geoname/geonames/admin1Codes.txt' REPLACE INTO TABLE admincodes1 FIELDS TERMINATED BY '\t';

LOAD DATA INFILE '/home/nei/projects/geoname/geonames/admin2Codes.txt' REPLACE INTO TABLE admincodes2 FIELDS TERMINATED BY '\t';

LOAD DATA INFILE '/home/nei/projects/geoname/geonames/alternateNames.txt' REPLACE INTO TABLE alternatename FIELDS TERMINATED BY '\t';

LOAD DATA INFILE '/home/nei/projects/geoname/geonames/allCountries.txt' REPLACE INTO TABLE geoname FIELDS TERMINATED BY '\t';

Thank a lot

Hi Marc, Thanks a lot for the reply, you have some forecast of this correction?

Nei

DIVISION OF FRANÇA ( LIMOUSIN )

- FRANÇA
-- 2998268 LIMOUSIN
--- 3023532 DEPARTMENT DE LA CORREZE
---- 2971297 ARRONDISSEMENT D'USSEL
------ Canton de Bort-les-Orgues
------- Bort-les-Orgues
------- Margerides
------- Monestier-Port-Dieu
------- Confolent-Port-Dieu
------- Saint-Bonnet-près-Bort
------- Saint-Julien-près-Bort
------- Saint-Victour
------- Sarroux
------- Thalamy
------- Veyrières

I tried to make of the following form

----- CONTINENTE
$sql2 = "SELECT t1.* FROM geoname as t1
LEFT JOIN countryinfo as t2 ON ( t1.geonameid = t2.other2 )
WHERE feature_class = 'A' AND feature_code = 'PCLI' AND t2.continent LIKE 'EU' ";

----- GET ADM1
$sql3 = "SELECT * FROM geoname WHERE feature_class LIKE 'A' AND feature_code LIKE 'ADM1' AND country_code LIKE '".$reg2['country_code']."' ";

----- GET ADM2
$sql4 = "SELECT t1.geonameid, t1.feature_code, t1.name, admin1_code, admin2_code FROM geoname as t1 LEFT JOIN admincodes2 as t2 ON (t1.geonameid = t2.geonameid) WHERE feature_class = 'A' AND feature_code = 'ADM2' AND t2.code LIKE '".$reg2['country_code'].".".$reg3['admin1_code']."%' AND admin1_code = '".$reg3['admin1_code']."' ";

---- GET ADM3
$sql5 = "SELECT t1.geonameid, t1.feature_code, t1.name, t1.admin1_code, t1.admin2_code FROM geoname as t1 LEFT JOIN admincodes2 as t2 ON (t1.geonameid = t2.geonameid) WHERE feature_class = 'A' AND feature_code = 'ADM3' AND country_code = '".$reg2['country_code']."' AND admin1_code = '".$reg4['admin1_code']."' AND admin2_code = '".$reg4['admin2_code']."' ";

----GET ADM4
$sql6 = "SELECT t1.geonameid, t1.feature_code, t1.name FROM geoname as t1 LEFT JOIN admincodes2 as t2 ON (t1.geonameid = t2.geonameid) WHERE feature_class = 'A' AND feature_code = 'ADM4' AND country_code = '".$reg2['country_code']."' AND admin1_code = '".$reg5['admin1_code']."' AND admin2_code = '".$reg5['admin2_code']."' LIMIT 100 ";

my tree is this:

L 2998268 ADM1 Limousin
+ 3 divisoes
L 3023532 ADM2 Département de la Corrèze
+ 3 divisoes
L 2971297 ADM3 Arrondissement d'Ussel
L 2971481 ADM3 Arrondissement de Tulle
L 3029973 ADM3 Arrondissement de Brive-la-Gaillarde
L 3022516 ADM2 Département de la Creuse
+ 2 divisoes
L 3014382 ADM3 Arrondissement de Guéret
L 3036293 ADM3 Arrondissement d'Aubusson
L 3013719 ADM2 Département de la Haute-Vienne
+ 3 divisoes
L 2983290 ADM3 Arrondissement de Rochechouart
L 2998285 ADM3 Arrondissement de Limoges
L 3033765 ADM3 Arrondissement de Bellac

my query 4 returns the equal data for each adm3
this is the correct form to mount all the structure?
as to make to only search adm4 of one specify adm3?

Thank's

Nei