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 | -- -------------------------------------
|
---|
42 | DELETE
|
---|
43 | FROM ax_wohnbauflaeche AS dublette
|
---|
44 | WHERE 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 | -- -------------------------------------
|
---|
52 | DELETE
|
---|
53 | FROM ax_IndustrieUndGewerbeflaeche AS dublette
|
---|
54 | WHERE 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 | -- -------------------------------------
|
---|
62 | DELETE
|
---|
63 | FROM ax_Halde AS dublette
|
---|
64 | WHERE 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 | -- -------------------------------------
|
---|
72 | DELETE
|
---|
73 | FROM ax_Bergbaubetrieb AS dublette
|
---|
74 | WHERE 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 | -- -------------------------------------
|
---|
82 | DELETE
|
---|
83 | FROM ax_TagebauGrubeSteinbruch AS dublette
|
---|
84 | WHERE 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 | -- -------------------------------------
|
---|
92 | DELETE
|
---|
93 | FROM ax_FlaecheGemischterNutzung AS dublette
|
---|
94 | WHERE 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 | -- -------------------------------------
|
---|
102 | DELETE
|
---|
103 | FROM ax_FlaecheBesondererFunktionalerPraegung AS dublette
|
---|
104 | WHERE 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 | -- -------------------------------------
|
---|
112 | DELETE
|
---|
113 | FROM ax_SportFreizeitUndErholungsflaeche AS dublette
|
---|
114 | WHERE 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 | -- -------------------------------------
|
---|
122 | DELETE
|
---|
123 | FROM ax_Friedhof AS dublette
|
---|
124 | WHERE 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 | -- -------------------------------------
|
---|
134 | DELETE
|
---|
135 | FROM ax_Strassenverkehr AS dublette
|
---|
136 | WHERE 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 | -- -------------------------------------
|
---|
144 | DELETE
|
---|
145 | FROM ax_Weg AS dublette
|
---|
146 | WHERE 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 | -- -------------------------------------
|
---|
154 | DELETE
|
---|
155 | FROM ax_Platz AS dublette
|
---|
156 | WHERE 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 | -- -------------------------------------
|
---|
164 | DELETE
|
---|
165 | FROM ax_Bahnverkehr AS dublette
|
---|
166 | WHERE 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 | -- -------------------------------------
|
---|
174 | DELETE
|
---|
175 | FROM ax_Flugverkehr AS dublette
|
---|
176 | WHERE 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 | -- -------------------------------------
|
---|
184 | DELETE
|
---|
185 | FROM ax_Schiffsverkehr AS dublette
|
---|
186 | WHERE 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 | -- -------------------------------------
|
---|
196 | DELETE
|
---|
197 | FROM ax_Landwirtschaft AS dublette
|
---|
198 | WHERE 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 | -- -------------------------------------
|
---|
206 | DELETE
|
---|
207 | FROM ax_Wald AS dublette
|
---|
208 | WHERE 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 | -- -------------------------------------
|
---|
216 | DELETE
|
---|
217 | FROM ax_gehoelz AS dublette
|
---|
218 | WHERE 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 | -- -------------------------------------
|
---|
226 | DELETE
|
---|
227 | FROM ax_Heide AS dublette
|
---|
228 | WHERE 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 | -- -------------------------------------
|
---|
236 | DELETE
|
---|
237 | FROM ax_Moor AS dublette
|
---|
238 | WHERE 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 | -- -------------------------------------
|
---|
246 | DELETE
|
---|
247 | FROM ax_Sumpf AS dublette
|
---|
248 | WHERE 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 | -- -------------------------------------
|
---|
256 | DELETE
|
---|
257 | FROM ax_UnlandVegetationsloseFlaeche AS dublette
|
---|
258 | WHERE 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 | -- -------------------------------------
|
---|
268 | DELETE
|
---|
269 | FROM ax_Fliessgewaesser AS dublette
|
---|
270 | WHERE 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 | -- -------------------------------------
|
---|
278 | DELETE
|
---|
279 | FROM ax_Hafenbecken AS dublette
|
---|
280 | WHERE 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 | -- -------------------------------------
|
---|
288 | DELETE
|
---|
289 | FROM ax_StehendesGewaesser AS dublette
|
---|
290 | WHERE 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 | -- -------------------------------------
|
---|
298 | DELETE
|
---|
299 | FROM ax_Meer AS dublette
|
---|
300 | WHERE 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 |
|
---|
311 | DELETE
|
---|
312 | FROM ax_flurstueck AS dublette
|
---|
313 | WHERE 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 |
|
---|
320 | DELETE
|
---|
321 | FROM ax_besondereflurstuecksgrenze AS dublette
|
---|
322 | WHERE 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 |
|
---|
331 | DELETE
|
---|
332 | FROM ax_buchungsblatt AS dublette
|
---|
333 | WHERE 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 |
|
---|
339 | DELETE
|
---|
340 | FROM ax_buchungsstelle AS dublette
|
---|
341 | WHERE 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 |
|
---|
349 | DELETE
|
---|
350 | FROM ax_grenzpunkt AS dublette
|
---|
351 | WHERE 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 |
|
---|
357 | DELETE
|
---|
358 | FROM ax_aufnahmepunkt AS dublette
|
---|
359 | WHERE 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 |
|
---|
365 | DELETE
|
---|
366 | FROM ax_sonstigervermessungspunkt AS dublette
|
---|
367 | WHERE 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 |
|
---|
373 | DELETE
|
---|
374 | FROM ax_besonderergebaeudepunkt AS dublette
|
---|
375 | WHERE 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 |
|
---|
398 | DELETE
|
---|
399 | FROM alkis_beziehungen AS dublette
|
---|
400 | WHERE 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
|
---|