SQL: Umkreissuche realisieren ...

[Wuppi]

Kann nicht überall Indexe setzen. prädestiniert für die wcf1_user_option_value wäre ja die userOption65 und userOption85 .. Plz und Land. Als Feldtyp ist hier TEXT hinterlegt.

Bekomme da als Fehlermeldung:
#1170 - BLOB/TEXT column 'userOption65' used in key specification without a key length

Besser wäre wohl varchar ... nur wenn ich DAS ändere, weiß ich nicht mehr was das System daraus macht [es geht nur direkt in der DB; im Frontend hab ich zwar Auswahl, aber da passt nix]... PLZ in INT geht ja nicht wg. der führenden Null für manche Städte.

INDEX auf plz/country in der geodb gesetzt (gemeinsamer Index) => brachte eine verbesserung 0.2905 => 0.2890 immerhin *G*
VOLLTEXT auf userOption65 und 85 (gemeinsam) => 0.2890 => 0.2869
Pimpen auf hohem niveau *G*

Und jetzt ein WOW:
UserOption65 und userOption85 hab ich jetzt auf varchar(5) und varchar(12) gesetzt - dazu index auf beide
0.2869 => 0.0182 Sekunden (das reine Umkreisquery dauert ja 0,0152sek [ohne index plz/country [-0,0015sek]])

Nachteil muss ich noch prüfen.

......... soviel zum Thema Index.

Mal eben ne Zwischenfrage: ich teste die querys mit der aktuellen phpmyadmin-Version. Gibt es da eine möglichkeit das CACHING je aufruf zu verhindern? [irgendwas vor das select ... "nocache" oder so]. Ich weiß das ich es per my.ini (Windows-Server) deaktivieren kann - nur wie ich mich kenne, werde ich das danach nie wieder einschalten ... also nix "permanentes".

Und weiter:
JOIN durch RIGHT JOIN (das nach dem AS gl): 0.0163 => 0.2636 (!) [also indexe sind ja nun gesetzt]
Ich bekomme das Subselect im GESAMT-Query nicht entfernt irgendwas mach ich falsch - div. Fehlermeldung - wie ich es auch rücke.

HAVING: ja nicht Standardkonform - hab ich jetzt mehrfach gelesen where Distanz < 75 wäre richtig - geht aber nicht "Unknown column 'Distance' in 'where clause'"

[gn#36]

Besser wäre wohl varchar ... nur wenn ich DAS ändere, weiß ich nicht mehr was das System daraus macht [es geht nur direkt in der DB; im Frontend hab ich zwar Auswahl, aber da passt nix]... PLZ in INT geht ja nicht wg. der führenden Null für manche Städte.

Sofern es nicht gleichzeitig 01234 und 1234 an verschiedenen Orten gibt (im gleichen Land) ist das aber für die Abfrage egal. Für die Darstellung natürlich nicht...

Ich glaube nicht, dass du mit dem Wechsel auf varchar Probleme bekommst, es sei denn es werden zu lange Werte von den Usern eingetragen. Bei der Länge muss man evtl. aufpassen wenn Umlaute im Text sind und das Format utf8 ist.

JOIN durch RIGHT JOIN (das nach dem AS gl): 0.0163 => 0.2636 (!) [also indexe sind ja nun gesetzt]

Hm, hätte ich jetzt eigentlich nicht erwartet, es sei denn Die DB weiß bereits, dass eine n:1 Beziehung zwischen den Tabellen besteht.

Ich bekomme das Subselect im GESAMT-Query nicht entfernt irgendwas mach ich falsch - div. Fehlermeldung - wie ich es auch rücke.

Poste mal ein Beispiel was du versucht hast.

[Pyramide]

Grundsätzlich kann die Abfrage ja in zwei Richtungen ablaufen:
a) Benutzer suchen -> deren PLZ/Ort herausfinden -> deren Koordinaten herausfinden und Entfernung prüfen
b) PLZ/Ort im Umkreis suchen -> zugehörige User-IDs suchen -> zugehörige User suchen

Ebenfalls grundsätzlich sollten Abfragen immer so ausgeführt werden, dass der am stärksten einschränkende und am wenigsten rechenintensive Faktor zuerst kommt, um danach mit so wenigen Daten wie möglich arbeiten zu müssen.

Variante a) macht demnach Sinn, wenn du in der Benutzertabelle bereits starke Einschränkungen machen kannst (z.B. nur die Top 100 Poster). Dann hilft ein Index auf die user-id in der useroptions-Tabelle, dort die Einträge schneller zu finden sowie ein Index auf die PLZ/Land Spalten in der geotabelle, um dort die Einträge schneller zu finden.

Umgekehrt macht Variante b) Sinn, wenn die Einschränkung über die Koordinaten nur noch einen Bruchteil der Gesamtdatenmenge bringt und eine Indexfähige Bedingung vorhanden ist (z.B. Index auf lat/lon und Filter mit < / >). Dann hilft wiederum ein Index auf die plz/land Spalten in der useroptions-Tabelle, um dort die passenden Einträge zu finden sowie ein Index auf die userid in der user-Tabelle (vermutlich schon als Primärschlüssel vorhanden), um dort die Einträge zu finden.

[Wuppi]

Hi

erstmal paar Tage pause gemacht - war schon so dicht das ich net mehr nen einfaches query hinbekommen habe (einfach nur 2 Tabellen abgleichen) ... jetzt gehts


@gn:
Bei wechsel auf varchar gibt es ein Problem - aber irrelevant - im ACP ist dieses Feld nicht mehr änderbar. Profil Anzeige als auch Profil-Edit sind aber problemlos machbar. Damit wäre die Lösung "gekauft" . Die Werte werden vorher geprüft - da kommt also nur das rein was ICH will


Gesamtquery:

Sowas hab ich z.b. versucht:

Code: Alles auswählen

SELECT 
uov.userID, uov.userOption7, uov.userOption8, ......... , up.userpicID, up.userpicExtension, u.userName , 

ACOS( SIN(RADIANS(gl.lat)) * SIN(RADIANS(51.941887)) + COS(RADIANS(gl.lat)) * COS(RADIANS(51.941887)) * COS(RADIANS(gl.lon) - RADIANS(8.872081)) ) * 6371 AS Distance 

FROM geodb_locations_long gl WHERE ( lat >= 51.191887 AND lat <= 52.691887 AND lon >= 7.6554578571711 AND lon <= 10.088704142829 ) 

JOIN wcf1_user_option_value uov ON (uov.userOption65 = gl.plz AND uov.userOption85 = gl.country ) 

LEFT OUTER JOIN wcf1_user_pictures up ON uov.userID = up.userID 
LEFT OUTER JOIN wcf1_user u ON uov.userID = u.userID 
WHERE uov.userOption76 = 'ja' AND ... AND ... AND .... AND .... Having Distance <= 75 
ORDER BY Distance 

also aus dem subselect ein "FROM" ... aber nix ... JOIN ... nix. Warum weiß ich - das passt da einfach nicht hin ich mach nen As Distances FROM ... das liest sich schon verkehrt.

@Pyramide:

Ebenfalls grundsätzlich sollten Abfragen immer so ausgeführt werden, dass der am stärksten einschränkende und am wenigsten rechenintensive Faktor zuerst kommt, um danach mit so wenigen Daten wie möglich arbeiten zu müssen.

Der erste Schritt - seperates Query ist erstmal LAND/PLZ vom aktuellen User zu ermitteln - damit hol ich mir in der anderen Tabelle dann LAT/LON.

LAT/LON wird jetzt Mathematisch (php) aufbereitet das es passend ist (das ja das nächste Thema - jetzt erstmal egal - wir gehen davon aus das ich alles richtig rechne *G*).

Jetzt kommt das Monster-Query. Auch wenn mathematisch viel gemacht wird (SQL), muss ich die Umkreisrelevantensachen ja am Anfang machen ... die Daten aus der geodb_locations_long werden ja nicht weiter eingeschränkt. Ich Bau mein Viereck, ermitteln den Umkreis - bekomme PLZ/LAND im Umkreis genannt. Das lasse ich nun gegen die User-DB laufen (derzeit zum Test 2000 User) ... das hängt aber ja alles zusammen ... somit bleibt das doch zwangsläufig an erster Stelle. Jetzt könnte ich aber alles was danach kommt, entsprechend deiner Aussage schieben. Rechenintensiv ist z.b. die Ermittlung des Alters (im der DB steht das Geb-Datum) von 18-35 ... sowas setze ich also an LETZTER Stelle (das wäre der Block der mit "where uov.userOption76" eingeleitet wird. Danach kommt die Größe 1,54-1,85m .. da setze ich nen Between ein (in der DB steht 1,54m ... 1,55m usw.) ... sowas wäre dann die vorletzte Position. Danach sind nur noch "einfache" Sachen xyz=1, abc=2 usw. Das würde ich dann eher nach durchschnittlichen Treffern sortieren? Wenn ich ein xyz=1 schon 75% der Zeile wegfiltert, kommt das an erster Stelle, acb=2 würde nur 50% wegfiltern - daher an 2. Stelle ... wobei das eher gefühlt ist ... kann mal so oder so sein.

Gruß

[gn#36]

Das kann so nicht funktionieren, da ist ja zwei mal "WHERE" drin.

Code: Alles auswählen

    SELECT
    uov.userID, uov.userOption7, uov.userOption8, ......... , up.userpicID, up.userpicExtension, u.userName ,

    ACOS( SIN(RADIANS(gl.lat)) * SIN(RADIANS(51.941887)) + COS(RADIANS(gl.lat)) * COS(RADIANS(51.941887)) * COS(RADIANS(gl.lon) - RADIANS(8.872081)) ) * 6371 AS Distance

    FROM geodb_locations_long gl

    JOIN wcf1_user_option_value uov ON (uov.userOption65 = gl.plz AND uov.userOption85 = gl.country )

    LEFT OUTER JOIN wcf1_user_pictures up ON uov.userID = up.userID
    LEFT OUTER JOIN wcf1_user u ON uov.userID = u.userID

    WHERE ( lat >= 51.191887 AND lat <= 52.691887 AND lon >= 7.6554578571711 AND lon <= 10.088704142829 ) AND (uov.userOption76 = 'ja' AND ... AND ... AND .... AND .... ) 

Having Distance <= 75 

ORDER BY Distance

[Wuppi]

Hi

hab gestern erstmal die Reihenfolge bissel geändert: die Rechenintensiven Sachen (Alter und Größe) ans Ende - davor die "="-Sachen und davor die "in()"-Sachen - und davor halt die Umkreisgeschichte. Brachte mir schon eine Verbesserung der Laufzeit

Jetzt hab ich gerade mal das "entfernte Subselect" von gn#36 probiert:
0.0140 vs. 0.0136s (keine gigantische Abfrage)

bin wohl jetzt in nem Bereich wo ich nur noch die vierte Nachkommastelle gepimpt bekomme (aber Kleinvieh macht ja auch Mist!). "Problem" bei der Variante: ich muss die Query-Erstellung bissel umbauen - also noch ne Variable wo der where-Teil drin ist und bei der Zusammenführung diese weitere Variable an die richtige stelle (ist jetzt kein technisches Problem). Aber ok das muss ich jetzt wissen ob ich das machen möchte. Genauso wie das "having"-Entfernen - setze da dann einen teil der Umkreisgeschichte mit where davor => bringt mir nichts erkennbares (da müsste ich ne größere Testreihe starten - weil die Werte je Variante mal besser, schlechter sind) ... aber wäre wohl die sauberere Lösung statt having.

Ich denke mal mit dem php/sql-Teil wäre ich somit fertig! Danke Pyradmide und gn#36!


------------------ kommen wir also noch mal zum Mathematischen Teil ...

Meine Formel die ich aktuell nutze bringt mir folgende Werte:
Meine Koords: Breite = 51.941887 | Breite = 8.872081
Umkreis: 75 - Länge Breitengrad: 110 - Erdradius: 6371 (wird hier jetzt aber nicht verwendet)
Ergebnis => Viereck:
Nord: 52.623705181818 - Sued: 51.260068818182 - West: 7.7660599610647 - Ost: 9.9781020389353

West/Ost hat 1,1 +/- ... Nord/Süd nur ~0,7 +/- unterschied zu den Koords ... normal? oder offenbart das schon einen Fehler?

Und so sieht die Berechnung aus:

Code: Alles auswählen

			$breite_nord = $lat + ($umkreis / $laenge_breitengrad);
			$breite_sued = $lat - ($umkreis / $laenge_breitengrad);
			$laenge_west = $lon - ($umkreis / (ABS(COS(deg2rad($lat))) * $laenge_breitengrad));
			$laenge_ost = $lon + ($umkreis / (ABS(COS(deg2rad($lat))) * $laenge_breitengrad));

Formate wie oben in der Test-Ausgabe

Gibts im Netz den einen Rechner wo ich das verifizieren kann? Würde es Sinn machen zur Viereck-Erstellung den Umkreis + z.b. 10% zu machen - um zu verhindern das der Kreis über die kanten rausgeht und somit ins "Leere" läuft? ... bei nem Quadrat auf ner flachen Fläche sehe ich da KEIN Problem - aber das haben wir hier ja nicht.

Gruß

[gn#36]

Ich würde sagen das ist in Ordnung so, ohne die Zahlen im Detail zu überprüfen.

Der springende Punkt der ganzen Berechnungen ist eigentlich imho, dass du bedenken musst, dass der Abstand zweier Längengrade abhängig vom Breitengrad ist, während der Abstand zweier Breitengrade immer ziemlich genau 111 km ist.

Wenn wir zunächst mal davon ausgehen, dass das Viereck in N/S Richtung exakt passend zum Radius des Kreises wählen, dann kann in der Richtung schon mal kein Fehler abhängig von der genauen Nord/Süd oder West/Ost Position kommen, denn der Abstand der Breitengrade ist ja immer gleich. In der Richtung reicht es also auf jeden Fall aus, wenn du exakt den Kreisdurchmesser als Abstand der beiden Grenzen wählst. Zwei der Kanten sind also schon mal recht einfach.

In West/Ost Richtung ist das ganze leider nicht mehr so einfach. Auf der Nordhalbkugel laufen die Längengrade Richtung norden immer stärker auf einander zu und treffen sich dann alle gemeinsam im Nordpol.

Wenn man sich jetzt wieder den Kreis vorstellt, den man in ein Viereck zwängen will, dann würde die Konstruktion grob (und übertrieben) so aussehen:

Code: Alles auswählen

   ---
  /   \
 /  O  \
/       \
---------

Also ist auf der Nordhalbkugel die nördliche Grenze kritisch, wenn wir hier den Abstand zwischen den beiden oben eingezeichneten Längengraden so groß wählen, dass dieser genau so breit ist wie der Kreis, dann ist der Kreis auf jeden Fall vollständig im Viereck enthalten. Noch besser wäre natürlich eine Tangente, aber den zusätzlichen Rechenaufwand wäre mir das nicht wert.

Der Rest ist Einheitenumrechnung. Es gibt $i =111 km/Grad N/S und $j =111km/Grad * cos(Breitengrad) W/O. Du musst den Umkreis von km in Grad umrechnen bevor du ihn addieren kannst, also dividierst du den Umkreis durch $i um die Änderung in N/S Richtung zu bekommen und durch $j um sie in W/O Richtung zu erhalten. Um sicher sämtliche Teile des Kreises im Viereck zu haben solltest du bei der Berechnung von $j den nördlichen Breitengrad nehmen (siehe oben).

Also bei der Berechnung dann

Code: Alles auswählen

    $breite_nord = $lat + ($umkreis / $laenge_breitengrad);
             $breite_sued = $lat - ($umkreis / $laenge_breitengrad);
             $laenge_west = $lon - ($umkreis / (ABS(COS(deg2rad($breite_nord))) * $laenge_breitengrad));
             $laenge_ost = $lon + ($umkreis / (ABS(COS(deg2rad($breite_nord))) * $laenge_breitengrad));

Ich denke aber, dass der Fehler den du machst wenn du den Mittelpunkt nimmst zumindest bei halbwegs kleinen Kreisen und in unseren Breitengraden noch nicht so einen riesigen Unterschied macht.

[Doris_Traunsee]

Hallo Zusammen,
auf der Suche nach einer Hilfe um eine Umkreissuche in meiner Seite einzubauen habe ich diese Seite hier gefunden. Erstmal muss ich mich gleich outen als Laie was die Programmierung generell betrifft. Ich habe meine Seite mit dem Programm xtodate umgesetzt und bis auf die Umkreissuche funktioniert das alles.
Nun bräuchte ich echt Hilfe um diese Suche bei mir einzubauen. Gibt es hier jemanden der xtodate kennt bzw. sich zutraut in einer MySQL Datenbank das zu integrieren? Natürlich würde ich den Zeitaufwand dafür entschädigen.
Vielen Dank für eure Hilfe
Doris

[AYYILDIZLAR]

Hallo,

Du möchtest das dein Vorhaben von jemandem für dich umgesetzt wird. Dann wäre unter Beachtung der Forum-FAQ, die Jobbörse für dich der geeignete Ort, um evtl. einen Interessenten dafür zu finden.


Grüße