by:

Hallo,

nachdem ich die letzten Tage wenig neues veröffentlicht habe, heute mal etwas interessantes für den ambitionierten PHP Programmierer.

Eine PLZ Umkreissuche in PHP, dazu benötigen wir:

  1. Eine Geo DB mit Long und LAT sowie PLZ (am besten von opengeodb auf sourceforge)
  2. Einen brauchbaren Umkreissuchen-Script
  3. Etwas Zeit

Bei meinen Versuchen hat sich herausgestellt, dass ich die meiste Zeit damit verbracht habe, eine brauchbare Datenbank zu finden und zu formatieren. Hilfe dazu weiter unten, jetzt erstmal etwas Code:

Code

<?
// Zuerst eine Verbindung zur Datenbank aufbauen!
 $connect=@mysql_connect("localhost", "user", "pass")
or die("Cant connect to Database");
 @mysql_select_db("geo_plz", $connect)or die("Cant select Database");

// die PLZ nach der wir suchen
$plz = '9220';

// der Umkreis in Km
$umkreis = 5;

// Erdradius (geozentrischer Mittelwert) in Km
$radius = 6368;

/* -------------------------- */

$sql_rad = mysql_query("SELECT lon, lat FROM `plz_at` WHERE `plz` = '$plz' ");
$erg_rad = mysql_fetch_object($sql_rad);

// Umrechnung von GRAD IN RAD

$lon = $erg_rad->lon / 180 * M_PI;
$lat = $erg_rad->lat / 180 * M_PI;

// jetzt erfolgt die eigentliche Abfrage

$query = "SELECT ort, plz, (
 ".$radius." * SQRT(2*(1-cos(RADIANS(lat)) * 
 cos(".$lat.") * (sin(RADIANS(lon)) *
 sin(".$lon.") + cos(RADIANS(lon)) * 
 cos(".$lon.")) - sin(RADIANS(lat)) * sin(".$lat.")))) AS Distance 
 FROM plz_at WHERE 
 ".$radius." * SQRT(2*(1-cos(RADIANS(lat)) * 
 cos(".$lat.") * (sin(RADIANS(lon)) * 
 sin(".$lon.") + cos(RADIANS(lon)) * 
 cos(".$lon.")) - sin(RADIANS(lat)) * sin(".$lat."))) <= ".$umkreis." 
 ORDER BY Distance
";

// die Ausgabe (vereinfacht)

$sql = mysql_query($query);
while( $erg = mysql_fetch_object($sql) ) {

 echo '
<pre>', print_r($erg), '</pre>
';
}
?>


Natürlich wäre es jetzt denkbar für jede gefundene PLZ im Umkreis eine Abfrage der Mitglieder oder Filialen zu machen, diesen Spass überlasse ich aber lieber euch :)

Wer die formatierte Datenbank für Deutschland, Österreich und Schweiz braucht oder eine Frage hat, einfach kurz melden.

LG

Andreas

Eine wichtige Adresse die beim Erstellen der Datenbank sowie der Gestaltung der Abfrage hilfreich ist lautet:

http://opengeodb.giswiki.org/wiki/OpenGeoDB_-_Umkreissuche

[UPDATE]

Ich habe die Datenbank online gestellt, übernehme aber keine Garantie für Vollständigkeit und Richtigkeit.

http://www.codejungle.org/code/zip.sql.gz



Name:
Hallo Andreas,

Dein Script versteht man wenigstens. Vielen Dank. Bei mir funktioniert es auch wunderbar.
Allerdings würde ich jetzt gerne noch auf eine KundenTabelle zugreifen und anhand dieser gefilterten Postleitzahlen die Kunden im Umkreis ausgeben lassen.
Bin nur noch nicht so fit mit den mysql Abfragen. Kannst DU mir da weiterhelfen, wie ich das mit den Join oder INNER JOIN etc. lösen kann?
Würde mich freuen über eine Antwort. Vielen Dank.
LG

Name:
Moin!

Erst einmal meine Hochachtung was Sie hier geleistet haben. Hätten Sie eine formatierte DB für die Schweiz?

Wäre echt super! Vielen Dank im Voraus und liebe Grüsse aus der Schweiz

Manuel

Name:
Naive Frage vielleicht. Warum nicht einfach die Google Maps Api dafür verwenden? Die würde statt der Luftlinie die tatsächliche Entfernung auf einer Straße berechnen.

Name:
Hallo Creativpur,

danke für dein Feedback!
ich glaube ich hab ein Fehler mal gefunden, es gibt keine Methode bei PDO die fetchObject heisst.

$sql->fetchObject($sql)


hier mal ein beispiel wie ich das mit PDO machen würde:
$db = new PDO("mysql:host=localhost;dbname=plz","benutzer","passwort");


$query="select * from plz_de";
$stmt = $db->query($query);

$result = $stmt->fetchALL(PDO::FETCH_CLASS);

foreach($result as $row)
{
var_dump($row);
}


So kannst z.b. über die Ergebnisse iterieren.
Das ganze in PDO umschreiben sollte nicht zu schwer sein, wenn du noch Hilfe brauchst melde dich einfach mal.


Liebe Grüße aus Wien

Andreas

Name:
Hallo Andreas,

ich finde Dein Script super. Als mysql funktioniert es auch wunderbar.. Nun versuche ich Dein Script in PDO umzuschreiben, jedoch erfolgt keine Ausgabe.. Hast Du vielleicht eine Idee, woran es liegen k?nnte ??

Hier das Script..
[code]error_reporting(E_ALL);
// Verbindung zur Datenbank aufbauen.
include "Datenbankverbindung/db_plz_umkreis.php";
?>


$abfrage = $db->query("SELECT COUNT(*) FROM `plz_de`");
$ergebnis = $abfrage->fetch();
?>

Es wurden Eintragungen gefunden.


// die PLZ nach der wir suchen
$plz = '34117';

// der Umkreis in Km
$umkreis = 10;

// Erdradius (geozentrischer Mittelwert) in Km
$radius = 6368;

/* -------------------------- */

$sql_rad = $db->query("SELECT lon, lat FROM `plz_de` WHERE `plz` = '$plz' ");
$erg_rad = $sql_rad->fetchObject($sql_rad);

// Umrechnung von GRAD IN RAD

$lon = $erg_rad->lon / 180 * M_PI;
$lat = $erg_rad->lat / 180 * M_PI;

// jetzt erfolgt die eigentliche Abfrage

$query = "SELECT ort, plz, (
".$radius." * SQRT(2*(1-cos(RADIANS(lat)) *
cos(".$lat.") * (sin(RADIANS(lon)) *
sin(".$lon.") + cos(RADIANS(lon)) *
cos(".$lon.")) - sin(RADIANS(lat)) * sin(".$lat.")))) AS Distance
FROM plz_de WHERE
".$radius." * SQRT(2*(1-cos(RADIANS(lat)) *
cos(".$lat.") * (sin(RADIANS(lon)) *
sin(".$lon.") + cos(RADIANS(lon)) *
cos(".$lon.")) - sin(RADIANS(lat)) * sin(".$lat."))) <= ".$umkreis."
ORDER BY Distance
";

// die Ausgabe (vereinfacht)
$sql = $db->query($query);

echo '


';
while($erg = $sql->fetchObject($sql)) {
echo '
';

print_r($erg);
echo '
';
}
?>[/code]

Name:
Danke für den Code! Ich hab' das Ganze 5 Minuten lang umgeschrieben und so einfach kann man es auf die aktuelle OpenGeoDB für Deutschland anwenden.
Wo ist der Flattr-Button? :D

Name:
Hi Leute,
das die gleiche PLZ nicht gefunden wird kann man so fixen, einfach den Code zwischen den Rauten einfügen.

cos(".$lon.")) - sin(RADIANS(lat)) * sin(".$lat."))) <= ".$umkreis."
##################
OR plz = ".$plz."
##################
ORDER BY Distance

Viele Grüße
Robert

Name:
hi zwergo,
danke fuer den Tip !!

lg
andreas

Böses Encoding :)

Name:
ok das hat leider nicht funktioniert... hier ein link der helfen könnte: http://pr11.de/mysql-umlaute-reparieren.html

Name:
vielen dank f?r die datenbank!
wenn jemand probleme mit den umlauten hat, kann das vielleicht helfen:
ALTER TABLE plz_at CONVERT TO CHARACTER SET UTF8;
ALTER TABLE plz_de CONVERT TO CHARACTER SET UTF8;
ALTER TABLE plz_ch CONVERT TO CHARACTER SET UTF8;
update plz_at set
ort=replace(ort,"ß", "?"),
ort=replace(ort, "ä", "?"),
ort=replace(ort, "ü", "?"),
ort=replace(ort, "ö", "?"),
ort=replace(ort, 'Ä', '?'),
ort=replace(ort, "Ü", "?"),
ort=replace(ort, "Ö", "?"),
ort=replace(ort, '?', '?');
update plz_de set
ort=replace(ort,"ß", "?"),
ort=replace(ort, "ä", "?"),
ort=replace(ort, "ü", "?"),
ort=replace(ort, "ö", "?"),
ort=replace(ort, 'Ä', '?'),
ort=replace(ort, "Ü", "?"),
ort=replace(ort, "Ö", "?"),
ort=replace(ort, '?', '?');
update plz_ch set
ort=replace(ort,"ß", "?"),
ort=replace(ort, "ä", "?"),
ort=replace(ort, "ü", "?"),
ort=replace(ort, "ö", "?"),
ort=replace(ort, 'Ä', '?'),
ort=replace(ort, "Ü", "?"),
ort=replace(ort, "Ö", "?"),
ort=replace(ort, '?', '?');

Name:
Hallo, ich habe alles eingebunden und es funktioniert.... fast....
Ich habe deine DB komplett drin also sowas wie 16000 eintr?ge...
Wenn ich jetzt 12059 such im umkreis von zb. 30KM dann kommt unter anderem 10827. Wenn ich 10827 mit zb. 30KM suche kommt 12059 nicht..... ? die entfernung ist ca. 5-10KM ....

Name:
Hi,

ich bin derzeit ziemlich eingebunden mit meinen Startup Moving Bytes und hab daher
keine Zeit mir die PLZ Umkreis suche anzuschauen ...

Wenn du eine L?sung findest, w?rde ich sie gerne Ver?ffentlichen, bzw wenn ich die Umkreissuche
das n?chste mal in ein Projekt verwende, werde ich Sie nochmal ?berarbeiten.

lg
Andreas


Name:
Hallo, gibt es inzwischen eine korrigierte Version des Codes mit der auch die Orte mit Distanz 0 gefunden werden?

Name:
Moin,
danke f?r die Anregung, aber da ist leider ein Fehler in deinem SQL Ausdruck, denn NULL ist niemals kleiner/gleich $umkreis. Somit wird niemals der Ort gefunden, dessen PLZ man eingegeben hat. Existiert ein Ort mit mehreren PLZ, wird nur die Anzahl Orte minus eins als Ergebnis erscheinen -> 5 Treffer, aber nur 4 in der Ergebnisliste.

Name:
Vielen Dank f?r den Code, die Umkreissuche klappt, bis auf ein Problem. Wenn die Koordinaten von suche und ergebnis identisch sind, wird der Datensatz nicht angezeigt. Gibt es daf?r eine L?sung?
Danke,
Britta

Name:
Moin Andreas,

das sieht ja nach grosskreis berechnung aus.
Das ist zwar genau aber auch aufwendig.
W?re f?r die Umkreissuche innerhalbs Deutschland nicht eine Mittelbreite Rechnung aussreichend ?
(sehr viel einfacher - schneller?)

MfG
Thorsten

Name:
Hi Andreas,

habe ein kleines Problemchen, wenn ich die zip_sql in MySQL importiere, werden die Spalten nach UTF-8 gef?llt. Habe dann einen Blick in die zip.sql geworfen. Dort sind die St?dte ebenfalls nicht mit ?,?,?,? ect versehen. Gibt\'s da einen Trick?

Gru?
Sebastian

Name:
Hi Andreas,

habe ein kleines Problemchen, wenn ich die zip_sql in MySQL importiere, werden die Spalten nach UTF-8 gef?llt. Habe dann einen Blick in die zip.sql geworfen. Dort sind die St?dte ebenfalls nicht mit ?,?,?,? ect versehen. Gibt\'s da einen Trick?

Gru?
Sebastian

Name:
Hey Andreas!

Bei mir berechnet das Script scheinbar die Differenzen in einem anderen Ma?stab. z.B.:

Koblenz - Stuttgart : 5759 (wirklich : 209 KM)
Koblenz - Hamburg : 6007 (wirklich : 391 KM)
Koblenz - Paris : 6135 (wirklich : 413 KM)
Koblenz - New York : 10416 ( wirklich : 6128 KM)

Ich habe das Script leicht abge?ndert aber die Lng und Lat Eingaben sind gleich. Kannst du dir da einen Reim drauf machen?

Vielen Dank
Jo

Name:
Hi Tom,

also der Artikel ist vom 2008-08-03,
daher schon etwas ?lter ...
Der DB Dump ist vom 2010-07-07, evtl werd ich mal ein script bastelen, das die daten regelm?ssig aktualisiert..

lg
andreas

Name:
Ich vermisse hier eine Datumsangabe von diesem HowTo bzw. Artikel. Wie alt ist dieser ca ?

Ich habe probleme damit eine aktuelle plz_de SQL DUMP zu bekommen.

Name:
Hi Ben,
ich nehm mal an du meinst diese Zeile:
// die PLZ nach der wir suchen
$plz = \'9220\';

falls ja, diese dient nur zur Demonstrationszwecken, hier k?nnte genauso gut der Inhalt von einem Eingabefeld stehen...

lg
Andreas

Name:
Hi,
danke erstmal f?r das Script und die sql!
aber mal ne bl?de frage:
warum legen wir fest nach was wir suchen???
oder kapier ich irgendwas ned?

danke schonmal f?r die antwort!
cheers,
ben

Name:
Hi Sam,
Also alles was im Umkreis von 1km der Postleitzahl 80634 zu finden ist findet er,
einschlie?lich die PLZ von anderen Bezirken. Willst du das nicht, k?nnte man die Ausgabe noch filtern, bzw schon in der abfrage ein distinct auf den ort machen ..
wenn du noch fragen hast, kannst dich auch gerne per mail an mich wenden.

lg
andreas

Name:
Sorry f?r den Doppelpost, bitte einen l?schen.
Oder ist das so gemeint, dass die PLZ 80634 zu M?nchen geh?rt, hier weitere 74 PLZs ausgegeben werden weil diese auch zu M?nchen geh?ren und er dann ausserhalb von M?nchen 1km mit dazu sucht?
Ich dachte er geht von der PLZ aus 1 km? Also alles was im Umkreis von der Postleitzahl 80634 zu finden ist?

Name:
Also irgendwie kann das doch nicht stimmen, nehme ich folgenden Link:

http://codejungle.org/api/plz.php?plz=80634&umkreis=1&land=de

Dann schmeisst er mir zig Ergebnisse raus was alles im Umkreis von 1km hier entfernt sein soll.

Schau ich hier: http://www.plz-umkreis.com/?zipcode=80637&radius=1&country=de

Dann kommt genau 1 Ergebnis raus bei 1km Umkreis und so stimmt es auch.

Wo h?ngts also?!

Name:
Hilfe! Egal welche parameter ich f?r $plz und $umkreis eingebe ich bekomme immer(!) dieselben 8 st?dte als ergebnis angezeigt

Name:
Sch?nes Script! Einfach und unkompliziert!
Allerdings habe ich den Eindruck, dass die Berechnung nicht ganz richtig arbeitet.

Dein Script gibt mir
von 51.3333, 6.56833333 nach 51.2601699, 6.5142186
8,9 km aus. Bei Google Maps ist allerdings die k?rzeste Strecke 15,4 km, wenn ich genau nach diesen Koordinaten suche.

Name:
Super Sache, klappt wunderbar f?r deutsche PLZ!

Ich habe mir die aktuelle DB/CSV von OpenGeoDB geholt: http://fa-technik.adfc.de/code/opengeodb/PLZ.tab
Datenbank anlegen, CSV importieren. In deinem Code noch \"plz_at\" durch \"plz_de\" (Name der Datenbanktabelle) ersetzt, Datenbankverbindung angepasst und schon l?uft es!

Danke das du den Code mit der Community teilst!

Name:
mhm wundert mich etwas,
ich verwende die datenbank selber auch bei meiner api, siehe http://codejungle.org/api/plz.php?plz=23747&umkreis=5&land=de

kann es sein das du in den beiden (bzw einer von beiden) abfragen noch plz_at drin stehen hast ?

lg
andreas

Name:
also irgendwas stimmt mit der datenbank plz_de nicht. egal welche parameter ich f?r $plz und $umkreis eingebe ich bekomme immer(!) dieselben 8 st?dte als ergebnis angezeigt

Name:
Hallo, tolle Seite.
Auch ich suche f?r unseren kleinen Onlineshop eine Funktion, in der Interessenten Ihre PLZ eingeben k?nnen und dann die n?chste Abholstelle von uns erfahren.

Beispiel: Kunde kommt aus 70190 und dann wird ihm die Abholstelle in 70200 angezeigt...

Wenn du mir da weiter helfen k?nntest, w?re ich dir sehr dankbar, da ich schon ganz verzweifelt so etwas suche.

Insgesamt hab ich 9 Abholstellen - meisst Verwandschaft - ich bin gespannt ob das technisch m?glich ist!

Gruss aus Stuttgart

Name:
Danke f?r solch ein klasse Script!

vg
martin

Name:
hallo jean,
der r?ckgabewert ist in km,
der grund wieso die formatierung teilweise so seltsam ausschaut, ist das es soviele nachkomma stellen gibt, das es in ein integar berreich nicht mehr reinpasst...
auf einem 64 bit system h?ttest im ?brigen ein dopellt so gro?en integar berreich...
um die ausgabe jetzt sch?n zu formatieren, w?rde ich mit round den wert einfach auf die 2 komma stelle runden.
hoffe ich konnte damit deine frage beantworten.

lg
andreas

Name:
Hi,
super Script! Endlich mal was brauchbares :)
Was mich noch interessieren w?rde:
In welcher Einheit kommt die Distance zur?ck? KM?
Ich habe zb die Angabe
Distance: 8.8364875934259e-05
Distance: 2.3394734447394
Beide PLZ sind 57572 (gesucht nach 57572).
Wie kann ich die Distance in KM formatieren?

Danke schonmal im Voraus! :)

Gr??e
Jean

Name:
ja super...
also es funktioniert jetzt alles.

habe einfach nur den DB-Verweis in der eigendlichen Abfrage ?bersehen.
(...AS Distance FROM plz_at WHERE...)

also alle Panik umsonst...
Danke f?r den Script, die DB und die rasche Antwort...

Gru?
Sandex

Name:
hallo sandex,
vermutlich ist das error reporting bei dir abgeschaltet und deswegen bekommst du eine wei?e seite. schau mal ob du irgendeine php error logs findest, ich kann dir dann vermutlich weiterhelfen.

lg
andreas

Name:
Hallo,
ich habe die Datenbank angelegt und die Inhalte importiert, den Script auf den Server geladen und getestet. Alles Super.

Jetzt habe ich versucht auf die Tabelle \"plz_de\" zuzugreifen und bekomme nur eine weisse Seite.

Die Tabellen \"plz_at\" und \"plz_ch\" funtionieren dagegen einwandfrei.

Nun m?chte ich gern wissen warum das so ist und wie ich das Problem l?sen kann.

Kannst du mir da weiterhelfen?

Name:
Hi,

klasse Script.

Danke und Gr??e aus Karlsruhe

Name:
Hi,
du kannst die Datenbank hier herunterladen:
http://www.codejungle.org/code/zip.sql.gz

best regards
andreas

Name:
Hallo Andreas,

erstmal Danke f?r Deine M?he.

Ich bin auf der Suche nach einer formatierten Datenbank f?r die deutschen St?dte auf Deine Seite gesto?en und habe Deinen Kommentar unter Deinem PLZ-Umkreissuche Skript gelesen.

W?re es m?glich, dass Du mir das SQL file mit den deutschen Geodaten zuschickst?

Vielen Dank und lieben Gru? aus K?ln,
Stephan

Name:
Hallo Oliver,

sorry das ich dir erst jetzt schreibe,
wenn du noch intresse hast, schicke ich dir gerne die datenbank.

lg
andreas

Name:
Hi.. w?re klasse wenn ich die Geo-Datenbank f?r Deutschland, ?sterreich und Schweiz von dir bekommen k?nnte.

Danke und Gruss
Oliver