Wie bekommt man die Punkte von zwei gleichen Teilstücken?

Hi,

man kann mit Postgis ja nach überschneidenden Geometrien suchen.

Ein kleines Beispiel wäre:

 ... box'((0,0),(1,1))' && box'((0,0),(2,2))' ...
 ... box'((0,0),(1,1))' &< box'((0,0),(2,2))' ...
 ... box'((0,0),(1,1))' &> box'((0,0),(2,2))' ...

Ok man bekommt als Antwort true oder false.
Alles super.

Angenommen man würde zwei Linestrings oder Polygone vergleichen. Dann bekommt man als Antwort auch wieder True und False.

Wie bekomme man jetzt aber die Punkte (Id’s) als Ergebnis, die gleich sind!?

Moin!

Das sollte mit ST_Intersection() oder ST_Difference() funktionieren. Die Funktionen liefern dir die jeweilige Schnittmengen alsGeometrien. Die kannst du dann weiterverarbeiten. Die jeweilligen IDs aus der Ausgangsgeometrie dürften dabei allerding wohl nicht erhaltenbleiben, die müsstest du dir in einem anderen Konstrukt besorgen.

Gruß Uwe

hi gisman

Danke erst mal wieder.

Doch was genau oder wie funktioniert st_intersection.
Aus dem Manual werde geht das leider nicht so hervor.
Legt st_intersection eine Box um die Werte oder einen eigenen Buffer oder oder. Sprich wie vergleicht es die beiden Geometryn.

Danke

AFAIR bekommst du die Schnittmenge der beiden Ausgangsgeometrien, d.h

Polygon-Polygon: Polygon, Linie oder ein Punkt
Polygon-Linie: Linie oder Punkt
Linie-Linie: Punkt oder Linie

Gruß Uwe

Hi,

Also mit intersection bekommt man die Schnitmenge das ist an sich auch super.
Doch was ist wenn man an die Punkte der einzelnen Tracks bekommen möchte weil sie mehr Informationen besitzen.

[CENTER]

[LEFT]Mit Intersection bekommt man die Schnittpunkte jeweils in den blauen Kreisen wieder. Ok Super.
Wie bekommt man aber die Original Punkte die sich annähernd berühren. Klar man muss ein Buffer um beide legen oder nur um einen.

Idee 1:
Geht das nur in dem ich jeden einzelnen Punk mit dem anderem Track vergleiche.
z.B Über Abstands Berechnung, der ganzen Punkte.
Punkt 1 von Rot zu Punkt 1 von Blau => Abstand < Wert X
dann Speicher Punkt 1 rot und Punkt 1 blau ab.

Oder gibt es eine Methode die dieses erledigt.

Idee 2:
Es gibt da ja auch Within aber die antwortet ja leider nur mit True oder Fals. Also muss man dann wieder Einzelne Punkte untersuchen.

Idee 3:

Suche gerade das gegenteil von difference!
Noch am überlegen bzw Suchen.


Ich wäre für eine Idee noch Dankbar

mfg


[/LEFT]
[/CENTER]

Also eine Methode, die dir die Schnittpunkte zweier Polygone zurückgibt existiert soweit ich weiß nicht. Wenn du die genauen Schnittpunkte möchtest, dann könnte es so funktionieren:

  1. Polygon mit ST_ExteriorRing(geometry) zu Linie konvertieren. Wobei ich mir nicht ganz sicher bin ob die Funktion auch wirklich das macht.
  2. Eine Intersektion durchführen. Dann bekommst du die Schnittpunkte.

Wenn du die annähernd gleichen Punkte haben möchtest kommst du glaube ich nicht darum herum die Punkte der beiden Polygone gegeneinander zu vergleichen.
Du kannst das ganze höchstens noch folgendermaßen einschränken:

  1. Buffer (Größe je nach Schwellenwert) um beide Polygone legen und Intersection ausführen.
  2. Alle Punkte der ursprünglichen Polygone die in der Schnittmenge liegen gegeneinander vergleichen.
    Mit diesem Hilfskonstrukt verhinderst du, dass du die Punkte, die sowieso nicht in Frage kommen unnötig bearbeiten musst. Das ganze dürfte sich vor allem bei “punktreichen” Polygonen auf die Geschwindigkeit auswirken.

Hi,

Danke erst mal für die Antworten.
Ich habe einiges ausprobiert und getestet mit Beispielen die sehr simpel sind.
Dabei habe ich festgestellt das intersection neue Schnittpunkte zurück gibt ist für manches gut aber das kann ich leider nicht gebrauchen.
Difference ist an sich super, aber ich brauch genau das gegenteil von dieser Methode.

Meine Lösung geht jetzt mit within. Mein Problem ist jetzt die Laufzeit.

Die liegt momentan leider bei 750’000ms.
Obwohl ich schon einige Abfragen vorher Stelle wo durch ich einige Punkte Ausschließe.

select g.id,g.messung_id
    from gps_messwerte g,
         track x inner join messung y on (x.id=y.track_id),
         track t inner join messung m on (t.id=m.track_id)
    where 
    t.id=6
    and x.id != t.id
    and box2d(x.geom) && box2d(t.geom) = True
    and g.messung_id != m.id
    and g.messung_id = y.id
    and box2d(g.geom) @ box2d(t.geom) = True
    and within(transform(g.geom,31467),buffer(transform(t.geom,31467),2) ) 
    
    order by id 

limit 10;

Ohne within liegt die Abfrage bei 1313 ms.
Und liefert 9300 Punkte wenn man das limit nicht Berücksichtigt.

Meine Frage wie kann man das schneller machen oder vereinfachen.

Desweiteren habe ich noch eine Frage wie geht Postgres bzw Postgis die Bedingungen durch.
Ich habe mal explain vor meine Abfrage gesetzt und habe folgendes erhalten.

"Nested Loop  (cost=15.86..2529.35 rows=1 width=8)"
"  Join Filter: (g.messung_id <> m.id)"
"  ->  Hash Join  (cost=15.86..2528.11 rows=1 width=12)"
"        Hash Cond: (g.messung_id = y.id)"
"        Join Filter: (((box2d(g.geom))::geometry @ (box2d(t.geom))::geometry) AND within(transform(g.geom, 31467), buffer(transform(t.geom, 31467), 2::double precision)))"
"        ->  Seq Scan on gps_messwerte g  (cost=0.00..2309.66 rows=29766 width=41)"
"        ->  Hash  (cost=15.86..15.86 rows=1 width=71475)"
"              ->  Hash Join  (cost=1.22..15.86 rows=1 width=71475)"
"                    Hash Cond: (x.id = y.track_id)"
"                    ->  Nested Loop  (cost=0.00..14.62 rows=1 width=71475)"
"                          Join Filter: ((x.id <> t.id) AND ((box2d(x.geom))::geometry && (box2d(t.geom))::geometry))"
"                          ->  Seq Scan on track t  (cost=0.00..7.16 rows=1 width=71471)"
"                                Filter: (id = 6)"
"                          ->  Seq Scan on track x  (cost=0.00..7.13 rows=13 width=71471)"
"                    ->  Hash  (cost=1.18..1.18 rows=18 width=8)"
"                          ->  Seq Scan on messung y  (cost=0.00..1.18 rows=18 width=8)"
"  ->  Seq Scan on messung m  (cost=0.00..1.23 rows=1 width=8)"
"        Filter: (6 = track_id)"

Ich kenne PostGIS/within() nicht, aber PostgreSQL kann funktionale Indexe. Möglicherweise könnte dies Dir hier massiv helfen.


Desweiteren habe ich noch eine Frage wie geht Postgres bzw Postgis die Bedingungen durch.
Ich habe mal > explain > vor meine Abfrage gesetzt und habe folgendes erhalten.

Ein EXPLAIN ANALYSE ist hilfreicher.

Ansonsten: das, was am tiefsten in der Verschachtelung da ist, wird zuerst gemacht.

Vielleicht kommt ja der Gisman gleich um die Ecke und kann noch a bissl mehr sagen…


Andreas

Hi,

Also Index habe ich gesetzt auf gist.

CREATE TABLE gps_messwerte
(
  id integer NOT NULL,
  zeitstempel timestamp with time zone,
  messung_id integer,
  breite double precision,
  laenge double precision,
  hoehe double precision,
  einheitbreite character(20),
  einheitlaenge character(20),
  einheithoehe character(20),
  geom geometry,
  CONSTRAINT gps_messwerte_pkey PRIMARY KEY (id),
  CONSTRAINT gps_messwerte_messung_id_fkey FOREIGN KEY (messung_id)
      REFERENCES messung (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT enforce_dims_geom CHECK (ndims(geom) = 3),
  CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POINT'::text OR geom IS NULL),
  CONSTRAINT enforce_srid_geom CHECK (srid(geom) = 4326)
)
WITH (OIDS=TRUE);
ALTER TABLE gps_messwerte OWNER TO till;

-- Index: index_messwerte

-- DROP INDEX index_messwerte;

CREATE INDEX index_messwerte
  ON gps_messwerte
  USING gist
  (geom);

Die explain Analyse läuft noch. Wenn ich das Ergebnis habe Poste ich es noch.

So das Ergebniss:

"Nested Loop  (cost=15.86..2529.35 rows=1 width=41) (actual time=2713.651..1428295.459 rows=1424 loops=1)"
"  Join Filter: (g.messung_id <> m.id)"
"  ->  Hash Join  (cost=15.86..2528.11 rows=1 width=45) (actual time=2713.606..1428033.129 rows=1424 loops=1)"
"        Hash Cond: (g.messung_id = y.id)"
"        Join Filter: (((box2d(g.geom))::geometry @ (box2d(t.geom))::geometry) AND within(transform(g.geom, 31467), buffer(transform(t.geom, 31467), 2::double precision)))"
"        ->  Seq Scan on gps_messwerte g  (cost=0.00..2309.66 rows=29766 width=41) (actual time=4.034..181.663 rows=29766 loops=1)"
"        ->  Hash  (cost=15.86..15.86 rows=1 width=71475) (actual time=4.948..4.948 rows=8 loops=1)"
"              ->  Hash Join  (cost=1.22..15.86 rows=1 width=71475) (actual time=1.927..4.922 rows=8 loops=1)"
"                    Hash Cond: (x.id = y.track_id)"
"                    ->  Nested Loop  (cost=0.00..14.62 rows=1 width=71475) (actual time=1.816..4.781 rows=6 loops=1)"
"                          Join Filter: ((x.id <> t.id) AND ((box2d(x.geom))::geometry && (box2d(t.geom))::geometry))"
"                          ->  Seq Scan on track t  (cost=0.00..7.16 rows=1 width=71471) (actual time=0.041..0.046 rows=1 loops=1)"
"                                Filter: (id = 6)"
"                          ->  Seq Scan on track x  (cost=0.00..7.13 rows=13 width=71471) (actual time=0.016..0.050 rows=13 loops=1)"
"                    ->  Hash  (cost=1.18..1.18 rows=18 width=8) (actual time=0.082..0.082 rows=18 loops=1)"
"                          ->  Seq Scan on messung y  (cost=0.00..1.18 rows=18 width=8) (actual time=0.008..0.040 rows=18 loops=1)"
"  ->  Seq Scan on messung m  (cost=0.00..1.23 rows=1 width=8) (actual time=0.131..0.135 rows=1 loops=1424)"
"        Filter: (6 = track_id)"
"Total runtime: 1428299.402 ms"

Jo, da bin ich, leider auch etwas ratlos. Meiner Erfahrung nach sind Funktionen wie within ziemlich teuer, je nach dem wie komplex die Geometrien sind, ich bin auch oft damit beschäftigt. Ich kann auch nicht abschätzen, welchen Effekt die verschachtelten Funktionen im within-Statement haben. Haben die Geometrien denn einen Index?
Über funktionale Indexe kann ich leider nichts berichte, da ich das noch nicht gehört habe. Aber es hört sich mal gut an und ich werde nachher gleich mal danach suchen…

Grüssle und bis später

Uwe

Also die Geometrien sollten einen Index haben.

Ich habe jeweils an die Tabelle den Index dran gehangen wie im Manual beschrieben und habe dann Analyse ausgeführt.

Ich gehe davon aus das, dass so richtig war. Oder habe ich da was falsch gemacht?

Funktionsindexe gibt es siehe Kapitel 11.5 des Postgres Manuals.

DANKE mal wieder

mfg

Mal ein sinnfreies Beispiel dafür:

test=# create table test1 as select i, md5(i::text) as md5 from generate_Series(1,100000) i;
SELECT
test=# analyse test1;
ANALYZE
test=# explain analyse select * from test1 where substring(md5,5,10) = '12345';
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Seq Scan on test1  (cost=0.00..2334.00 rows=500 width=37) (actual time=97.916..97.916 rows=0 loops=1)
   Filter: ("substring"(md5, 5, 10) = '12345'::text)
 Total runtime: 97.942 ms
(3 rows)

test=# create index idx1_test1 on test1((substring(md5,5,10)));
CREATE INDEX
test=# explain analyse select * from test1 where substring(md5,5,10) = '12345';
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test1  (cost=12.14..774.89 rows=500 width=37) (actual time=0.056..0.056 rows=0 loops=1)
   Recheck Cond: ("substring"(md5, 5, 10) = '12345'::text)
   ->  Bitmap Index Scan on idx1_test1  (cost=0.00..12.02 rows=500 width=0) (actual time=0.053..0.053 rows=0 loops=1)
         Index Cond: ("substring"(md5, 5, 10) = '12345'::text)
 Total runtime: 0.078 ms
(5 rows)

Andreas

Jo, Danke für das Beispiel. Ich hatte es gerade gelesen und es scheint bereits ein Problem zu lösen, bevor es aufgetaucht ist :slight_smile: In der Theorie zumindest…

@Wolf: Das stimmt schon alles, wenn ich mich nicht verkuckt hab. Warum transformst du eigentlich jede Geometrie nach GK3? Sind die aus der Track-Tabelle nicht auch 4326/lat-lon? Wenn das nicht nötig ist, könnte das auch ein bissle was einsparen.

Grüssle Uwe

Hi,

Also, an der Transformation liegt es nicht, die brauch 500 ms für 20 Lienestrings rund 30’000 Points.

Es liegt er an dem Buffer der brauch zu lange. Da aus einem Linestring ein Polygon erstellt wird. Mit Radius 2 oder x.

Ich werde jetzt eine zusätzliche Spalte erstellen und diese dann mit dem fertigen Buffer, vom Linestring in GK Koordinaten, fühlen. Das ganze wird einmal über SQL oder über einen Trigger passieren mal sehen was schneller und besser ist.

mfg

Hi,

So Seit dem die neue Spalte mit den Linestrings als Buffer besteht geht das ganze schneller.


“1424 rows fetched (52,734 sec)” von damals rund 750’000ms


THX @ all