Index: trunk/data/konvert/postnas_0.5/alkis_doppelte_objekte_loeschen.sh
===================================================================
--- trunk/data/konvert/postnas_0.5/alkis_doppelte_objekte_loeschen.sh	(revision 81)
+++ trunk/data/konvert/postnas_0.5/alkis_doppelte_objekte_loeschen.sh	(revision 81)
@@ -0,0 +1,36 @@
+#!/bin/bash
+echo "* =================================================================="
+echo "* Bereinigung 'doppelte Objekte' in den Tabellen der ALKIS-Datenbank"
+echo "* =================================================================="
+## Z.B. nachdem eine NAS-Datei doppelt konvertiert wurde.
+## Die Tabellen werden jeweils mit sich selbst verglichen.
+## Wenn eine "gml_id" doppelt ist, dann wird der Eintrag mit dem groesseren serial "ogc_fid" geloescht.
+## Warnung: Bei grossen Datenbanken erzeugt dies Script fuer laengere Zeit eine hohe Last auf dem DB-Server!!
+## Stand 03.02.2011
+#
+# Konfig:
+# -------
+  port="5432"       # 5432  PG 8.3 main/UTF
+  db="alkis05300"   # Datenbank-Name
+#
+# Hilfs-Variable
+# --------------
+  mylist="/data/bkup/${db}_tablenames"
+  myselect="SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND tablename like 'ax_%' ORDER BY tablename;"
+#
+# Here we go ....
+# ---------------
+echo "Datenbank : " $db
+echo "Liste     : " $mylist
+#echo "** Liste der Tabellen erzeugen"
+echo $myselect | psql -t -h localhost -p $port -d ${db} > ${mylist}
+for tabname in  `cat $mylist` 
+do
+  echo " "
+  echo " * Tabelle  $tabname "
+  mysql="DELETE FROM $tabname AS dublette WHERE EXISTS (SELECT solitaer.gml_id FROM $tabname AS solitaer WHERE solitaer.gml_id  = dublette.gml_id AND dublette.ogc_fid > solitaer.ogc_fid);"
+  echo $mysql | psql -t -h localhost -p $port -d ${db}
+  echo "-----------------------------"
+done
+##
+echo "** fertig!  (vaccuum empfohlen)"
Index: trunk/data/konvert/postnas_0.5/alkis_doppelte_objekte_loeschen.sql
===================================================================
--- trunk/data/konvert/postnas_0.5/alkis_doppelte_objekte_loeschen.sql	(revision 81)
+++ trunk/data/konvert/postnas_0.5/alkis_doppelte_objekte_loeschen.sql	(revision 81)
@@ -0,0 +1,411 @@
+
+-- Bereinigung "doppelte Objekte"
+
+-- z.B. nachdem eine NAS-Datei doppelt konvertiert wurde
+
+-- Stand 03.02.2011
+
+-- Wenn eine NAS-Datei versehentlich ein zweites mal konvertiert wird, dann merkt PostNAS das nicht!
+-- Die "gml_id" wäre ein eindeutiges Kennzeichen, aber als Primary-Key wird die "ogc_fid" künstlich gebildet.
+
+--   ==>  Anpassung Schema?
+
+-- Vorher DB-Sicherung!
+--
+-- sudo pg_dump -h localhost -p 5432 -U postgres -o -C alkis05300 | gzip > /data/bkup/alkis05300.sql.gz
+--
+
+
+-- Hier die wichtigsten Tabellen:
+-- - Die Tabellen aus der Gruppe Nutzungsart muessen eindeutige gml_ids haben fuer die
+--   Zusammenfassung in einer Tabelle fuer die Buchauskunft.
+-- - Flurstueck wird per WMS-Feature-Info abgefragt.
+-- - "alkis_beziehungen" ist die meist-beanspruchte Tabelle
+
+-- Um alle Tabellen zu entruempeln: siehe Shell-Script.
+
+
+--CREATE VIEW redundanzen_ax_gehoelz
+--AS
+--  SELECT 
+--       a.gml_id, 
+--       a.ogc_fid AS erster, 
+--       b.ogc_fid AS weiterer
+--  FROM ax_gehoelz AS a
+--  JOIN ax_gehoelz AS b
+--    ON a.gml_id  = b.gml_id 
+--   AND b.ogc_fid > a.ogc_fid;
+
+
+-- 01 REO: ax_Wohnbauflaeche
+-- -------------------------------------
+DELETE 
+  FROM ax_wohnbauflaeche AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_wohnbauflaeche AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+-- 02 REO: ax_IndustrieUndGewerbeflaeche
+-- -------------------------------------
+DELETE 
+  FROM ax_IndustrieUndGewerbeflaeche AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_IndustrieUndGewerbeflaeche AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+-- 03 REO: ax_Halde
+-- -------------------------------------
+DELETE 
+  FROM ax_Halde AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_Halde AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+-- 04 ax_Bergbaubetrieb
+-- -------------------------------------
+DELETE 
+  FROM ax_Bergbaubetrieb AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_Bergbaubetrieb AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+-- 05 REO: ax_TagebauGrubeSteinbruch
+-- -------------------------------------
+DELETE 
+  FROM ax_TagebauGrubeSteinbruch AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_TagebauGrubeSteinbruch AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+-- 06 REO: ax_FlaecheGemischterNutzung
+-- -------------------------------------
+DELETE 
+  FROM ax_FlaecheGemischterNutzung AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_FlaecheGemischterNutzung AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+-- 07 REO: ax_FlaecheBesondererFunktionalerPraegung
+-- -------------------------------------
+DELETE 
+  FROM ax_FlaecheBesondererFunktionalerPraegung AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_FlaecheBesondererFunktionalerPraegung AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+-- 08 REO: ax_SportFreizeitUndErholungsflaeche
+-- -------------------------------------
+DELETE 
+  FROM ax_SportFreizeitUndErholungsflaeche AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_SportFreizeitUndErholungsflaeche AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+-- 09 REO: ax_Friedhof
+-- -------------------------------------
+DELETE 
+  FROM ax_Friedhof AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_Friedhof AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+-- ** Objektartengruppe: Verkehr **
+
+-- 10 ax_Strassenverkehr
+-- -------------------------------------
+DELETE 
+  FROM ax_Strassenverkehr AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_Strassenverkehr AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+-- 11 ax_Weg
+-- -------------------------------------
+DELETE 
+  FROM ax_Weg AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_Weg AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+-- 12 ax_Platz
+-- -------------------------------------
+DELETE 
+  FROM ax_Platz AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_Platz AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+-- 13 ax_Bahnverkehr
+-- -------------------------------------
+DELETE 
+  FROM ax_Bahnverkehr AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_Bahnverkehr AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+-- 14 ax_Flugverkehr
+-- -------------------------------------
+DELETE 
+  FROM ax_Flugverkehr AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_Flugverkehr AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+-- 15 ax_Schiffsverkehr
+-- -------------------------------------
+DELETE 
+  FROM ax_Schiffsverkehr AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_Schiffsverkehr AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+-- ** Objektartengruppe: Vegetation **
+
+-- 16 ax_Landwirtschaft
+-- -------------------------------------
+DELETE 
+  FROM ax_Landwirtschaft AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_Landwirtschaft AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+-- 17 ax_Wald
+-- -------------------------------------
+DELETE 
+  FROM ax_Wald AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_Wald AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+-- 18 ax_Gehoelz
+-- -------------------------------------
+DELETE 
+  FROM ax_gehoelz AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_gehoelz AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+-- 19 ax_Heide
+-- -------------------------------------
+DELETE 
+  FROM ax_Heide AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_Heide AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+-- 20 ax_Moor
+-- -------------------------------------
+DELETE 
+  FROM ax_Moor AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_Moor AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+-- 21 ax_Sumpf
+-- -------------------------------------
+DELETE 
+  FROM ax_Sumpf AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_Sumpf AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+-- 22 ax_UnlandVegetationsloseFlaeche
+-- -------------------------------------
+DELETE 
+  FROM ax_UnlandVegetationsloseFlaeche AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_UnlandVegetationsloseFlaeche AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+-- ** Objektartengruppe: Gewässer **
+
+-- 24 ax_Fliessgewaesser
+-- -------------------------------------
+DELETE 
+  FROM ax_Fliessgewaesser AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_Fliessgewaesser AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+-- 25 ax_Hafenbecken
+-- -------------------------------------
+DELETE 
+  FROM ax_Hafenbecken AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_Hafenbecken AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+-- 26 ax_StehendesGewaesser
+-- -------------------------------------
+DELETE 
+  FROM ax_StehendesGewaesser AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_StehendesGewaesser AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+-- 27 ax_Meer
+-- -------------------------------------
+DELETE 
+  FROM ax_Meer AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_Meer AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+
+-- ---------------------------------------------------------------
+
+-- Flurstück
+
+DELETE 
+  FROM ax_flurstueck AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_flurstueck AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+
+DELETE 
+  FROM ax_besondereflurstuecksgrenze AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_besondereflurstuecksgrenze AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+
+-- Grundbuch
+
+DELETE 
+  FROM ax_buchungsblatt AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_buchungsblatt AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+DELETE 
+  FROM ax_buchungsstelle AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_buchungsstelle AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+-- Punkte
+
+DELETE 
+  FROM ax_grenzpunkt AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_grenzpunkt AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+DELETE 
+  FROM ax_aufnahmepunkt AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_aufnahmepunkt AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+DELETE 
+  FROM ax_sonstigervermessungspunkt AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_sonstigervermessungspunkt AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+DELETE 
+  FROM ax_besonderergebaeudepunkt AS dublette
+WHERE EXISTS
+   (SELECT solitaer.gml_id 
+      FROM ax_besonderergebaeudepunkt AS solitaer
+     WHERE solitaer.gml_id  = dublette.gml_id 
+       AND dublette.ogc_fid > solitaer.ogc_fid);
+
+
+-- Verbindungen / Relationen     -  S o n d e r f a l l
+-- -------------------------
+
+--CREATE VIEW redundanzen_alkis_beziehungen
+--AS
+--  SELECT solitaer.ogc_fid  AS erster,
+--         dublette.ogc_fid  AS zweiter 
+--    FROM alkis_beziehungen AS solitaer
+--    JOIN alkis_beziehungen AS dublette
+--      ON solitaer.beziehung_von = dublette.beziehung_von 
+--     AND solitaer.beziehungsart = dublette.beziehungsart 
+--     AND solitaer.beziehung_zu  = dublette.beziehung_zu  
+--     AND solitaer.ogc_fid       < dublette.ogc_fid;
+
+--  100573 Zeilen
+
+DELETE 
+  FROM alkis_beziehungen AS dublette
+WHERE EXISTS
+   (SELECT solitaer.ogc_fid
+      FROM alkis_beziehungen AS solitaer
+     WHERE solitaer.beziehung_von = dublette.beziehung_von 
+       AND solitaer.beziehungsart = dublette.beziehungsart 
+       AND solitaer.beziehung_zu  = dublette.beziehung_zu  
+       AND solitaer.ogc_fid       < dublette.ogc_fid);
+
+-- Achtung: sehr lange Ausführungszeit
+
+
+-- THE (happy) END
