PLZ Geokoordinaten

[paule78]

Hallo zusammen, erstmal großes Lob für diesen Informativen Thread, hat mir super weitergeholfen.

Ich baue gerade eine Umkreissuche für eine gegeben PLZ.
RGummi hat ja schon eine seite gebastelt mit der das wunderbar geht

siehe RGummi:
Hallo,
ist jetzt was zum Testen da
Umkreissuche
eure PLZ eingeben und umkreis

So, nur leider hab ich es nicht geschaft die beiden selects zu vereinen, ich hole mir also mit dem ersten select anhand der plz die längen und breitenangaben, gebe die dann an php und hole mir damit dann mit dem zweiten select die plz's im umkreis von x km.

Kann man diese beiden select nicht zu einem vereinen (subselects oder joins oder sowas) um das ganze etwas perfomanter zu gestalten. Durch das zurückliefern des querrys an php, dann das durchlaufen (auch wenns nur ein eintrag ist) und dann der erneute querry wird man wohl nicht gerade zeit gutmachen im vergleich zu einem einzelnen, kombinierten select, oder?

hier nochmal direkt die beiden selects (etwas vereinfacht):

SELECT Phi,Theta FROM PLZ_Daten WHERE PLZ='<irgendeine  plz>';

SELECT PLZ, Ort FROM PLZ_Daten WHERE 6367.46*ACOS(cos(Theta)*cos(0.862091)*(sin(Phi)*sin(0.154011)+cos(Phi)*cos(0.154011))+sin(Theta)*sin(0.862091)) <= 10

gruss,
paul

[cEEkAy]

Hi Leute, hab das ganze Thema mit Interesse verfolgt, steig aber noch nicht so ganz dahinter.

Hat von euch vielleicht jemand ein fertiges script und eine kleine datenbank damit ich das ganze mal selber testen kann ???

Ich möchte erstmal versuchen eine einfache Umkreissuche nach Postleitzahlen hinzubekommen, und später erst vielleicht dieses ganze fortgeschrittenenzeugs mit entfernung und koordinaten hinzufügen...

Freu mich über Tips und Tricks

Grüße cEEkAY

[andik2000]

Nun, um eine "einfache" Umkreissuche zu realisieren, mußt Du dich zwangsläufig mit den koordinaten auseinandersetzen.
Der Einfachste Fall ist eine Datenbank mit allen Postleitzahlen und den dazugehörigen Geokoordinaten.
Um die Entfernung zwischen zwei PLZs zu berechnen brauchst Du pro PLZ die geografische Längen- und Breitenangabe.
Natürlich sind diese nicht auf den Meter genau, da immer der Mittelpunkt eines PLZ-Gebietes als Berechnung der Geokoorinaten dient. Bei einem Dorf, was evtl. nur eine PLZ hat, wird also der Ortskern gespeichert, obwohl das ganze Dorf vielleicht einen Durchmesser von mehreren Kilometern hat. Das nur mal als Info nebenbei.

Alle nötigen Formeln und Scripts (auch die zum "einfachen" Berechnen) findest Du eigentlich hier.

Schau mal am Anfang, da habe ich ein Script gepostet. Damit habe ich es auch gelernt.

Grüße, Andi

[stanFRA]

Hi Ihr Alle,

also ich bin jetzt seit 2 Tagen mit nem ähnlichen Problem wie einige andere auch im web unterwegs...

Ich benötige alle ca. 8300 PLZ in Deutschland und die dazugehörigen Geokoordinaten (im Mittelpunkt), kommaseparierte Textdatei ist vollkommend ausreichend.

Irgendwie führen alle hier genanten Links nicht so wirklich zum Erfolg....

Weiss einer eventl. weiter - ausser das Ding bei der Post zu kaufen...

THX Stan

[andik2000]

Völlig kostenlos!

http://opengeodb.de/download/
Wurde hier bereits gepostet!

Grüße
Andi

[stanFRA]

danke für den tip, hab das teil gestern gesaugt aber war nicht ganz zufrieden...da sind doch nur die koordinaten der einzelnen städte hinterlegt, nicht aber die mittelkoordinaten JEDER postleitzahl....oder bin ich jetzt blöd oder was ?

[andik2000]

Also darin ist alles anthalten : PLZ, Geodaten, Stadtname, Bundesland, Landkreis, KFZ-Kennzeichen, Gemeindeschlüssel etc.

Jenachdem was Du brauchst kannst Du den Rest rausschmeißen. Letztendlich bis auf PLZ und Geodaten, falls Du nur diese zur Berechnung brauchst.
Einige hier arbeiten ja schon fleißig damit, die Scripts beziehen sich teilweise auch direkt auf diese DB, darum sollte es mittlerweile aufgefallen sein, wenn nicht alle PLZs erfasst worden seien.
Ich arbeite noch mit einer gekauften DB, die ca. 8250 Einträge hat. Die Open Geo DB ist ca. genau so lang, sogar länger. Darum glaube ich schon, dass akke PLZ erfasst sind.

Grüße

[Primusio]

Hallöchen,
habe gerade mit gierigen Augen jeden Beitrag verschlungen

Habe darauf hin ein Test Script geschrieben, was aber nicht so funzt wie es soll und zwar auf Basis von opengeodb.

$phi = deg2rad(12.25);
$theta = deg2rad(51.8333);
$radius = 6367.46;

$result = mysql_query("SELECT plz, ort, ".$radius."*ACOS(cos(breite)*cos(".$theta.")*(sin(laenge)*sin(".$phi.")+cos(laenge)*cos(".$phi."))+sin(breite)*sin(".$theta.")) AS Distance 
         FROM geodb_locations 
         WHERE ".$radius."*ACOS(cos(breite)*cos(".$theta.")*(sin(laenge)*sin(".$phi.")+cos(laenge)*cos(".$phi."))+sin(breite)*sin(".$theta.")) <= 1000 ORDER BY Distance");

while($ort = mysql_fetch_array($result)) {
   echo $ort['plz']." ".$ort['Distance']."<br>";
}

Die Homekoordinaten sind von meiner Heimatstadt und bei der WHERE Klausel habe ich "<= 1000" genommen, um überhaupt Werte zu bekommen.

Die erste Distanz ist "113.640198" und soweit liegt unsere Stadt auch nicht vom Schuss ab. Kann mir jemand sagen, wo mein Fehler liegt ? probiere hier schon seit 3 Stunden herum.

Würde mich über schnelle Hilfe wirklich freuen.

MfG
Primusio

[Pkolb]

Hallo Leutz,
so, jetzt schalte ich mich nach einer langen Pause auch wieder in diese Gesprächsrunde.

Nach einigen Tests mit der Open- Geodatenbank bin ich der Meinung, es wären (fast) alle PLZs enthalten.
Das Problem mit diesen Datensätzen ist jedoch, dass z.B. gesamt Frankfurt zwar viele PLZs aber nur eine einzigste Koordinate hat, was den Leistungsumfang der Datenbank erheblich beeinträchtigt.
Das bedeutet nämlich, dass wenn ich in Frankfurt z.B. eine Filiale suche und wohne selbst in Frankfurt, wird mir die Filiale mit 0km Entfernung angegeben. Dabei kann aber sein, das die Filiale in Wirklichkeit mehrere Kilometer weit weg liegt!
Also ist die Datenbank in solchen Fällen total funktionslos.

[Chriss]

Original geschrieben von RGummi
$phi = deg2rad(11.5583); // zum Test
$theta = deg2rad(48.1627); // zum Test
$radius = 6367.46; // Mittelwert zwichen Äquator und Polradius
$umkreis = 25;

"SELECT PLZ, Ort, ".$radius."*SQRT(2*(1-cos(Theta)*cos(".$theta.")*
(sin(Phi)*sin(".$phi.")+cos(Phi)*cos(".$phi."))-sin(Theta)*sin(".$theta.")))
AS Distance FROM ".PLZ_DATA_TABLE." WHERE ".$radius."*SQRT(2*(1-
cos(Theta)*cos(".$theta.")*(sin(Phi)*sin(".$phi.")+cos(Phi)*cos(".$phi."))-
sin(Theta)*sin(".$theta."))) <= ".$umkreis." ORDER BY Distance";

wobei theta und phi in rad anzugeben sind !!! Auch in der Datenbank

deg2rad() leistet da gute Dienste beim konvertieren

Da ich die Tabelle geo_locations von opengeo verwende, und hier die Koordinaten in Grad (nicht Rad) vorliegen, müsste ich diese also in Rad umrechnen, um Deine Abfrage nutzen zu können.

Die Funktion Rad in Grad habe ich gefunden GRAD(), aber wie lautet die Funktion für Grad in Rad in MySQL?

@all: Hat schon jemand den Ansatz von @RGummi auf opengeo übertragen? Falls ja, könnte er ja mal seine Abfrage hier posten.

Auch ich finde diesen Thread äußerst interessant und danke allen Beteiligten für die interessanten Hinweise.

Gruß,
Chriss

[TobiaZ]

Blick ins Manual hilft

[Chriss]

Original geschrieben von TobiaZ
Blick ins Manual hilft

Danke! Im deutschen Manual (http://www.little-idiot.de/mysql/) habe ich es leider nicht gefunden und eine ordentliche Suche gibt es dort leider nicht.

Gruß,
Chriss

[TobiaZ]

dann solltest du dir das OFFIZIELLE mal vornehmen: http://www.mysql.com/doc/de/Mathematical_functions.html (auch in Deutsch)

[Chriss]

Original geschrieben von TobiaZ
dann solltest du dir das OFFIZIELLE mal vornehmen: http://www.mysql.com/doc/de/Mathematical_functions.html (auch in Deutsch)

Ja, da habe ich nun reingeschaut. Vielen Dank.
Chriss

[Chriss]

Den Ansatz von @RGummi habe ich nun auf die Tabelle geo_locations vom
Projekt opengeo übertragen:

SELECT ort, plzs, ".$radius."*SQRT(2*(1-cos(RADIANS(breite))*cos(".$breite.")*
(sin(RADIANS(laenge))*sin(".$laenge.")+cos(RADIANS(laenge))*cos(".$laenge."))-
sin(RADIANS(breite))*sin(".$breite."))) AS Distance FROM geo_locations
WHERE ".$radius."*SQRT(2*(1-cos(RADIANS(breite))*cos(".$breite.")*(sin(RADIANS(laenge))*
sin(".$laenge.")+cos(RADIANS(laenge))*cos(".$laenge."))-sin(RADIANS(breite))*
sin(".$breite."))) <= ".$umkreis." ORDER BY Distance" 

Anschließend habe ich eine Verknüpfung zu einer "Mitglieds"-Tabelle, eine Tabelle mit Namen und u. a. PLZ, erstellt, so dass ich diese Tabelle nun sortiert nach PLZ/Entfernung ausgeben kann. Zur Zeit nutze ich einen Umkreis von max. 50 km. Dann dauert die Abfrage ca. 4 Sekunden. Gibt es vielleicht noch Möglichkeiten, die Performance zu verbessern, so dass man den Umkreis noch weiter vergrößern kann?

Bringt ein Update auf MySQL 4.x ggf. einen entscheidenden Vorteil?

Gruß
Chriss
PS: Beirtrag bearbeitet, da sich anfängliche Frage erledigt hat.