loc_id=0; $this->Teilvon=false; $this->Ebene=0; $this->Type=false; $this->Plz=''; $this->Vorwahl=''; $this->Name=''; $this->Kfz=''; $this->Verw=''; $this->GemSchl=''; $this->Einwohner=0; $this->Flaeche=0; $this->Lat=false; $this->Lon=false; $this->cntgemeinden=0; } function getData($loc_id) { $sql = 'SELECT ta.loc_id,ta.text_type,tn.name,ta.text_val,ta.text_locale from '; $sql.= 'geodb_textdata ta left join geodb_type_names tn on tn.type_id=ta.text_type '; $sql.= 'where loc_id ='.$loc_id; $sql.= ' union all '; $sql.= 'SELECT loc_id,float_type,tn.name,cast(float_val as text),\'\' from '; $sql.= 'geodb_floatdata left join geodb_type_names tn on tn.type_id=float_type '; $sql.= 'where loc_id ='.$loc_id; $sql.= ' union all '; $sql.= 'SELECT loc_id,int_type,tn.name,cast(int_val as text),\'\' from '; $sql.= 'geodb_intdata left join geodb_type_names tn on tn.type_id=int_type '; $sql.= 'where loc_id ='.$loc_id; $sql.= ' union all '; $sql.= 'SELECT loc_id,coord_type,textcat(tn.name,\' lat\'),cast(lat as text),\'\' from '; $sql.= 'geodb_coordinates left join geodb_type_names tn on tn.type_id=coord_type '; $sql.= 'where loc_id ='.$loc_id; $sql.= ' union all '; $sql.= 'SELECT loc_id,coord_type,textcat(tn.name,\' lon\'),cast(lon as text),\'\' from '; $sql.= 'geodb_coordinates left join geodb_type_names tn on tn.type_id=coord_type '; $sql.= 'where loc_id ='.$loc_id; $rsort=$this->db->getAll($sql); $this->clean(); $this->loc_id=$loc_id; if ($rsort) foreach ($rsort as $row) { switch ($row['text_type']) { case 400100000 : $this->Teilvon=$row['text_val']; break; case 400200000 : $this->Ebene=$row['text_val']; break; case 400300000 : $this->Type=$row['text_val']; break; case 500100000 : if (empty($this->Name)) { $this->Name=$row['text_val']; } else if ($row['text_locale']=='de') { $this->Name=$row['text_val']; } break; case 500300000 : $this->Plz=(($this->plz)?", ":"").$row['text_val']; break; case 500400000 : $this->Vorwahl=$row['text_val']; break; case 500500000 : $this->Kfz=$row['text_val']; break; case 500600000 : $this->GemSchl=$row['text_val']; break; case 500700000 : $this->Verw=$row['text_val']; break; case 600700000 : $this->Einwohner=$row['text_val']; break; case 610000000 : $this->Flaeche=$row['text_val']; break; case 200100000 : if (substr($row["name"],-3)=='lat') { $this->Lat=$row['text_val']; } else { $this->Lon=$row['text_val']; } break; } } $sql='SELECT count(*) as cnt from geodb_textdata where text_val=\''.$this->loc_id.'\' and text_type = 400100000'; $rsort=$this->db->getAll($sql); $this->cntgemeinden=$rsort[0]['cnt']; } function MyGeo($db) { $this->db=$db; } function suchOrt($ort,$plz,$tel,$fuzzy=0,$ao="or") { $sql= 'select loc_id,text_type,name,text_val from geodb_textdata '; $sql.='ta left join geodb_type_names tn on tn.type_id=ta.text_type where loc_id in ('; $subsel='SELECT loc_id from geodb_textdata where '; $where=''; $type=array(); if ($fuzzy==1) { $f1='like'; $f2='%'; } else { $f1='='; $f2=''; } if ($ao == "and") { if ($ort<>"") { $where=$subsel.' UPPER(text_val) '.$f1.' \''.strtoupper($ort).$f2.'\' and text_type = \'500100000\' '; } if ($plz<>"") { $where.=((empty($where))?'':'INTERSECT ').$subsel.' text_val '.$f1.' \''.$plz.$f2.'\' and text_type = \'500300000\' '; } if ($tel<>"") { $where.=((empty($where))?'':'INTERSECT ').$subsel.' text_val '.$f1.' \''.$tel.$f2.'\' and text_type = \'500400000\' '; } } else { if ($ort<>"") { $where=$subsel.'( UPPER(text_val) '.$f1.' \''.strtoupper($ort).$f2.'\' and text_type = \'500100000\' ) '; } if ($plz<>"") { $where.=((empty($where))?$subsel:' OR ').'( text_val '.$f1.' \''.$plz.$f2.'\' and text_type = \'500300000\' ) '; } if ($tel<>"") { $where.=((empty($where))?subsel:' OR ').'( text_val '.$f1.' \''.$tel.$f2.'\' and text_type = \'500400000\' ) '; } } $sql.=$where.') order by loc_id,text_type'; $rs=$this->db->getAll($sql); return $rs; } function suchGemeinden($id) { $sql= 'select loc_id,text_type,name,text_val from geodb_textdata '; $sql.='ta left join geodb_type_names tn on tn.type_id=ta.text_type where '; $sql.='loc_id in (SELECT loc_id from geodb_textdata where text_val = \''.$id.'\' '; $sql.='and text_type = 400100000 ) and text_type in (500100000,500300000)'; $sql.='order by loc_id,text_type'; $rs=$this->db->getAll($sql); return $rs; } } function ebene($nr) { for ($i=1; $i<$nr; $i++) { $str.='-'; } return $str; } echo "Surfen durch die Geodaten
\n"; echo "[n] = weiterführende Links
\n"; echo "\n"; $geo=new MyGeo($db); if ($_GET["loc_id"]) { $geo->getData($_GET['loc_id']); echo ""; echo ""; echo ''; echo ""; echo ''; echo ""; if ($geo->Lat) { echo ''; echo ""; } if ($geo->cntgemeinden>0) { echo ''; echo "";}; echo ""; echo ""; while ($geo->Ebene>1) { $geo->getData($geo->Teilvon); echo ''; echo ''."\n"; } } else { $lastid=0;$lasttype=''; if ($_GET['gemid']>0) { $rs=$geo->suchGemeinden($_GET['gemid']); } else { $rs=$geo->suchOrt($_GET["ort"],$_GET["plz"],$_GET["tel"],$_GET["fuzzy"],$_GET["ao"]); } if ($rs) foreach($rs as $row) { if ($lastid<>$row["loc_id"]) { if ($lastid<>0); ''."\n"; $lastid=$row["loc_id"]; if ($lasttype<>$row["name"]) { $type="".$row["name"].":"; $lasttype=$row["name"];} else { $type=''; } echo '

".$geo->Plz.' '.$geo->Name."	".$geo->Type."
Bezeichnung	Type
'.$geo->Vorwahl.'	'.$geo->Kfz.'
Vorwahl	KFZ
'.$geo->Einwohner.'	'.$geo->Flaeche.'
Einwohner	Fläche qkm
'.(($geo->Lat>0)?'N':'S').$geo->Lat.' '.(($geo->Lon>0)?'E':'W').$geo->Lon.'	'.$geo->Verw.'
GEO-Koordinaten	Verwaltung
['.$geo->cntgemeinden.']
Zugehörige Gemeinden
Übergeordnet:	Type:
'.ebene($geo->Ebene).$geo->Name.'	'.$geo->Type.' ['.$geo->cntgemeinden.']
'.$type.$row['text_val'].' '; } else { if ($lasttype<>$row["name"]) { $type="".$row["name"].":"; $lasttype=$row["name"];} else { $type=''; } echo $type.$row['text_val'].' '; }; } } ?>

[Fenster schließen]