Quelltext der Seite OpenGeoClassByChristianFigge

<big><syntaxhighlight lang="php" line="GESHI_NO_LINE_NUMBERS" highlight="9">

<?php 

/**
 * Yet another OpenGeo class
 * version 0.1
 * 
 * @author Christian Figge
 * @date 14.03.2012
 * @email info@flazer.net
 * @url http://flazer.net/
 * @url http://opengeodb.org/
 * This class uses the openGEO-Database
 * and supports Germany, Austria and Switzerland
 * (i didn't need more)
 * so feel free to add more.
 * 
 * For better performance I used a selfwritten filecache.
 * I commented this lines out, so for now everytime the database gets requested.
 * This produces a unnecessary load on your db-machine.
 * 
 * I'm using ADOdb for databasehandling, so if you want to use something different,
 * you have to implement your own handling. ($this->getDB())
 * 
 * example:
 * 
 * $oOpenGeo = new OpenGeo();
 * $oOpenGeo->setCountry('de');
 * $oOpenGeo->getProvinceByZipCode(20539);
 * returns:  
 * 	   array(
 * 			[loc_id] => 114
 * 			[text_val] => Hamburg
 *		)
 */

class OpenGeo {

	private $oDB = null;
	private $sCountry = 'de';
	private $aCountriesCfg = array(
		'name'	 		=> 500100000,
		'layer'			=> 400200000,
		'province'		=> 100300000,
		'locale' 		=> 'de',
		'zipID'	 		=> 500300000,
		'countryLvl' 	=> 'ld_lvl1',
		'countries' 	=> array(
			'de'	=> 105,
			'at'	=> 106,
			'ch'	=> 107
		)
	);
	
	/**
	 * Checks if the country is supported
	 * (de,at,ch)
	 * @param string $sCountry
	 * @return boolean
	 */
	public function checkSupportCountry($sCountry) {
		if(in_array($sCountry, array_keys($this->aCountriesCfg['countries']))) {
			return true;
		}
		return false;
	}

	
	/**
	 * Returns province for given zipCode
	 * @param integer $iZipCode
	 * @return array $aProvince
	 */
	public function getProvinceByZipCode($iZipCode) {
		
		$sTextType		= $this->aCountriesCfg['name'];
		$sCountryID		= $this->aCountriesCfg['countries'][$this->sCountry];
		$sZipID			= $this->aCountriesCfg['zipID'];
		$sProvinceID	= $this->aCountriesCfg['province'];
		$aProvince		= array();
		
// 		$sCacheKey = 'province_'.$this->sCountry.'_'.$iZipCode;
// 		$aProvince = System_Controller::getFilecache()->getData($sCacheKey);
		
		if(!is_array($aProvince) || empty($aProvince)) {
			$sql = <<<SQL
SELECT
	geodb_textdata.loc_id, geodb_textdata.text_val
FROM
	geodb_textdata
WHERE
	geodb_textdata.loc_id = (
		SELECT
			geodb_hierarchies.id_lvl3
		FROM
			geodb_hierarchies, geodb_textdata
		WHERE
			geodb_hierarchies.id_lvl2 = ?
			AND  geodb_textdata.text_type = ?
			AND geodb_textdata.text_val = ?
			AND geodb_textdata.loc_id = geodb_hierarchies.loc_id
		LIMIT 1
	)
	AND geodb_textdata.text_type = ?
SQL;
			$aProvince = $this->getDB()->getRow($sql, array($sCountryID, $sZipID, $iZipCode, $sTextType));
			
// 			System_Controller::getFilecache()->setData($sCacheKey, $aProvince, 900);
		}
		
		return $aProvince;
	}
	
	
	/**
	 * Returns city for given zipCode
	 * @param integer $iZipCode
	 * @return array $aCity
	 */
	public function getCityByZipCode($iZipCode) {
		
		$sTextType 		= $this->aCountriesCfg['name'];
		$sLayer 		= $this->aCountriesCfg['layer'];
		$sCountryID 	= $this->aCountriesCfg['countries'][$this->sCountry];
		$sZipID			= $this->aCountriesCfg['zipID'];
		$sLocale	 	= $this->aCountriesCfg['locale'];
		$aCity			= array();
		
// 		$sCacheKey = 'city'.$this->sCountry.'_'.$iZipCode;
// 		$aCity = System_Controller::getFilecache()->getData($sCacheKey);
		
		if(!is_array($aCity) || empty($aCity)) {
			$sql = <<<SQL
SELECT
	geodb_textdata.loc_id, geodb_textdata.text_val
FROM
	geodb_textdata
WHERE
	loc_id = (
		SELECT
			geodb_textdata.loc_id
		FROM
			geodb_textdata
		WHERE
			geodb_textdata.loc_id = (
				SELECT
					MIN(geodb_textdata.loc_id)
				FROM
					geodb_hierarchies, geodb_textdata
				WHERE
					geodb_hierarchies.id_lvl2 = ?
					AND  geodb_textdata.text_type = ?
					AND geodb_textdata.text_val = ?
					AND geodb_textdata.loc_id = geodb_hierarchies.loc_id
			)
			AND geodb_textdata.text_type = ?
		LIMIT 1
	)
	AND geodb_textdata.text_type = ?
	AND geodb_textdata.text_locale = ?
SQL;
			$aCity = $this->getDB()->getRow($sql, array($sCountryID, $sZipID, $iZipCode, $sLayer, $sTextType,$sLocale));
			
// 			System_Controller::getFilecache()->setData($sCacheKey, $aCity, 900);
		}
		return $aCity;
	}
	
	
	/**
	 * Returns all possible provinces for selected country (set via $this->setCountry())
	 * @return array $aProvinces
	 */
	public function getProvinces() {
		$sTextType 		= $this->aCountriesCfg['name'];
		$sCountryID 	= $this->aCountriesCfg['countries'][$this->sCountry];
		$sProvinceID 	= $this->aCountriesCfg['province'];
		
// 		$sCacheKey = 'provinces_'.$this->sCountry;
// 		$aProvinces = System_Controller::getFilecache()->getData($sCacheKey);
		
		if(!is_array($aProvinces) || empty($aProvinces)) {
			$sql = <<<SQL
SELECT
	DISTINCT geodb_textdata.loc_id, geodb_textdata.text_val 
FROM
	geodb_locations, geodb_hierarchies, geodb_textdata
WHERE
	geodb_locations.loc_type = 100300000
	AND geodb_locations.loc_id = geodb_hierarchies.id_lvl3
	AND geodb_locations.loc_id = geodb_textdata.loc_id
	AND geodb_textdata.text_type = ?
	AND geodb_hierarchies.id_lvl2 = ?
SQL;
			$aProvinces = $this->getDB()->getArray($sql, array($sTextType, $sCountryID));
			
// 			System_Controller::getFilecache()->setData($sCacheKey, $aProvinces, 900);
		}
		
		return $aProvinces;
	}
	
	
	public function setCountry($sCountry) {
		$this->sCountry = $sCountry;
	}
	
	/*
	 * Databasehandler
	 * Put your own databasehandling in here.
	 */
	private function getDB() {
		if(!$this->oDB) {
			$db = ADONewConnection('mysql');
			$db->Connect(_OPENGEO_DATABASE_HOST_, _OPENGEO_DATABASE_USER_, _OPENGEO_DATABASE_PASS_, _OPENGEO_DATABASE_NAME_);
			$db->SetFetchMode(ADODB_FETCH_ASSOC);
			$this->oDB = $db;
		}
		return $this->oDB;
	}
}


</syntaxhighlight></big>