PHPGangsta - Der praktische PHP Blog

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.

Aufgabe 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.