SQL: Umkreissuche realisieren ...

[Pyramide]

Code: Alles auswählen

SELECT uov.user_id, gl.country, gl,plz, [hier die entfernungsbrechnung] AS entfernung
FROM geodb_locations gl
JOIN user_option_value uov ON (uov.userOption65 = gl.plz AND uov.userOption85 = gl.country)
WHERE [entfernungsfilter]

[gn#36]

@gn#36: auf den Teil der DB hab ich keinen Einfluss - ich lese nur das aus was über die Profileinstellungen da rein wandert. Von daher hab ich da "Österreich, Deutschland, Schweiz" stehen. Das Ö hatte mir schon Probleme bereitet ... DE, AT, CH wäre mir lieber ... würde mir auch das umformen für die GeoDB sparen (da steht nur DE, AT, CH drin)

Indize sind in der Tabelle nur auf die userID - und wie gesagt: die Tabelle wird von einem System verwaltet. K.a. was da passiert wenn ich da indize setze und dann kommt nen update was mir das alles wieder zurücksetzt. Je weiter ich bei dem Seite vorankomme, desto mehr komme ich der Überzeugung das es einfacher gewesen hätte ich hätte hierfür eine eigene Tabelle genommen aktuell brauch ich hier 4 Tabellen, mit einer eigenen Tabelle bräuchte ich nur noch 2. Nur da hätte ich die Datenerfassung halt komplett selber machen müssen (statt was fertiges zu nehmen) - das wollte ich mir ersparen - das war der Fehler:( ... Version 2 wird kommen

Danke
Gruß

Das sollte aber trotzdem gehen und bei sehr vielen Vergleichen auch schneller sein. Ob da nun DE oder Deutschland steht ist doch egal. Mit concat machst du einen Stringvergleich bei der Postleitzahl während eigentlich ein einfacher Zahlenvergleich reicht. Das lässt dann falls irgendwie möglich nämlich auch noch anderweitige Optimierungen zu und sollte vom Ergebnis aber das gleiche liefern wie concat und auch in den selben Situationen nutzbar sein.

Du solltest in deiner Abstandsberechnung zusätzlich noch ein Rechteck um deinen Kreis legen und mit einer AND Operation verknüpfen. Aber das hatten wir im anderen Thema ja schon. Vermutlich musst du auch nur einmal den ellenlangen Befehl eingeben und kannst im WHERE Teil dann mit distance als begriff arbeiten, möglicherweise wird das dann auch nur ein mal berechnet und nicht doppelt...

[Wuppi]

Will nicht klappen:

Der Umkreiscode (fertig ausgeschrieben)

Code: Alles auswählen

SELECT name, plz, country, ( 6371 * SQRT(2*(1-cos(RADIANS(lat)) * cos(0.88895378390603) * (sin(RADIANS(lon)) * sin(0.12130038301361) + cos(RADIANS(lon)) * cos(0.12130038301361)) - sin(RADIANS(lat)) * sin(0.88895378390603)))) AS Distance FROM geodb_locations WHERE 6371 * SQRT(2*(1-cos(RADIANS(lat)) * cos(0.88895378390603) * (sin(RADIANS(lon)) * sin(0.12130038301361) + cos(RADIANS(lon)) * cos(0.12130038301361)) - sin(RADIANS(lat)) * sin(0.88895378390603))) <= 25 ORDER BY Distance

Ergebniss:
4 Spalten:
name
plz
country
Distance

Den muss ich jetzt in den Code frimmeln - wobei ich mir die hälfte davon sparen kann (ab dem where!)
=>

Code: Alles auswählen

SELECT uov.userid, gl.country, gl.plz, 

( 6371 * SQRT(2*(1-cos(RADIANS(gl.lat)) * cos(0.88895378390603) * (sin(RADIANS(gl.lon)) * sin(0.12130038301361) + cos(RADIANS(gl.lon)) * cos(0.12130038301361)) - sin(RADIANS(gl.lat)) * sin(0.88895378390603))))


AS entfernung
FROM geodb_locations gl
JOIN user_option_value uov ON (uov.userOption65 = gl.plz )

bekomme bei 2000 Usern, 8700 Datensätze angezeigt. User-Ids doppelt - aber mit Unterschiedlichen Entfernungen.

wenn ich jetzt am Ende ein where entfernung <100 mache = "#1054 - Unknown column 'entfernung' in 'where clause'"

Zudem kann ich kein "AND uov.userOption85 = gl.country" setzen, da country nur 2stellig ist, userOption85 aber ausgeschrieben. Wobei ich das in der geo-DB ja komplett ändern kann!

Also irgendwas hab ich hier falsch umgesetzt.

Edit:

UserID - PLZ (userOption65) LAND - PLZ (GeoDB) - entfernung

1580 2000 AT 2000 717.703720377867
1580 2000 AT 2000 723.245430019754
0 2034 AT 2034 712.321244907324

hab mir mal die userOption65 - PLZ mit anzeigen lassen... Ergebnis sieht ja aus wie gewürfelt
Ne keine Ahnung wie ich den String umsetze ... jetzt div. Möglichkeiten probiert - das wird nicht besser.

@gn#36:
einfacher Zahlenvergleich reicht ja nicht? Österreich und Schweiz haben z.b. die PLZ 4000 ... für rein Deutschland kann ich rein Zahlen abgleichen - das stimmt. Aber Sobald AT/CH dazukommen, muss ich auf jedenfall das LAND dazu nehmen.

Zur Abstandsberechnung: uff ... das mit Rechteck in dem Zusammenhang hab ich schon gelesen ... allerdings nicht verstanden Wenn die grundsätzliche Ermittlung steht und mir das System klar ist, kann ich die Formel ja noch anpassen. Würde den Punkt daher vorerst pausieren. Diese Berechnung klappt ja auch erstmal.

[Pyramide]

Zudem kann ich kein "AND uov.userOption85 = gl.country" setzen, da country nur 2stellig ist, userOption85 aber ausgeschrieben. Wobei ich das in der geo-DB ja komplett ändern kann!

Naja ohne das Land mit reinzunehmen, kann es natürlich nicht funktionieren, weil dann ja einfach deutsche Postleitzahlen aus der einen Tabelle auf österreichische in der anderen gemappt werden...

[Wuppi]

ne mit Land gehts auch nicht. Hab in der geodb_locations jetzt mal AT = Österreich, DE = Deutschland und CH = Schweiz getauscht. Somit ist country jetzt genauso gefüllt wie userOption85.

Bekomme weiterhin über 8000 Treffer die hinten und vorne nicht stimmen können.

auch wieder dieses Beispiel:

Code: Alles auswählen

1580 	2000 	Österreich	Österreich 	2000 	716.061769724001
1580 	2000 	Österreich	Österreich 	2000 	717.703720377867
1580 	2000 	Österreich	Österreich 	2000 	723.245430019754
0 	2034	Österreich	Österreich 	2034 	712.321244907324

UserID '0' gibbet nicht.
PLZ ist beides mal aus Österreich ... aber wieso 3 Unterschiedliche Entfernungen für exakt den gleichen Ort? Die Umkreisabfrage oben klappt aber - keine duplicate - der Fehler passiert wo anders ...

[Pyramide]

aber wieso 3 Unterschiedliche Entfernungen für exakt den gleichen Ort?

Ohne die Inhalte der Datenbank zu kennen, kann man nur raten. Da es üblicherweise mehrere kleinere Orte oder Ortsteile mit der selben Postleitzahl gibt, würde ich aber mal darauf tippen.

[Wuppi]

nein

jede PLZ ist nur einmal drin - demnach kann ich pro PLZ auch nur eine Entfernung haben. In der UserDB ist natürlich auch jede UserID nur einmal drin (die 0 hab ich gefunden - fehler beim Generieren von Testusern ... aber die spalte ist Unique - somit keine doppelten UserIDs)

Die "Hardcore"-DB wo gleiche PLZ wg. Strasse drin sind, nutze ich hier nicht.

die DB hab ich ja oben zum runterladen verlinkt.

=> hier mal die beiden Datenbanken:
http://www.fileuploadx.de/847013
Die UserDB hab ich auf userid, useroption65, useroption85 gekürzt.
Diese GeoDB hat die Länder bereits ausgeschrieben
=> mit dem Suchstring von oben ... sollten bei 25km Entfernung 107 PLZen rauskommen (nur DE) - diese müssen dann natürlich noch gegen die UserDB laufen ... da müssten dann am Ende 15 Treffer sein (15 User die innerhalb von 25km wohnen)

[gn#36]

Meine Vermutung wäre, dass du jede User ID bis zu 3 mal bekommst. Die Abfrage muss im ON Teil des JOIN nämlich auch berücksichtigen, dass die PLZ ja in 3 Ländern sein könnte. Du sortierst so gewissermaßen jeden User in allen drei Ländern jeweils ein mal ein, wenn die PLZ übereinstimmt. Das würde auch erklären, dass die Entfernungen unterschiedlich sind.

[Wuppi]

GRRR EINEN fehler gefunden SORRY

jetzt weiß ich wofür die ID in der GeoDB ist ;( ... dachte die wäre unnötig.

In Österreich gibt es tatsächlich mehrfach 2-4 Orte mit gleicher PLZ und leicht unterschiedlichen Koords/Entfernungen. Super! Naja ich wollte die DB eh noch mal besser zusammensuchen. Dann schmeiß ich pauschal doppelte PLZ raus (dann hab ich halt kleine Fehler von paar km in der Entfernung ... bissel Schwund gibts immer)

Habe das jetzt so gelöst

Code: Alles auswählen

SELECT uov.userid, gl.country, gl.plz,

( 6371 * SQRT(2*(1-cos(RADIANS(gl.lat)) * cos(0.88895378390603) * (sin(RADIANS(gl.lon)) * sin(0.12130038301361) + cos(RADIANS(gl.lon)) * cos(0.12130038301361)) - sin(RADIANS(gl.lat)) * sin(0.88895378390603))))

AS entfernung
FROM geodb_locations gl
JOIN user_option_value uov ON (uov.userOption65 = gl.plz AND uov.userOption85 = gl.country ) group by userid

also group by userid => keine userid mehr doppelt. Bei der Ausgabe bekomme ich daher etwa 2091 von 2099 Datensätze angezeigt (8 sind ohne PLZ - gibts auch)

Aber 2091 ist immer noch zuviel ... nein es passt zur Abfrage => keine Entfernungsbegrenzung - ALLE User werden angezeigt. Aber ich möchte ja sagen das nur die User angezeigt werden in mit Entfernung z.b. <75km

Ein where entfernung < 75 (anstelle des group by) bringt mir ein
"#1054 - Unknown column 'entfernung' in 'where clause'"
Unknown column? Aber nach der Spalte SORTIEREN klappt (order by entfernung) ... versteh ich gerade nicht

Ich brauche also ein group by userid (um doppelte PLZ abzufangen) und ein entfernung < xxx ... wie bekomm ich das rein?

[Pyramide]

Du musst bei der Entfernung die Berechnung noch mal angeben - in einer WHERE-Bedingung kann man sich leider nicht auf Berechnungen aus dem SELECT beziehen. Das ginge nur, wenn du die ganze Abfrage in einen Subquery packst.