source: trunk/data/konvert/postnas_0.6/gemeinde_person.sql @ 163

Revision 140, 7.4 KB checked in by frank.jaeger, 10 years ago (diff)

Filter auf Gemeinde bei Navigation bei Suche nach Eigentümer. Dazu Hilfstabelle aufbauen.

Line 
1
2-- Neue Tabelle "gemeinde_person" definieren UND laden.
3--
4-- Zusammenfassung der neuen Teile aus 2 Dateien zum Nachtragen in vorhandene Datenbanken
5--
6-- Laufzeit
7--  für eine Stadt       ca.  1 Minute
8--  für ein  Kreisgebiet ca. 10 Minuten
9
10
11-- ** aus gemeinden_definition.sql
12
13-- =======================================================
14-- Tabelle fuer die Zuordnung vom Eigentümern zu Gemeinden
15-- =======================================================
16
17-- Die Feststellung, ob eine Person (Mit-) Eigentümer von mindestens einem
18-- Flurstück in einer Gemeinde ist, geht über viele Relationen.
19-- Dabei kann es mehrere Varianten geben.
20-- Dies sollte nach dem Laden ermittelt und gespeichert werden, damit dies in
21-- der Navigation auf einfache Art verwendet werden kann.
22
23
24-- DROP TABLE gemeinde_person;
25
26CREATE TABLE gemeinde_person (
27  land                  integer,
28  regierungsbezirk      integer,
29  kreis                 integer,
30  gemeinde              integer,
31  person                character varying(16),
32  buchtyp               integer,
33  CONSTRAINT gemeinde_person_pk PRIMARY KEY (gemeinde, person)
34);
35
36
37COMMENT ON TABLE  gemeinde_person                IS 'Person ist Eigentümer von mindestens einem Flurstück in der Gemeinde';
38COMMENT ON COLUMN gemeinde_person.gemeinde       IS 'Gemeindenummer';
39COMMENT ON COLUMN gemeinde_person.buchtyp        IS 'Typ der Buchung 1=direkt, 2=Recht einer Buchungsstele an andere Buchungsstelle';
40COMMENT ON COLUMN gemeinde_person.person         IS 'gml_id von Person';
41
42-- Index zum Filtern in der Buchauskunft
43CREATE INDEX person_gemeinde  ON gemeinde_person (person, gemeinde);
44
45
46-- =======================================================
47-- VIEWs  fuer die Zuordnung vom Eigentümern zu Gemeinden
48-- =======================================================
49
50-- "Normale" Buchungen
51
52CREATE VIEW gemeinde_person_typ1
53AS
54  SELECT DISTINCT
55    p.gml_id          AS person,
56    g.land, g.regierungsbezirk, g.kreis, g.gemeinde
57
58  FROM ax_person               p
59
60-- Person < benennt < Namensnummer
61  JOIN alkis_beziehungen      bpn  ON bpn.beziehung_zu   = p.gml_id  -- Bez. Person - Nummer
62  JOIN ax_namensnummer         n   ON bpn.beziehung_von  = n.gml_id
63
64-- Namensnummer > istBestandteilVon > Blatt
65  JOIN alkis_beziehungen      bnb  ON bnb.beziehung_von  = n.gml_id  -- Bez. Nummer - Blatt
66  JOIN ax_buchungsblatt        b   ON bnb.beziehung_zu   = b.gml_id
67
68-- Blatt < istBestandteilVon < buchungsStelle
69  JOIN alkis_beziehungen      bbg  ON bbg.beziehung_zu   = b.gml_id  -- Bez. Blatt  - Stelle
70  JOIN ax_buchungsstelle       s   ON bbg.beziehung_von  = s.gml_id
71
72-- buchungsStelle < istGebucht < flurstück
73  JOIN alkis_beziehungen      bsf  ON bsf.beziehung_zu   = s.gml_id  -- Bez. Stelle - Flurstück
74  JOIN ax_flurstueck           f   ON bsf.beziehung_von  = f.gml_id
75
76  JOIN ax_gemarkung            k   ON f.land             = k.land
77                                  AND f.gemarkungsnummer = k.gemarkungsnummer
78  JOIN gemeinde_gemarkung      g   ON k.gemarkungsnummer = g.gemarkung
79
80  WHERE bpn.beziehungsart = 'benennt'
81    AND bnb.beziehungsart = 'istBestandteilVon'
82    AND bbg.beziehungsart = 'istBestandteilVon'
83    AND bsf.beziehungsart = 'istGebucht'
84;
85
86COMMENT ON VIEW gemeinde_person_typ1 IS 'Personen die Eigentümer vom Flurstücken in einer Gemeinde sind. Typ1 = nomale Buchungen mit direkter Beziehung.';
87
88
89-- "Komplexe" Buchungen mit Rechten von Buchungen an Buchungen
90
91CREATE VIEW gemeinde_person_typ2
92AS
93  SELECT DISTINCT
94    p.gml_id          AS person,
95  --bpn.beziehungsart AS bpnbez,
96  --bnb.beziehungsart AS bnbbez,
97  --bbg.beziehungsart AS bbgbez,
98  --bsf.beziehungsart AS bsfbez,
99  --k.gemarkungsnummer,
100    g.land, g.regierungsbezirk, g.kreis, g.gemeinde
101
102  FROM ax_person               p
103
104-- Person < benennt < Namensnummer
105  JOIN alkis_beziehungen      bpn  ON bpn.beziehung_zu   = p.gml_id  -- Bez. Person - Nummer
106  JOIN ax_namensnummer         n   ON bpn.beziehung_von  = n.gml_id
107
108-- Namensnummer > istBestandteilVon > Blatt
109  JOIN alkis_beziehungen      bnb  ON bnb.beziehung_von  = n.gml_id  -- Bez. Nummer - Blatt
110  JOIN ax_buchungsblatt        b   ON bnb.beziehung_zu   = b.gml_id
111
112-- Blatt < istBestandteilVon < buchungsStelle1
113  JOIN alkis_beziehungen      bbg  ON bbg.beziehung_zu   = b.gml_id  -- Bez. Blatt  - Stelle
114  JOIN ax_buchungsstelle       s1  ON bbg.beziehung_von  = s1.gml_id
115
116-- buchungsStelle2 < an < buchungsStelle1
117  JOIN alkis_beziehungen      bss  ON bss.beziehung_von  = s1.gml_id  -- Bez. Stelle  - Stelle
118  JOIN ax_buchungsstelle       s2  ON bss.beziehung_zu   = s2.gml_id
119
120-- buchungsStelle2 < istGebucht < flurstück
121  JOIN alkis_beziehungen      bsf  ON bsf.beziehung_zu   = s2.gml_id  -- Bez. Stelle - Flurstück
122  JOIN ax_flurstueck           f   ON bsf.beziehung_von  = f.gml_id
123
124  JOIN ax_gemarkung            k   ON f.land             = k.land
125                                  AND f.gemarkungsnummer = k.gemarkungsnummer
126  JOIN gemeinde_gemarkung      g   ON k.gemarkungsnummer = g.gemarkung
127
128  WHERE bpn.beziehungsart = 'benennt'
129    AND bnb.beziehungsart = 'istBestandteilVon'
130    AND bbg.beziehungsart = 'istBestandteilVon'
131    AND bss.beziehungsart = 'an'
132    AND bsf.beziehungsart = 'istGebucht'
133 -- LIMIT 100  -- Test-Option
134;
135
136COMMENT ON VIEW gemeinde_person_typ2 IS 'Personen die Eigentümer vom Flurstücken in einer Gemeinde sind. Typ2 = Buchungen mit Rechten einer Buchungssstelle an einer anderen.';
137
138
139-- Statistik über die Buchungs-Typen je Gemeinde
140CREATE VIEW gemeinde_person_statistik
141AS
142  SELECT p.land, p.regierungsbezirk, p.kreis, p.gemeinde, g.gemeindename, p.buchtyp, count(p.person) as personen
143  FROM   gemeinde_person    p
144  JOIN   gemeinde_gemarkung g
145    ON   p.land     = g.land
146    AND  p.regierungsbezirk = g.regierungsbezirk
147    AND  p.kreis    = g.kreis
148    AND  p.gemeinde = g.gemeinde
149  GROUP BY p.land, p.regierungsbezirk, p.kreis, p.gemeinde, g.gemeindename, p.buchtyp
150  ORDER BY p.land, p.regierungsbezirk, p.kreis, p.gemeinde, p.buchtyp
151;
152
153COMMENT ON VIEW gemeinde_person_statistik IS 'Zählen der Personen je Gemeinde und Buchungstyp';
154
155
156
157-- ** aus gemeinden_laden.sql
158
159
160-- =======================================================
161-- Tabelle fuer die Zuordnung vom Eigentümern zu Gemeinden
162-- =======================================================
163
164
165-- erst mal sauber machen
166DELETE FROM gemeinde_person;
167
168-- alle direkten Buchungen mit View ermitteln und in Tabelle speichern
169-- Für eine Stadt: ca. 20 Sekunden
170INSERT INTO  gemeinde_person
171       (land, regierungsbezirk, kreis, gemeinde, person, buchtyp)
172 SELECT land, regierungsbezirk, kreis, gemeinde, person, 1
173   FROM gemeinde_person_typ1;
174
175
176-- noch die komplexeren Buchungen ergänzen (Recht an ..)
177-- Mit View ermitteln und in Tabelle speichern
178-- Für eine Stadt: ca. 10 Sekunden
179INSERT INTO  gemeinde_person
180       (  land,   regierungsbezirk,   kreis,   gemeinde,   person,  buchtyp)
181 SELECT q.land, q.regierungsbezirk, q.kreis, q.gemeinde, q.person,  2
182   FROM gemeinde_person_typ2 q   -- Quelle
183   LEFT JOIN gemeinde_person z   -- Ziel
184     ON q.person   = z.person    -- Aber nur, wenn dieser Fall im Ziel
185    AND q.land     = z.land
186    AND q.regierungsbezirk = z.regierungsbezirk
187    AND q.kreis    = z.kreis
188    AND q.gemeinde = z.gemeinde
189  WHERE z.gemeinde is Null;      -- ..  noch nicht vorhanden ist
190
191
192-- Berechtigungen
193
194-- GRANT SELECT ON TABLE ...
195
196
197-- ENDE --
Note: See TracBrowser for help on using the repository browser.