[FOSSGIS-Talk] Räumlicher Join von Grundstücken und Gebäuden in PostGIS

R. Reiterer r.reiterer at posteo.at
Mo Mai 23 21:30:08 CEST 2016 

Hallo Felix,

das war wohl der entscheidende Hinweis. Nachfolgende Abfrage 
funktioniert sehr flott.

CREATE MATERIALIZED VIEW public.gebaeude_mit_nummer AS
SELECT
unterabfrage.gid, unterabfrage.geom, unterabfrage.nummer
FROM
	(SELECT
		row_number() over() AS gid,
		gebaeude.geom,
		grundstueck.geom AS grundstueck_geom,
		grundstueck.nummer
	FROM public.gebaeude, public.grundstueck
	WHERE ST_Intersects(gebaeude.geom, grundstueck.geom))
	AS unterabfrage
WHERE ST_Within(ST_POintOnSurface(unterabfrage.geom), 
unterabfrage.grundstueck_geom);

Einen räumlichen Index erstelle ich übrigens immer, wenn ich Daten in 
PostGIS importiere. Den EXPLAIN Befehl kenne ich aktuell aber noch 
nicht.

Vielen Dank für die Hilfe und schöne Grüße, Reinhard

Am 23.05.2016 10:30 schrieb Felix Kunde:
> Hi,
>  
> in dem gegebenen Beispiel überlappt das Gebäude beide Grundstücke,
> wenn auch nur minimal (0.00000000122).
> Deshalb funktioniert ein einfaches ST_Contains oder ST_Touches auch
> nicht. St_Overlaps gibt beide Grundstücke zurück.
> Theoretisch könnte ST_PointOnSurface auch einen Punkt zurückliefern,
> der im Überlappungsbereich liegt, oder?
> ST_ApproximateMedialAxis gibt es erst ab PostGIS 2.2.
> 
> Die Performanceprobleme kommen, denke ich, durch den Join zustande. Du
> hast bestimmt einen räumlichen Index auf der Gebäudetabelle.
> Mit einer Funktion wie ST_PointOnSurface dürfte dieser aber
> ausgehebelt werden, und im Join muss dann jeder Joinpartner
> sequentiell gesucht werden.
> Ist ne Vermutung. Kannst du mit dem EXPLAIN-Befehl überprüfen.
> 
> Du könntest erst in einer Unterabfrage den Join durchführen (z.B. mit
> ST_Overlaps), und danach die Ergebnismenge mit ST_PointOnSurface
> eingrenzen.
> Viel Erfolg.
> 
> Felix
> 
> 
> Gesendet: Sonntag, 22. Mai 2016 um 21:50 Uhr
> Von: "R. Reiterer" <r.reiterer at posteo.at>
> An: "Fossgis talk liste" <fossgis-talk-liste at fossgis.de>
> Betreff: Re: [FOSSGIS-Talk] Räumlicher Join von Grundstücken und
> Gebäuden in PostGIS
> Hallo Felix,
> 
> die ergänzten Gebäude wurden in QGIS mit aktiviertem Objektfang 
> gesetzt.
> ST_ApproximateMedialAxis scheint die DB-Verwaltung nicht zu kennen (?),
> auch die Autovervollständigung springt nicht an. Ich habe bereits 
> „WHERE
> ST_Contains(grundstuecke.geom, ST_PointOnSurface(gebaeude.geom))“
> getestet, aber QGIS stürzt dabei wie gesagt immer ab. Seltsamerweise
> benötigt die nachfolgende Abfrage für dieselben Daten (1333 Gebäude) 
> nur
> 0,5 Sekunden.
> 
> CREATE MATERIALIZED VIEW public.gebaeude_pointonsurface AS SELECT
> gid,
> ST_PointOnSurface(geom)::geometry(Point, 31256) AS geom
> FROM public.gebaeude;
> 
> Schöne Grüße, Reinhard
> 
> Am 22.05.2016 08:22 schrieb R. Reiterer:
>> -- Beispieldaten 'Räumlicher Join von Grundstücken und Gebäuden in
>> PostGIS':
>> 
>> CREATE TABLE public.grundstuecke
>> (gid serial NOT NULL,
>> geom geometry(Polygon, 31256),
>> nummer varchar(12),
>> CONSTRAINT grundstuecke_pkey PRIMARY KEY (gid)
>> );
>> 
>> CREATE TABLE public.gebaeude
>> (gid serial NOT NULL,
>> geom geometry(Polygon, 31256),
>> CONSTRAINT gebaeude_pkey PRIMARY KEY (gid)
>> );
>> 
>> INSERT INTO public.grundstuecke(gid, geom, nummer) VALUES
>> (1, ST_GeomFromText('Polygon ((-6508.65473045294038457
>> 274373.8524561341619119, -6594.73447251523521118
>> 274381.97558137698797509, -6608.56900586268056941
>> 274528.33038152626249939, -6581.62807250186688179
>> 274653.56931498734047636, -6493.38998014411117765
>> 274634.58786488714395091, -6532.84313911877234204
>> 274537.79611486929934472, -6508.65473045294038457
>> 274373.8524561341619119))', 31256), '1020/1'),
>> (2, ST_GeomFromText('Polygon ((-6439.42522136095067253
>> 274367.31944430840667337, -6508.65473045294038457
>> 274373.8524561341619119, -6532.84313911877234204
>> 274537.79611486929934472, -6493.38998014411117765
>> 274634.58786488714395091, -6420.07921508541949152
>> 274618.81752589193638414, -6449.19572207274086395
>> 274515.12352700304472819, -6439.42522136095067253
>> 274367.31944430840667337))', 31256), '1020/2');
>> 
>> INSERT INTO public.gebaeude(gid, geom) VALUES
>> (1, ST_GeomFromText('Polygon ((-6503.68534673 274609.32989886,
>> -6532.84313912 274537.79611487, -6519.83843264 274449.65310426,
>> -6565.04383857 274442.9834542, -6576.53113916 274534.1554482,
>> -6551.04647247 274612.79384828, -6503.68534673 274609.32989886))',
>> 31256));
>> 
>> CREATE OR REPLACE VIEW public.gebaeude_mit_nummer AS
>> SELECT
>> gebaeude.*,
>> grundstuecke.nummer
>> FROM public.gebaeude, public.grundstuecke
>> WHERE ST_Within(gebaeude.geom, grundstuecke.geom);
>> 
>> /*
>> Am 21.05.2016 19:22 schrieb R. Reiterer:
>>> Ahoi Felix und danke für deine Mail. Sowohl ST_Contains und ST_Covers
>>> funktionieren leider nur, wenn die Knotenstruktur der Polygone
>>> (Grundstücke/Gebäude) an den Kanten übereinstimmt. Viele Gebäude
>>> wurden jedoch händisch über das Orthofoto ergänzt. Die Knoten liegen
>>> also meist irgendwo an der Grundstücksgrenze.
>>> 
>>> Schöne Grüße,
>>> 
>>> Reinhard
>>> 
>>> Am 21.05.2016 15:35 schrieb Felix Kunde:
>>>> Ahoi Reinhard,
>>>> 
>>>> Müsste es nicht gut mit ST_Contains(grundstuecke.geom, 
>>>> gebaeude.geom)
>>>> funktionieren? Contains erlaubt auch Randbeührungen. Sollten evtl.
>>>> mal
>>>> alle Gebäudepunkte auf der Grundstückskante liegen wäre ST_Covers zu
>>>> wählen. Wenn keine Randberührungen erlaubt sind nehme
>>>> ST_ContainsProperly.
>>>> 
>>>> ST_DWithin ist eher für Umkreissuchen. Sobald eine Geometrie den
>>>> Suchradius berührt, wird sie von ST_DWithin erfasst. Soll das erst
>>>> geschehen, wenn die Geometrie vollständig im Suchradius liegt, nimmt
>>>> man ST_DFullyWithin.
>>>> 
>>>> Viele Grüße
>>>> Felix 
>>>>  
>>>> 
>>>> Gesendet: Samstag, 21. Mai 2016 um 12:08 Uhr
>>>> Von: "R. Reiterer" <r.reiterer at posteo.at>
>>>> An: "Fossgis talk liste" <fossgis-talk-liste at fossgis.de>
>>>> Betreff: [FOSSGIS-Talk] Räumlicher Join von Grundstücken und 
>>>> Gebäuden
>>>> in PostGIS
>>>> Hallo an alle,
>>>> 
>>>> ich habe ein vermutlich einfaches Problem, leider klappt es aber
>>>> nicht
>>>> so recht mit der Lösung :/ Konkret benötige ich einen räumlichen 
>>>> Join
>>>> von Grundstücken und den darauf befindlichen Gebäuden. Bei den
>>>> Geometrien handelt es sich jeweils um Polygone (MGI / Austria GK
>>>> East).
>>>> „WHERE ST_Intersects(gebaeude.geom, grundstuecke.geom) AND NOT
>>>> ST_Touches(gebaeude.geom, grundstuecke.geom)“ funktioniert 
>>>> allerdings
>>>> nicht.
>>>> 
>>>> Das Problem liegt wohl an der möglichen Knotenstruktur von Gebäuden
>>>> an
>>>> Grundstücksgrenzen. Die Abfrage liefert falsche Ergebnisse, sobald
>>>> sich
>>>> ein Gebäudeknoten an einer Grundstückskante anstelle eines
>>>> Grundstücksknoten befindet.
>>>> 
>>>> Ich habe auch ST_DWithin(gebaeude.geom, grundstuecke.geom, 1) und
>>>> ST_DFullyWithin(gebaeude.geom, grundstuecke.geom, 1) getestet,
>>>> erhalte
>>>> aber in beiden Fällen fehlerhafte Ergebnisse. Auch ist mir der
>>>> Unterschied zwischen ST_DWihtin und ST_DFullyWithin nicht bewusst,
>>>> „Within“ steht m.W. ohnehin für vollständig enthalten.
>>>> 
>>>> Richtig gefährlich wird übrigens „WHERE
>>>> ST_Within(ST_PointOnSurface(gebaeude.geom), grundstuecke.geom)“,
>>>> sowohl
>>>> QGIS (DB-Verwaltung) als auch pgAdmin gehen dabei in die Knie und
>>>> stürzen ab.
>>>> 
>>>> Weiß jemand guten Rat?
>>>> 
>>>> Vielen Dank, Reinhard
>>>> --
>>>> ....................................................................
>>>> FOSSGIS 2016, Die Konferenz für Open Source GIS mit OpenData und
>>>> OpenStreetMap in Zusammenarbeit mit der AGIT 2016!
>>>> 4.-6. Juli 2016 in Salzburg (3. Juli OpenStreetMap Workshoptag)
>>>> http://www.fossgis.de/konferenz/2016/
>>>> 
>>>> AGIT 2016 vom 6.-8. Juli 2016
>>>> http://agit.at/[http://agit.at/][http://agit.at/[http://agit.at/]]
>>>> 
>>>> FOSS4G 2016 Bonn - annual global event of the Open Source Geospatial
>>>> Foundation (OSGeo) - 24.-26. August 2016 in Bonn (zusätzlich noch
>>>> FOSS4G Hacking Event und Workshops)
>>>> http://2016.foss4g.org[http://2016.foss4g.org][http://2016.foss4g.org[http://2016.foss4g.org]]
>>>> 
>>>> 
>>>> FOSSGIS e.V, der Verein zur Förderung von Freier Software aus dem
>>>> GIS-Bereich und Freier Geodaten!
>>>> http://www.fossgis.de/[http://www.fossgis.de/][http://www.fossgis.de/[http://www.fossgis.de/]]
>>>> https://twitter.com/fossgis_eV[https://twitter.com/fossgis_eV]
>>>> 
>>>> ____________________________________________________________________
>>>> FOSSGIS-Talk-Liste mailing list
>>>> FOSSGIS-Talk-Liste at fossgis.de
>>>> https://lists.fossgis.de/mailman/listinfo/fossgis-talk-liste[https://lists.fossgis.de/mailman/listinfo/fossgis-talk-liste][https://lists.fossgis.de/mailman/listinfo/fossgis-talk-liste[https://lists.fossgis.de/mailman/listinfo/fossgis-talk-liste]]
>>> --
>>> ....................................................................
>>> FOSSGIS 2016, Die Konferenz für Open Source GIS mit OpenData und
>>> OpenStreetMap in Zusammenarbeit mit der AGIT 2016!
>>> 4.-6. Juli 2016 in Salzburg (3. Juli OpenStreetMap Workshoptag)
>>> http://www.fossgis.de/konferenz/2016/[http://www.fossgis.de/konferenz/2016/]
>>> 
>>> AGIT 2016 vom 6.-8. Juli 2016
>>> http://agit.at/[http://agit.at/]
>>> 
>>> FOSS4G 2016 Bonn - annual global event of the Open Source Geospatial
>>> Foundation (OSGeo) - 24.-26. August 2016 in Bonn (zusätzlich noch
>>> FOSS4G Hacking Event und Workshops)
>>> http://2016.foss4g.org[http://2016.foss4g.org]
>>> 
>>> 
>>> FOSSGIS e.V, der Verein zur Förderung von Freier Software aus dem
>>> GIS-Bereich und Freier Geodaten!
>>> http://www.fossgis.de/[http://www.fossgis.de/] 
>>> https://twitter.com/fossgis_eV
>>> 
>>> ____________________________________________________________________
>>> FOSSGIS-Talk-Liste mailing list
>>> FOSSGIS-Talk-Liste at fossgis.de
>>> https://lists.fossgis.de/mailman/listinfo/fossgis-talk-liste[https://lists.fossgis.de/mailman/listinfo/fossgis-talk-liste]
>> */
>> --
>> ....................................................................
>> FOSSGIS 2016, Die Konferenz für Open Source GIS mit OpenData und
>> OpenStreetMap in Zusammenarbeit mit der AGIT 2016!
>> 4.-6. Juli 2016 in Salzburg (3. Juli OpenStreetMap Workshoptag)
>> http://www.fossgis.de/konferenz/2016/[http://www.fossgis.de/konferenz/2016/]
>> 
>> AGIT 2016 vom 6.-8. Juli 2016
>> http://agit.at/[http://agit.at/]
>> 
>> FOSS4G 2016 Bonn - annual global event of the Open Source Geospatial
>> Foundation (OSGeo) - 24.-26. August 2016 in Bonn (zusätzlich noch
>> FOSS4G Hacking Event und Workshops)
>> http://2016.foss4g.org[http://2016.foss4g.org]
>> 
>> 
>> FOSSGIS e.V, der Verein zur Förderung von Freier Software aus dem
>> GIS-Bereich und Freier Geodaten!
>> http://www.fossgis.de/[http://www.fossgis.de/] 
>> https://twitter.com/fossgis_eV
>> 
>> ____________________________________________________________________
>> FOSSGIS-Talk-Liste mailing list
>> FOSSGIS-Talk-Liste at fossgis.de
>> https://lists.fossgis.de/mailman/listinfo/fossgis-talk-liste[https://lists.fossgis.de/mailman/listinfo/fossgis-talk-liste]
> --
> ....................................................................
> FOSSGIS 2016, Die Konferenz für Open Source GIS mit OpenData und
> OpenStreetMap in Zusammenarbeit mit der AGIT 2016!
> 4.-6. Juli 2016 in Salzburg (3. Juli OpenStreetMap Workshoptag)
> http://www.fossgis.de/konferenz/2016/[http://www.fossgis.de/konferenz/2016/]
> 
> AGIT 2016 vom 6.-8. Juli 2016
> http://agit.at/[http://agit.at/]
> 
> FOSS4G 2016 Bonn - annual global event of the Open Source Geospatial
> Foundation (OSGeo) - 24.-26. August 2016 in Bonn (zusätzlich noch
> FOSS4G Hacking Event und Workshops)
> http://2016.foss4g.org[http://2016.foss4g.org]
> 
> 
> FOSSGIS e.V, der Verein zur Förderung von Freier Software aus dem
> GIS-Bereich und Freier Geodaten!
> http://www.fossgis.de/[http://www.fossgis.de/] 
> https://twitter.com/fossgis_eV
> 
> ____________________________________________________________________
> FOSSGIS-Talk-Liste mailing list
> FOSSGIS-Talk-Liste at fossgis.de
> https://lists.fossgis.de/mailman/listinfo/fossgis-talk-liste[https://lists.fossgis.de/mailman/listinfo/fossgis-talk-liste]