PLZ- Umkreissuche

Philipp88 · 04.07.2012

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:

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

Würde mich auf eure Hilfen sehr freuen.
Philipp

Duddle · 04.07.2012

AW: PLZ- Umkreissuche

Du nimmst , 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 )
c) alternativ die Klasse in dem Tutorial nutzen (die ja schon optimiert wurde)

Duddle

Philipp88 · 06.07.2012

AW: PLZ- Umkreissuche

@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 · 06.07.2012

AW: PLZ- Umkreissuche

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

Duddle

Philipp88 · 09.07.2012

AW: PLZ- Umkreissuche

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

PHP:

 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 · 09.07.2012

AW: PLZ- Umkreissuche

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 · 09.07.2012

AW: PLZ- Umkreissuche

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.

Code:

plz: 01127 
koordx 3890.3007065438 
koordy 951.95052658408 
koordz 4954.6938964583

Für unsere Postleizahlen 01067 berechnet php folgende Werte:

Code:

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

Danke für Deine Hilfe.

Duddle · 09.07.2012

AW: PLZ- Umkreissuche

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 · 09.07.2012

AW: PLZ- Umkreissuche

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 · 09.07.2012

AW: PLZ- Umkreissuche

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 · 02.10.2012

AW: PLZ- Umkreissuche

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:

<?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 · 07.12.2012

AW: PLZ- Umkreissuche

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?option=com_ap_labor&task=searchForm&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 · 07.12.2012

AW: PLZ- Umkreissuche

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 · 07.12.2012

AW: PLZ- Umkreissuche

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

Code:

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 · 07.12.2012

AW: PLZ- Umkreissuche

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

Philipp88 · 07.12.2012

AW: PLZ- Umkreissuche

Na ok danke. Da werde ich wohl mal nachfragen.
Trotzdem danke für die Hilfe

Philipp

Philipp88 · 10.01.2013

AW: PLZ- Umkreissuche

Hi,

sorry das ich das Thema ausgraben muss.

Ich habe nochmal in der GEODB bei textdata geschaut: Wenn ich die PLZ "01324" suche, kommen 5 gleiche Datensätze mit unterschiedliche loc_id
Wenn ich aber nach der PLZ "01099" suche bekomme ich nur 4 Datensätze mit unterschiedlichen loc_id.

Das heißt die ersten 4 loc_ids der zwei PLZs sind identisch, bei "01324" gibt es noch eine weitere loc_id. Heißt das die letztere ist die Aktuellste und die wird zur Berechnung benötigt?
Mein Script nehmt ja die erstgefundene loc_id.
Da ist es ja logisch, wenn die Distanz gleich ist wenn die erste loc_id identisch ist und nur die letzter unterschiedlich.
Liege ich da richtig?

edit: Ich habe gerade gemerkt, manchmal sind auch die loc_ids in der mitte unterschiedlich.

Duddle · 10.01.2013

AW: PLZ- Umkreissuche

Tabellen in relationalen Datenbanken sind grundsätzlich unsortiert. Es gibt also keinen letzten, ersten oder mittleren Eintrag. Auch kannst du von der angezeigten Reihenfolge keine Aktualität ableiten.

In der FAQ von OpenGeoDB, auch wenn diese etwas veraltet ist, steht:

Die Ortsdaten sind bislang nur mäßig getestet, wesentliche Fehler dürfte es noch in der Größenordnung von bis zu ein- oder zweihundert geben. Ungenauigkeiten der Koordinaten in der Größenordnung von 1 bis 4 Bogenminuten (bis zu 2 km) sind allerdings sehr viel häufiger zu vermuten. Das gilt für Deutschland, und auch nur für die eigenständigen Gemeinden. Ortschaften, die nicht eigenständig sind, fehlen zu weit über 90%. Die vielen Änderungen der Gemeindezugehörigkeiten der letzten Jahre in den östlicheren Bundesländern sind noch NICHT eingearbeitet (Stand Dezemeber 2005).

Bei Gemeinden sind i.A. die Koordinaten eines gleichnamigen Hauptortes angegeben, gelegentlich aber abweichend von dieser Regel der ungefähre Gemeindemittelpunkt ermittelt aus den Koordinaten aller beteiligter Ortschaften, was von der Logik her eh konsistenter erscheint.

Du benutzt von Nutzern in freier Arbeit erzeugte Daten. Diese Daten sind ungenau und unvollständig, also kommt es zu Fehlern. Wenn du bessere Daten brauchst musst du sie entweder selbst einpflegen oder von einer anderen Quelle beziehen.

Duddle

Philipp88 · 11.01.2013

AW: PLZ- Umkreissuche

Ich habe jetzt eine andere Datenbank mit den X- und Y-Koordinaten der einzelnen Postleitzahlen. Jetzt fehlt mir aber noch die Z-Koordinate. Wie kann ich diese aus den zwei Gegebenen ermitteln? Kann man mit der X- und Y-Koordinate auf lat und lon umrechnen - wenn ja wie geht das?

Danke im Vorraus,

Philipp

Duddle · 11.01.2013

AW: PLZ- Umkreissuche

Es kommt darauf an, was in den neuen Daten x und y bedeuten. Falls das die kartesischen Koordinaten sind, wie von der vorher gezeigten PHP-Klasse aus lat & lon berechnet, dann kannst du das zurückrechnen.

Aus

Code:

radius = sqrt(x**2 + y**2 + z**2)

erhälst du z. Dann kannst du die Gleichungen aus der umstellen und erhälst $lambda und $phi, was lat und lon entspricht (wenn du das Winkelmaß beachtest).

Duddle