source: trunk/data/konvert/postnas_0.5/alkis_doppelte_objekte_loeschen.sql @ 163

Revision 81, 11.8 KB checked in by frank.jaeger, 11 years ago (diff)

ALKIS-Auskunft überarbeitet

Line 
1
2-- Bereinigung "doppelte Objekte"
3
4-- z.B. nachdem eine NAS-Datei doppelt konvertiert wurde
5
6-- Stand 03.02.2011
7
8-- Wenn eine NAS-Datei versehentlich ein zweites mal konvertiert wird, dann merkt PostNAS das nicht!
9-- Die "gml_id" wäre ein eindeutiges Kennzeichen, aber als Primary-Key wird die "ogc_fid" künstlich gebildet.
10
11--   ==>  Anpassung Schema?
12
13-- Vorher DB-Sicherung!
14--
15-- sudo pg_dump -h localhost -p 5432 -U postgres -o -C alkis05300 | gzip > /data/bkup/alkis05300.sql.gz
16--
17
18
19-- Hier die wichtigsten Tabellen:
20-- - Die Tabellen aus der Gruppe Nutzungsart muessen eindeutige gml_ids haben fuer die
21--   Zusammenfassung in einer Tabelle fuer die Buchauskunft.
22-- - Flurstueck wird per WMS-Feature-Info abgefragt.
23-- - "alkis_beziehungen" ist die meist-beanspruchte Tabelle
24
25-- Um alle Tabellen zu entruempeln: siehe Shell-Script.
26
27
28--CREATE VIEW redundanzen_ax_gehoelz
29--AS
30--  SELECT
31--       a.gml_id,
32--       a.ogc_fid AS erster,
33--       b.ogc_fid AS weiterer
34--  FROM ax_gehoelz AS a
35--  JOIN ax_gehoelz AS b
36--    ON a.gml_id  = b.gml_id
37--   AND b.ogc_fid > a.ogc_fid;
38
39
40-- 01 REO: ax_Wohnbauflaeche
41-- -------------------------------------
42DELETE
43  FROM ax_wohnbauflaeche AS dublette
44WHERE EXISTS
45   (SELECT solitaer.gml_id
46      FROM ax_wohnbauflaeche AS solitaer
47     WHERE solitaer.gml_id  = dublette.gml_id
48       AND dublette.ogc_fid > solitaer.ogc_fid);
49
50-- 02 REO: ax_IndustrieUndGewerbeflaeche
51-- -------------------------------------
52DELETE
53  FROM ax_IndustrieUndGewerbeflaeche AS dublette
54WHERE EXISTS
55   (SELECT solitaer.gml_id
56      FROM ax_IndustrieUndGewerbeflaeche AS solitaer
57     WHERE solitaer.gml_id  = dublette.gml_id
58       AND dublette.ogc_fid > solitaer.ogc_fid);
59
60-- 03 REO: ax_Halde
61-- -------------------------------------
62DELETE
63  FROM ax_Halde AS dublette
64WHERE EXISTS
65   (SELECT solitaer.gml_id
66      FROM ax_Halde AS solitaer
67     WHERE solitaer.gml_id  = dublette.gml_id
68       AND dublette.ogc_fid > solitaer.ogc_fid);
69
70-- 04 ax_Bergbaubetrieb
71-- -------------------------------------
72DELETE
73  FROM ax_Bergbaubetrieb AS dublette
74WHERE EXISTS
75   (SELECT solitaer.gml_id
76      FROM ax_Bergbaubetrieb AS solitaer
77     WHERE solitaer.gml_id  = dublette.gml_id
78       AND dublette.ogc_fid > solitaer.ogc_fid);
79
80-- 05 REO: ax_TagebauGrubeSteinbruch
81-- -------------------------------------
82DELETE
83  FROM ax_TagebauGrubeSteinbruch AS dublette
84WHERE EXISTS
85   (SELECT solitaer.gml_id
86      FROM ax_TagebauGrubeSteinbruch AS solitaer
87     WHERE solitaer.gml_id  = dublette.gml_id
88       AND dublette.ogc_fid > solitaer.ogc_fid);
89
90-- 06 REO: ax_FlaecheGemischterNutzung
91-- -------------------------------------
92DELETE
93  FROM ax_FlaecheGemischterNutzung AS dublette
94WHERE EXISTS
95   (SELECT solitaer.gml_id
96      FROM ax_FlaecheGemischterNutzung AS solitaer
97     WHERE solitaer.gml_id  = dublette.gml_id
98       AND dublette.ogc_fid > solitaer.ogc_fid);
99
100-- 07 REO: ax_FlaecheBesondererFunktionalerPraegung
101-- -------------------------------------
102DELETE
103  FROM ax_FlaecheBesondererFunktionalerPraegung AS dublette
104WHERE EXISTS
105   (SELECT solitaer.gml_id
106      FROM ax_FlaecheBesondererFunktionalerPraegung AS solitaer
107     WHERE solitaer.gml_id  = dublette.gml_id
108       AND dublette.ogc_fid > solitaer.ogc_fid);
109
110-- 08 REO: ax_SportFreizeitUndErholungsflaeche
111-- -------------------------------------
112DELETE
113  FROM ax_SportFreizeitUndErholungsflaeche AS dublette
114WHERE EXISTS
115   (SELECT solitaer.gml_id
116      FROM ax_SportFreizeitUndErholungsflaeche AS solitaer
117     WHERE solitaer.gml_id  = dublette.gml_id
118       AND dublette.ogc_fid > solitaer.ogc_fid);
119
120-- 09 REO: ax_Friedhof
121-- -------------------------------------
122DELETE
123  FROM ax_Friedhof AS dublette
124WHERE EXISTS
125   (SELECT solitaer.gml_id
126      FROM ax_Friedhof AS solitaer
127     WHERE solitaer.gml_id  = dublette.gml_id
128       AND dublette.ogc_fid > solitaer.ogc_fid);
129
130-- ** Objektartengruppe: Verkehr **
131
132-- 10 ax_Strassenverkehr
133-- -------------------------------------
134DELETE
135  FROM ax_Strassenverkehr AS dublette
136WHERE EXISTS
137   (SELECT solitaer.gml_id
138      FROM ax_Strassenverkehr AS solitaer
139     WHERE solitaer.gml_id  = dublette.gml_id
140       AND dublette.ogc_fid > solitaer.ogc_fid);
141
142-- 11 ax_Weg
143-- -------------------------------------
144DELETE
145  FROM ax_Weg AS dublette
146WHERE EXISTS
147   (SELECT solitaer.gml_id
148      FROM ax_Weg AS solitaer
149     WHERE solitaer.gml_id  = dublette.gml_id
150       AND dublette.ogc_fid > solitaer.ogc_fid);
151
152-- 12 ax_Platz
153-- -------------------------------------
154DELETE
155  FROM ax_Platz AS dublette
156WHERE EXISTS
157   (SELECT solitaer.gml_id
158      FROM ax_Platz AS solitaer
159     WHERE solitaer.gml_id  = dublette.gml_id
160       AND dublette.ogc_fid > solitaer.ogc_fid);
161
162-- 13 ax_Bahnverkehr
163-- -------------------------------------
164DELETE
165  FROM ax_Bahnverkehr AS dublette
166WHERE EXISTS
167   (SELECT solitaer.gml_id
168      FROM ax_Bahnverkehr AS solitaer
169     WHERE solitaer.gml_id  = dublette.gml_id
170       AND dublette.ogc_fid > solitaer.ogc_fid);
171
172-- 14 ax_Flugverkehr
173-- -------------------------------------
174DELETE
175  FROM ax_Flugverkehr AS dublette
176WHERE EXISTS
177   (SELECT solitaer.gml_id
178      FROM ax_Flugverkehr AS solitaer
179     WHERE solitaer.gml_id  = dublette.gml_id
180       AND dublette.ogc_fid > solitaer.ogc_fid);
181
182-- 15 ax_Schiffsverkehr
183-- -------------------------------------
184DELETE
185  FROM ax_Schiffsverkehr AS dublette
186WHERE EXISTS
187   (SELECT solitaer.gml_id
188      FROM ax_Schiffsverkehr AS solitaer
189     WHERE solitaer.gml_id  = dublette.gml_id
190       AND dublette.ogc_fid > solitaer.ogc_fid);
191
192-- ** Objektartengruppe: Vegetation **
193
194-- 16 ax_Landwirtschaft
195-- -------------------------------------
196DELETE
197  FROM ax_Landwirtschaft AS dublette
198WHERE EXISTS
199   (SELECT solitaer.gml_id
200      FROM ax_Landwirtschaft AS solitaer
201     WHERE solitaer.gml_id  = dublette.gml_id
202       AND dublette.ogc_fid > solitaer.ogc_fid);
203
204-- 17 ax_Wald
205-- -------------------------------------
206DELETE
207  FROM ax_Wald AS dublette
208WHERE EXISTS
209   (SELECT solitaer.gml_id
210      FROM ax_Wald AS solitaer
211     WHERE solitaer.gml_id  = dublette.gml_id
212       AND dublette.ogc_fid > solitaer.ogc_fid);
213
214-- 18 ax_Gehoelz
215-- -------------------------------------
216DELETE
217  FROM ax_gehoelz AS dublette
218WHERE EXISTS
219   (SELECT solitaer.gml_id
220      FROM ax_gehoelz AS solitaer
221     WHERE solitaer.gml_id  = dublette.gml_id
222       AND dublette.ogc_fid > solitaer.ogc_fid);
223
224-- 19 ax_Heide
225-- -------------------------------------
226DELETE
227  FROM ax_Heide AS dublette
228WHERE EXISTS
229   (SELECT solitaer.gml_id
230      FROM ax_Heide AS solitaer
231     WHERE solitaer.gml_id  = dublette.gml_id
232       AND dublette.ogc_fid > solitaer.ogc_fid);
233
234-- 20 ax_Moor
235-- -------------------------------------
236DELETE
237  FROM ax_Moor AS dublette
238WHERE EXISTS
239   (SELECT solitaer.gml_id
240      FROM ax_Moor AS solitaer
241     WHERE solitaer.gml_id  = dublette.gml_id
242       AND dublette.ogc_fid > solitaer.ogc_fid);
243
244-- 21 ax_Sumpf
245-- -------------------------------------
246DELETE
247  FROM ax_Sumpf AS dublette
248WHERE EXISTS
249   (SELECT solitaer.gml_id
250      FROM ax_Sumpf AS solitaer
251     WHERE solitaer.gml_id  = dublette.gml_id
252       AND dublette.ogc_fid > solitaer.ogc_fid);
253
254-- 22 ax_UnlandVegetationsloseFlaeche
255-- -------------------------------------
256DELETE
257  FROM ax_UnlandVegetationsloseFlaeche AS dublette
258WHERE EXISTS
259   (SELECT solitaer.gml_id
260      FROM ax_UnlandVegetationsloseFlaeche AS solitaer
261     WHERE solitaer.gml_id  = dublette.gml_id
262       AND dublette.ogc_fid > solitaer.ogc_fid);
263
264-- ** Objektartengruppe: Gewässer **
265
266-- 24 ax_Fliessgewaesser
267-- -------------------------------------
268DELETE
269  FROM ax_Fliessgewaesser AS dublette
270WHERE EXISTS
271   (SELECT solitaer.gml_id
272      FROM ax_Fliessgewaesser AS solitaer
273     WHERE solitaer.gml_id  = dublette.gml_id
274       AND dublette.ogc_fid > solitaer.ogc_fid);
275
276-- 25 ax_Hafenbecken
277-- -------------------------------------
278DELETE
279  FROM ax_Hafenbecken AS dublette
280WHERE EXISTS
281   (SELECT solitaer.gml_id
282      FROM ax_Hafenbecken AS solitaer
283     WHERE solitaer.gml_id  = dublette.gml_id
284       AND dublette.ogc_fid > solitaer.ogc_fid);
285
286-- 26 ax_StehendesGewaesser
287-- -------------------------------------
288DELETE
289  FROM ax_StehendesGewaesser AS dublette
290WHERE EXISTS
291   (SELECT solitaer.gml_id
292      FROM ax_StehendesGewaesser AS solitaer
293     WHERE solitaer.gml_id  = dublette.gml_id
294       AND dublette.ogc_fid > solitaer.ogc_fid);
295
296-- 27 ax_Meer
297-- -------------------------------------
298DELETE
299  FROM ax_Meer AS dublette
300WHERE EXISTS
301   (SELECT solitaer.gml_id
302      FROM ax_Meer AS solitaer
303     WHERE solitaer.gml_id  = dublette.gml_id
304       AND dublette.ogc_fid > solitaer.ogc_fid);
305
306
307-- ---------------------------------------------------------------
308
309-- Flurstück
310
311DELETE
312  FROM ax_flurstueck AS dublette
313WHERE EXISTS
314   (SELECT solitaer.gml_id
315      FROM ax_flurstueck AS solitaer
316     WHERE solitaer.gml_id  = dublette.gml_id
317       AND dublette.ogc_fid > solitaer.ogc_fid);
318
319
320DELETE
321  FROM ax_besondereflurstuecksgrenze AS dublette
322WHERE EXISTS
323   (SELECT solitaer.gml_id
324      FROM ax_besondereflurstuecksgrenze AS solitaer
325     WHERE solitaer.gml_id  = dublette.gml_id
326       AND dublette.ogc_fid > solitaer.ogc_fid);
327
328
329-- Grundbuch
330
331DELETE
332  FROM ax_buchungsblatt AS dublette
333WHERE EXISTS
334   (SELECT solitaer.gml_id
335      FROM ax_buchungsblatt AS solitaer
336     WHERE solitaer.gml_id  = dublette.gml_id
337       AND dublette.ogc_fid > solitaer.ogc_fid);
338
339DELETE
340  FROM ax_buchungsstelle AS dublette
341WHERE EXISTS
342   (SELECT solitaer.gml_id
343      FROM ax_buchungsstelle AS solitaer
344     WHERE solitaer.gml_id  = dublette.gml_id
345       AND dublette.ogc_fid > solitaer.ogc_fid);
346
347-- Punkte
348
349DELETE
350  FROM ax_grenzpunkt AS dublette
351WHERE EXISTS
352   (SELECT solitaer.gml_id
353      FROM ax_grenzpunkt AS solitaer
354     WHERE solitaer.gml_id  = dublette.gml_id
355       AND dublette.ogc_fid > solitaer.ogc_fid);
356
357DELETE
358  FROM ax_aufnahmepunkt AS dublette
359WHERE EXISTS
360   (SELECT solitaer.gml_id
361      FROM ax_aufnahmepunkt AS solitaer
362     WHERE solitaer.gml_id  = dublette.gml_id
363       AND dublette.ogc_fid > solitaer.ogc_fid);
364
365DELETE
366  FROM ax_sonstigervermessungspunkt AS dublette
367WHERE EXISTS
368   (SELECT solitaer.gml_id
369      FROM ax_sonstigervermessungspunkt AS solitaer
370     WHERE solitaer.gml_id  = dublette.gml_id
371       AND dublette.ogc_fid > solitaer.ogc_fid);
372
373DELETE
374  FROM ax_besonderergebaeudepunkt AS dublette
375WHERE EXISTS
376   (SELECT solitaer.gml_id
377      FROM ax_besonderergebaeudepunkt AS solitaer
378     WHERE solitaer.gml_id  = dublette.gml_id
379       AND dublette.ogc_fid > solitaer.ogc_fid);
380
381
382-- Verbindungen / Relationen     -  S o n d e r f a l l
383-- -------------------------
384
385--CREATE VIEW redundanzen_alkis_beziehungen
386--AS
387--  SELECT solitaer.ogc_fid  AS erster,
388--         dublette.ogc_fid  AS zweiter
389--    FROM alkis_beziehungen AS solitaer
390--    JOIN alkis_beziehungen AS dublette
391--      ON solitaer.beziehung_von = dublette.beziehung_von
392--     AND solitaer.beziehungsart = dublette.beziehungsart
393--     AND solitaer.beziehung_zu  = dublette.beziehung_zu 
394--     AND solitaer.ogc_fid       < dublette.ogc_fid;
395
396--  100573 Zeilen
397
398DELETE
399  FROM alkis_beziehungen AS dublette
400WHERE EXISTS
401   (SELECT solitaer.ogc_fid
402      FROM alkis_beziehungen AS solitaer
403     WHERE solitaer.beziehung_von = dublette.beziehung_von
404       AND solitaer.beziehungsart = dublette.beziehungsart
405       AND solitaer.beziehung_zu  = dublette.beziehung_zu 
406       AND solitaer.ogc_fid       < dublette.ogc_fid);
407
408-- Achtung: sehr lange Ausführungszeit
409
410
411-- THE (happy) END
Note: See TracBrowser for help on using the repository browser.