MySQL und GEO-Location, Umkreissuche

[Malcolm Beck´s]

હેલો

ich schreibe gerade diese kleine „App“, in der ich viel mit Adressen arbeite. Jetzt wollte ich eine kleine Umkreisssuche realisieren, bzw. Kunden in der nähe vom Standort des Nutzers finden. Die Adressen speichere ich einmal in klartext ab und zusätzlich die GEO-Koordinaten.

CREATE TABLE IF NOT EXISTS `firmen_geo_pointer` (  
  `id` int(6) unsigned NOT NULL AUTO_INCREMENT,  
  `firmenid` int(6) DEFAULT NULL,  
  `location` point NOT NULL,  
  `short_lat` varchar(10) COLLATE utf8_unicode_ci NOT NULL,  
  `short_lng` varchar(10) COLLATE utf8_unicode_ci NOT NULL,  
  PRIMARY KEY (`id`),  
  SPATIAL KEY `location` (`location`)  
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Die GEO-Koordinaten sind ja ziemlich einfach. Ich habe mal einige Adressen aus meiner direkten nachbarschaft zusammengetragen:

lat       lng
Johannesstr 34       51.53849  7.15512
laurentius 2         51.53867  7.15332
stöckstr 100         51.53884  7.15208
karlstr 2            51.53720  7.15485
hauptstr 335         51.53801  7.15541
Melanchthonstraße 7  51.54000  7.15532

Begrenzt auf 5 Zeichen nach dem Komma. Wie eine Rückwärtssuche gezeigt hat, reicht dass für eine ziemlich genaue lokalisierung (geht sogar mit 4 stellen nach dem Komma).

Im Grunde sollte es doch hier reichen, wenn ich im Statement alle Ergebnisse möchte, die vom

(Standort_lat + 0.01000) > Aktueller_Standort
&&
(Standort_lng + 0.00999) > Aktueller_Standort
&&
(Standort_lat + 0.01000) < Aktueller_Standort
&&
(Standort_lng + 0.00999) < Aktueller_Standort

Bevor ich jetzt wieder Stunden meines Lebens vergeude: Kann das, anhand der oben gegebenen Werte, mit MySQL funktionieren?

બાય

[Jörg Reinholz]

Bevor ich jetzt wieder Stunden meines Lebens vergeude: Kann das, anhand der oben gegebenen Werte, mit MySQL funktionieren?

Aber ja doch. Du findest dazu aber (sogar hier im Forum unter dem Stichwort "Umkreissuche") reichlich Diskussionen über das wie und die Effekte hinsichtlich der Genauigkeit.

Jörg Reinholz

[Malcolm Beck´s]

હેલો

Aber ja doch. Du findest dazu aber (sogar hier im Forum unter dem Stichwort "Umkreissuche") reichlich Diskussionen über das wie und die Effekte hinsichtlich der Genauigkeit.

Die meisten Ansätze sind ja ganz anders als meiner. Ich beschneide die Koordinaten beim speichern einfach auf 5 stellen nach dem Komma. So brauche ich im Grunde ein ziemlich einfaches Statement, welches seinerseits kaum rechnen müsste. Keine Radius-rechnereien oder dergleichen.

Aber ich denke ich werde es einfach mal angehen, kann ja nicht viel schief gehen.

બાય

[Jörg Reinholz]

હેલો

Aber ja doch. Du findest dazu aber (sogar hier im Forum unter dem Stichwort "Umkreissuche") reichlich Diskussionen über das wie und die Effekte hinsichtlich der Genauigkeit.

Die meisten Ansätze sind ja ganz anders als meiner. Ich beschneide die Koordinaten beim speichern einfach auf 5 stellen nach dem Komma. So brauche ich im Grunde ein ziemlich einfaches Statement, welches seinerseits kaum rechnen müsste. Keine Radius-rechnereien oder dergleichen.

Aber ich denke ich werde es einfach mal angehen, kann ja nicht viel schief gehen.

Naja. Also die ultraeinfache und superungenaue Viereck-Variante:

$umkreis_l = $umkreis/71.12;
$umkreis_b = $umkreis/87.27;
$b_max     = $b + $umkreis_b;
$b_min     = $b - $umkreis_b;
$l_max     = $l + $umkreis_l;
$l_min     = $l - $umkreis_l;
$sql = "SELECT
         nr,ort,breite,laenge
        FROM geo
        WHERE
           laenge > "$l_min "
        AND laenge <  "$l_max "
        AND breite >  "$b_min "
        AND breite <  "$b_max "
ORDER BY ort";

In einem von meinen asbach-älteren Projekten gibt's danach noch einen Durchlauf, der auf den Radius prüft. Ich habs verlinkt, Damit Du siehst mit was für Geschwindigkeiten zu rechnen ist. Liegt auf einem shared host, Allerdings ist der Hoster gerade hinsichtlich Datenbanken einer von der brauchbaren Sorte.

Jörg Reinholz

[Malcolm Beck´s]

હેલો

In einem von meinen asbach-älteren Projekten gibt's danach noch einen Durchlauf, der auf den Radius prüft. Ich habs verlinkt, Damit Du siehst mit was für Geschwindigkeiten zu rechnen ist.

Das meiste entfällt bei meiner Variante doch. Es hat ja an sich nichts mehr mit einer Umkreissuche zutun.

lat       lng
Johannesstr 34       51.53849  7.15512
laurentius 2         51.53867  7.15332
stöckstr 100         51.53884  7.15208
karlstr 2            51.53720  7.15485
hauptstr 335         51.53801  7.15541
Melanchthonstraße 7  51.54000  7.15532

Die Strassen sind in unmittelbarer umgebung, also sollte es doch reichen, wenn ich in etwa nach „Standort + 0.01000“ und „Standort - 0.01000“ suche? Das ist zwar ziemlich Grob, aber letztenendes eine Umkreissuche?

Ich hab gerade nur schwierigkeiten mit der Logik:

Aktueller Standort Latitude: 51.53849, Longitude: 7.15512

SELECT  
      short_lat, short_lng  
FROM  
      GEO  
WHERE  
        short_lat < (Latitude+0.01000)  
      AND  
        short_lat > (Latitude-0.01000)  
AND  
        short_lng < (Longitude+0.00999)  
      AND  
        short_lng > (Longitude-0.00999)

Müsste ja so die richtung sein? So ein Statement sollte doch kaum ins Gewicht fallen? Ich probiere einfach mal rum.

બાય

[bubble]

Es hat ja an sich nichts mehr mit einer Umkreissuche zutun.
Die Strassen sind in unmittelbarer umgebung, also sollte es doch reichen, wenn ich in etwa nach „Standort + 0.01000“ und „Standort - 0.01000“ suche? Das ist zwar ziemlich Grob, aber letztenendes eine Umkreissuche?

Du widersprichst dir selbst. Klar, mit einem Radius zu rechnen dauert länger, als mit dem Rechteck. Ich bezweifle aber, dass die Optimierung in Anbetracht der Ungenauigkeit der Berechnung "besser" ist.

Je nach dem, was du vor hast, können solche "Optimierungen" auch zum Nachteil werden. Fakt ist, dass die Berechnungsgeschwindigkeit durch einen größeren Radius nicht steigt, aber die Ungenauigkeit des Ergebnisses.

Dazu kommt, dass man wahrscheinlich eh eine Distanz ausgeben will; spätestens dafür musst du dann eh "ordentlich" rechnen. (Auch wenn's nur Luftlinie ist)

MfG
bubble

[Malcolm Beck´s]

હેલો

Es hat ja an sich nichts mehr mit einer Umkreissuche zutun.
Die Strassen sind in unmittelbarer umgebung, also sollte es doch reichen, wenn ich in etwa nach „Standort + 0.01000“ und „Standort - 0.01000“ suche? Das ist zwar ziemlich Grob, aber letztenendes eine Umkreissuche?

Du widersprichst dir selbst.

Du hast mich falsch verstanden. Die Art, wie ich suche, ist keine „Umkreissuche“ in dem Sinne. Ich suche nach 2 Werten, die jeweils kleiner und grösser sind als X.
Ich rechne ohne radius oder dergleichen.

zahl_1    zahl_2
51.53849  7.15512
51.53867  7.15332
51.53884  7.15208
51.53720  7.15485
51.53801  7.15541
51.54000  7.15532

Ausgehend von: 51.53849  7.15512

  zahl_1 < 51.53849+0.01000  
und  
  zahl_2 < 7.15512+0.00999

Klar, mit einem Radius zu rechnen dauert länger, als mit dem Rechteck. Ich bezweifle aber, dass die Optimierung in Anbetracht der Ungenauigkeit der Berechnung "besser" ist.

Ich wollte nicht optimieren, ich wollte nur hören, ob es hier bedenken zu meinem Vorhaben gibt.

Dazu kommt, dass man wahrscheinlich eh eine Distanz ausgeben will; spätestens dafür musst du dann eh "ordentlich" rechnen. (Auch wenn's nur Luftlinie ist)

Wir leben im 21. Jahrhundert, wer rechnet da noch selber, und vor allem so ungenau?

બાય

[Malcolm Beck´s]

હેલો

Wir leben im 21. Jahrhundert, wer rechnet da noch selber, und vor allem so ungenau?

So ist's besser.

બાય

[Malcolm Beck´s]

હેલો

es funzt.

  
  
      $_distanceLat = 0.00950; // „radius“ latitude  
      $_distanceLng = 0.00950; // „radius“ longitude, beide kann ich nicht abschätzen. Die Werte haben sich beim spielen ergeben  
  
      $_shortLat = number_format( $_GET['lat'], 5, '.', '');  
      $_shortLng = number_format( $_GET['lon'], 5, '.', '');  
  
      $_nshortlat = number_format( ($_shortLat-$_distanceLat), 5, '.', '');  
      $_nslonglat = number_format( ($_shortLat+$_distanceLat), 5, '.', '');  
  
      $_nshortlng = number_format( ($_shortLng-$_distanceLng), 5, '.', '');  
      $_nslonglng = number_format( ($_shortLng+$_distanceLng), 5, '.', '');  
  
      define('CustomerNear','SELECT  
                                     firmenid  
                             FROM  
                                     `firmen_geo_pointer`  
                             WHERE  
                                     userid = "%3$s"  
                             AND  
                                     short_lat >= %1$s  
                             AND  
                                     short_lat <= %2$s  
                             AND  
                                     short_lng >= %4$s  
                             AND  
                                     short_lng <= %5$s  
                             ');  
  
      $_SearchQuery = sprintf(CustomerNear  
                            , escape($_nshortlat)  
                            , escape($_nslonglat)  
                            , escape(UserID)  
                            , escape($_nshortlng)  
                            , escape($_nslonglng)  
                          );

Funktioniert jetzt wie gewünscht.

બાય

[Malcolm Beck´s]

હેલો

meine Umkreissuche hat eine kleine schwäche.

Nach der Abfrage habe ich ein Array mit allen Firmenids aus der GEO-Datenbank (da stehen nur die GEO-Koordinaten).

$_CustomerArray = array(6) { [0]=> string(2) "57"  
                             [1]=> string(2) "58"  
                             [2]=> string(2) "59"  
                             [3]=> string(2) "62"  
                             [4]=> string(2) "63"  
                             [5]=> string(2) "64" }

Mit diesen Werten lese ich dann die Firmendatenbank aus und hol mir die Daten zu den IDs. Um an die gewünschten Daten zu kommen, jage ich das Array durch eine foreach-schleife, nur kann ich dessen resultat nicht wie gewünscht sortieren, bspw. nach Strassen.

# vereinfacht  
define('FoundCustomer','SELECT  
                               name, strasse, ort, hausnummer  
                        FROM  
                               `firmenverzeichnis`  
                        WHERE  
                               id = %2$s  
                               ');  
  
  
if (is_array($_CustomerArray))  
  foreach ($_CustomerArray as $_KEY)  
  {  
          if ($_CN = $_connect->query( sprintf(FoundCustomer, escape($_KEY) ) ))  
          {  
              if ($_CNR = $_CN->fetch_assoc())  
              {  
                  $_CustomerNear .= h($_CNR['name']).', ' .h($_CNR['strasse']);  
              }  
          }  
  }

Wie kann ich das resultat nach „name“ oder „strasse“ sortieren? Geht das überhaupt?

બાય

[bubble]

Wie kann ich das resultat nach „name“ oder „strasse“ sortieren? Geht das überhaupt?

Fordere alle Daten gleichzeitig ab und dann ganz normal mit ORDER BY.

Das Stichwort ist hierbei IN bzw. = ANY.
Man beachte dabei, dass man nicht zwingend Subqueries benutzen muss, sondern auch Werte auflisten kann:

SELECT  
    *  
FROM  
    `table`  
WHERE  
    `table`.`id` IN (234, 6, 3, 678, 1)  
ORDER BY  
    `table`.`potato` ASC;

(Ich finde IN besser lesbar als = ANY, was du in dem Fall verwendest ist Wurst)

MfG
bubble

[Malcolm Beck´s]

હેલો

Das Stichwort ist hierbei IN bzw. = ANY.

Interessant, wenn ich das Statement in PHPMyAdmin ausführe, bekomme ich sechs treffer, aus dem Script heraus nur eins. Woran kann das liegen?

Ich muss mich revidieren, Danke, genau das habe ich gesucht.

Der Fehler war im Statement:

SELECT  
       id, name, strasse, ort, hausnummer  
FROM  
       firmenverzeichnis  
WHERE  
       userid = %1$s  
AND  
       id IN (%2$s)  
ORDER BY  
      strasse, hausnummer, name

Ich hatte %2$s mit falschen Werten gefüttert.

બાય

[Malcolm Beck´s]

હેલો

So sieht es übrigens aus. Derzeit zwar sehr grob und ungenau, aber für meine Zwecke mehr als genügend.

બાય