SQL: Umkreissuche realisieren ...

[Wuppi]

Hi

ich habe ein Suchform welches derzeit 10 Felder durchsucht (je nachdem - der nutzer kann auch etwas einschränken indem ihn gewisse sachen egal sind). Als nächstes muss ich eine Umkreissuche reinbasteln.

Nutze hierfür eine GeoDB-Datenbank für Deutschland, Österreich und die Schweiz. Das GeoDB-Script gibt mir Länderkürzel (DE, AT, CH raus ... in der Abgleich DB stehts je ausgeschrieben - aber das ist kein Problem), PLZ, Ort (optional - erstmal unwichtig) und natürlich die Entfernung raus. Das Script scheint soweit zu klappen.

Nur jetzt muss ich in diesem Riesen-SQL-String eben dieses noch mit einbauen. Plz und LAND ... würde ich nur Deutschland anbieten, wäre das ganze einfach: "AND PLZ in('12345', '12365', '12367')". Nur leider hat man in AT/CH 4stellige die sich auch gerne mal gleichen können - und wer Grenznah wohnt ... sagen wir mal am Bodensee und 50km Reichweite (PLZ jetzt exemplarisch):
Treffer:
DE 80000
DE 80001
AT 4000
CH 8000 (CH 4000 existiert aber vielleicht am anderen Ende der Schweiz - also weiter als die 50km)
Mit IN würde ich so also IN('80000', '80001', '4000', '8000') suchen und somit auch Fehlerhafte treffer bekommen. Ich muss also die Länderabfrage integrieren. Wie mach ich das? Das ganze soll am Ende dann nach ENTFERNUNG sortiert ausgeben werden (wobei ich das ja so machen kann das ich den ersten Suchstring welcher mit die PLZs ausgibt schon nach Entfernung sortiere - im Endergebniss-String wird NIX sortiert - Denkfehler?)

Ich habe auf die Tabelle wo die Hauptsuche ausgeführt keinen wirklichen einfluss. Ich kann die auslesen - natürlich kann ich die auch bearbeiten - allerdings wird diese Tabelle vom System genutzt und gefüllt. Ich würde sonst, wenn die User ihre persönlichen Daten eingeben, mir eine Hilfsspalte füllen: "LandPlz" ... DE-80000, CH-8000 usw. Das geht NICHT - würde die suche aber wesentlich vereinfachen. Ich könnte dieses Hilfsspalte in eine andere Tabelle auslagern .. "userID | LandPlz" ... nur die bekomm ich ebenfalls nicht Live aktuallisiert => per Cronjob 3-4x am Tag ... nicht schön ...

Gruß

PS: zum Umfang: Köln -> 75km Umkreis => 964 Plz ...

[Wuppi]

man kann sich ja die temporäre Tabelle selber basteln ...

concat war die Lösung

Code: Alles auswählen

SELECT * FROM tabelle WHERE concat(land,plz) IN('88131Deutschland'..........)

[Wuppi]

tja jetzt hab ich das nächste Problem.


Mit der ersten Datenbankabfrage realisiere ich das ermitteln aller Plz innerhalb eines Umkreises.
Bekomme da als Ergebnis eine Tabelle mit
PLZ | LAND | STADT | ENTFERNUNG
jede PLZ kommt natürlich nur einmal vor

Jetzt jage ich dieses Ergebniss (PLZ-LAND [concat siehe letzten Post]) gegen die User-Datenbank. Ich bekomme jetzt als Ergebniss eine Tabelle:
PLZ-LAND | UserID | ...
PLZ-LAND kann natürlich mehrfach vorkommen.

Jetzt fehlt mir hier aber die ENTFERNUNG ... in der Ausgabe möchte ich halt hinter jedem User die Entfernung zum aufrufenden User Anzeigen lassen.

Lösung die mir einfällt: Ich errechne für JEDEN User die Entfernung NEU.
Ich muss also für 2 PLZ die LAT/LON aus der GeoDB holen und das dann durch ne Formel (als Funktion dann) jagen. Aber irgendwie find ich das eine DB-Abfrage zuviel ...

Wie kann ich das sonst realisieren?

Gruß

[Pyramide]

Warum kombinierst du das ganze nicht einfach mit JOIN zu einer großen abfrage?

[gn#36]

Noch als Hinweis zum ersten Problem: Ich wette es wäre deutlich performanter wenn du die Abfrage so in der Art machen würdest:

Code: Alles auswählen

SELECT * FROM tabelle WHERE
(plz IN (1, 2, 3) AND land = 'DE') OR (plz IN (4, 5, 6) AND land = 'AT') OR (...)

Das sucht nach den PLZ 1, 2 und 3 in Deutschland und nach 4, 5, 6 in Österreich.
Vorteil: Dann können weiterhin Indizes benutzt werden anders als bei dem Concat. Musst du natürlich zumindest grob die Länder für kennen in denen du suchen möchtest. Aber wenn's nur drei sind ist das ja nicht so schlimm. Wenn möglich würde ich auch bei den Tabelleneinträgen nicht die Länderkürzel speichern, sondern stattdessen IDs (also z.B. statt DE eine 1, statt AT eine 2 usw. oder sonstwas).

[Wuppi]

Hi

@Pyramide: JOIN kam mir gerade aufm Weg zur Arbeit auch. Muss ich mir mal näher anschauen - mit JOIN erst einmal gearbeitet - und ich weiß bis heute net warum das geklappt hat ;(

@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


Also für meine Entfernung mach ich ein JOIN mit der anderen Tabelle und für das andere Problem arbeite ich wie gn#36 vorgeschlagen - ich probiere es mal

Danke
Gruß

[mad-manne]

... auf den Teil der DB hab ich keinen Einfluss - ich lese nur das aus was über die Profileinstellungen da rein wandert.
... / ...
würde mir auch das umformen für die GeoDB sparen (da steht nur DE, AT, CH drin)
... / ...
desto mehr komme ich der Überzeugung das es einfacher gewesen hätte ich hätte hierfür eine eigene Tabelle genommen

Wenn ich es richtig verstehe, dann wird die "externe" Tabelle aber weiterhin(von dritten ?) gepfelgt und somit aktuell gehalten, weshalb du die Tabelle nicht einfach kopieren und nach eigenen Wünschen "umbauen" kannst ??

Wäre es dann nicht evtl. trotzdem einfacher ein IMPORT-Script zu schreiben, welches die "externen" Daten ausliest, wie von dir gewünscht umwandelt und in einer eigenen Tabelle ablegt? Dann noch ein UPDATE-Script, welches in regelmässigen Abständen die Änderungen aus der "externen" Tabelle in deine eigene nachträgt.

Nur mal so ne Idee am Rande,
Manne.

[Wuppi]

Erstes Problem:
Das will nicht so klappen.
Um die 3 IN-OR-Felder zu machen "(plz in(12345,23456) and land='Deutschland') OR", muss ich ja erstmal dieses DB-Query ausführen:

Code: Alles auswählen

$query = "SELECT name, plz, country, (
 ".$radius." * SQRT(2*(1-cos(RADIANS(lat)) * 
 cos(".$lat.") * (sin(RADIANS(lon)) *
 sin(".$lon.") + cos(RADIANS(lon)) * 
 cos(".$lon.")) - sin(RADIANS(lat)) * sin(".$lat.")))) AS Distance 
 FROM geodb_locations WHERE 
 ".$radius." * SQRT(2*(1-cos(RADIANS(lat)) * 
 cos(".$lat.") * (sin(RADIANS(lon)) * 
 sin(".$lon.") + cos(RADIANS(lon)) * 
 cos(".$lon.")) - sin(RADIANS(lat)) * sin(".$lat."))) <= ".$umkreis." 
 ORDER BY Distance
";

($lat, $lon kommt aus einer DB-Abfrage zuvor wo der eigene Standort ermittelt wird - dazu bissel Mathe und die variablen sind fertig)

Wenn jetzt noch ein JOIN dazkommt, frage ich dieses ja nochmal ab. Nur ohne JOIN keine Entfernung ... ohne IN-OR nur ein concat ...

Da ich ja nicht mit indizen arbeiten kann, wäre es wohl egal wenn ich bei concat bleibe und mit JOIN auf die Entfernung gehe.

Ich bastel bei den einzeln abfragen immer Teil-SQL-Strings die ich am Ende zusammenfüge (mit implode das AND dazwischen einfüge)

Der GeoDB-Teilstring sieht dan derzeit so aus:

Code: Alles auswählen

$geostring = "concat(plz,land) IN('" .implode("', '", $geostringMerged) ."') ";

$geostringMerged = PLZ mit LAND gemerged [51103Deutschland]
$geostring übergebe ich dann $teilstring[] (was dann zur großen Abfrage zusammengefügt wird)

Da ja keine indize - bleib ich bei concat. Nur wie mach ich das mit dem JOIN zusammen?




@Manne: ja ... ich bastel mein script in ein wbb rein und nutze dafür Benutzerdefinierte Profilfelder. Brauche da also keine Angst haben das die nachm Update weg sind ... nur wenn die meinen diese UserTabelle zu optimieren etc. oder selber was hinzfügen usw. könnten meine indize weg sein. Daher will ich in keinster weise an dieser Tabelle rumspielen.

Meine Idee ERSTE Idee war, eine eigene Tabelle ... Dagegen sprach:
* keine Integration in den "Profil bearbeiten"-Dialog
* keine Integration in "Profil anzeigen"
* es gibt Infos die in der ursprünglichen UserDB bleiben müssen ... ich hätte also dann wieder 2 Tabellen zum Abfragen.
* das komplette Eingabeform hätte ich erstellen müssen
* Ausgabeform für die seperaten Daten (die jetzt nach aktuellem Stand im normalen Profil angezeigt werden)
** Jeder Singlebörsen-User hätte somit 2 Profile. unschön
* Administrativ nicht "kontrollierbar" - ich müsste also dieses 2. Profil via ACP erreichbar machen ...

Daher kommen mir die Benutzerdefinierten Profilfelder schon ganz recht - integration ins Profil. Manche Infos kann ich auch bei Posts unter dem Avatar anzeigen (wichtig ... wir sind eine Reallife-Community - da braucht es andere Infos in den Profilen als bei Online-Communitys) usw. Das Problem was ich aktuell habe, hat rein mit der Umkreissuche zu tun. Hierzu fehlen mir in der UserDB einfach 2 Spalten für LON/LAT ...das würde es einfacher machen. Ich weiß nur nicht wie ich die automatisiert bei Änderungen vom User, geändert bekomme. Wobei das mit dem Updatescript ne gute Idee wäre ... 3-4x am Tag läuft nen Script über die UserDB und liest die PLZ ein und trägt dazu die LON/LATs ein. Bzw. so der Performancekiller wirds nicht sein ... Stündlich. Aber auch unschön für die User ... aber ich denke mal drüber nach bzw. mich erstmal über die Risiken von "fremden" Spalten in dieser Tabelle erkundigen

[Pyramide]

Zeig doch mal, wie deine Tabellenstruktur aussieht. Ich habe den Eindruck, dass du da viel zu kompliziert denkst.

[Wuppi]

das mit dem Kompliziert denken ist sicher nicht falsch
die komplette Struktur zum "importieren" oder reicht auch erklärt?

Tabelle: geodb_locations
* ID (unnötig, wird nirgends referenziert)
* country (DE, AT, CH; wenn es "einfacher" wird, würde ich das ändern in "ausgeschrieben")
* plz (5stellig für DE, 4stellig für AT/CH)
* name (Stadt; auch "unnötig")
* lat (48.209)
* lon (16.37)
=> hier stehen ca. 22.000 PLZen aus 3 Ländern drin - mit den Längen- und Breitenangaben. Die Datenbank hab ich von hier: http://www.tutorials.de/content/1314-ph ... suche.html (das Script ist aber von http://www.cix-blog.de/thm/PHP-Umkreissuche-mit-MySQL)

1. Step der Abfrage: ich muss die lat/lon vom Nutzer auslesen (anhand der PLZ und LAND was er im Profil stehen hat). Diese Angaben müssen anschließend noch umgerechnet werden ( /180 * M_PI)

2. Step ist der SQL-Auszug von oben. Welche PLZ sind im Umkreis X vom User. Mit im Ergebnis bekomme ich halt auch die Entfernung. Bei 100km Umkreis von Köln bekomme ich so etwa 1000 (!) PLZen raus.


Tabelle: user_option_value
Problem ist das die Spalten nur durchnummeriert sind:
* userID
* UserOption1-99 (mit paar lücken)

Relevant:
* userOption65 => PLZ (5stellig für DE, 4stellig für AT/CH)
* userOption85 => Land (Deutschland, Österreich, Schweiz)
=> Diese Tabelle ist auch meine Haupttabelle. Der ganze rest vom Suchstring bezieht sich also darauf - spielt hier jetzt aber keine rolle.

Gruß