Messages posted by "nrauni"

Re:admin3Codes.txt AND admin4Codes.txt ??

nrauni — GMT

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

Re:Brazil with more two division

nrauni — GMT

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 with more two division

nrauni — GMT

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

admin3Codes.txt AND admin4Codes.txt ??

nrauni — GMT

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

Re:many repeated cities in brazil

nrauni — GMT

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

to improve script

nrauni — GMT

i need your help to improve this code http://201.86.7.131/geonames/lista.php Thank's Nei Rauni Santos

Re:many repeated cities in brazil

nrauni — GMT

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

Re:As to make this consultation??

nrauni — GMT

no update?

many repeated cities in brazil

nrauni — GMT

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

I made one script for importation, case somebody I want

nrauni — GMT

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

Re:As to make this consultation??

nrauni — GMT

Thank a lot

Re:As to make this consultation??

nrauni — GMT

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

As to make this consultation??

nrauni — GMT

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