Hier im Forum bekommt ihr bei euren fragen schnelle hilfe.Hier geht es rund um das Web SeitenProgrammieren.Alles rund ums Javascript,Html,Php,Css und Sql.Auf fast allen Fragen haben wir eine Antwort.
Der Soforthilfe-chat verspricht das ,was sein Name sagt. Hier sind Leute Online die sofort ihre hilfe anbieten.Seht in der OnlineListe nach und wenn einer Online ist werdet ihr auch antwort bekommen. Admine ,Moderatoren und Helfer sind unsere Spezialisten in Sachen Web Programierung
71 Unwetterwarnungen in Deutschland
Die Datenbank wurde zuletzt am 02.08.2020 17:34:28 aktualiesiert
71

Eine PLZ-Umkreissuche in PHP

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:

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. Anhand dieser Anleitung (die genau das zeigt was ich vorhabe) wurde eine Zwischentabelle „zip_coordinates“ erstellt, die nur Postleitzahlen und Latitude+Longitude enthält.

Folgendes kleines Script lieferte dann die Ergebnisse, die wir haben wollen:

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