PLZ Umkreissuche


#1
Hallo,

ich bin grad wieder am verzweifeln.

Habe hier in Forum paar Tutorials gefunden, aber verstehen tu ich sie nicht ganz.

Also ich habe eine Tabelle mit Firmen wo auch die entsprechende PLZ steht (deutsch österreich, schweiz)

Parallel dazu hab ich noch eine Tabelle mit den ganzen x und y Koordinaten der PLZ's.
So seht zum Beispiel ein Datensatz aus:
'01067', 13.7194, 51.0626

Bei mein Formular gibt es ein Auswahlfeld in der Umgebung von 50, 20 oder 10km.

In der Ausgabe soll dann stehen: Ca. ...Km entfernt.

Wie geht sowas am besten?

Eine Frage nebenbei: Wo bekommt man eigentlich die Tabelle hier mit den ganzen Koordinaten (hab die jetzigen nur bekommen)?

Kann mir einer helfen?

Danke im vorraus
PC-Gamer
 

sheel

I love Asm
#2
Hi

willst das unbedingt selbst machen oder würdest du Fertiges auch verwenden?
Hätte im Dezember nämlich so was gemacht:
http://www.tutorials.de/content/1314-php-klasse-fuer-ortsbezogene-umkreissuche.html
Geht sicher noch besser, funktioniert aber so auch ganz gut.

Ortstabellen mit allen Angaben:
Entweder von der Post (kostet was) oder Opengeodb (nicht ganz so vollständig, aber dafür gratis)

Wenn die Tabelle vom Stand Dezember 2011 sein darf
findest du alles Benötigte schon bei meinem Link dabei
(paar kleinere Änderungen hats seitdem sicher gegeben).

Gruß
 
#3
Danke für die Antwort :)

Geht das auch mit zwei Tabellen?

Ich hab 2 MySql- Tabellen.

Meine Haupttabelle seht ungefähr so aus:
Name, Straße, Land, Ort, PLZ, Bemerkungen.

Meine 2 Tabelle mit den Koordinaten so (oben ist ein Beispiel Datensatz):
plz, x-cord, y-cord

meine SQL Abfrage prüft zurzeit nur welche Firm zu der jeweiligen PLZ gehört.

Wie kann ich die zweite Tabelle am besten einbinden und wie muss ich prüfen?
 

sheel

I love Asm
#4
(Willst du jetzt meins abändern oder selbst was Neues machen?
Ist mir noch immer nicht ganz klar.)

Von der Ausgangsfirma musst du die PLZ sowieso mal per Select rausholen.

Einfachlösung wäre dann nach PLZ zu suchen und dann noch eine "where plz in(...)"-Abfrage
für die Firmen zusammenzubasteln. Ca. sowas:
PHP:
//Zuerst PLZ-Suche

$abfrage = 'select name from firma where plz in(';
foreach($PLZSuchergebnis)
    $abfrage .= $ergebnis->plz . ', ';
$abfrage .= ');';
//Ausführen
Schöner (und etwas schneller bei der Ausführung) wäre das natürlich gleich alles in SQL...
aber auch schwieriger zu programmieren.
Vorhandene Klasse müsste dafür auch größeren Änderungen unterzogen werden.
 
#5
Am einfachsten wäre deins abzuändern das er die zwei Tabellen nutzt. Also du meinst erstmal die PLZ in der Tabelle wo die ganzen Längen unf Breitenkoordinaten abzufragen? Wie komme ich dann aber zu den anderen PLZ in der nähe, sagen wir 30km? und wie bekomme ich ür die Ausgabe dann die Entfernung raus?
 
#7
Brauchst du nicht sein :D

Also jetzt werde ich mal konkret:

In meiner Haupttabelle befinden sich bestimmte Ärzte auf einen Gebiet. Also werden Datensätze mit Namen, Straße, Ort und der PLZ angelegt.
Der Benutzer hat ein Formular wo er eine PLZ eintragen kann, dazu hat er die Möglichkeit in den Umkreis von 50, 20 und 10Km zu suchen. Als Ausgabe sollen alle Ärzte angezeigt werden, die die PLZ haben oder eben im Umkreis. Zusätzlich soll für jeden gefunden Datensatz die ungefähre Entfernung ausgegeben werden.
Parallel zur Haupttabelle hab ich eine andere Tabelle wo alle PLZ von Deutschland, Österreich und der Schweiz mit den x bzw. y- Koordinaten drinstehen.

Jetzt habe ich in meiner Sql- Abfrage nur ein "where plz = '$plz'" drin, also ohne Berücksichtig des Umkreises.
 

erik s.

Erfahrenes Mitglied
#8
Angenommen, die beiden Tabelle sehen so aus:

Code:
geodb_firmen
+---------+
| id      |
| name    |
| adresse |
| plz     |
+---------+

geodb_koordinaten
+---------+
| loc_id  |
| plz     |
| ort     |
| lat     |
| lon     |
+---------+
sowie
PHP:
$plz  = '12345'; // PLZ als VARCHAR
$dist = 10; // maximale Entfernung in Kilometern
Dann könnte deine Abfrage wie folgt aussehen:
SQL:
SELECT F.*,K.ort,
((ACOS(SIN(K2.lat * PI() /180) * SIN(K.lat * PI() /180) + COS(K2.lat * PI() /180) * COS(K.lat * PI() /180) * COS((K2.lon - K.lon) * PI() /180)) *180 / PI()) *60 * 1.1515 * 1.609344) AS distance
FROM geodb_firmen AS F
JOIN geodb_koordinaten AS K ON K.plz=F.plz
LEFT JOIN geodb_koordinaten AS K2 ON K2.plz='$plz'
HAVING distance <= $dist
ORDER BY distance
Das Ergebnis sieht dann in etwa so aus: (PLZ war 60385, Umkreis maximal 5km)
umkreissuche.png
 
#9
Danke für die Antwort. Fast funktioniert es. Ist diese Abfrage Performance fressend, da er ungefähr 2 Minuten lang läd bis eine Ausgabe kommt oder liegt das an den folgenden Fehle?
Der andere Fehler ist: 9mal gibt er den selben Datensatz aus bis der nächste kommt (dann auch wieder 9mal):(

edit: opps mein Code vergessen:

PHP:
SELECT F.*, ((ACOS(SIN(K2.coordx * PI() /180) * SIN(K.coordx * PI() /180) + COS(K2.coordx * PI() /180) * COS(K.coordx * PI() /180) * COS((K2.coordy - K.coordy) * PI() /180)) *180 / PI()) *60 * 1.1515 * 1.609344) AS distance FROM tx_implantateadressen_adr AS F JOIN tx_implantateadressen_plz AS K ON K.plz=F.plz LEFT JOIN tx_implantateadressen_plz AS K2 ON K2.plz='01324' HAVING distance <= 100 ORDER BY distance
 
#10
Wie du schon sagst ist die direkte Entfernungsberechnung in SQL leider sehr langsam.
Hab deshalb die Nachbearbeitung per PHP drin (in SQL nur linear).
Der Nachteil ist eben, dass du nicht mit einem SQL-Befehl deine Ärzte direkt bekommen kannst...
 

erik s.

Erfahrenes Mitglied
#12
Kannst du uns noch ein bisschen mehr "Futter" geben, mit dem wir arbeiten können?
Wie genau sehen denn deine Tabellenstrukturen aus? Ein paar Beispieleinträge dazu? Wie sieht der PHP-Code im Kontext der mySQL-Abfrage aus?
Dass deine Ergebnisse mehrfach ausgegeben werden, deutet darauf hin, dass in deiner plz-Tabelle mehrere Einträge zu einer PLZ vorkommen.

Hier gibt's auch noch ein paar nette Denkanstöße.
Du kannst das Ganze natürlich auch anders realisieren, zum Beispiel mittels OOP. Es gibt dafür auch sehr einfache, aber effektive fertige Klassen, z.B. hier.

So könnte die Abfrage bei dir auch aussehen (Tabellen bzw. Spalten noch anpassen):
SQL:
SELECT F . * , K.ort, (
(
ACOS( SIN( K2.lat * PI( ) /180 ) * SIN( K.lat * PI( ) /180 ) + COS( K2.lat * PI( ) /180 ) * COS( K.lat * PI( ) /180 ) * COS( (
K2.lon - K.lon
) * PI( ) /180 ) ) *180 / PI( )
) *60 * 1.1515 * 1.609344
) AS distance
FROM geodb_firmen AS F
JOIN geodb_koordinaten AS K ON K.plz = F.plz
JOIN (
SELECT lat, lon
FROM geodb_koordinaten
WHERE plz = '01324'
LIMIT 1
) AS K2
HAVING distance <= 20
ORDER BY distance
 
#13
Ich hab jetzt doch die geodb Datenbank genommen, da es mehrere doppelte Datensätze in der anderen gab. Wie kann man die geodb mit den deutschen Datensätzen am besten importieren, da wegen der größe immer ein PHp- Timeout kommt (nicht beim Upload sondern bei den inserts)?. Ich benötige doch nicht alle geodb's für meine PLZ Umkreisuche oder? Zum Testzwecken habe ich jetzt ca. 2000 Datensätze importiert, hoffe es reicht.

Also meine Tabelle mit den Ärzten hat folgenden aufbau:
ID. Name, Ort, PLZ, Bemerkungen

mit folgender Datensatz:

214, Mustermann, Dresden, 01324, blabla

Der Nutzer sucht z.B. nach der PLZ 01829, 30KM Entfernung

Bei der Ausgabe soll dann der Arzt Mustermann angezeigt werden inklusive mit der ungefähtet Entfernung in Km.

Wie mach ich das?
 
#14
Ich hab jetzt doch die geodb Datenbank genommen, da es mehrere doppelte Datensätze in der anderen gab.
Nö. Glaub ich nicht.
Wie kann man die geodb mit den deutschen Datensätzen am besten importieren, da wegen der größe immer ein PHp- Timeout kommt (nicht beim Upload sondern bei den inserts)?
Hast du SSH-Zugriff?

...
Bei der Ausgabe soll dann der Arzt Mustermann angezeigt werden inklusive mit der ungefähtet Entfernung in Km.

Wie mach ich das?
Aus dem, was oben steht, könnte man doch schon was machen...?
 
#15
Hi,

sorry das ich mich jetzt erst melde.

Ein SSH Zugriff habe ich nicht, aber wie würde sowas gehen wenn ich ein hätte (vll. bekomme ich ja einen)?

Also ich habe jetzt das Beispiel (versucht) von GEODB abzuarbeiten.

Mein PHP- CODES ist jetzt:

PHP:
$erdradius = 6371;

     $sql = "SELECT coo.lon, coo.lat FROM geodb_coordinates AS coo INNER JOIN geodb_textdata AS textdata ON textdata.loc_id = coo.loc_id
     WHERE textdata.text_val = '$plz'";
     $res = $GLOBALS["TYPO3_DB"]->sql_query($sql);

list($lon, $lat) = mysql_fetch_row($res);

$lambda = $lon * pi() / 180;
$phi = $lat * pi() / 180;
$ursprungx = $erdradius * cos($phi) * cos($lambda);
$ursprungy = $erdradius * cos($phi) * sin($lambda);
$ursprungz = $erdradius * sin($phi);
$this->plzcoord($plz);

$sql = "SELECT adressen.*, coo.loc_id, coo.lon, coo.lat FROM tx_implantateadressen_adr AS adressen INNER JOIN geodb_textdata AS textdata ON textdata.text_val = adressen.plz
AND textdata.text_type = '500300000' INNER JOIN geodb_coordinates AS coo ON textdata.loc_id = coo.loc_id WHERE (2 * $erdradius) * ASIN(SQRT(POWER($ursprungx - $erdradius * COS(coo.lat * PI() / 180) * COS(coo.lon * PI() / 180), 2)
+ POWER($ursprungy - $erdradius * COS(coo.lat * PI() / 180) * SIN(coo.lon * PI() / 180), 2) + POWER($ursprungz - $erdradius * SIN(coo.lat * PI() / 180), 2)
) / (2 * $erdradius)) <= $entf ";
Letztere SQL- Befehl seht dann so aus (echo):
Code:
SELECT adressen.*, coo.loc_id, coo.lon, coo.lat FROM tx_implantateadressen_adr AS adressen INNER JOIN geodb_textdata AS textdata ON textdata.text_val = adressen.plz AND textdata.text_type = '500300000' INNER JOIN geodb_coordinates AS coo ON textdata.loc_id = coo.loc_id WHERE (2 * 6371) * ASIN(SQRT(POWER(3890.3007065438 - 6371 * COS(coo.lat * PI() / 180) * COS(coo.lon * PI() / 180), 2) + POWER(951.95052658408 - 6371 * COS(coo.lat * PI() / 180) * SIN(coo.lon * PI() / 180), 2) + POWER(4954.6938964583 - 6371 * SIN(coo.lat * PI() / 180), 2) ) / (2 * 6371)) <= 100
Wenn ich den mit PHPMyAdmin ausführen will, läd er Stundenlang aber nix passiert:-(

Sind die Berechnung zu lang, wenn ja wie kann ich sie per PHP machen?

Im Tutorial steht als nächster Schritt, das man in der Haupttabelle noch die Spalten lon, lat, koordx, koordy und koordz hinzufügen soll. Wie bekomme ich aber die Werte da hinein?
 
#16
Hallo, ich habe mal ein PHP PLZ Umkreissuche Script fuer USA/Canada geschrieben. Das sollte sich einfach an eine deutsche PLZ Datenbank anpassen lassen.

Hier der Code:

PHP:
<?php 
/*====================================================================== 
** ZIP Codes in a Radius in USA and Canada 
** 
** This PHP Script requires 4 GET parameters: zipcode, country (us/ca), radius, unit (miles/km) 
** Plus the database tables us and ca containing the ZIP Code-Lon/Lat data. 
** 
** Example call: /tools_radius.php?zipcode=90210&country=us&radius=10&unit=miles 
**  
**====================================================================== 
*/ 

/* Connecting to MySQL server: */ 
@mysql_connect($CFG_DB['db_host'], $CFG_DB['db_user'], $CFG_DB['db_pass']) 
    or die("Error: mysql_connect() failed"); 

/* Selecting client character set: */ 
mysql_set_charset('utf8'); 

/* Selecting database: */ 
@mysql_select_db($CFG_DB['db_base']) 
    or die("Error: mysql_select_db() failed"); 


function getCountryIndex($sCountry) { 
    static $aIndexes= array("us" => 1, "ca" => 2); 
    return isset($aIndexes[$sCountry])? $aIndexes[$sCountry] : false; 
} 

function getZipName($sCountry) { 
    if (!($nIndex= getCountryIndex($sCountry))) return false; 
    static $aVals= array(1=> 'zipcode', 2=> 'postalcode'); 
    return $aVals[$nIndex]; 
} 

/* Get info for a given ZIP Code value */ 
function getInfoByZip($sCountry, $sZipValue) { 
    if (!($sZipName= getZipName($sCountry))) return false; 
    $sql= "SELECT * FROM `$sCountry` WHERE `$sZipName`='$sZipValue' LIMIT 1"; 
    if (!($h_res= mysql_query($sql)) || !mysql_num_rows($h_res)) return false; 
    $b_ok= ($a_row= mysql_fetch_assoc($h_res)) && count($a_row); 
    mysql_free_result($h_res); 
    return $b_ok? $a_row : false; 
} 

/* Get coordinates for a given ZIP Code value */ 
function getCoordsByZip($sCountry, $sZipValue) { 
    if (!($sZipName= getZipName($sCountry))) return false; 
    $sql= "SELECT `longitude`, `latitude` FROM `$sCountry` WHERE `$sZipName`='$sZipValue' LIMIT 1"; 
    if (!($h_res= mysql_query($sql)) || !mysql_num_rows($h_res)) return false; 
    $b_ok= ($a_row= mysql_fetch_row($h_res)) && count($a_row) == 2; 
    mysql_free_result($h_res); 
    return $b_ok? $a_row : false; 
} 

/* Get all ZIP Codes in within the given Radius from a given ZIP Code */ 
function getZipsByRadius($sRadius, $sCountry, $sZipValue, $sLatitude, $sLongitude) { 
    if (!($nIndex= getCountryIndex($sCountry))) return false; 
    $fRadius = (float)$sRadius; 
    $fLatitude = (float)$sLatitude; 
    $fLongitude = (float)$sLongitude; 
    $sXprDistance =  "SQRT(POWER(($fLatitude-latitude)*110.7,2)+POWER(($fLongitude-longitude)*75.6,2))"; 
    static $aVals= array(1=> ", statecode AS areacode", 2=> ", provincecode AS areacode"); 
    $sXtraFields= $aVals[$nIndex]; 
    $sql = "SELECT `city`, `longitude`, `latitude`, `zipcode`, $sXprDistance AS distance $sXtraFields FROM `$sCountry` WHERE $sXprDistance <= '$fRadius' ORDER BY distance ASC"; 
    if (!($h_res= mysql_query($sql)) || !mysql_num_rows($h_res)) return false; 
    $a_ret= array(); 
    while ($a_row= mysql_fetch_assoc($h_res)) { 
        if (count($a_row)) $a_ret[]= $a_row; 
    } 
    mysql_free_result($h_res); 
    return count($a_ret)? $a_ret : false; 
} 

define('F_KMPERMILE', 1.609344    ); 

function sqr($x) { 
    return $x * $x; 
} 

$b_ok= isset($_REQUEST['zipcode']) && isset($_REQUEST['country']) && isset($_REQUEST['radius']); 
if (!$b_ok) 
    die("Error: parameters are missed"); 

$sZipCode = $_REQUEST['zipcode']; 
$sCountry = $_REQUEST['country']; 
$sRadius = $_REQUEST['radius']; 
$fRadius = (float)$sRadius; 
$sUnit = (isset($_REQUEST['unit']) && $_REQUEST['unit'] == "km")? "km" : "miles"; 
if ($bUnitMiles = $sUnit=="miles") $fRadius = $fRadius * F_KMPERMILE; 

/* Get Info for ZIP Code */ 
if (!($a_info = getInfoByZip($sCountry, $sZipCode))) 
    die("Error: zipcode not found"); 

$sCity = $a_info["city"]; 
$sLongitude = $a_info["longitude"]; 
$sLatitude = $a_info["latitude"]; 
$fLatitude = (float)$sLatitude; 
$fLongitude = (float)$sLongitude; 
if ($sCountry == "us") { 
    $sAreacode = $a_info["statecode"]; 
} 
else { 
    $sAreacode = $a_info["provincecode"]; 
} 
$sMaptxt = "$sRadius $sUnit around $sZIPName<br/>$sZIPCode $sCity"; 

/* Get Info for ZIP Code */ 
if (!($a_result = getZipsByRadius($fRadius, $sCountry, $sZipCode, $sLatitude, $sLongitude))) 
    die("Error: zipcode not found"); 

$sResultlist = ''; $sCondition = ''; 
foreach ($a_result as $i=> $a_row) { 
    $sZipCode= $a_row["zipcode"]; 
    $sCity= $a_row["city"]; 
    $sAreacode= $a_row["areacode"]; 
    if ($i==0) { 
        $sDistance = " (0  $sUnit)"; 
        $sResultlist = "$sZipCode $sCity, $sAreacode$sDistance<br>"; 
        $sCondition .= "'$sZipCode'"; 
        continue; 
    } 
    if (strpos($sCondition, "'$sZipCode'")!==false) continue; 
    $sCondition .= ", '$sZipCode'"; 
    $fLatDiff = $fLatitude - (float)$a_row["latitude"]; 
    $fLonDiff = $fLongitude - (float)$a_row["longitude"]; 
    if ($bUnitMiles) 
        $sDistance = " (". Round(sqrt(sqr($fLatDiff*110.7)+sqr($fLonDiff*75.6))/F_KMPERMILE,1). "  ". $sUnit. ")"; 
    else 
        $sDistance = " (". Round(sqrt(sqr($fLatDiff*110.7)+sqr($fLonDiff*75.6)),1). "  ". $sUnit. ")"; 
    $sResultlist .= "$sZipCode $sCity, $sAreacode$sDistance<br />"; 
} 
$sCondition = " zipcode IN ($sCondition)"; 
/* $sCondition contains the SQL-WHERE statement that could now be used to extract all the 'in-radius' datasets out of another table */ 


?><!DOCTYPE html> 
<html> 
  <head> 
    <meta charset="utf-8"> 
    <title>ZIP Codes in a Radius</title> 

</head> 
  <body> 
    <?=$sResultlist?> 
  </body> 
</html>

Die OpenGeoDB ist auch meiner Erfahrung nach leider nicht zu gebrauchen. Es fehlen viele PLZ und sehr viele Long/Lat Werte sind fehlerhaft.
Ich habe sehr gute Erfahrung mit http://www.plz-umkreis.com gemacht. Ist zwar nicht umsonst, aber die Daten sind top. Ich denke fuer ein kommerzielles Projekt empfehlenswert.
 
#17
Es liegt an der Allgemeinheit, die OpenGeoDB zu verbessern :)
Und für so etwas Öffentliches hätt ich kommerzielle Sachen nicht nehmen dürfen.

Wenns etwas kosten darf, kann ich auch einfach die gute alte Post empfehlen :rolleyes: