Punkte Flächen zuordnen

Fragen und Probleme rund um die Abfragespache SQL.
Lasagne45
Neuer Benutzer
Beiträge: 2
Registriert: Mi 3. Apr 2019, 13:39

Punkte Flächen zuordnen

Beitrag von Lasagne45 » Mi 3. Apr 2019, 13:50

Hallo zusammen,

ich möchte einer Fläche Informationen zuweisen, falls ein Punkt innerhalb liegt. Ich habe drei Tabellen und mein Programm läuft auch. Das Problem ist das ich die für Deutschland ca 30000 einzelflächen und um die 50 mio Punkte. Der folgende Code macht auch genau das richtige. Mein Problem ist, dass ich für 10 000 Werte 30 Minuten brauchen und ich vermute, dass es dann halt 40 Tage dauert für die 50 mio Punkte.

INSERT INTO ergebniss(
"Typ", "Def.", d, h_geometry, fid)

SELECT "Typ", "Def.", d,h_geometry, cde_ogc_fid
FROM sortiert, zusammen

WHERE ST_Intersects(ST_Transform(c_geometry, 32632), ST_Transform(s_geometry, 32632));

Ich bin noch ziemlicher Anfänger und habe mal folgenden Eckdaten die evtl noch interessant sind.

- Ein Punkt liegt auch immer nur in einer Fläche
- Die Flächen sind alle gleich groß
- Es liegen mehrere Punkte in einer Fläche.
- Es liegt nicht in jeder Fläche ein Punkt

Meine Lösung: Ich vermute, dass falls der Intersects ein true zurück gibt, trotzdem noch alle andern Flächen geprüft werden. Weiß aber nicht wie ich daran etwas ändern kann.

Würde mich über Vorschläge freuen.

akretschmer
Erfahrener Benutzer
Beiträge: 7625
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Punkte Flächen zuordnen

Beitrag von akretschmer » Mi 3. Apr 2019, 14:10

das sieht nach einem cross join aus. Vielleicht hilft es, jeweils auf die geometry-Spalten einen Index auf die Funktion st_transforms(...) zu legen. Das löst das Problem mit dem cross join zwar nicht, könnte aber die Prüfung signifikant verkürzen.
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Gisman
Erfahrener Benutzer
Beiträge: 1126
Registriert: Fr 20. Jan 2006, 15:30
Wohnort: Pfalz
Kontaktdaten:

Re: Punkte Flächen zuordnen

Beitrag von Gisman » Mi 3. Apr 2019, 16:33

Sind die Geometriespalten indiziert? Wie sehen deine Flächen aus? Isometrisch oder lang und schmal, oder gar geschlängelt? Hast du die Bedingung mal als Join-Bedingung geschrieben (weiß allerdings nicht ob das was bringt). Hast du mal st_within oder ähnliches ausprobiert statt st_Intersects? Hast du Indizes auf der Ergebnistabelle, die aktualisiert werden?

1. Ein Punkt liegt auch immer nur in einer Fläche
Schön. Andernfalls wird für jede Fläche eine Zeile ausgegeben. Verhindern kann man das mit den anderen Topologischen Funktionen (st_contains, st_within,...)
2. Die Flächen sind alle gleich groß
Sollte im wesentlichen wenig Einfluß haben, es sei denn, die Flächen sind stark unterschiedlich oder eben ungünstig geformt.
3. Es liegen mehrere Punkte in einer Fläche.
Dann wird für jeden Punkt eine Zeile ausgegeben. man könnte das verhindern in dem man über die Punkte aggregiert.
4. Es liegt nicht in jeder Fläche ein Punkt
Dann gibt es keine Ergebnisse. Wenn aber die leeren Flächen trotzdem erscheinen sollen, empfiehlt sich ein Left Outer Join zwischen den Tabellen.
Eventuell hilft es, Punkte und Flächen nach einem Raster aufzuteilen, so dass immer nur Inhalte einer Rasterzelle bearbeitet werden müssen. Diese können dann anhand des ID der Rasterzelle gesucht werden, so dass teure geometrische Vergleiche ausbleiben.

Soweit erstmal. Bin gerade an ähnlichen Aufgaben.

Gruß Uwe

Lasagne45
Neuer Benutzer
Beiträge: 2
Registriert: Mi 3. Apr 2019, 13:39

Re: Punkte Flächen zuordnen

Beitrag von Lasagne45 » Fr 5. Apr 2019, 08:44

Also ich habe meine Code noch mal geändert und lass den ST_INTERSECT Befehl jetzt ohne den Transform Befehl laufen, hat mir aber nichts gebracht.


Sind die Geometriespalten indiziert? Wie sehen deine Flächen aus? Isometrisch oder lang und schmal, oder gar geschlängelt?

Jede spalte hat eine eigene ID, wobei sie nicht 1,2,3 sonder 1,3,7,12 durchläuft. Die Flächen sind quadratisch und haben eine Fläche von ca 7 km².

Hast du mal st_within oder ähnliches ausprobiert statt st_Intersects?

Join hab ich nicht versucht WITHIN und CONTAINS hab ich noch versucht, die eine Funktion gibt kein Wert zurück, die andere genau das gleiche wie Intersects und auch bei gleicher Laufzeit.


Hast du Indizes auf der Ergebnistabelle, die aktualisiert werden?

Ich versteh die Frage nicht ganz, aber meine ID ändert sich nicht, ich ändere gar kein Wert, sondern möchte nur den Zusammenhang ermitteln.

Ich könnte evtl. die Flächen und Punkte z.B. auf Bundesländer runterbrechen, weiß aber nicht ob das so viel bringt. Im moment rechne ich nur mit ca. 1 mio Punkte und habe den Intersect Befehl jetzt nach 2 Tagen abgebrochen.

Meine Idee war noch, dass ich mit dem Befehl st_closestpoint arbeite, weiß aber nicht wie ich darauß eine boolsche Funktion bekomme.

pogomips
Erfahrener Benutzer
Beiträge: 103
Registriert: Fr 18. Nov 2016, 09:55

Re: Punkte Flächen zuordnen

Beitrag von pogomips » Fr 5. Apr 2019, 18:22

Es ging Dir doch um Geschwindigkeit oder?
Und Spalten die ID haben? Versteh ich nicht.

"Index" ist ein Fachbegriff bei Datenbanken, ein Index liefert bei Existenz und Anwendung innerhalb einer Abfrage einen Beschleunigten Zugriff auf gegebene Werte. Kannst Du notfalls mal nachschlagen.

Du änderst keine Werte, hast aber ein Insert Statement in Verwendung. Insertstatements ändern tatsächlich nichts, aber erzeugen Werte. Dabei ist ein bereits definierter Index hinderlich (aus Performanceperspektive). Darauf spielen diese Fragen an.

akretschmer
Erfahrener Benutzer
Beiträge: 7625
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Punkte Flächen zuordnen

Beitrag von akretschmer » Fr 5. Apr 2019, 22:03

@pogomips: das Insert hier wird von einem Select gespeist, von daher wird alles, was das Select beschleunigt, also auch ein Index, die ganze Aktion beschleunigen.

@Lasagne45: Deine Bemerkung "Join hab ich nicht versucht " ist für den Eimer, weil Dein "FROM sortiert, zusammen" ein implizieter JOIN ist.
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

pogomips
Erfahrener Benutzer
Beiträge: 103
Registriert: Fr 18. Nov 2016, 09:55

Re: Punkte Flächen zuordnen

Beitrag von pogomips » So 7. Apr 2019, 20:37

akretschmer hat geschrieben:
Fr 5. Apr 2019, 22:03
@pogomips: das Insert hier wird von einem Select gespeist, ..
Mir musst Du das nicht erklären.
Ich dachte, die Antworten des TE wirken so, als ob er den Hinweis vertragen könnte.

Gisman
Erfahrener Benutzer
Beiträge: 1126
Registriert: Fr 20. Jan 2006, 15:30
Wohnort: Pfalz
Kontaktdaten:

Re: Punkte Flächen zuordnen

Beitrag von Gisman » Mo 8. Apr 2019, 08:33

Hallo nochmal!
Wie schon angedeutet habe ich gerade eine ähnliche Aufgabenstellung und möchte mal kurz beschreiben, wie ich vorgehe. Konkret geht es darum aus ca 200Mio. Punkten einer Laserscanbefliegung ein Geländemodell in Form eines Multibandrasters zu erzeugen. Hierzu werden Rasterkacheln von 250x250m erzeugt, die Zellen von je 1m Seitenlänge enthalten. Danach werden die Bodenpunkte jeder Rasterzelle gesucht, aggregiert und als Mittelwert, Min oder Max, etc. in verschiedene Bänder des Rasters geschrieben. Hierzu habe ich mir aus den Koordinaten der Punkte die jeweilige Kachel und die x/x-Position der Zellen errechnet und in entsprechende Spalten geschrieben. Das war einfach zu bewerkstelligen ohne geometrische Abfragen und die Abfrage nach dem Inhalt einer Rasterzelle ist so meiner Erfahrung zufolge schneller als ein ST_Intersects o.ä. auf die Geometrie der Rasterzellen. Auch dürfte der notwendige Index bei 200 Mio Punkten sehr umfangreich werden und langsam im Aufbau sein.
Dazu jetzt die Zusatzfrage:
Ich möchte einen zusammengesetzten Index auf die Kachel, x- und y-Position der Punkte einführen. Die Daten sind durch das Einlesen mehr oder weniger strukturiert entsprechend der Reihenfolge des Einlesens (Dateien entsprechen je 1 km²), intern durch das Processing des Datenlieferanten Kacheln von 500m und darunter Kacheln von geschätzt 50m. Zumindest erfolgt der Bildaufbau so, wenn man eine Datei bzw. deren importierten Inhalt mit QGIS darstellt. Das Ergebnis der Abfrage nach einer Rasterzelle ergibt ca. 10-15 Punkte, die in der Tabelle recht eng zusammen stehen sollten. Die Abfrage nach einer Rasterzelle dauert ohne Index als Referenz so etwa 30-40 Sek, was für eine Gesamtprozessierung des Modells zu langsam ist. Dies soll im übrigen mit einem externen Pythonskript gemacht werden, das per Multiprocessing jeweils eine der 250m-Kacheln bearbeitet.
nun aber die Frage ;):
Ist es sinnvoll hier einen BRIN-Index einzusetzen oder ist ein normaler BTree besser? Wie baue ich den am Besten auf, mit drei Feldern (Kachel, x, y) oder Kachel für sich und x,y extra?
Oder liege ich insgesamt falsch und die Arbeit mit Geometrien ist besser und ich habe mich bisher bloß dumm angestellt?

Ich hoffe, die Beschreibung hilft auch den TE ein bisschen weiter.
Grüße Uwe

akretschmer
Erfahrener Benutzer
Beiträge: 7625
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Punkte Flächen zuordnen

Beitrag von akretschmer » Mo 8. Apr 2019, 08:42

BRIN ist sehr effektiv, wenn:
  • die Daten nach der/den Spalte(n), über die der Index geht, physisch sortiert vorliegt. Wenn, physisch gesehen, alle Daten zu eine Kachel in der Tabelle physisch dicht beeinander stehen UND die Kachel dann auch in der WHERE-Condition auftacht, dann wäre ein BRIN sehr effektiv, wenn außerdem
  • die Daten sich dann nicht mehr ändern
Wenn ich Dich nicht falsch verstehe, würde ich einen BRIN für die Kachel erstellen und einen normalen für x,y. Bei einer Abfrage mit Kachel, x und y im WHERE würde ich dann einen Bitmap Index Scan erwarten.
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Gisman
Erfahrener Benutzer
Beiträge: 1126
Registriert: Fr 20. Jan 2006, 15:30
Wohnort: Pfalz
Kontaktdaten:

Re: Punkte Flächen zuordnen

Beitrag von Gisman » Mo 8. Apr 2019, 10:02

Habe mal kurz ein paar Dinge getestet:
Ohne Index: Abfrage so ca. 2 min, die 40 sec waren wohl ein anderer Effekt.
Btree über 3 Spalten mit und ohne Clustering: 11ms konstant, Clustering hatte keinen Effekt.
BRIN: 4 sek. Konstant.

Jeweils für zufällig gewählte Kacheln und x/y-Werte.
EDIT:
Die von Andreas beschriebene Variante liegt um 100ms, mal etwas schneller oder langsamer.

Die lange Laufzeit für die geometrische Abfrage kann ich auch bestätigen.
Zuletzt geändert von Gisman am Mo 8. Apr 2019, 10:44, insgesamt 1-mal geändert.

Antworten