PHPGangsta - Der praktische PHP Blog

PHP Blog von PHPGangsta


Eine PLZ-Umkreissuche in PHP

with 12 comments

Ich bekam vor ein paar Tagen folgende kleine Programmieraufgabe:

Gegeben ist eine große Datenbank mit > 300.000 Kunden, worin jeder Kunde eine Postleitzahl hinterlegt hat. Firma X möchte nun diese Kunden kontaktieren, aber nur diejenigen die in 35km Umkreis um eine seiner 42 Filialen wohnen.

Tja, wie macht man das?

Die einfachste Lösung die mir in den Sinn kam war eine Geo-Datenbank zu nutzen, in der deutsche Postleitzahlen und deren Latitude+Longitude hinterlegt sind. Dann durchlaufe ich die gegebenen 42 Postleitzahlen der Filialen und berechne jeweils die Distanz zu allen 17052 Postleitzahlen die es in Deutschland gibt. Wenn die Distanz unter 35 Kilometer beträgt kommt die PLZ in ein Ergebnis-Array. Am Ende filtere ich noch die doppelten raus, fertig.

OpenGeoDBAufgabe 1 war also eine Geo-Datenbank mit Postleitzahlen für Deutschland zu finden. Ich wurde fündig bei der OpenGeoDB.

Aufgabe 2 war doch etwas schwieriger als gedacht: Den Datenbank-Dump zu importieren in meine MySQL-Datenbank und in ein Format zu bekommen das ich benutzen kann. Um die OpenGeoDB in meine MySQL zu importieren musste ich leider nicht nur einen SQL-Dump importieren, sondern ein paar Schritte beachten, die aber nach etwas Suche im Wiki beschrieben sind. Die Schritte lauten:

  • Ersetzen von „TYPE=InnoDB“ durch „ENGINE=InnoDB“, da ich eine MySQL 5.6. Datenbank habe
  • opengeodb-begin.sql importieren
  • DE.sql importieren
  • DEhier.sql importieren
  • opengeod-end.sql importieren

Gerade bei den beiden großen Dumps musste ich zwischendurch abbrechen, „BEGIN;“  und „COMMIT;“ einfügen oben und unten, und dann nochmal laufen lassen, denn ohne diese Transaktion war der Import sehr sehr langsam.

Aufgabe 3: Dann hatte ich die Rohdaten in meiner Datenbank. Die OpenGeoDB ist relativ groß da sie viele Daten zu Städten, Landkreisen, Postleitzahlen usw. enthält. Ich habe anhand dieser Anleitung (die genau das zeigt was ich vorhabe) als nächstes eine Zwischentabelle „zip_coordinates“ erstellt, die nur Postleitzahlen und Latitude+Longitude enthält.

Folgendes kleines Script lieferte mir dann die Ergebnisse, die ich haben wollte:

<?php
$sourceZipCodes = array(59302,50667,38855,57271,38518,23730);
$distance = 35;
$resultingZipCodes = array();

$dbh = new PDO('mysql:host=127.0.0.1;dbname=opengeodb', 'opengeodb', 'secretpassword');
$getZcIdStmt = $dbh->prepare("SELECT zc_id
                        FROM zip_coordinates
                        WHERE zc_zip = :zip");
$getZipList = $dbh->prepare("SELECT
                              dest.zc_zip,
                              dest.zc_location_name,
                              ACOS(
                                   SIN(RADIANS(src.zc_lat)) * SIN(RADIANS(dest.zc_lat))
                                   + COS(RADIANS(src.zc_lat)) * COS(RADIANS(dest.zc_lat))
                                   * COS(RADIANS(src.zc_lon) - RADIANS(dest.zc_lon))
                              ) * 6380 AS distance
                          FROM zip_coordinates dest
                          CROSS JOIN zip_coordinates src
                          WHERE src.zc_id = :zcid
                          AND dest.zc_id <> src.zc_id
                          HAVING distance < :distance
                          ORDER BY distance;");

foreach ($sourceZipCodes as $sourceZipCode) {
    $getZcIdStmt->execute(array(':zip' => $sourceZipCode));
    $row = $getZcIdStmt->fetch();

    $getZipList->execute(array(':zcid' => $row['zc_id'], ':distance' => $distance));
    $zipList = $getZipList->fetchAll();

    foreach ($zipList as $zipResult) {
        if (!in_array($zipResult['zc_zip'], $resultingZipCodes)) {
            $resultingZipCodes[] = $zipResult['zc_zip'];
        }
    }
}

echo join(',', $resultingZipCodes);

Den etwas komplizierteren SQL-Query habe ich aus dem besagten Wiki-Artikel, den hätte ich selbst nur schwerlich erstellen können in kurzer Zeit.

Das war es eigentlich schon. Aus der Liste mit den gegebenen 42 Postleitzahlen kam eine Liste mit 3028 Postleitzahlen heraus, die ich dann gegen die Kundendatenbank habe laufen lassen, und schon wusste ich wie viele und welche Kunden im 35km Umkreis der 42 Filialen wohnen.

Bevor jemand fragt: Die OpenGeoDB enthält neben den Daten für Deutschland auch Daten aus Österreich, Belgien, Schweiz und Lichtenstein. Damit müsste das ganze äquivalent möglich sein.

Written by Michael Kliewe

Februar 22nd, 2015 at 4:22 pm

12 Responses to 'Eine PLZ-Umkreissuche in PHP'

Subscribe to comments with RSS or TrackBack to 'Eine PLZ-Umkreissuche in PHP'.

  1. Eine Alternative zur OpenGeoDB für internationale Zwecke wäre http://www.geonames.org, zumindest die großen Industriestaaten sollten ziemlich vollständig drin sein

    Jens S.

    23 Feb 15 at 20:10

  2. Oh, da werde ich etwas nostalgisch, vor zig Jahren habe ich mit der OpenGeoDB so geografische Suchen realisiert (Wo ist der nächste Händler im Umkreis?) 😉

    Spannend fand ich das ich irgendwann Ungenauigkeiten feststellte und dann verstand das die „6380“ aus obigen SQL, den Radius des Globus darstellen und nur vereinfacht als Konstante angegeben ist. Denn eigentlich ist die Welt ja eine etwas plattgedrückte Kugel. Noch dazu gibt es Berge und Täler. Zumindest führten einer oder beide dieser Faktoren schon zu merkbaren Ungenauigkeiten (war zumindest damals meine Schlussfolgerung), selbst bei der ja nicht soo großen Fläche von Deutschland.

    Florian Heinze

    23 Feb 15 at 22:13

  3. Die OpenGeoDB wird leider seit Jahren nicht mehr gepflegt. Die letzten Einträge auf der Seite sind von 2012. Und wie du schon erkannt hast lassen sich die SQLs ohne weiteres nicht in aktuelle MySQL Versionen importieren.

    Nico

    24 Feb 15 at 07:58

  4. Die Doku ist leider wirklich nicht up tp date. Schaut man sich aber die Daten an, von wann die Downloads sind, dann sind die auch relativ aktuell.

    Was ich jedoch leider nicht geschafft habe, war es die changes.sql zu importieren. Eventuell war dies ein temporäres Problem, aber wenn ich es richtig im Kopf habe, hat damals ein Duplicate Primary Key Probleme gemacht.

    Hat das mit der changes.sql mal jemand hinbekommen, bzw. weiß jemand ob man diese überhaupt benötigt?
    Das Modify-Date der DE.tab scheint deutlich aktueller als das der DE.sql, was für mich so wirkt, als ob man die changes.sql zwingend bräuchte.

    CRenner

    3 Mrz 15 at 07:53

  5. Gerade für den deutschen Raum habe ich bei einem Kundenprojekt deutliche Ungenauigkeiten festgestellt. Oft sind diverse Postleitzahlen mit den selben Koordinaten versehen (manchmal ganze Städte). Leider sowohl bei geonames als auch opengeodb (durch Stichprobe getestet). Habe das damals mit der GoogleMaps API gelöst. Denke deine große Anzahl Postleitzahlen lässt sich auch auf dieses Problem zurückführen.

    martin

    10 Mrz 15 at 12:39

  6. Eine Umkreissuche lässt sich mittlerweile über eine Google API realisieren.

    Michael Rimbach

    15 Mrz 15 at 20:03

  7. @Michael Rimbach:

    Auszug aus den Nutzungsbedingungen:

    „Hinweis: Das Geocoding API darf nur in Verbindung mit einer Google-Karte verwendet werden, das heißt, das Abrufen von Geocodierungsergebnissen, die nicht auf einer Karte angezeigt werden, ist nicht zulässig.“

    Möchte oder kann man keine Google Karte anzeigen, darfst du auch die PLZ-Lat/Lng-Ergebnisse nicht nutzen.

    CRenner

    16 Mrz 15 at 08:01

  8. google erlaubt das zwar niocht, aber machen kann man es trotzdem. ist halt „grey hat“!

    carl lauer

    29 Mrz 15 at 16:12

  9. Wie schaut es aus mit einer Lösung in MongoDB?

    Alexej

    22 Mai 15 at 15:29

  10. Lustigerweise war Umkreissuche einer der ersten Dinge, über die ich gebloggt habt. http://www.thewebhatesme.com/allgemein/umkreissuche-in-php-teil-1/

    Nils

    2 Jun 15 at 11:32

  11. Danke, das ist easy und schlank.

    Kevin

    3 Dez 15 at 14:57

  12. Genau das, was ich suche. Nur komme ich hier nicht weiter: wie bzw. wo muss ich „TYPE=InnoDB“ durch „ENGINE=InnoDB“ ersetzen?

    Simon

    5 Jul 16 at 11:53

Leave a Reply

You can add images to your comment by clicking here.