OpenGeoClassByChristianFigge

OpenGeoDB & GISWiki - Das freie Portal für Geoinformatik (GIS)
Wechseln zu: Navigation, Suche
  1. <?php 
  2.  
  3. /**
  4.  * Yet another OpenGeo class
  5.  * version 0.1
  6.  * 
  7.  * @author Christian Figge
  8.  * @date 14.03.2012
  9.  * @email info@flazer.net
  10.  * @url http://flazer.net/
  11.  * @url http://opengeodb.org/
  12.  * This class uses the openGEO-Database
  13.  * and supports Germany, Austria and Switzerland
  14.  * (i didn't need more)
  15.  * so feel free to add more.
  16.  * 
  17.  * For better performance I used a selfwritten filecache.
  18.  * I commented this lines out, so for now everytime the database gets requested.
  19.  * This produces a unnecessary load on your db-machine.
  20.  * 
  21.  * I'm using ADOdb for databasehandling, so if you want to use something different,
  22.  * you have to implement your own handling. ($this->getDB())
  23.  * 
  24.  * example:
  25.  * 
  26.  * $oOpenGeo = new OpenGeo();
  27.  * $oOpenGeo->setCountry('de');
  28.  * $oOpenGeo->getProvinceByZipCode(20539);
  29.  * returns:  
  30.  * 	   array(
  31.  * 			[loc_id] => 114
  32.  * 			[text_val] => Hamburg
  33.  *		)
  34.  */
  35.  
  36. class OpenGeo {
  37.  
  38. 	private $oDB = null;
  39. 	private $sCountry = 'de';
  40. 	private $aCountriesCfg = array(
  41. 		'name'	 		=> 500100000,
  42. 		'layer'			=> 400200000,
  43. 		'province'		=> 100300000,
  44. 		'locale' 		=> 'de',
  45. 		'zipID'	 		=> 500300000,
  46. 		'countryLvl' 	=> 'ld_lvl1',
  47. 		'countries' 	=> array(
  48. 			'de'	=> 105,
  49. 			'at'	=> 106,
  50. 			'ch'	=> 107
  51. 		)
  52. 	);
  53.  
  54. 	/**
  55. 	 * Checks if the country is supported
  56. 	 * (de,at,ch)
  57. 	 * @param string $sCountry
  58. 	 * @return boolean
  59. 	 */
  60. 	public function checkSupportCountry($sCountry) {
  61. 		if(in_array($sCountry, array_keys($this->aCountriesCfg['countries']))) {
  62. 			return true;
  63. 		}
  64. 		return false;
  65. 	}
  66.  
  67.  
  68. 	/**
  69. 	 * Returns province for given zipCode
  70. 	 * @param integer $iZipCode
  71. 	 * @return array $aProvince
  72. 	 */
  73. 	public function getProvinceByZipCode($iZipCode) {
  74.  
  75. 		$sTextType		= $this->aCountriesCfg['name'];
  76. 		$sCountryID		= $this->aCountriesCfg['countries'][$this->sCountry];
  77. 		$sZipID			= $this->aCountriesCfg['zipID'];
  78. 		$sProvinceID	= $this->aCountriesCfg['province'];
  79. 		$aProvince		= array();
  80.  
  81. // 		$sCacheKey = 'province_'.$this->sCountry.'_'.$iZipCode;
  82. // 		$aProvince = System_Controller::getFilecache()->getData($sCacheKey);
  83.  
  84. 		if(!is_array($aProvince) || empty($aProvince)) {
  85. 			$sql = <<<SQL
  86. SELECT
  87. 	geodb_textdata.loc_id, geodb_textdata.text_val
  88. FROM
  89. 	geodb_textdata
  90. WHERE
  91. 	geodb_textdata.loc_id = (
  92. 		SELECT
  93. 			geodb_hierarchies.id_lvl3
  94. 		FROM
  95. 			geodb_hierarchies, geodb_textdata
  96. 		WHERE
  97. 			geodb_hierarchies.id_lvl2 = ?
  98. 			AND  geodb_textdata.text_type = ?
  99. 			AND geodb_textdata.text_val = ?
  100. 			AND geodb_textdata.loc_id = geodb_hierarchies.loc_id
  101. 		LIMIT 1
  102. 	)
  103. 	AND geodb_textdata.text_type = ?
  104. SQL;
  105. 			$aProvince = $this->getDB()->getRow($sql, array($sCountryID, $sZipID, $iZipCode, $sTextType));
  106.  
  107. // 			System_Controller::getFilecache()->setData($sCacheKey, $aProvince, 900);
  108. 		}
  109.  
  110. 		return $aProvince;
  111. 	}
  112.  
  113.  
  114. 	/**
  115. 	 * Returns city for given zipCode
  116. 	 * @param integer $iZipCode
  117. 	 * @return array $aCity
  118. 	 */
  119. 	public function getCityByZipCode($iZipCode) {
  120.  
  121. 		$sTextType 		= $this->aCountriesCfg['name'];
  122. 		$sLayer 		= $this->aCountriesCfg['layer'];
  123. 		$sCountryID 	= $this->aCountriesCfg['countries'][$this->sCountry];
  124. 		$sZipID			= $this->aCountriesCfg['zipID'];
  125. 		$sLocale	 	= $this->aCountriesCfg['locale'];
  126. 		$aCity			= array();
  127.  
  128. // 		$sCacheKey = 'city'.$this->sCountry.'_'.$iZipCode;
  129. // 		$aCity = System_Controller::getFilecache()->getData($sCacheKey);
  130.  
  131. 		if(!is_array($aCity) || empty($aCity)) {
  132. 			$sql = <<<SQL
  133. SELECT
  134. 	geodb_textdata.loc_id, geodb_textdata.text_val
  135. FROM
  136. 	geodb_textdata
  137. WHERE
  138. 	loc_id = (
  139. 		SELECT
  140. 			geodb_textdata.loc_id
  141. 		FROM
  142. 			geodb_textdata
  143. 		WHERE
  144. 			geodb_textdata.loc_id = (
  145. 				SELECT
  146. 					MIN(geodb_textdata.loc_id)
  147. 				FROM
  148. 					geodb_hierarchies, geodb_textdata
  149. 				WHERE
  150. 					geodb_hierarchies.id_lvl2 = ?
  151. 					AND  geodb_textdata.text_type = ?
  152. 					AND geodb_textdata.text_val = ?
  153. 					AND geodb_textdata.loc_id = geodb_hierarchies.loc_id
  154. 			)
  155. 			AND geodb_textdata.text_type = ?
  156. 		LIMIT 1
  157. 	)
  158. 	AND geodb_textdata.text_type = ?
  159. 	AND geodb_textdata.text_locale = ?
  160. SQL;
  161. 			$aCity = $this->getDB()->getRow($sql, array($sCountryID, $sZipID, $iZipCode, $sLayer, $sTextType,$sLocale));
  162.  
  163. // 			System_Controller::getFilecache()->setData($sCacheKey, $aCity, 900);
  164. 		}
  165. 		return $aCity;
  166. 	}
  167.  
  168.  
  169. 	/**
  170. 	 * Returns all possible provinces for selected country (set via $this->setCountry())
  171. 	 * @return array $aProvinces
  172. 	 */
  173. 	public function getProvinces() {
  174. 		$sTextType 		= $this->aCountriesCfg['name'];
  175. 		$sCountryID 	= $this->aCountriesCfg['countries'][$this->sCountry];
  176. 		$sProvinceID 	= $this->aCountriesCfg['province'];
  177.  
  178. // 		$sCacheKey = 'provinces_'.$this->sCountry;
  179. // 		$aProvinces = System_Controller::getFilecache()->getData($sCacheKey);
  180.  
  181. 		if(!is_array($aProvinces) || empty($aProvinces)) {
  182. 			$sql = <<<SQL
  183. SELECT
  184. 	DISTINCT geodb_textdata.loc_id, geodb_textdata.text_val 
  185. FROM
  186. 	geodb_locations, geodb_hierarchies, geodb_textdata
  187. WHERE
  188. 	geodb_locations.loc_type = 100300000
  189. 	AND geodb_locations.loc_id = geodb_hierarchies.id_lvl3
  190. 	AND geodb_locations.loc_id = geodb_textdata.loc_id
  191. 	AND geodb_textdata.text_type = ?
  192. 	AND geodb_hierarchies.id_lvl2 = ?
  193. SQL;
  194. 			$aProvinces = $this->getDB()->getArray($sql, array($sTextType, $sCountryID));
  195.  
  196. // 			System_Controller::getFilecache()->setData($sCacheKey, $aProvinces, 900);
  197. 		}
  198.  
  199. 		return $aProvinces;
  200. 	}
  201.  
  202.  
  203. 	public function setCountry($sCountry) {
  204. 		$this->sCountry = $sCountry;
  205. 	}
  206.  
  207. 	/*
  208. 	 * Databasehandler
  209. 	 * Put your own databasehandling in here.
  210. 	 */
  211. 	private function getDB() {
  212. 		if(!$this->oDB) {
  213. 			$db = ADONewConnection('mysql');
  214. 			$db->Connect(_OPENGEO_DATABASE_HOST_, _OPENGEO_DATABASE_USER_, _OPENGEO_DATABASE_PASS_, _OPENGEO_DATABASE_NAME_);
  215. 			$db->SetFetchMode(ADODB_FETCH_ASSOC);
  216. 			$this->oDB = $db;
  217. 		}
  218. 		return $this->oDB;
  219. 	}
  220. }