OpenGeoDb Umkreissuche optimieren und in Yii implementieren

In diesem Kurzartikel geht es darum, wie die aktuelle OpenGeoDb-Geolokalisationsdatenbank durch ein PHP-Skript so umbauen, dass eine Umkreissuche möglich ist. Es gibt bereits zwei Tutorials zu diesem Thema, nämlich hier und hier, dessen Resultate in meinen Augen aber ineffizient sind. Trotzdem danke ich diesen Autoren, dass sie mir die Arbeit leichter gemacht haben. Besonderen Dank möchte ich an den Autor dieses Tutorials ausrichten, da er den mathematischen Hintergrund der Umkreissuche sehr einleuchtend vermittelt.

Mein Skript benutzt das Yii-Framework, speziell die PDO-Klasse, um die Datenbankverbindung aufzubauen.

1.) Vorbereitung:

Die eleganteste Datenquelle für unsere Umkreissuche wäre eine Tabelle aller deutschen Städte und Ortschaften, mit einmaliger Postleitzahl, Ortsbezeichnung, Breitengrad und Längengrad. Wir bereiten eine SQL-Tabelle dementsprechend vor:

CREATE TABLE IF NOT EXISTS `Stadt` (
`Plz` INT(11) UNSIGNED NOT NULL ,
`Ort` VARCHAR(80) NOT NULL ,
`Latitude` DOUBLE NULL ,
`Longitude` DOUBLE NULL ,
PRIMARY KEY (`Plz`) )
ENGINE = InnoDB;

2.) Extrahieren der Geo-Daten

Als erstes werden wir aber nur dazu kommen, die Spalten Plz und Ort zu füllen, da die Extraktion der Breiten,- und Längengrade komplizierter ist. Dies bewerkstelligen wir, wie an dieser Stelle im OpenGeoDb-Handbuch beschrieben, folgendermaßen:

SELECT code.text_val as "Plz", name.text_val as "Ort"
FROM geodb_textdata code, geodb_textdata name
WHERE name.loc_id=code.loc_id AND
code.text_type=500300000 /* AREA_CODE */ AND
name.text_type=500100000 /* NAME */
ORDER by 2;

Wir lesen diese Daten in unsere Tabelle ein.

Anschliessend erzeugen wir uns eine Hilfstabelle z.B. tmp_stadt, die genau so aussieht, wie unsere Tabelle Stadt.
Mithilfe meines Skriptes werden nun für jeden einzelnen Ort die Koordinaten ausgelesen:

active = True; // Verbindung aktivieren

// Optional: group by Ort; dann wird jeder Ort nur einmalig mit seiner Haupt-PLZ aufgeführt.
$command = $connection->createCommand("select * from Stadt group by Ort");

$data = $command->queryAll();

foreach($data as $object) {
$query = "SELECT zc_id, zc_location_name, zc_lat, zc_lon FROM zip_coordinates WHERE zc_zip = '";
// Da Integer keine führenden Nullen haben, müssen wir hier unter Umständen eine einfügen
if (strlen($object['Plz']) == 4) $query .= "0";

$query .= $object['Plz'] . "'; ";

$reader = $connection->createCommand($query)->query();
$result = $reader->read();

// Sollten die Geodaten einmal fehlen, müssen sie mit 0 hineingeschrieben werden, da das Skript sonst abbricht:
if($result['zc_lat'] == '') $result['zc_lat'] = 0;
if($result['zc_lon'] == '') $result['zc_lon'] = 0;

$query = "insert into tmp_stadt (Plz, Ort, Latitude, Longitude) values ('" . $object['Plz']."','" . $object['Ort'] . "', " . $result['zc_lat'] . ", " . $result['zc_lon'] . ")";

// Wenn gewünscht, zeige an, was du tust:
if($verbose) {
$i++;
echo $i . ": " . $query . "";
}

if(!$connection->createCommand($query)->execute()) {
echo $query;
die("something went wrong!");
}
}
?>

Da in der OpenGeoDb leider nicht alle Stadtteile eingepflegt sind, beschränkt sich mein Skript darauf, für Ballungsgebiete wie z.B. Hamburg oder Berlin die Position der ersten gefundenen, also kleinsten Postleitzahl zu verwenden. Soll aber trotzdem jede Postleitzahl auftauchen, kann man das Group by Ort durch Group by Plz ersetzen. Da aber nach dem SQL-Feld Ort gruppiert wird, kann man das Skript auf eventuell später nachgepflegte Datenbestände anwenden, und es würde dann den Bestand präzisieren.

Nach dem hoffentlich erfolgreichen durchlaufen, was je nach Rechenleistung einige Sekunden oder Minuten dauern wird, finden wir unser Ergebnis in tmp_stadt wieder. Wir löschen Stadt, benennen tmp_stadt in Stadt um und geniessen unseren Datenbestand.

Dieser hat aber leider selten vereinzelt einige Lücken, siehe z.B. hier:

mysql> select * from tmp_stadt where Ort = 'Leipzig';
+------+---------+----------+-----------+
| Plz | Ort | Latitude | Longitude |
+------+---------+----------+-----------+
| 4318 | Leipzig | 51.3667 | 12.3833 |
| 4328 | Leipzig | 0 | 0 |
| 4329 | Leipzig | 51.3667 | 12.3833 |

Diese Lücken sind aber selten, und ich denke, man kann damit erstmal leben.

3.) Umkreissuche

Für die Realisierung der Umkreissuche bedient man sich einfach diesem recht guten Algorithmus, hier auch ein mal in Yii implementiert:

$query = "SELECT Plz, Ort, Latitude, Longitude FROM Stadt WHERE Plz = '";

if (strlen($plz) == 4) $query .= "0"; // wieder auf führende Nullen achten

$query .= $plz . "'; ";

$reader = Yii::app()->db->createCommand($query)->query();
$result = $reader->readAll();

$latitude = $result[0]['Latitude'] / 180 * M_PI;
$longitude = $result[0]['Longitude'] / 180 * M_PI;

$query = "SELECT Plz, Ort, (
6368 * SQRT(2*(1-cos(RADIANS(Latitude)) *
cos(".$latitude.") * (sin(RADIANS(Longitude)) *
sin(".$longitude.") + cos(RADIANS(Longitude)) *
cos(".$longitude.")) - sin(RADIANS(Latitude)) * sin(".$latitude.")))) AS Distance
FROM Stadt WHERE
6368 * SQRT(2*(1-cos(RADIANS(Latitude)) *
cos(".$latitude.") * (sin(RADIANS(Longitude)) *
sin(".$longitude.") + cos(RADIANS(Longitude)) *
cos(".$longitude.")) - sin(RADIANS(Latitude)) * sin(".$latitude."))) <= ".$radius." ORDER BY Distance "; $reader = Yii::app()->db->createCommand($query)->query();
$result = $reader->readAll();

In $result habe ich nun ein Array aller Städte im Umkreis von $radius km der Stadt mit der Plz $plz. Ausserdem gehe ich in diesem Beispiel davon aus, dass ich die Datenbankverbindung in meiner Yii-Applikation in Yii::app()->db konfiguriert habe.

Ich benutze für diese Beispiele die Version opengeodb-0.2.5a-UTF8-sql.gz. Nach diesem Durchlauf hatte ich 6358 Städte in meiner Datenbank, wenn jede Stadt nur ihre Haupt-Postleitzahl benutzt, und 8270 Städte, wenn alle Städte mit ihren Stadtteilen aufgeführt werden (Group by PLZ vs. Group by Ort).