<?php
/*************************************************************
Copyright Virgilio Lino 2011 Geozonedb.
Based on data taken from geonames.org
The framework and sql files are released under dual license,
Creative Commons Attribution 3.0 License license and gpl license 2.
Thanks to www.geonames.org for offering this usefull service, data about countries is taken from
geonames.org under the Creative Commons Attribution 3.0 License
Version 0.4 please see readme for changes
**************************************************************/
require('includes/engine.php');
$locale=false;if(isset($_GET['locale']))$locale=$_GET['locale'];
$geo=new geoZoneDB($locale,false);
//exit(var_dump($geo));
$geo->loadZones();
class Zone {
private $iso=false;
private $isoNumeric;
private $UID;
private $engine;
public function __construct($engine='MyISAM',$iso='') {
if($iso) {
$this->iso=$iso;
$this->setZone($iso);
}
$this->UID=(float)1;
$this->engine=$engine;
}
private function __prepareString($string) {
if(mb_detect_encoding($string)=='UTF-8') {
$string=utf8_decode($string);
}
return ucwords(mb_convert_case(str_replace(array("(",")"),"", str_replace("'","\'",$string)), MB_CASE_LOWER));
}
public function setZone($iso) {
if($iso) {
echo "confronto Iso:$iso con ".$this->iso;
if($iso!=$this->iso) {
$qCreated=xQuery("SELECT 1 FROM ".$iso." LIMIT 1");
if(!$qCreated) {
xQuery("CREATE TABLE IF NOT EXISTS ".$iso." ( `zone_ID` int(4) unsigned NOT NULL, `postalCode` varchar(10) NOT NULL, `Name` varchar(180) NOT NULL, `StateName` varchar(100) NOT NULL, `StateCode` varchar(20) NOT NULL, `ProvinceName` varchar(100) NOT NULL, `ProvinceCode` varchar(20) NOT NULL, `CommunityName` varchar(100) NOT NULL, `CommunityCode` varchar(20) NOT NULL, `latitude` decimal(6,2) NOT NULL, `longitude` decimal(6,2) NOT NULL, `accuracy` tinyint(1) unsigned NOT NULL, PRIMARY KEY (`zone_ID`) ) ENGINE=".$this->engine." DEFAULT CHARSET=utf8");
xQuery("ALTER TABLE ".$iso." ADD FULLTEXT(Name)");
xQuery("ALTER TABLE ".$iso." ADD FULLTEXT(StateName)");
xQuery("ALTER TABLE ".$iso." ADD FULLTEXT(ProvinceName)");
xQuery("ALTER TABLE ".$iso." ADD FULLTEXT(CommunityName)");
$q=xQuery("SELECT count(*) FROM zoneToCountry WHERE ISO='".$iso."'");
if($q->fields[0]==0) {
//xQuery("UPDATE zoneToCountry SET firstZone_ID='".$this->UID."' WHERE ISO='".$iso."'");
//} else {
xQuery("INSERT INTO zoneToCountry VALUES ('".$this->UID."','".$iso."')");
}
echo "<div><strong>$iso</strong> Table created </div>";
} else {
echo "<div><strong>$iso</strong> Already exists </div>";
}
$this->iso=$iso;
$qIsoNum=xQuery("SELECT ISONumeric FROM country WHERE ISO='".$iso."'");
$this->isoNumeric=$qIsoNum->fields[0];
}
} /*else {
$qLastID=xQuery("SELECT ISO FROM zoneToCountry ORDER BY firstZone_ID DESC LIMIT 0,1");
$iso=$qLastID->fields[0];
if($iso!='') {
$qLastID=xQuery("SELECT zone_ID FROM ".$iso." ORDER BY zone_ID DESC LIMIT 0,1");
$this->UID=(float)($qLastID->fields[0]+1);
}
}*/
}
private function Index($iso) {
}
public function add($columns) {
echo "<br />Working on table:".$this->iso."....".$this->__prepareString($columns[2])." - ".$this->__prepareString($columns[3]);
xQuery("INSERT INTO ".$this->iso." VALUES ('".$this->UID."','".$columns[1]."','".
$this->__prepareString($columns[2])."','".
$this->__prepareString($columns[3])."','".
$columns[4]."','".
$this->__prepareString($columns[5])."','".
$columns[6]."','".
$this->__prepareString($columns[7],1)."','".
$columns[8]."','".
$columns[9]."','".
$columns[10]."','".
$columns[11]."')");
$this->UID=(float)$this->UID+1;
}
public function iso() { return $this->iso; }
public function isoNumeric() { return $this->isoNumeric; }
public function UID() { return $this->UID; }
public function setZoneUID($UID) { $this->UID=$UID; }
public function engine() { return $this->engine; }
}
class geoZoneDB {
public $zone;
private $locale;
private $path;
private $zFile;
private $zoneUID;
private $engine; //for know i'm just working on mysql InnoDB or MyIsam(i'm trying on version 0.4 Text Capabilities Search)
function __construct($locale=false,$reload=false,$path='./',$engine='MyISAM') {
if($locale)$this->setLocale($locale);
$this->setZonesPath($path);
$this->engine=$engine;
//if used for first time creates the first three tables of the database
$this->loadCountries($reload);
$this->zone=&new Zone($engine);
}
public function loadZones() {
$error=false;
if(!is_file($this->path.'allCountries.txt')) {
$error=true;
$zip = new ZipArchive;
$zip->open($this->path.'allCountries.zip');
$p=fileperms('./');
if(($p & 0x0100)||($p & 0x0020)||($p & 0x0004)) {
if($zip->extractTo('./')) {
$zip->close();
$error=!is_file($this->path.'allCountries.txt');
}
}
}
if($error) {
die("Error trying to unzip allCountries.zip file, maybe in your php configuration you don't have zip extension installed, or the directory is not writeble. <br />However a simple solution could be:<br />
1.Just unzip the file by yourself, you may find allCountries.zip in the root directory of geozonedb extract to allCountries.txt or if you want a newer files you may download the file from:<br/>
2.<a href=\"http://download.geonames.org/export/zip/allCountries.zip\">http://download.geonames.org/export/zip/allCountries.zip</a> and unzip to the root path(if not specified another)
3.change the permission of the root directory where resides the geozonedb files to writeble.");
} else {
$h = fopen($this->path.'allCountries.txt', 'r');
$r = fgets($h, 512);
$i=(float)0;$p=(float)0;
if(isset($_GET['p'])) {
$p=(float)$_GET['p'];
while($i<$p&&!feof($h)) {
fgets($h, 512);
$i=(float)($i+1);
}
$r = fgets($h, 512);
$this->zone->setZoneUID($i);
//$this->zone->setZone($_GET['piso']);
//$pIso=$_GET['piso'];
//$isoNumeric=$_GET['isonumeric'];
}
$fine=(float)($p+1000);
while ($this->zone->UID()<$fine&&!feof($h)) {
$columns=explode("\t",$r);
$iso=$columns[0];
$this->zone->setZone($iso);
$this->zone->add($columns);
$r = fgets($h, 512);
}
if(!feof($h)) {
fclose($h);
?><script type="text/javascript"> setTimeout("document.location='geozonedb.php?locale=<?=$this->getLocale();?>&p=<?=$this->zone->UID();?>&piso=<?=$this->zone->iso();?>&isonumeric=<?=$isoNumeric;?>'",100);
</script><?php
} else {
fclose($h);
}
}
}
public function setLocale($locale) {
$this->locale=$locale;
}
public function getLocale() {
return $this->locale;
}
public function setZonesPath($path) {
$this->path=$path;
}
public function getZonesPath($path) {
return $this->path;
}
// this function loads country data in the country table and country info table
//if refill==true we remove every country data and recreates it
public function loadCountries($refill=false) {
$lh='';
$tmp=xQuery("SELECT count(*) from country");
if($tmp) {
if($refill) {
xQuery("TRUNCATE TABLE country");
xQuery("TRUNCATE TABLE countryInfo");
xQuery("TRUNCATE TABLE zoneToCountry");
$tmp=false;
}
}
if(!$tmp) {
xQuery("CREATE TABLE `country` ( `ISONumeric` smallint(2) unsigned zerofill NOT NULL, `ISO` char(2) NOT NULL, `Country` varchar(255) NOT NULL, PRIMARY KEY (`ISONumeric`)) ENGINE=".$this->engine." DEFAULT CHARSET=utf8; ");
xQuery("CREATE TABLE `countryInfo` ( `ISONumeric` smallint(2) unsigned zerofill NOT NULL, `ISO3` char(3) NOT NULL, `FIPS` char(2) NOT NULL, `Capital` varchar(163) NOT NULL, `Area` int(4) NOT NULL, `Population` int(4) unsigned NOT NULL,
`Continent` char(2) NOT NULL, `tld` char(3) NOT NULL, `CurrencyCode` char(3) NOT NULL, `CurrencyName` varchar(25) NOT NULL, `Phone` varchar(10) NOT NULL, `PostalCodeFormat` varchar(10) NOT NULL, `PostalCodeRegex` varchar(50) NOT NULL, `Languages` varchar(10) NOT NULL, `GeonameID` int(3) unsigned NOT NULL, `Neighbours` varchar(25) NOT NULL, `EquivalentFips` char(3) NOT NULL, PRIMARY KEY (`ISONumeric`), KEY `ISO3` (`ISO3`,`Capital`)) ENGINE=".$this->engine." DEFAULT CHARSET=utf8;");
xQuery("CREATE TABLE `zoneToCountry` (`firstZone_ID` int(4) unsigned NOT NULL, `ISO` char(2) NOT NULL, PRIMARY KEY (`firstZone_ID`)) ENGINE=".$this->engine." DEFAULT CHARSET=utf8;");
}
if(!$tmp) {
if($this->locale) {
$pathLocale='http://ws.geonames.org/countryInfoCSV?lang='.$this->locale;
$lh=fopen($pathLocale,'r');
$lr=fgets($lh,512);
while(!feof($lh)) {
$columns=explode("\t",$lr);
$translated[$columns[0]]=array('name'=>$columns[4],'capital'=>$columns[5]);
$lr=fgets($lh,512);
}
}
$path='http://download.geonames.org/export/dump/countryInfo.txt';
$h = fopen($path, 'r');
$r = fgets($h, 512);
while (!feof($h)) {
$r = fgets($h, 512);
while(substr($r,0,1)=='#')$r = fgets($h, 512);
$columns=explode("\t",$r);
$iso=$columns[0];
if($lh&&isset($translated[$iso])) {
$countryName=str_replace("'","\'",$translated[$iso]['name']);
$capital=str_replace("'","\'",$translated[$iso]['capital']);
} else {
$countryName=str_replace("'","\'",$columns[4]);
$capital=str_replace("'","\'",$columns[5]);
}
xQuery("INSERT INTO country VALUES ('".$columns[2]."','".$iso."','".$countryName."')");
xQuery("INSERT INTO countryInfo VALUES ('".$columns[2]."','".$columns[1]."','".$columns[3]."','".$capital."','".$columns[6]."','".$columns[7]."','".$columns[8]."','".substr($columns[9],1)."','".$columns[10]."','".$columns[11]."','".$columns[12]."','".$columns[13]."','".$columns[14]."','".$columns[15]."','".$columns[16]."','".$columns[17]."','".$columns[18]."')");
$pFips=$fips;
}
fclose($h);
echo "OK countries loaded in the database, there should be in the country and countryinfo tables every world country info";
}
}
}
|
Download
