<?php
/* * * * * * * * * * * * * * * * * * * * * * * * *
Klasse koordinatensuche
* * * * * * * * * * * * * * * * * * * * * * * * */
class koordinatensuche {
// Erdradius in Kilometern
private $Erdradius = 6371;
// mysql link identifier
private $db;
// Datentabelle
private $table = false;
// Fehler zeigen?
public $zeigeFehler = true;
public function __construct($db, $table = 'koordinaten') {
if (!is_resource($db) || get_resource_type($db) != 'mysql link') {
trigger_error('Keine MySQL-Ressource übergeben', E_USER_ERROR);
}
$this->db = $db;
$this->table = $table;
// leere Koordinaten in Tabelle füllen
$sql = 'SELECT `id`, `straße`, `Ortname´,´plz´
FROM `' . $this->table . '`
WHERE
`KoordX` = "0"
AND `KoordY` = "0"
AND `KoordZ` = "0"
';
$re = mysql_query($sql, $this->db);
while ($rd = mysql_fetch_object($re)) {
if (!$this->StraßeKoord($rd->straße, $lon, $lat)) {
if ($this->zeigeFehler) {
trigger_error('Straße ' . $rd->straße . ' konnte nicht zugeordnet werden', E_USER_NOTICE);
}
continue;
}
$this->Kugel2Kartesisch($lon, $lat, $x, $y, $z);
$sql = 'UPDATE `' . $this->table . '`
SET
`Longitude` = "' . $lon . '",
`Latitude` = "' . $lat . '",
`KoordX` = "' . $x . '",
`KoordY` = "' . $y . '",
`KoordZ` = "' . $z . '"
WHERE
`ID` = "' . (int)$rd->ID . '"
';
mysql_query($sql, $this->db);
}
$re1 = mysql_query($sql, $this->db);
while ($rd = mysql_fetch_object($re)) {
if (!$this->OrtnameKoord($rd->Ortname, $lon, $lat)) {
if ($this->zeigeFehler) {
trigger_error('Ortname ' . $rd->Ortname . ' konnte nicht zugeordnet werden', E_USER_NOTICE);
}
continue;
}
$this->Kugel2Kartesisch($lon, $lat, $x, $y, $z);
$sql = 'UPDATE `' . $this->table . '`
SET
`Longitude` = "' . $lon . '",
`Latitude` = "' . $lat . '",
`KoordX` = "' . $x . '",
`KoordY` = "' . $y . '",
`KoordZ` = "' . $z . '"
WHERE
`ID` = "' . (int)$rd->ID . '"
';
mysql_query($sql, $this->db);
}
$re2 = mysql_query($sql, $this->db);
while ($rd = mysql_fetch_object($re)) {
if (!$this->Plz2Koord($rd->plz, $lon, $lat)) {
if ($this->zeigeFehler) {
trigger_error('PLZ ' . $rd->plz . ' konnte nicht zugeordnet werden', E_USER_NOTICE);
}
continue;
}
$this->Kugel2Kartesisch($lon, $lat, $x, $y, $z);
$sql = 'UPDATE `' . $this->table . '`
SET
`Longitude` = "' . $lon . '",
`Latitude` = "' . $lat . '",
`KoordX` = "' . $x . '",
`KoordY` = "' . $y . '",
`KoordZ` = "' . $z . '"
WHERE
`ID` = "' . (int)$rd->ID . '"
';
mysql_query($sql, $this->db);
}
}
public function Kugel2Kartesisch($lon, $lat, &$x, &$y, &$z) {
$lambda = $lon * pi() / 180;
$phi = $lat * pi() / 180;
$x = $this->Erdradius * cos($phi) * cos($lambda);
$y = $this->Erdradius * cos($phi) * sin($lambda);
$z = $this->Erdradius * sin($phi);
return true;
}
public function StraßeKoord($straße, &$lon, &$lat) {
$sql = 'SELECT
coo.lon,
coo.lat
FROM geodb_coordinates AS coo
INNER JOIN geodb_textdata AS textdata
ON textdata.loc_id = coo.loc_id
WHERE
textdata.text_val = "' . mysql_real_escape_string($straße, $this->db) . '"
AND textdata.text_type = "500100000"
LIMIT 1';
$re = mysql_query($sql, $this->db);
if (mysql_num_rows($re) != 1) {
return false;
}
list($lon, $lat) = mysql_fetch_row($re);
return true;
}
public function OrtnameKoord($Ortname, &$lon, &$lat) {
$sql = 'SELECT
coo.lon,
coo.lat
FROM geodb_coordinates AS coo
INNER JOIN geodb_textdata AS textdata
ON textdata.loc_id = coo.loc_id
WHERE
textdata.text_val = "' . mysql_real_escape_string($Ortname, $this->db) . '"
AND textdata.text_type = "500100000"
LIMIT 1';
$re = mysql_query($sql, $this->db);
if (mysql_num_rows($re) != 1) {
return false;
}
list($lon, $lat) = mysql_fetch_row($re);
return true;
}
public function Plz2Koord($plz, &$lon, &$lat) {
$sql = 'SELECT
coo.lon,
coo.lat
FROM geodb_coordinates AS coo
INNER JOIN geodb_textdata AS textdata
ON textdata.loc_id = coo.loc_id
WHERE
textdata.text_val = "' . mysql_real_escape_string($plz, $this->db) . '"
AND textdata.text_type = "500300000"
LIMIT 1';
$re = mysql_query($sql, $this->db);
if (mysql_num_rows($re) != 1) {
return false;
}
list($lon, $lat) = mysql_fetch_row($re);
return true;
}
public function Suche($straße,$Ortname,$plz, $Radius, $Spalten = array(), $Reihenfolge = false, $Richtung = 'ASC') {
if (!is_array($Spalten) || count($Spalten) == 0) {
$Spalten = '*';
} else {
$Spalten = '`' . implode('`, `', $Spalten) . '`';
}
if (!$this->straßeKoord($straße, $lon, $lat)) {
if ($this->zeigeFehler) {
trigger_error('Straße ' . $straße . ' konnte nicht zugeordnet werden', E_USER_NOTICE);
}
return false;
}
if (!$this->OrtnameKoord($Ortname, $lon, $lat)) {
if ($this->zeigeFehler) {
trigger_error('Ortname ' . $Ortname . ' konnte nicht zugeordnet werden', E_USER_NOTICE);
}
return false;
}
if (!$this->Plz2Koord($plz, $lon, $lat)) {
if ($this->zeigeFehler) {
trigger_error('Postleitzahl ' . $plz . ' konnte nicht zugeordnet werden', E_USER_NOTICE);
}
return false;
}
$this->Kugel2Kartesisch($lon, $lat, $UrsprungX, $UrsprungY, $UrsprungZ);
$sql = 'SELECT ' . $Spalten . '
FROM `' . $this->table . '`
WHERE
KoordX >= ' . ($UrsprungX - $Radius) . '
AND KoordX <= ' . ($UrsprungX + $Radius) . '
AND KoordY >= ' . ($UrsprungY - $Radius) . '
AND KoordY <= ' . ($UrsprungY + $Radius) . '
AND KoordZ >= ' . ($UrsprungZ - $Radius) . '
AND KoordZ <= ' . ($UrsprungZ + $Radius) . '
AND POWER(' . $UrsprungX .' - KoordX, 2)
+ POWER(' . $UrsprungY .' - KoordY, 2)
+ POWER(' . $UrsprungZ .' - KoordZ, 2)
<= "' . pow(2 * $this->Erdradius * sin($Radius / (2 * $this->Erdradius)), 2) . '"';
if ($Reihenfolge && strpos($Spalten, $Reihenfolge) !== false) {
$Richtung = (strtoupper($Richtung) == 'DESC') ? 'DESC' : 'ASC';
$sql .= "\n" . 'ORDER BY `' . $Reihenfolge . '` ' . $Richtung;
}
$re = mysql_query($sql, $this->db);
$result = array();
while ($rd = mysql_fetch_object($re)) {
$result[] = $rd;
}
return $result;
}
}
?>
