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.
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.
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?
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,…)
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.
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.
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.
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.
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.
@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.
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
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.
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.
Okay, ja, BRIN ist relativ ungenau, da ist immer noch ein Blick in die Tabelle nötig, daher ist der auch langsamer. Ein BTREE liefert genau die Datensätze, hier ist nur noch die Sichbarkeit zu prüfen. Wenn alle Spalten im Result im BTREE-Index drin sind UND die visibility-Map (VM) aktuell ist, kann es sogar zu einem noch schnelleren Index-Only-Scan kommen. Um die VM zu aktualisieren, einmal manuell ein VACUUM auf der Tabelle laufen lassen (bei Insert-Only-Tabellen, was ich hier vermute)
Dafür hat BRIN den Vorteil, daß solch ein Index auch bei Tabellen im TB-Bereich sehr klein ist, einige MB. BRIN vers. BTREE ist also eine Abwägung zwischen Geschwindigkeit (BTREE) und Index-Größe (BRIN).
: