Changes between Version 62 and Version 63 of PostNASMapbender3

Timestamp:

08/11/20 16:43:02 (4 years ago)

Author:

astrid.emde

Comment:

--

PostNASMapbender3

@@ -260 +260 @@
 
 {{{
-
--- DROP VIEW qry_mb3_grundbuch_suche;
-
-CREATE OR REPLACE VIEW qry_mb3_grundbuch_suche AS 
- SELECT foo.buchungsart, foo.bezirkname, foo.bezirk, foo.gb_blatt, foo.blattart, foo.flurstueckskennzeichen, foo.geom, foo.endet
-   FROM ( SELECT s.buchungsart, b.bezeichnung AS bezirkname, b.bezirk,g.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt,
-            g.blattart, f.flurstueckskennzeichen,f.wkb_geometry AS geom, f.endet
-           FROM ax_buchungsblatt g
-             JOIN ax_buchungsblattbezirk b ON g.land::text = b.land::text AND g.bezirk::text = b.bezirk::text
-             JOIN alkis_beziehungen bgs ON bgs.beziehung_zu = g.gml_id
-             JOIN ax_buchungsstelle s ON s.gml_id = bgs.beziehung_von
-             JOIN v_bs_buchungsart art ON s.buchungsart = art.wert
-             JOIN alkis_beziehungen bf ON s.gml_id = bf.beziehung_zu
-             JOIN ax_flurstueck f ON f.gml_id = bf.beziehung_von
-          WHERE g.endet IS NULL AND b.endet IS NULL AND s.endet IS NULL AND f.endet IS NULL
-        UNION
-         SELECT s.buchungsart,  b.bezeichnung AS bezirkname,  b.bezirk, g.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt,
-            g.blattart, f.flurstueckskennzeichen, f.wkb_geometry AS geom, f.endet
-           FROM ax_buchungsblatt g
-             JOIN ax_buchungsblattbezirk b ON g.land::text = b.land::text AND g.bezirk::text = b.bezirk::text
-             JOIN alkis_beziehungen bgs ON bgs.beziehung_zu = g.gml_id
-             JOIN ax_buchungsstelle s ON s.gml_id = bgs.beziehung_von
-             JOIN v_bs_buchungsart art ON s.buchungsart = art.wert
-             JOIN alkis_beziehungen bg ON s.gml_id = bg.beziehung_von
-             JOIN ax_buchungsstelle bs ON bs.gml_id = bg.beziehung_zu
-             JOIN alkis_beziehungen bf ON bs.gml_id = bf.beziehung_zu
-             JOIN ax_flurstueck f ON f.gml_id = bf.beziehung_von
-          WHERE g.endet IS NULL AND s.endet IS NULL AND b.endet IS NULL AND s.endet IS NULL AND f.endet IS NULL) foo;
-
-         
+DROP VIEW IF EXISTS qry_mb3_grundbuch_suche;
+
+CREATE OR REPLACE VIEW public.qry_mb3_grundbuch_suche AS
+ SELECT 
+    foo.buchungsart, 
+    foo.bezirkname, 
+    foo.bezirk, 
+    foo.gb_blatt,
+    foo.blattart, 
+    foo.flurstueckskennzeichen, 
+    foo.geom, 
+    foo.endet
+ FROM 
+ (SELECT 
+   s.buchungsart, 
+   b.bezeichnung AS bezirkname, 
+   b.bezirk,
+   g.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt, 
+   g.blattart,
+   f.flurstueckskennzeichen, 
+   f.wkb_geometry AS geom, 
+   f.endet
+   FROM  ax_buchungsblatt g
+        JOIN ax_buchungsblattbezirk b ON g.land::text = b.land::text AND g.bezirk::text = b.bezirk::text  AND b.endet IS NULL
+        JOIN ax_buchungsstelle s ON s.istbestandteilvon = g.gml_id
+        JOIN v_bs_buchungsart art ON s.buchungsart = art.wert AND s.endet IS NULL
+        JOIN ax_flurstueck f ON f.istgebucht = s.gml_id AND f.endet IS NULL
+  UNION
+   SELECT 
+   s.buchungsart, 
+   b.bezeichnung AS bezirkname, 
+   b.bezirk,
+   g.buchungsblattnummermitbuchstabenerweiterung AS gb_blatt,
+   g.blattart,
+   f.flurstueckskennzeichen, 
+   f.wkb_geometry AS geom, 
+   f.endet
+   FROM ax_buchungsblatt g
+    JOIN ax_buchungsblattbezirk b ON g.land::text = b.land::text AND g.bezirk::text = b.bezirk::text  AND b.endet IS NULL
+    JOIN ax_buchungsstelle s ON s.istbestandteilvon = g.gml_id
+    JOIN v_bs_buchungsart art ON s.buchungsart = art.wert AND s.endet IS NULL
+    JOIN ax_buchungsstelle bs ON (bs.gml_id=ANY(s.an) OR bs.gml_id=ANY(s.zu))
+    JOIN ax_flurstueck f ON f.istgebucht = bs.gml_id AND f.endet IS NULL
+  ) foo; 
 }}}