PLZ- Umkreissuche

[Philipp88]

Hallo ihr,


für mein Projekt muss ich eine PLZ- Umkreissuche realisieren welches die Ergebnisse der unmittelbaren Umgebung anzeigt. Dazu habe ich die PLZ- Datenbank von Geodb genommen.

Jetzt habe ich versucht das Beispiel von GEODB abzuarbeiten.
Mein PHP- CODES ist jetzt:

PHP-Code:

$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):

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?

Würde mich auf eure Hilfen sehr freuen.
Philipp

[Duddle]

Du nimmst dieses Tutorial, stimmts?

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?

Schau dir den Konstruktor der Umkreissuche-Klasse an, die weiter unten steht. Dort wird alles (einmalig) ausgefüllt.

Ich weiß nicht, wie viele Einträge in der GeoDB-Tabelle sind, aber die Entfernungsabfrage muss ja für jeden Eintrag diese Berechnung durchführen. Ich bezweifle aber, dass das mehr als ein paar Sekunden dauern würde (naja, stark abhängig von dem MySQL-Server). An deiner Stelle würde ich
a) es erstmal ohne das WHERE versuchen, dann muss er nur Daten holen und das sollte sehr fix funktionieren
b) alternativ die Anzahl der Datensätze reduzieren und dort messen wie lange es für x viele Zeilen braucht (vielleicht hilft hier EXPLAIN)
c) alternativ die Klasse in dem Tutorial nutzen (die ja schon optimiert wurde)


Duddle

[Philipp88]

@Duddle ja das Tutorial hab ich genommen und jetzt läuft es mit der Optimierung Wie kann ich jetzt aber noch die ungefähre Entfernung ausgeben lassen?

[Duddle]

Das ist eine der Fragen im Fragen und Antworten-Bereich des Tutorials.


Duddle

[Philipp88]

Ich habe die Berechnung der Distanz in einer seperaten Funktion getan.
Leider berechnet er mir für jede PLZ eine unrealistische Entfernung aus...

PHP-Code:

 function distance($erdradius, $plz) {

   $dist = array();
   
   $res = $GLOBALS["TYPO3_DB"]->exec_SELECTquery("plz, longitude, latitude, koordx, koordy, koordz", "tx_implantateadressen_adr");
   $num = $GLOBALS["TYPO3_DB"]->sql_num_rows($res);
   
   for($i = 0; $i < $num; $i++) {

     $row = $GLOBALS["TYPO3_DB"]->sql_fetch_assoc($res);
     $plz2 = $row["plz"];
     $koordx = $row["koordx"];
     $koordy = $row["koordy"];
     $koordz = $row["koordz"];
     
     list($lon, $lat) = $this->plzcoord($plz);
     list($x, $y, $z) = $this->kugel2kartesisch($erdradius, $lon, $lat);
     echo "<p>plz $plz2 <br> $erdradius<br>koordx $koordx <br>koordy $koordy <br>koordz $koordz</p>";
     
     $distance = 2* $erdradius * asin(sqrt(
                     pow($x - $koordx, 2)
                     + pow($y - $koordy, 2)
                     + pow($z - $koordz, 2)
                     ) / (2 * $erdradius));

     $dist[$plz2] = $distance;
   }
 } 


Ein Ergebnis sieht zb. so aus (01067 bis 01127):
1.5402783496429E-11

Wenn ich das via Google-Maps vergleiche, sind da nur 4,5km Entfernung.
Was ist da verkehrt?

$plz ist der Ursprung.

$plz2 sind die ganzen PLZ von der Datenbank mit den ganzen Koordinaten.

[Duddle]

Könntest du bitte alle benutzten Zwischenwerte ausgeben? Ich möchte nicht das ganze Ding aufsetzen um den Berechnungen folgen zu können.


Duddle

[Philipp88]

Hier mal die Daten zwischen PLZ 01067 und PLZ 01127 Dresden.
Diese Daten kommen von meiner Tabelle für die PLZ 01127:
Der Erdradius ist 6371.

plz: 01127 
koordx 3890.3007065438 
koordy 951.95052658408 
koordz 4954.6938964583

Für unsere Postleizahlen 01067 berechnet php folgende Werte:

lon: 13.75 
lat: 51.05 
ursprungx: 3890.3007065438 
ursprungy: 951.95052658408 
ursprungz 4954.6938964583

Danke für Deine Hilfe.

[Duddle]

Fällt dir nicht auf, dass die x, y und z-Koordinaten von beiden PLZ gleich sind?

Entweder stehen die falsch in der DB drin oder in deiner Logik ist ein Fehler. Ersteres kannst du leicht mit phpMyAdmin o.ä. überprüfen, letzteres mit nachvollziehen/debuggen der Zwischenschritte in den Berechnungen.


Duddle

[Philipp88]

Nach Stundenlager suche habe ich nun den Fehler:

In der geodb haben die PLZ von Dresden den selben lon bzw. lat Wert
Ist die geodb fehlerhaft oder gabs beim Inport bei mir nen Fehler?

[Duddle]

Auch das ist eine Frage im Frage und Antworten-Bereich des Tutorials.

Für viel Großstädte gilt leider, dass die Geo-Daten nicht bis auf Stadtteilebene / Postleitzahlenbereiche genau gespeichert sind.

Duddle

[oleolevfb]

Hallo, ich habe mal ein PLZ Umkreissuche Script fuer USA/Canada geschrieben. Das sollte sich leicht an eine deutsche PLZ Datenbank anpassen lassen.

Hier der Code:

PHP-Code:

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

[Philipp88]

Ich habe es jetzt selber hinbekommen, dass die Distanz richtig berechnet wird und angezeigt wird (der Fehler war, dass die Werte nicht richtig übergeben wurden).

Doch jetzt hab ich das Problem, dass innerhalb einer Ortschaft (Essen) die Distanz nicht berechnet wird. Ich weiß, dass dazu schon Kommentiert wurde. Allerdings zeigt die Originalseite die Distanzen konkret innerhalb einer Ortschaft an. Siehe:
http://www.implantate.com/index.php?...orm&Itemid=564

Die Postleitzahl, die ich von Essen bei der Suche eingegeben habe,lautet: 45127.
Somit wird eine Distanz zum nächsten Labor von etwa 11 km angezeigt.

Auf der aktuellen Seite wird zur Zeit 0 km angezeigt. Vielleicht hat doch noch jemand eine Lösung für dieses Problem.

[Duddle]

Wie eine fehlerhafte Schallplatte wiederhole ich mich, wiederhole ich mich: gib alle verfügbaren Daten für deine Berechnungen und deinen Algorithmus an. "Raten" ist kein effizienter Weg zu einer Lösung.


Duddle

[Philipp88]

Hallo,

ja sry, ich wusste nicht das die daten für das Problem noch von bedeutung sind, da ja wie gesagt die Distanz über größere Entfernung korrekt berechnet wird. Nur eben nicht innerhalb einer Ortschaft (Essen z.B.).

also hier die Ausgegeben Werte (gesuchte PLZ: 45127):

Von DB PLZ 45219:
koordx: 3939.6446407457, koordy: 484.33092653183, koordz: 4983.3989061933
 
meine gesuchte PLZ 45127:
lon: 7.00864483062742, lat: 51.4624880801468
ursprung_x: 3939.6446407457, ursprung_y: 484.33092653183, ursprung_z: 4983.3989061933

So wie es scheint sind die zwei Daten der PLZ hier identisch. Was ja oben schon diskutiert wurde, das in der geodb nicht alle aufgelistet sind der gleichen Stadt. Nur meine Vorlageseite (siehe oben) scheint auch die GeoDB zu nutzen, da sind aber auch die Distanz der selben Stadt drin.

[Duddle]

scheint

Das ist hier das Schlüsselwort.

Wenn du nur ungenaue Daten hast, kannst du nur ungenaue Ergebnisse berechnen. In der Informatik gibt es keine Magie. Entweder hat deine verlinkte Seite also mehr oder sie benutzt andere Daten.


Duddle