Abfrage OpenGeoDB, postleitzahlen groupieren?

[Juergen]

Hallo, ich hoffe mal das ich hier einigermassen richtig bin?

ich beschäftige mich mit der opengeodb Datenbank. Dabei habe ich ein Ausgabe Problem.

SELECT plz.text_val AS plz,gtv.loc_id, name.text_val AS name, typ.text_val AS typ
FROM geodb_textdata gtv
LEFT JOIN geodb_textdata name ON gtv.loc_id = name.loc_id
LEFT JOIN geodb_textdata typ ON gtv.loc_id = typ.loc_id
LEFT JOIN geodb_textdata plz ON gtv.loc_id = plz.loc_id
WHERE name.text_type = 500100000 /* Name */
AND plz.text_type = 500300000 /* Postleitzahl */
AND typ.text_type = 400300000 /* Typ */
AND gtv.text_type = 400100000 /* Teil von */
AND gtv.text_val = '77690 ' /* loc_id des Bezirkes */;

falls bei der Abfrage zu einer Gemeinde mehrere Postleitzahlen vorhanden sind, werden die Gemeinden doppelt, dreifach etc ausgegeben.

6330 	77707 	Ebbs 	Gemeinde
6341 	77707 	Ebbs 	Gemeinde
6352 	77716 	Ellmau 	Gemeinde
6343 	77717 	Erl 	Gemeinde

In diesem Beispiel

http://www.fa-technik.adfc.de/code/opengeodb.pl?parts=77690;c=AT

werden diese dann nur per Komma getrennt.

77707 	Ebbs 	6330, 6341 

Jetzt die Frage, kann ich das in meiner Select Abfrage groupieren? Bzw. irgendwie bündeln.

Für Hilfe bin ich sehr dankbar.

Juergen

[dedlfix]

Tach!

Jetzt die Frage, kann ich das in meiner Select Abfrage groupieren? Bzw. irgendwie bündeln.

Ja. Dazu die entsprechende Tabelle erstmal aus dem Join herausnehmen. Dann schreibst du ein separates Statement, das für einen bestimmten Ort (sprich: loc_id) die Postleitzahlen ermitteln. Klappt das soweit, nimmst du GROUP_CONCAT() hinzu. Es sollte nun ein Ergebnis für einen bestimmten Ort und die PLZen dazu als eine Zeile herauskommen. Das Statement nimmst du nun und hängst es in deine Haupt-Query als Subquery an die Stelle im SELECT, wo das Postleitzahlenfeld ist. Die zum Test verwendete feste loc_id in der Subquery tauschst du nun noch gegen einen Verweis auf das Feld in der Hauptquery aus.

dedlfix.

[Juergen]

SELECT 
->>>>>>>>> plz.text_val AS plz,
gtv.loc_id, name.text_val AS name, typ.text_val AS typ
FROM geodb_textdata gtv
LEFT JOIN geodb_textdata name ON gtv.loc_id = name.loc_id
LEFT JOIN geodb_textdata typ ON gtv.loc_id = typ.loc_id
->>>>>>> LEFT JOIN geodb_textdata plz ON gtv.loc_id = plz.loc_id
WHERE name.text_type = 500100000 /* Name */
AND plz.text_type = 500300000 /* Postleitzahl */
AND typ.text_type = 400300000 /* Typ */
AND gtv.text_type = 400100000 /* Teil von */
AND gtv.text_val = '77690 ' /* loc_id des Bezirkes */;

Also die beiden müssen raus

Dann schreibst du ein separates Statement

Bei WHERE?

[Juergen]

Ich habe es so gelösst:

Ich habe deinenRat mit GROUP_CONCAT() gefolgt, habe aber nichts gelöscht, und dann groupiert

SELECT GROUP_CONCAT(plz.text_val AS plz),gtv.loc_id, name.text_val AS name, typ.text_val AS typ
FROM geodb_textdata gtv
LEFT JOIN geodb_textdata name ON gtv.loc_id = name.loc_id
LEFT JOIN geodb_textdata typ ON gtv.loc_id = typ.loc_id
LEFT JOIN geodb_textdata plz ON gtv.loc_id = plz.loc_id
WHERE name.text_type = 500100000 /* Name */
AND plz.text_type = 500300000 /* Postleitzahl */
AND typ.text_type = 400300000 /* Typ */
AND gtv.text_type = 400100000 /* Teil von */
AND gtv.text_val = '77690' /* loc_id des Bezirkes */
GROUP by gtv.loc_id

[dedlfix]

Tach!

Dann schreibst du ein separates Statement

Bei WHERE?

Nein, ein komplett neues Statement. Das bleibt erstmal so lange eigenständig, bis es wie gewünscht läuft. Dann erst wird es wieder in der Haupt-Query eingebaut.

Ich habe es so gelösst:

Ich habe deinenRat mit GROUP_CONCAT() gefolgt, habe aber nichts gelöscht, und dann groupiert

Nein, das war nicht mein Rat, denn nun hast du ein Statement, das nur aufgrund einer MySQL-Gutmütigkeit funktioniert. Du gruppierst über ein einzelnes Feld, hast aber mehrere andere Felder in der SELECT-Klausel stehen. Aus welchem Datensatz der Gruppe soll nun die Information für diese Felder genommen werden? Bei aggregierten Daten (zusammengefasst durch Summenbildung oder ähnliches, und auch das GROUP_CONCAT()) ist das kein Problem. Und es ist auch kein Problem, wenn die Datensätze nur aufgrund des JOINS vervielfältigt werden.

Außerdem wird durch Joins dein Statement recht komplex. Correlated Subquerys mache es zwar nicht kleiner, aber dadurch werden die Teilabfragen in eigene Querys separiert. Und die kann man auch mal extrahieren und separat testen.

Es ist auch nicht notwendig, erst eine große Datenmasse aus vervielfältigten Datensätzen entstehen zu lassen, die man dann wieder eindampft. Correlated Subquerys können dabei helfen, die Übersicht zu bewahren und das Joinprodukt zu vermeiden. Diese Technik geht erst dann nicht mehr zu verwenden, wenn aus den anderen Tabellen mehr als ein Wert und ein Datensatz kommen sollen. Dann braucht man weiterhin Joins.

Das ist das Statement zum Testen der Postleitzahlenabfrage. Die id 42 hab ich mir ausgedacht, nimm da eine real existierende für den Test.

SELECT GROUP_CONCAT(text_val) FROM geodb_textdata WHERE text_type = 500300000 AND loc_id = 42;

Genauso kannst du die anderen Datenabfragen in eigene Statements auslagern:

SELECT text_val FROM geodb_textdata WHERE text_type = 500100000 AND loc_id = 42;

SELECT text_val FROM geodb_textdata WHERE text_type = 400300000 AND loc_id = 42;

Zusammengebaut sieht das dann so aus, wobei aus der 42 der Verweis auf die Haupt-Query geworden ist:

SELECT 

(SELECT GROUP_CONCAT(text_val) FROM geodb_textdata WHERE text_type = 500300000 /* Postleitzahl */ AND loc_id = gtv.loc_id) AS plz,

gtv.loc_id, 

(SELECT text_val FROM geodb_textdata WHERE text_type = 500100000 /* Name */ AND loc_id = gtv.loc_id) AS name,

(SELECT text_val FROM geodb_textdata WHERE text_type = 400300000 /* Typ */ AND loc_id = gtv.loc_id) AS typ

FROM geodb_textdata gtv
WHERE text_type = 400100000 /* Teil von */
AND text_val = '77690' /* loc_id des Bezirkes */

dedlfix.

[Juergen]

Boah, das muss ich mir jetzt in RUHE anschauen, aber es ist ja viel einfacher als mit den ganze LEFT JOIN und WHERE abfragen. Super danke dedlfix, das probiere ich jetzt mal alles durch

[Rolf b]

Hast Du Dir das mal mit einem EXPLAIN angeschaut? Normalerweise müsste der SQL Statement Compiler aus einer correlated Subquery implizit einen Join machen.

Rolf

[dedlfix]

Tach!

Hast Du Dir das mal mit einem EXPLAIN angeschaut? Normalerweise müsste der SQL Statement Compiler aus einer correlated Subquery implizit einen Join machen.

Das kann der Optimizer gern halten wie er will, solange es für mich übersichtlicher und wartungsfreundlicher wird (und die Performance annehmbar bleibt).

dedlfix.

[Juergen]

Wenn ich aber jetzt doch wieder alle PLZ haben möchte und den GROUP_CONCAT entferne um deinen Code weiter beibehalten möchte, weil er einfach beim erweitern und zufügen weitere Abfragen ist

SELECT 
(SELECT text_val FROM geodb_textdata WHERE text_type = 500300000 /* Postleitzahl */ AND loc_id = gtv.loc_id) AS plz,
gtv.loc_id, 
(SELECT text_val FROM geodb_textdata WHERE text_type = 500100000 /* Name */ AND loc_id = gtv.loc_id) AS name,
(SELECT text_val FROM geodb_textdata WHERE text_type = 400300000 /* Typ */ AND loc_id = gtv.loc_id) AS typ
FROM geodb_textdata gtv
WHERE text_type = 400100000 /* Teil von */
AND text_val = '77690' /* loc_id des Bezirkes */

kommt folgtende Fehlermeldung!

Warning: mysqli_query(): (21000/1242): Subquery returns more than 1 row Warning: mysqli_error() expects exactly 1 parameter, 0 given

[dedlfix]

Tach!

Wenn ich aber jetzt doch wieder alle PLZ haben möchte und den GROUP_CONCAT entferne um deinen Code weiter beibehalten möchte, weil er einfach beim erweitern und zufügen weitere Abfragen ist kommt folgtende Fehlermeldung!

Warning: mysqli_query(): (21000/1242): Subquery returns more than 1 row

Ja, deswegen schrieb ich auch:

• Diese Technik geht erst dann nicht mehr zu verwenden, wenn aus den anderen Tabellen mehr als ein Wert und ein Datensatz kommen sollen. Dann braucht man weiterhin Joins.

Ein Subquery im SELECT-Teil kann nur einen einzelnen Wert für den aktuellen Datensatz liefern. Wenn du mehrere Datensätze aufgrund einer 1:n-Beziehung erzeugen möchtest, dann Join.

Zumindest die Postleitzahlen müssen dann als Join ausgeführt werden, die anderen beiden sind ja weiterhin Einzeldaten.

Warning: mysqli_error() expects exactly 1 parameter, 0 given

Das ist wohl eher ein Fehler in deinem PHP-Code.

dedlfix.

[Juergen]

Aber ein Problem habe ich noch. und zwar wenn es zwei Ausgaben pro Ort,Gemeinde gibt,

SELECT gtv.loc_id,
(SELECT text_val test FROM geodb_textdata WHERE text_type = 400300000 /* Typ */ AND loc_id = gtv.loc_id) AS typ,
(SELECT text_val FROM geodb_textdata WHERE text_type = 400200000 /* Typ */ AND loc_id = gtv.loc_id) AS ebene,
gtv.text_val AS name
FROM geodb_textdata gtv
WHERE text_val LIKE '%pas%' 
AND text_type = 500100000 

Das wäre die Ausgabe

id   typ   ebene    name
65295 	Ort 	7 	Wolfpassing an der Hochleithen
65609 	Gemeinde 	6 	Wimpassing im Schwarzatale
65610 	Ort 	7 	Wimpassing im Schwarzatale
65775 	Ort 	7 	Wimpassing an der Pielach
66406 	Ort 	7 	Windpassing
66467 	Gemeinde 	6 	Wolfpassing
66482 	Ort 	7 	Wolfpassing
66613 	Gemeinde 	6 	Zeiselmauer-Wolfpassing
66614 	Ort 	7 	Wolfpassing
67417 	Ort 	7 	Wimpassing

[dedlfix]

Tach!

Aber ein Problem habe ich noch.

Du hast vergessen, es zu beschreiben.

dedlfix.

[Juergen]

Du hast vergessen, es zu beschreiben.

und zwar wenn es zwei Ausgaben pro Ort,Gemeinde gibt. Sollte nur einer erscheinen. Ich habe es jetzt nach Namen Groupiert, dann kommt nur ein Ort. Aber wie groupiere ich es (sauberer), das nur die Ebene 7 erfasst wird und nicht die 6

Michael

[dedlfix]

Tach!

und zwar wenn es zwei Ausgaben pro Ort,Gemeinde gibt. Sollte nur einer erscheinen. Ich habe es jetzt nach Namen Groupiert, dann kommt nur ein Ort. Aber wie groupiere ich es (sauberer), das nur die Ebene 7 erfasst wird und nicht die 6

Vermutlich ist nicht das Gruppieren die Lösung, denn dann bleibt ja die Frage, welcher von beiden Einträgen soll nun genommen werden. Die haben ja beide eine unterschiedliche ID. Da wäre zunächst zu klären, warum es zwei/mehrere Einträge gibt, und ob sich dann nicht ein Kriterium ergibt, anhand dessen der eine oder der andere ausgeschlossen werden kann.

Mit anderen Worten, das ist erstmal ein fachliches Problem und weniger eins der Programmierung.

dedlfix.

[Juergen]

Ja das hast du Recht, jetzt habe ich noch eine Frage, dann glaube ich kapier ich es:

SELECT 

(SELECT GROUP_CONCAT(text_val) FROM geodb_textdata WHERE text_type = 500300000 /* Postleitzahl */ AND loc_id = gtv.loc_id) AS plz,

gtv.loc_id, 

(SELECT text_val FROM geodb_textdata WHERE text_type = 500100000 /* Name */ AND loc_id = gtv.loc_id) AS name,

(SELECT text_val FROM geodb_textdata WHERE text_type = 400300000 /* Typ */ AND loc_id = gtv.loc_id) AS typ,
(SELECT text_val FROM geodb_textdata WHERE text_type = 400100000 /* Typ */ AND loc_id = gtv.loc_id) AS teilvon
FROM geodb_textdata gtv
WHERE text_type = 400100000 /* Teil von */
AND text_val = '77690' /* loc_id des Bezirkes */

Das ist dein Code vom Anfang her. Ich habe diesen erweitert und ausprobiert. Jetzt habe ich noch eine Frage.

(SELECT text_val FROM geodb_textdata WHERE text_type = 400100000 /* Typ */ AND loc_id = gtv.loc_id) AS teilvon

Wenn ich das einfüge wie oben geschehen kommt, als Antwort die betreffende loc_id. Was erwünscht ist, doch wie wiederum, kann ich zu diese Loc_id, auch den Namen wie bei der Abfrage

(SELECT text_val FROM geodb_textdata WHERE text_type = 500100000 /* Name */ AND loc_id = gtv.loc_id) AS name

anzeigen.

[dedlfix]

Tach!

Wenn ich das einfüge wie oben geschehen kommt, als Antwort die betreffende loc_id. Was erwünscht ist, doch wie wiederum, kann ich zu diese Loc_id, auch den Namen wie bei der Abfrage

Als erstes wäre da, die Aufgabe in Einzelteile herunterzubrechen. Du bekommst also bei einem bestimmten Statement eine bestimmte Antwort und die ist noch nicht das Ziel sondern nur ein Zwischenschritt.

Ausgangspunkt ist eine loc-id aus der Haupt-Query. Die nehmen wir erstmal als festen Wert (42 fürs Beispiel). Eine Abfrage mit dieser id und dem Typ 400100000 ("Teil von") ergibt die loc-id der übergeordneten Einheit. Und damit kannst du dann weitere Detaildaten zu dieser abfragen. Diese Abfragen kannst du jeweils als einzelne Querys formulieren. Dazu setzt du das Ergebnis aus der vorherigen Abfrage erstmal zu Fuß in den WHERE-Teil der nächsten Query. Wenn die einzeln Querys wie gewünscht arbeiten, kannst du nun rückwärts gehen und sie als Subquerys in die vorhergehende einfügen, da wo das Ergebnis hinkommen soll. Statt dem händischen Wert muss nun ein Bezug zur übergeordneten Query über deren entsprechendes Feld hergestellt werden. Die ganze Geschichte verschachtelt sich in dem Fall. Und du brauchs sie mehrfach, wenn mehrere Daten aus der übergeordneten Einheit abgefragt werden sollen.

Alternativ zu dieser Vermehrung kannst du die Query für die übergeordneten Daten erstmal separat erstellen (nach dem bisherigen Subquery-Prinzip), so dass du je loc-id einen Datensatz erhältst. Dieses Gebilde kannst du dann als Subquery in den FROM-Teil bringen und einen Join zur Haupt-Query erstellen. Insgesamt nicht schön, aber ...

Die Tabelle hat ein nicht ganz günstiges Format für solche Abfragen, aber so ist sie nun mal aufgebaut. Ihre Struktur ist einfach, dafür aber sehr erweiterbar. Eine Empfehlung des Projektes lautet auch, sich eine eigene Struktur anzulegen, die für den eigenen Anwendungsfall besser geeignet ist und die Daten dahinein zu transformieren. Dann hat man den komplexen Teil zwar immer noch, ihn aber in die Transformationsroutine delegiert, und kann mit einfacheren Abfragen im eigenen Projekt arbeiten. Das lohnt sich vermutlich aber erst dann, wenn man Geschwindigkeitsprobleme mit der Originaldatenstruktur bekommt.

Das Prinzip sieht dann so aus, wenn die Aufgabe wäre, Name und Einwohner eines Ortes und dessen übergeordneter Einheit zu ermitteln.

SELECT 
    loc-id, 
    (Subquery für Name zu loc-id),
    (Subquery für Einwohner zu loc-id),
    (Subquery für Name zu (Subquery für TeilVon zu loc-id)),
    (Subquery für Einwohner zu (Subquery für TeilVon zu loc-id))
FROM ...

oder

SELECT 
    loc-id, 
    (Subquery für Name zu loc-id),
    (Subquery für Einwohner zu loc-id),
    übergordnet.Name,
    übergordnet.Einwohner
FROM tabelle AS haupt 
LEFT JOIN (
    SELECT 
        (Subquery für Name zu loc-id) AS Name,
        (Subquery für Einwohner zu loc-id) AS Einwohner,
    FROM tabelle
) AS übergeordnet ON übergeordnet.loc-id = haupt.TeilVon
WHERE ...

dedlfix.