[Opengeopp-svn] SF.net SVN: opengeopp:[5] trunk/opengeopp/geonames/data

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 454-5900

Revision: 5
          http://opengeopp.svn.sourceforge.net/opengeopp/?rev=5&view=rev
Author:   denis_arnaud
Date:     2010-02-04 19:31:20 +0000 (Thu, 04 Feb 2010)

Log Message:
-----------
[Geonames] Re-structured a little bit the data for POR and Zip/Postal codes.

Modified Paths:
--------------
    trunk/opengeopp/geonames/data/getDataFromGeonamesWebsite.sh

Added Paths:
-----------
    trunk/opengeopp/geonames/data/admin/
    trunk/opengeopp/geonames/data/admin/updateGeonamesDatabase.sql

Removed Paths:
-------------
    trunk/opengeopp/geonames/data/por/admin/
    trunk/opengeopp/geonames/data/por/data/

Property Changed:
----------------
    trunk/opengeopp/geonames/data/por/
    trunk/opengeopp/geonames/data/zip/

Copied: trunk/opengeopp/geonames/data/admin/updateGeonamesDatabase.sql (from rev 4, trunk/opengeopp/geonames/data/por/data/updateGeonamesDatabase.sql)
===================================================================

--- trunk/opengeopp/geonames/data/admin/updateGeonamesDatabase.sql	                        (rev 0)
+++ trunk/opengeopp/geonames/data/admin/updateGeonamesDatabase.sql	2010-02-04 19:31:20 UTC (rev 5)
@@ -0,0 +1,44 @@
+#!/bin/bash
+# Script got from: http://forum.geonames.org/gforum/posts/list/15/732.page
+
+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
+

Modified: trunk/opengeopp/geonames/data/getDataFromGeonamesWebsite.sh
===================================================================
--- trunk/opengeopp/geonames/data/getDataFromGeonamesWebsite.sh	2010-02-04 19:04:43 UTC (rev 4)
+++ trunk/opengeopp/geonames/data/getDataFromGeonamesWebsite.sh	2010-02-04 19:31:20 UTC (rev 5)
@@ -1,6 +1,7 @@
 #!/bin/sh
 
 URL_CATCH=wget
+UNCOMPRESS="unzip -f"
 
 function retrieveFiles() {
 	if [ ! -d ${TARGET_DIR} ];
@@ -17,6 +18,15 @@
 			echo "Retrieving ${file} from ${BASE_URL}"
 			${URL_CATCH} ${BASE_URL}/${file}
 		fi
+		if [ -r ${file} ];
+		then
+			BASE_FILE=`basename ${file} .zip`
+			if [ "${file}" = "${BASE_FILE}.zip" ];
+			then
+				echo "Uncompressing, if necessary, ${file}"
+				${UNCOMPRESS} ${file}
+			fi
+		fi
 	done
 
 	cd -
@@ -25,8 +35,8 @@
 # Retrieve the data files for the Point Of Reference (POR), i.e., the main
 # Geonames database
 BASE_URL=http://download.geonames.org/export/dump
-FILES="allCountries.zip cities1000.zip cities5000.zip cities15000.zip alternateNames.zip admin1Codes.txt admin1CodesASCII.txt admin2Codes.txt iso-language codes.txt featureCodes.txt timeZones.txt countryInfo.txt userTags.zip"
-TARGET_DIR=por/data
+FILES="allCountries.zip alternateNames.zip cities1000.zip cities5000.zip cities15000.zip admin1Codes.txt admin1CodesASCII.txt admin2Codes.txt countryInfo.txt iso-languagecodes.txt featureCodes_en.txt no-country.zip timeZones.txt userTags.zip"
+TARGET_DIR=por
 retrieveFiles
 
 # Retrieve the data files for the Postal Codes (Zip)
@@ -36,5 +46,5 @@
 #
 BASE_URL=http://download.geonames.org/export/zip
 FILES="allCountries.zip"
-TARGET_DIR=zip/data
+TARGET_DIR=zip
 retrieveFiles


Property changes on: trunk/opengeopp/geonames/data/por
___________________________________________________________________
Added: svn:ignore
   + allCountries.*
alternateNames.*
admin1Codes.*
admin1CodesASCII.*
admin2Codes.*
cities1000.*
cities15000.*
cities5000.*
continentCodes.*
countryInfo.*
featureCodes_??.*
iso-languagecodes.*
no-country.*
null.*
timeZones.*
userTags.*
readme.txt




Property changes on: trunk/opengeopp/geonames/data/zip
___________________________________________________________________
Added: svn:ignore
   + allCountries.*




This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.


