CIX Blog / Kategorie /
PHP Programmierung,
PHP Umkreissuche mit MySQL
verfasst 23.05.2008 von MediaCix | kurze URL: /t308 | 59 Kommentare
Da ich hin wieder gefragt werde wie man eine Umkreissuche macht, hier mal ein Code-Beispiel mit Link. Die Fragen nach Umkreissuchen kann man fast in jedem Forum lesen. Ich möchte ein Beispiel zeigen, wie man mit 1-2 Querys zur Datenbank eine Umkreissuche umsetzen könnte.
Zunächst brauchen wir eine Datenbank und eine Tabelle die Ort, PLZ, Längen- und Breitengrade enthält. Umso genauer diese Daten sind, umso genauer ist die Ausgabe. Um an solche Daten zu kommen bietet sich OpenGeoDB an. Hier gibt es unter Download die Möglichkeit die meisten Orte von Deutschland zu bekommen.
Link zu OpenGeoDB
Sicher gibt es noch mehr Anbieter, einige sind kostenlos, andere wollen Geld dafür. Welche Anbieter da besser ist, kann ich im Moment nicht sagen - habe bisher nicht alle ausprobiert.
Eine SQL-Datei mit 8178 Orten habe ich mal erstellt, und kann hier gezogen werden.
- Datei: plz.zip
- Größe: ca. 0.26 MByte
- Upload: 2010-12-26 12:28:53
- Downloads: 1463
Sollten Orte fehlen oder falsche Angaben haben dann schreibt mir eine Mail :)
Kommen wir nun zur Tabelle ...
Als Beispiel benutze ich mal diese Struktur:
CREATE TABLE `plz` (
`id` int(10) NOT NULL auto_increment,
`land` varchar(5) collate latin1_german1_ci NOT NULL,
`bundesland` varchar(50) collate latin1_german1_ci NOT NULL,
`bezirk` varchar(50) collate latin1_german1_ci NOT NULL,
`kreis` varchar(50) collate latin1_german1_ci NOT NULL,
`ort` varchar(50) collate latin1_german1_ci NOT NULL,
`geo_l` double NOT NULL,
`geo_b` double NOT NULL,
`plz` varchar(6) collate latin1_german1_ci NOT NULL,
`bland` varchar(5) collate latin1_german1_ci NOT NULL,
PRIMARY KEY (`id`)
);Wichtig sind erstmal Ort, PLZ, Länge und Breite in Grad. Alles andere (Bundesland etc.) kann bei Bedarf und je nach Einsatz erweitert bzw. ergänzt werden. Als Beispiel nehme ich mir den Ort Görlitz mit der PLZ 02826.
Zunächst müssen die Angaben von Länge und Breite in RAD umgerechnet werden. Das machen wir einfach mit einen Query zur Datenbank. Bei Görlitz ist z.B. die Länge 14.9841912137216 und die Breite 51.1468640705067. Später werden wir diese Angaben zur Berechnung benötigen.
» PHP
<?php
$plz = '02828';
$sql_rad = mysql_query("SELECT geo_l, geo_b FROM `plz` WHERE `plz` = '$plz' ");
$erg_rad = mysql_fetch_object($sql_rad);
// Umrechnung von GRAD IN RAD
$rad_l = $erg_rad->geo_l / 180 * M_PI;
$rad_b = $erg_rad->geo_b / 180 * M_PI;
?> Als Ergebnis erhalten wir dann RAD Länge 0.261498445209 und RAD Breite 0.893074262017. Und jetzt kann es schon losgehen. Damit die Berechnung schnell geht, ist es natürlich von Vorteil, wenn die Berechnung in einen MysQL-Query schon enthalten ist. Umwege über PHP-Scripte sind umständlich und aus Gründen der Performance nicht zu empfehlen. Jedenfalls würde ich davon abraten.
Nunja, und schon kann es losgehen und hier ein komplettes Beispiel:
» PHP
<?php
// natürlich erst eine Verbing zur Datenbank aufbauen!
// die PLZ nach der wir suchen
$plz = '02828';
// der Umkreis in Km
$umkreis = 30;
// Erdradius (geozentrischer Mittelwert) in Km
$radius = 6368;
/* -------------------------- */
$sql_rad = mysql_query("SELECT geo_l, geo_b FROM `plz` WHERE `plz` = '$plz' ");
$erg_rad = mysql_fetch_object($sql_rad);
// Umrechnung von GRAD IN RAD
$rad_l = $erg_rad->geo_l / 180 * M_PI;
$rad_b = $erg_rad->geo_b / 180 * M_PI;
// jetzt erfolgt die eigentliche Abfrage
$query = "SELECT ort, plz, (
".$radius." * SQRT(2*(1-cos(RADIANS(geo_b)) *
cos(".$rad_b.") * (sin(RADIANS(geo_l)) *
sin(".$rad_l.") + cos(RADIANS(geo_l)) *
cos(".$rad_l.")) - sin(RADIANS(geo_b)) * sin(".$rad_b.")))) AS Distance
FROM plz WHERE
".$radius." * SQRT(2*(1-cos(RADIANS(geo_b)) *
cos(".$rad_b.") * (sin(RADIANS(geo_l)) *
sin(".$rad_l.") + cos(RADIANS(geo_l)) *
cos(".$rad_l.")) - sin(RADIANS(geo_b)) * sin(".$rad_b."))) <= ".$umkreis."
ORDER BY Distance
";
// die Ausgabe (vereinfacht)
$sql = mysql_query($query);
while( $erg = mysql_fetch_object($sql) ) {
echo '<pre>', print_r($erg, true), '</pre>';
}
?> Als Ausgabe erhalten wir dann eine Liste:
stdClass Object
(
[ort] => Görlitz
[plz] => 02828
[Distance] => 0.00013418313233838
)
stdClass Object
(
[ort] => Görlitz
[plz] => 02826
[Distance] => 2.50490840864
)
stdClass Object
(
[ort] => Görlitz
[plz] => 02829
[Distance] => 3.7415466901342
)
...Mit den Orten können nun andere Werte verbunden werden - zum Beispiel Autohäuser, Angaben von Usern etc... Das ganze kann dann auch noch auf einer Karte dargestellt werden.
ein Online Beispiel gibt es hier
Die Umkreissuche kann auch so umgestellt werden, dass man nach MIN- und MAX-Umkreis suchen kann.
MIN- und MAX-Umkreissuche
Ich möchte aber auch an dieser Stelle auf eine andere Möglichkeit ohne eigene Datenbank hinweisen. Es gibt Dienste, wo man Orte mittels URL abfragen kann. Das Resultat kommt dann in Form einer XML-Datei.
Beispiel Link
Allerdings hat das auch Nachteile. Der Server ist nicht immer erreichbar. Man ist also darauf angewiesen sich darauf zu verlassen, was ich persönlich aber nie machen würde. Abfragen in kurzen Zeitabständen könnte zu Performance-Problemen führen.
Letztlich muss jeder selber für sich entscheiden welche Variante er benutzen möchte. Ich hoffe, dass ich einen kleinen Hinweis geben konnte, wie man eine Umkreissuche umsetzen könnte.
[Update]
Der Hinweis mit Having war natürlich gut, zumal er wirklich Tipparbeit erspart.
Hier noch die geänderte Version vom Query mit Having (Danke an Hendrik):
» PHP
<?php
$query = "SELECT ort, plz, (
".$radius." * SQRT(2*(1-cos(RADIANS(geo_b)) *
cos(".$rad_b.") * (sin(RADIANS(geo_l)) *
sin(".$rad_l.") + cos(RADIANS(geo_l)) *
cos(".$rad_l.")) - sin(RADIANS(geo_b)) * sin(".$rad_b.")))) AS Distance
FROM plz Having Distance <= ".$umkreis."
ORDER BY Distance
";
?> ein Online Beispiel mit Having
[Änderungen vorbehalten]
bisherige Kommentare
1
Hey, cool. Danke für die Ideen. Muss ich auch mal dran basteln. Hast du deine Tabelle und dein Skript auf die GeoDB aufgebaut oder woher nimmst du deine Datenbankeinträge?
01.07.2008
84.179.95.246 meint dazu: Hey, cool. Danke für die Ideen. Muss ich auch mal dran basteln. Hast du deine Tabelle und dein Skript auf die GeoDB aufgebaut oder woher nimmst du deine Datenbankeinträge?
2
Also die Einträge (ursprünglich eine CSV) sind von OpenGeoDB. Ich habe diese dann aber nachträglich noch etwas erweitert und teilweise umgeschrieben, da einige Orte nicht korrekte Angaben haben. Über ein kleines Script habe ich dann diese CSV als SQL-Insert in die Datenbank importiert.
01.07.2008
CIX88 meint dazu: Also die Einträge (ursprünglich eine CSV) sind von OpenGeoDB. Ich habe diese dann aber nachträglich noch etwas erweitert und teilweise umgeschrieben, da einige Orte nicht korrekte Angaben haben. Über ein kleines Script habe ich dann diese CSV als SQL-Insert in die Datenbank importiert.
3
Wäre es möglich die passende DB von dir zu bekommen? Evt. gegen einen kleinen Obolus?
02.07.2008
84.179.95.246 meint dazu: Wäre es möglich die passende DB von dir zu bekommen? Evt. gegen einen kleinen Obolus?
4
Jo kein Problem. Ich schicke dir eine ZIP-Datei bzw. Gzip-Datei an die E-Mail, die du hier mit angegeben hast. Damit kannst du dann die Datei zum Beispiel mit phpMyAdmin sofort hochladen. Die angelegte Tabelle kannst du natürlich vorher editieren oder dann umbenennen.
02.07.2008
CIX88 meint dazu: Jo kein Problem. Ich schicke dir eine ZIP-Datei bzw. Gzip-Datei an die E-Mail, die du hier mit angegeben hast. Damit kannst du dann die Datei zum Beispiel mit phpMyAdmin sofort hochladen. Die angelegte Tabelle kannst du natürlich vorher editieren oder dann umbenennen.
5
Vielen Dank, dann warte ich auf Ihre Nachricht. Bisher habe ich noch keine bekommen. Grüße
02.07.2008
84.179.95.246 meint dazu: Vielen Dank, dann warte ich auf Ihre Nachricht. Bisher habe ich noch keine bekommen. Grüße
6
Hallo!
Wie hast du denn die Datenbank umgestellt?
Von MySQL in CSV und dann wieder zu MySQL?
Wäre nett wenn du ne Info geben könntest :)
04.07.2008
Bruno Fischer meint dazu: Hallo!
Wie hast du denn die Datenbank umgestellt?
Von MySQL in CSV und dann wieder zu MySQL?
Wäre nett wenn du ne Info geben könntest :)
7
Ich hatte damals nur eine CSV gehabt. Da war nichts mit einer SQL-Datei. Also hab ich aus der CSV eine SQL gemacht.
04.07.2008
CIX88 meint dazu: Ich hatte damals nur eine CSV gehabt. Da war nichts mit einer SQL-Datei. Also hab ich aus der CSV eine SQL gemacht.
8
achso. schade. weil ich bei der datenbank die da online ist keine abfrage hinkriege. und diese hier wäre perfekt
04.07.2008
Bruno Fischer meint dazu: achso. schade. weil ich bei der datenbank die da online ist keine abfrage hinkriege. und diese hier wäre perfekt
9
Cooles Skript, aber wie schon mein Vorgänger, kannst du mir auch die Dateninhalte zur Verfügung stellen? Wäre echt super. Vielen Dank
Gruß
Andreas
14.07.2008
[Andreas Besier] meint dazu: Cooles Skript, aber wie schon mein Vorgänger, kannst du mir auch die Dateninhalte zur Verfügung stellen? Wäre echt super. Vielen Dank
Gruß
Andreas
10
Sehr gutes Skript, ich hätte auch gerne ein ZIP-File, bevor ich nun anfange das Rad neu erfinden. Wäre das möglich?
Danke im voraus,
Andy
23.07.2008
80.153.118.82 meint dazu: Sehr gutes Skript, ich hätte auch gerne ein ZIP-File, bevor ich nun anfange das Rad neu erfinden. Wäre das möglich?
Danke im voraus,
Andy
11
Im Grunde habe ich nichts anderes was schon auf OpenGeoDB angeboten wird. Nur mit dem Unterschied, dass ich daraus eine SQL-Datei gemacht habe. Derzeit bastel ich allerdings an einer neuen SQL-Datei. Über einen freien Download denke ich noch nach.
29.07.2008
CIX88 meint dazu: Im Grunde habe ich nichts anderes was schon auf OpenGeoDB angeboten wird. Nur mit dem Unterschied, dass ich daraus eine SQL-Datei gemacht habe. Derzeit bastel ich allerdings an einer neuen SQL-Datei. Über einen freien Download denke ich noch nach.
13
Absolutely fantastic! Ich habe die Skript verwendet und es funz. wunderbar. Als Erweiterung möchte ich gleich Österreich mit einbinden. Gibt es ein DB für Österreich und Schweiz, das ich herunterladen kann?
Danke vorrus
12.08.2008
88.70.134.182 meint dazu: Absolutely fantastic! Ich habe die Skript verwendet und es funz. wunderbar. Als Erweiterung möchte ich gleich Österreich mit einbinden. Gibt es ein DB für Österreich und Schweiz, das ich herunterladen kann?
Danke vorrus
14
Für Österreich und Schweiz habe ich leider keine Daten.
Ich weis nicht ob Daten bei OpenGeoDB vorhanden sind.
12.08.2008
CIX88 meint dazu: Für Österreich und Schweiz habe ich leider keine Daten.
Ich weis nicht ob Daten bei OpenGeoDB vorhanden sind.
15
Tolle Arbeit! :-)
Jedoch eine Auffälligkeit: Bei Umkreissuche (auch bei min/max wenn min=0 und SQL angepasst >= ".$min_umkreis.") wird die vorgegebene PLZ nicht mit ausgegeben - ausser bei deinem Beispiel mit Görlitz 02828 ;-)
Getestet z.B. mit 70176 oder 72160.
Gibts da nen Lösungsansatz?
16.08.2008
84.57.180.24 meint dazu: Tolle Arbeit! :-)
Jedoch eine Auffälligkeit: Bei Umkreissuche (auch bei min/max wenn min=0 und SQL angepasst >= ".$min_umkreis.") wird die vorgegebene PLZ nicht mit ausgegeben - ausser bei deinem Beispiel mit Görlitz 02828 ;-)
Getestet z.B. mit 70176 oder 72160.
Gibts da nen Lösungsansatz?
16
Klar gibt es eine Lösung. Bei der zweiten Variante mit MIN und MAX musst du nur das Query bei MIN und MAX ändern.
Beispiel:
Code Beispiel
16.08.2008
CIX88 meint dazu: Klar gibt es eine Lösung. Bei der zweiten Variante mit MIN und MAX musst du nur das Query bei MIN und MAX ändern.
Beispiel:
Code Beispiel
17
Danke für die schnelle Antwort (ich hätt auch schneller reagiert, aber die Emailbenachrichtigung bei Antwort scheint nicht zu funktionieren ;-)
Cool, bei Dir klappt das.
Bei mir 100% copy/paste bleibts immer noch weg :-(
Daten sind Deine aus der SQL-Datei - hast Du selbst einen anderen Datenbestand?
.. ich weiss erst mal nicht weiter.
PHP Version: 4.3.10
mySQL: 4.1.11-Debian_4sarge8
18.08.2008
Bernd meint dazu: Danke für die schnelle Antwort (ich hätt auch schneller reagiert, aber die Emailbenachrichtigung bei Antwort scheint nicht zu funktionieren ;-)
Cool, bei Dir klappt das.
Bei mir 100% copy/paste bleibts immer noch weg :-(
Daten sind Deine aus der SQL-Datei - hast Du selbst einen anderen Datenbestand?
.. ich weiss erst mal nicht weiter.
PHP Version: 4.3.10
mySQL: 4.1.11-Debian_4sarge8
18
Die Email-Benachrichtigung sollte jetzt wieder gehen.
Also ich habe hier keinen anderen Datenbestand.
Du hast allerdings recht altes PHP und MySQL.
18.08.2008
CIX88 meint dazu: Die Email-Benachrichtigung sollte jetzt wieder gehen.
Also ich habe hier keinen anderen Datenbestand.
Du hast allerdings recht altes PHP und MySQL.
19
Interessant ist ja aber, dass es bei PLZ 02828 funktioniert.
Ich geh mal davon aus, dass bei mir eben insgesamt anders gerechnet/gerundet wird und daher das eine oder andere Ergebnis mal so oder so ausfällt für deine Funktionen.
Wobei es mir schlichtweg schleierhaft ist, dass '0' nicht zwischen '0' und '10' liegen soll *lach*
18.08.2008
Bernd meint dazu: Interessant ist ja aber, dass es bei PLZ 02828 funktioniert.
Ich geh mal davon aus, dass bei mir eben insgesamt anders gerechnet/gerundet wird und daher das eine oder andere Ergebnis mal so oder so ausfällt für deine Funktionen.
Wobei es mir schlichtweg schleierhaft ist, dass '0' nicht zwischen '0' und '10' liegen soll *lach*
21
Ohje, da ist wohl ein Bug in meinen Blog.
Hoffe es geht jetzt wieder.
Also ich probier nochmal mit deinen PLZ-Beispielen.
Kann max. sein, dass an deinen Query etwas nicht stimmt.
Möglich das es deiner MySQL-Version liegt, aber da muss ich selber erstmal schauen.
18.08.2008
[CIX88] meint dazu: Ohje, da ist wohl ein Bug in meinen Blog.
Hoffe es geht jetzt wieder.
Also ich probier nochmal mit deinen PLZ-Beispielen.
Kann max. sein, dass an deinen Query etwas nicht stimmt.
Möglich das es deiner MySQL-Version liegt, aber da muss ich selber erstmal schauen.
22
hm, hab die gesamte php 1:1 kopiert, also kein Unterschied zu Deiner.
Hab auch versucht das genauer zu untersuchen. Es taucht bei recht vielen PLZs auf, jedoch konnte ich keinerlei Gemeinsamkeiten/Unterschiede feststellen.
18.08.2008
Bernd meint dazu: hm, hab die gesamte php 1:1 kopiert, also kein Unterschied zu Deiner.
Hab auch versucht das genauer zu untersuchen. Es taucht bei recht vielen PLZs auf, jedoch konnte ich keinerlei Gemeinsamkeiten/Unterschiede feststellen.
25
Kannst du deine Scripte als PHP5 laufen lassen?
Bei einigen Servern geht das mit der Endung *.php5 zu machen.
Kann das im Moment das nicht nachvollziehen.
Oder versuche das mal Lokal mit XAMPP zu machen - so als Vergleich.
18.08.2008
[CIX88] meint dazu: Kannst du deine Scripte als PHP5 laufen lassen?
Bei einigen Servern geht das mit der Endung *.php5 zu machen.
Kann das im Moment das nicht nachvollziehen.
Oder versuche das mal Lokal mit XAMPP zu machen - so als Vergleich.
26
Leider funktioniert das "Online Beispiel" und das Beispiel "MIN- und MAX-Umkreissuche" nicht (mehr).
Könntest Du die bitte aktualisieren?
Gruss Joe
26.09.2009
Joe meint dazu: Leider funktioniert das "Online Beispiel" und das Beispiel "MIN- und MAX-Umkreissuche" nicht (mehr).
Könntest Du die bitte aktualisieren?
Gruss Joe
27
Vielen Dank für den Hinweis mit dem Beispielen. Die Links sollten jetzt wieder gehen.
26.09.2009
Mario meint dazu: Vielen Dank für den Hinweis mit dem Beispielen. Die Links sollten jetzt wieder gehen.
28
Hab das gleiche Problem wie Bernd, gibt es dafür jetzt schon eine Lösung?
Das Problem tritt z.B. bei der PLZ 71634 auf.
13.11.2009
Gege meint dazu: Hab das gleiche Problem wie Bernd, gibt es dafür jetzt schon eine Lösung?
Das Problem tritt z.B. bei der PLZ 71634 auf.
29
Das Skript funktioniert nicht. Zumindest nicht wie es hier zu sehen ist.
01.12.2009
Thomas meint dazu: Das Skript funktioniert nicht. Zumindest nicht wie es hier zu sehen ist.
30
Was soll denn genau nicht gehen? Versucht mal Fehlermeldungen mit zu posten.
Mit dieser Aussage kann ich nicht viel anfangen.
01.12.2009
Mario meint dazu: Was soll denn genau nicht gehen? Versucht mal Fehlermeldungen mit zu posten.
Zitat von Thomas„Das Skript funktioniert nicht”
Mit dieser Aussage kann ich nicht viel anfangen.
31
Es funktioniert nun, nur was soll man denn mit der Ausgabe in dieser Formatierung machen ? stdClass Object
(
[ort] => Görlitz
[plz] => 02828
[Distance] => 0
)
Es macht ja keinen Sinn, dass so auszugeben.
01.12.2009
Thomas meint dazu: Es funktioniert nun, nur was soll man denn mit der Ausgabe in dieser Formatierung machen ? stdClass Object
(
[ort] => Görlitz
[plz] => 02828
[Distance] => 0
)
Es macht ja keinen Sinn, dass so auszugeben.
32
Es ist nur ein Beispiel! Du kannst deine Ausgabe auch völlig anders gestalten und verarbeiten als im Beispiel. Das stdClass Object kommt durch mysql_fetch_object(). Nichts spricht dagegen dort auch mysql_fetch_array() oder mysql_fetch_assoc() zu verwenden :)
01.12.2009
Mario meint dazu: Zitat von Thomas„Es funktioniert nun, nur was soll man denn mit der Ausgabe in dieser Formatierung machen ? stdClass Object ( [ort] => Görlitz [plz] => 02828 [Distance] => 0 ) Es macht ja keinen Sinn, dass so auszugeben.”
Es ist nur ein Beispiel! Du kannst deine Ausgabe auch völlig anders gestalten und verarbeiten als im Beispiel. Das stdClass Object kommt durch mysql_fetch_object(). Nichts spricht dagegen dort auch mysql_fetch_array() oder mysql_fetch_assoc() zu verwenden :)
33
Sehr cool. Gefällt mir. Was mich interessiert: Wie hast du dir die SQL Datei erstellt (die mit den 8000+ Orten) Habe mir die Tabelle erzeugt und es funktioniert ganz wunderbar. Aber ich würde gerne neuere Daten und vielleicht mehr haben. Also hast du opengeodb Daten genutzt ? Und wie hast du dir daraus deine Tabelle zusammen "geschustert" ... Viele Grüße Michi
15.12.2009
[Michi] meint dazu: Sehr cool. Gefällt mir. Was mich interessiert: Wie hast du dir die SQL Datei erstellt (die mit den 8000+ Orten) Habe mir die Tabelle erzeugt und es funktioniert ganz wunderbar. Aber ich würde gerne neuere Daten und vielleicht mehr haben. Also hast du opengeodb Daten genutzt ? Und wie hast du dir daraus deine Tabelle zusammen "geschustert" ... Viele Grüße Michi
34
Sagen wir mal - deutsche Handarbeit :)
Mit Suche/Ersetzen, CSV mit Excel bearbeiten ... und dann wird das schon. Natürlich mussten unzähliche INSERTs erst erstellt werden, damit alles in die DB geht. Ohne Fleiß kein Preis :)
16.12.2009
Mario meint dazu: Zitat von Michi„Wie hast du dir die SQL Datei erstellt”
Sagen wir mal - deutsche Handarbeit :)
Mit Suche/Ersetzen, CSV mit Excel bearbeiten ... und dann wird das schon. Natürlich mussten unzähliche INSERTs erst erstellt werden, damit alles in die DB geht. Ohne Fleiß kein Preis :)
35
Die Where-Klausel mit Formel kann man durch "Having Distance<=$umkreis" ersetzen. Spart Schreibarbeit und macht das Ganze ein wenig leserlicher :)
01.01.2010
Hendrik meint dazu: Die Where-Klausel mit Formel kann man durch "Having Distance<=$umkreis" ersetzen. Spart Schreibarbeit und macht das Ganze ein wenig leserlicher :)
36
Jo werd ich mir bei Gelegenheit mal angucken ... danke für die Info.
03.01.2010
Mario meint dazu: Zitat von Hendrik„Die Where-Klausel mit Formel kann man durch "Having Distance<=$umkreis" ersetzen. Spart Schreibarbeit und macht das Ganze ein wenig leserlicher :)”
Jo werd ich mir bei Gelegenheit mal angucken ... danke für die Info.
37
hi, das script (vor allem das query) gibts fast überall im Netz. Kann es sein, das damit was nicht stimmt? Wenn ich bei der Suche 02826 oder 02827 eingebe, bekomme ich den ort selber nicht, nur die Umliegenden (Umkreis 10). Wenn ich 02828 eingebe, bekomme ich auch alle 3 Görlitz. Distance 0 wird anscheinend nicht sauber gefunden. Hat wer eine Idee an was das liegen könnte ?
danke
Martin
31.01.2010
Martin meint dazu: hi, das script (vor allem das query) gibts fast überall im Netz. Kann es sein, das damit was nicht stimmt? Wenn ich bei der Suche 02826 oder 02827 eingebe, bekomme ich den ort selber nicht, nur die Umliegenden (Umkreis 10). Wenn ich 02828 eingebe, bekomme ich auch alle 3 Görlitz. Distance 0 wird anscheinend nicht sauber gefunden. Hat wer eine Idee an was das liegen könnte ?
danke
Martin
38
Habs gefunden.
Bischen andere Formel mit direkt den Daten aus der Datenbank:
Damit wird auch IMMER distance 0 sauber gefunden!
01.02.2010
Unbekannt meint dazu: Zitat von Martin„Hat wer eine Idee an was das liegen könnte ?”
Habs gefunden.
Bischen andere Formel mit direkt den Daten aus der Datenbank:
$lng = $erg_rad->geo_l;
$lat = $erg_rad->geo_b;
$query = "SELECT ort, plz, ACOS(
SIN(RADIANS(latitude)) * SIN(RADIANS(".$lat."))
+ COS(RADIANS(latitude)) * COS(RADIANS(".$lat.")) * COS(RADIANS(longitude)
- RADIANS(".$lng."))
) * 6380 AS Distance
FROM geodb Having Distance <= ".$distance."
ORDER BY Distance
";
Damit wird auch IMMER distance 0 sauber gefunden!
39
@Unbekannt:
Hast du dein query richtig kopiert? Bei mir will es nicht funktionieren ...
02.02.2010
Marcel meint dazu: @Unbekannt:
Hast du dein query richtig kopiert? Bei mir will es nicht funktionieren ...
40
Ich habe den Code ausgeführt und erhalte folgende Fehlermeldungen:
Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in C:xampplitehtdocsUMKREIS.php on line 15
Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in C:xampplitehtdocsUMKREIS.php on line 37
Wie kann das sein ? Weiss jemand Rat ?
19.02.2010
Fritz meint dazu: Ich habe den Code ausgeführt und erhalte folgende Fehlermeldungen:
Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in C:xampplitehtdocsUMKREIS.php on line 15
Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in C:xampplitehtdocsUMKREIS.php on line 37
Wie kann das sein ? Weiss jemand Rat ?
41
Sehr nützliches Tutorial das sich sehr schön auf das wesentliche beschränkt.
Danke dafür!
15.03.2010
[roka] meint dazu: Sehr nützliches Tutorial das sich sehr schön auf das wesentliche beschränkt.
Danke dafür!
42
Wo kann man den sämtliche Koordinaten von Deutschland für die DB bekommen?
27.07.2010
JeffLebowski meint dazu: Wo kann man den sämtliche Koordinaten von Deutschland für die DB bekommen?
43
z.B. hier:
http://www.plz-umkreis.com/plz-datenbank-deutschland
oder hier:
http://opengeodb.giswiki.org/wiki/OpenGeoDB
Kann man schauen, ob man mit dem kostenlosen zurecht kommt, oder einfach mal was zahlt für die Daten.
Ich hab mir damals die Daten gekauft bei http://www.geodatas.net/de/ da kann man sich eine Beispieldatei runterladen und erstmal schauen, das man den Code hinbekommt, bevor man sich die Komplettversion kauft.
Außerdem sind auch Beispielcodes dabei. Wobei die gibts ja auch hier ;-)
viel Spass beim coden
Martin
27.07.2010
Martin meint dazu: z.B. hier:
http://www.plz-umkreis.com/plz-datenbank-deutschland
oder hier:
http://opengeodb.giswiki.org/wiki/OpenGeoDB
Kann man schauen, ob man mit dem kostenlosen zurecht kommt, oder einfach mal was zahlt für die Daten.
Ich hab mir damals die Daten gekauft bei http://www.geodatas.net/de/ da kann man sich eine Beispieldatei runterladen und erstmal schauen, das man den Code hinbekommt, bevor man sich die Komplettversion kauft.
Außerdem sind auch Beispielcodes dabei. Wobei die gibts ja auch hier ;-)
viel Spass beim coden
Martin
44
Vielen Dank,
Das Problem ist jetzt, wie bekomme ich die Daten aus der Geo DB in das Format für diese Beispiel DB?
27.07.2010
JeffLebowski meint dazu: Vielen Dank,
Das Problem ist jetzt, wie bekomme ich die Daten aus der Geo DB in das Format für diese Beispiel DB?
45
hm das versteh ich jetzt nicht. welche Geo DB hast denn und was ist die beispiel DB?
Auf der geodatas.net bekommst eine csv Datei, die du in deine Mysql Datenbank importieren musst. Die Überschrift sind praktisch die Feldbeschreibungen.
Oder hab ich deine Frage falsch verstanden!
cu
Martin
27.07.2010
Martin meint dazu: Zitat von JeffLebowski„Das Problem ist jetzt, wie bekomme ich die Daten aus der Geo DB in das Format für diese Beispiel DB?”
hm das versteh ich jetzt nicht. welche Geo DB hast denn und was ist die beispiel DB?
Auf der geodatas.net bekommst eine csv Datei, die du in deine Mysql Datenbank importieren musst. Die Überschrift sind praktisch die Feldbeschreibungen.
Oder hab ich deine Frage falsch verstanden!
cu
Martin
46
Die Beispieldatei ist ja so aufgebaut:
"DE";"14199";"Berlin-Wilmersdorf";"Berlin";"BE";;;"Kreisfreie Stadt Berlin";"52,47728468";"13,29632805"
Diese Feldstruktur finde ich aber nicht in der OpenGeoDB.
Kannst Du mir sagen in welcher Tabelle die Daten PLZ, geo_l und geo_b sich verstecken?
Sind es in der Tabelle "geodb_coordinates" die Felder für "loc_id", "lon" und "lat"?
André
27.07.2010
JeffLebowski meint dazu: Die Beispieldatei ist ja so aufgebaut:
"DE";"14199";"Berlin-Wilmersdorf";"Berlin";"BE";;;"Kreisfreie Stadt Berlin";"52,47728468";"13,29632805"
Diese Feldstruktur finde ich aber nicht in der OpenGeoDB.
Kannst Du mir sagen in welcher Tabelle die Daten PLZ, geo_l und geo_b sich verstecken?
Sind es in der Tabelle "geodb_coordinates" die Felder für "loc_id", "lon" und "lat"?
André
47
In der OpenGeoDB gibt es ja einige zusätzliche Tabellen.
Meine Frage wäre, ob jemand eine Idee hat, wie man diese schnellstmöglich umstellen kann, dass sie wie dieses Format der Beispiel Datei aussieht!
Gruß
André
28.07.2010
JeffLebowski meint dazu: In der OpenGeoDB gibt es ja einige zusätzliche Tabellen.
Meine Frage wäre, ob jemand eine Idee hat, wie man diese schnellstmöglich umstellen kann, dass sie wie dieses Format der Beispiel Datei aussieht!
Gruß
André
48
Naja, openGeoDB hat sein format und geodatas hat auch sein format. und wenn du die Postleitzahlen noch woanders herbekommst, sind sie wahrscheinlich auch wieder in einem anderen format.
Wichtig ist doch nur, das du die Informationen hast und dein format (also deine Tabelle) so aufbauen kannst, wie du es brauchst. Die Feldnamen kannst dann ja nennen, wie du möchtest.
Und EIN Beispiel dafür ist ja in dem block gang, gang oben ebschreiben ;-)
30.07.2010
Martin meint dazu: Naja, openGeoDB hat sein format und geodatas hat auch sein format. und wenn du die Postleitzahlen noch woanders herbekommst, sind sie wahrscheinlich auch wieder in einem anderen format.
Wichtig ist doch nur, das du die Informationen hast und dein format (also deine Tabelle) so aufbauen kannst, wie du es brauchst. Die Feldnamen kannst dann ja nennen, wie du möchtest.
Und EIN Beispiel dafür ist ja in dem block gang, gang oben ebschreiben ;-)
49
Wäre es nicht noch vorteilhafter, wenn man die Daten, die man nicht erst in der Query berechnen muss, vorher schon berechnet?
Die Stellen, an denen mit einem für die ganze Query gleichen Wert etwas ausgerechnet wird - cos(".$rad_b."), sin(".$rad_l.") - die kann MySQL bestimmt schön optimieren, so dass die nicht für jede Zeile neu ausgerechnet werden müssen.
Aber was ist mit den anderen, die pro Zeile neu zu ermitteln sind?
SQRT(2*(1-cos(RADIANS(geo_b)),
sin(RADIANS(geo_l)),
cos(RADIANS(geo_l)),
sin(RADIANS(geo_b))
- die bei jedem Mal neu ausrechnen zu lassen, ist doch bestimmt nicht so performant, als wenn
05.08.2010
ChrisB meint dazu: Damit die Berechnung schnell geht, ist es natürlich von Vorteil, wenn die Berechnung in einen MysQL-Query schon enthalten ist.
Wäre es nicht noch vorteilhafter, wenn man die Daten, die man nicht erst in der Query berechnen muss, vorher schon berechnet?
( ".$radius." * SQRT(2*(1-cos(RADIANS(geo_b)) *
cos(".$rad_b.") * (sin(RADIANS(geo_l)) *
sin(".$rad_l.") + cos(RADIANS(geo_l)) *
cos(".$rad_l.")) - sin(RADIANS(geo_b)) * sin(".$rad_b.")))) AS Distance
Die Stellen, an denen mit einem für die ganze Query gleichen Wert etwas ausgerechnet wird - cos(".$rad_b."), sin(".$rad_l.") - die kann MySQL bestimmt schön optimieren, so dass die nicht für jede Zeile neu ausgerechnet werden müssen.
Aber was ist mit den anderen, die pro Zeile neu zu ermitteln sind?
SQRT(2*(1-cos(RADIANS(geo_b)),
sin(RADIANS(geo_l)),
cos(RADIANS(geo_l)),
sin(RADIANS(geo_b))
- die bei jedem Mal neu ausrechnen zu lassen, ist doch bestimmt nicht so performant, als wenn
50
[Fortsetzung] ... als wenn man die vorher ein mal per UPDATE „berechnen“ und in zusätzliche Spalten einfügen würde, wo man sie dann beim Auslesen direkt draus entnehmen kann, ohne noch was rechnen zu müssen.
Also wenigstens sin(radians()) und cos(radians()) von jeweils geo_l und geo_b würde ich da doch vorberechnet ablegen (die Wurzel ggf. auch noch, aber trigonometrische Funktionen dürften m.E. mit am teuersten sein.)
05.08.2010
ChrisB meint dazu: [Fortsetzung] ... als wenn man die vorher ein mal per UPDATE „berechnen“ und in zusätzliche Spalten einfügen würde, wo man sie dann beim Auslesen direkt draus entnehmen kann, ohne noch was rechnen zu müssen.
Also wenigstens sin(radians()) und cos(radians()) von jeweils geo_l und geo_b würde ich da doch vorberechnet ablegen (die Wurzel ggf. auch noch, aber trigonometrische Funktionen dürften m.E. mit am teuersten sein.)
51
hm, macht das Sinn? bei den wenigen Daten bewegt sich ein query im millisekunden bereich. Selbst eine optimierung wird nur unwesendlich schneller sein, weil die meiste performance bei öffnen der connection verbraucht wird.
Mal davon abgesehen, das bei diesem Datenbestand das ganze wahrscheinlich kaum messbar ist, oder ?
gruß
martin
06.08.2010
Martin meint dazu: hm, macht das Sinn? bei den wenigen Daten bewegt sich ein query im millisekunden bereich. Selbst eine optimierung wird nur unwesendlich schneller sein, weil die meiste performance bei öffnen der connection verbraucht wird.
Mal davon abgesehen, das bei diesem Datenbestand das ganze wahrscheinlich kaum messbar ist, oder ?
gruß
martin
52
Martin, das kommt darauf an, was „wenige Daten“ sind.
Wenn du so eine Umkreissuche nur für Buxtehude und Orte im Umkreis von 10km überhaupt implementieren willst, und nur die entsprechenden Daten aus dem Bestand von opengeodb in deine Tabelle übernimmst, dann vielleicht nicht.
Aber wenn du das bspw. Deutschlandweit implementieren willst - dann hast du da mehrere zehntausend(?) Datensätze, und für jeden davon müssen die Werte berechnet werden, unabhängig davon ob er nun letztendlich ein „Treffer“ ist, oder nicht. Und da würde ich schon davon ausgehen, dass sich das bemerkbar macht.
06.08.2010
ChrisB meint dazu: Martin, das kommt darauf an, was „wenige Daten“ sind.
Wenn du so eine Umkreissuche nur für Buxtehude und Orte im Umkreis von 10km überhaupt implementieren willst, und nur die entsprechenden Daten aus dem Bestand von opengeodb in deine Tabelle übernimmst, dann vielleicht nicht.
Aber wenn du das bspw. Deutschlandweit implementieren willst - dann hast du da mehrere zehntausend(?) Datensätze, und für jeden davon müssen die Werte berechnet werden, unabhängig davon ob er nun letztendlich ein „Treffer“ ist, oder nicht. Und da würde ich schon davon ausgehen, dass sich das bemerkbar macht.
53
mal ne Frage: kann man noch von jemand hier die fertige SQL bekommen? WÜrde gern das bei mir in mein neugeplantes Project einbaun und würd da nich zum hundersten male das aufbauen was schon andere gebaut haben
04.10.2010
Swen S. meint dazu: mal ne Frage: kann man noch von jemand hier die fertige SQL bekommen? WÜrde gern das bei mir in mein neugeplantes Project einbaun und würd da nich zum hundersten male das aufbauen was schon andere gebaut haben
54
Hätte da auch Interesse dran!
08.10.2010
JeffLebowski meint dazu: Zitat von Swen S.„Swen S. meint dazu:
mal ne Frage: kann man noch von jemand hier die fertige SQL bekommen? WÜrde gern das bei mir in mein neugeplantes Project einbaun und würd da nich zum hundersten male das aufbauen was schon andere gebaut haben”
Hätte da auch Interesse dran!
55
Habs gepostet wurde nicht veröffentlicht!
08.10.2010
[Martin] meint dazu: Zitat von JeffLebowski„Hätte da auch Interesse dran!”
Habs gepostet wurde nicht veröffentlicht!
56
Die Online-Beispiele sind zur Zeit nicht Online, aber das SQL Query steht doch da! Was ist denn jetzt unklar?
08.10.2010
Unbekannt meint dazu: Zitat von Swen S.„mal ne Frage: kann man noch von jemand hier die fertige SQL bekommen?”
Die Online-Beispiele sind zur Zeit nicht Online, aber das SQL Query steht doch da! Was ist denn jetzt unklar?
57
Darin muss ich Dir leider widersprechen.
Auch mit der Abfrage bekomme ich Suchergebnisse mit Distanz = 0 nicht zuverlässig ausgegeben.
Erst nachdem ich die Längen- und Breitengrade mit 0.0000000000001 addiert habe, findet der SQL Server die Orte, die ursprünglich Distanz 0 hatten.
Darüber hinaus gibt es noch Unterschiede in den einzelnen SQL-Server Versionen. Gleiche Abfragen unter MySQL 5.0 liefern andere Ergebnisse als unter MySQL 5.1.
Das ist alles nicht so stabil, wie ich es erwartet hätte.
20.03.2011
Thomas meint dazu: Zitat von Unbekannt„Bischen andere Formel mit direkt den Daten aus der Datenbank:
$lng = $erg_rad->geo_l;
$lat = $erg_rad->geo_b;
$query = "SELECT ort, plz, ACOS(
SIN(RADIANS(latitude)) * SIN(RADIANS(".$lat."))
+ COS(RADIANS(latitude)) * COS(RADIANS(".$lat.")) * COS(RADIANS(longitude)
- RADIANS(".$lng."))
) * 6380 AS Distance
FROM geodb Having Distance <= ".$distance."
ORDER BY Distance
";
Damit wird auch IMMER distance 0 sauber gefunden!”
Darin muss ich Dir leider widersprechen.
Auch mit der Abfrage bekomme ich Suchergebnisse mit Distanz = 0 nicht zuverlässig ausgegeben.
Erst nachdem ich die Längen- und Breitengrade mit 0.0000000000001 addiert habe, findet der SQL Server die Orte, die ursprünglich Distanz 0 hatten.
Darüber hinaus gibt es noch Unterschiede in den einzelnen SQL-Server Versionen. Gleiche Abfragen unter MySQL 5.0 liefern andere Ergebnisse als unter MySQL 5.1.
Das ist alles nicht so stabil, wie ich es erwartet hätte.
58
Vielen Dank für den tollen Script.
Hab es eingebaut und funktioniert wunderbar.
Nun eine Frage: Kannst du mir einen Tipp geben, wie ich die ausgebenen PLZ in dem Array benutze, um alle User aus einer anderen Tabelle zu holen, die in diesen PLZ Bereich fallen und die User auch gleich nach Entfernung sortiert sind?
Danke!
03.01.2012
Unbekannt meint dazu: Vielen Dank für den tollen Script.
Hab es eingebaut und funktioniert wunderbar.
Nun eine Frage: Kannst du mir einen Tipp geben, wie ich die ausgebenen PLZ in dem Array benutze, um alle User aus einer anderen Tabelle zu holen, die in diesen PLZ Bereich fallen und die User auch gleich nach Entfernung sortiert sind?
Danke!
59
Hallo,
Das ist wirklich eine tolle Anleitung, leider steige ich aber am Ende noch nicht so durch !
Also bei mir ist folgender Fall:
Ich habe bereits folgende Werte:
den lat Wert: $lat = 51.664402149701985;
den lng Wert: $lng = 7.635207550292989;
einen Radius in km: $km=5;
Jetzt steht in meiner Adress Datenbank bereits zu jeder Adresse der Lat und Lng wert drin.
nun möchte ich einfach nur zählen wie viele Adressen sich in diesem Radius befinden.
Könntest Du mir da evtl. einen Tip geben ?
Vielen Dank
05.02.2012
Unbekannt meint dazu: Hallo,
Das ist wirklich eine tolle Anleitung, leider steige ich aber am Ende noch nicht so durch !
Also bei mir ist folgender Fall:
Ich habe bereits folgende Werte:
den lat Wert: $lat = 51.664402149701985;
den lng Wert: $lng = 7.635207550292989;
einen Radius in km: $km=5;
Jetzt steht in meiner Adress Datenbank bereits zu jeder Adresse der Lat und Lng wert drin.
nun möchte ich einfach nur zählen wie viele Adressen sich in diesem Radius befinden.
Könntest Du mir da evtl. einen Tip geben ?
Vielen Dank
neuen Kommentar schreiben
Themen mit ähnlichen Inhalten
Tags zum Beitrag
umkreissuche usermapKategorien
- Umfragen
- Leserbrief
- Allgemein
- Fotografie
- CIX Projekte
- Projekt FPDF AcroForm
- Projekt Regex Tester
- PHP Programmierung
- Flash Programmierung
- Funk und Elektronik
- Internet
- Mediendesign
- Bildbearbeitung
- Audiobearbeitung
- Videobearbeitung
- Space
- Spam per E-Mail
- BfeK 3 Eilenburg
- TV, Radio und Kino
- Status Blog
letzte Kommentare
- MediaCix: Fröbel Runde auf 3688 khz
- Jueegg: Fröbel Runde auf 3688 khz
- Stephan: Fröbel Runde auf 3688 khz
- Unbekannt: MP3 aus FLV extrahieren
- Klaus: Fröbel Runde auf 3688 khz
- Mike: Die Bauernschnitten von Brotland GmbH in Schenefeld
Sonstiges
Archiv
- 2008, Dezember
- 2008, November
- 2008, Oktober
- 2008, September
- 2008, August
- 2008, Juli
- 2008, Juni
- 2008, Mai
- 2008, April
- 2008, März
- 2008, Februar
- 2008, Januar