source: trunk/import/atkis_rp_rendering/processing/flaechen.sh @ 261

Revision 261, 8.2 KB checked in by astrid.emde, 7 years ago (diff)

move atlḱis files back to trunk

  • Property svn:executable set to *
Line 
1#!/bin/bash
2
3source psql.conf
4
5####################################################################
6#                                       ab hier nichts mehr Àndern
7#                               do not change anything below this line.
8####################################################################
9
10# Kommandozeilen-Variante
11# @params
12# Tabellenparameter::
13# $1= einzulesende Tabelle
14table_input=$1
15# $2= auszugebende Tabelle
16table_output=$2
17# $3= in welcher Spalte wird die Widmung vorgehalten? (kann NULL-Werte enthalten)
18column_label=$3
19# Generalisierungsparameter:
20# $4= Mindestgröße fÃŒr Außenpolygone
21minArea_outer=$4
22# $5= Mindestgröße fÃŒr Innenpolygono
23minArea_inner=$5
24# $6= SimplifyDistance fÃŒr Außenpolygone
25simplDist_outer=$6
26# $7= SimplifyDistance fÃŒr Innenpolygone
27simplDist_inner=$7
28# $8= RasterCellsize fÃŒr Perimter-Verschneidungen (siehe Doku)
29rasterCellsize=$8
30##
31##
32##
33# @ToDo: "Input at hoc"-Variante (read "Geben Sie den Tabellennamen der Eingabedaten ein:" table_input, ...)
34# @ToDo: Automatisierte Variante mit cat betroffene.txt
35
36
37force=false
38if [ $9 ]; then
39        if [ $9 == 'true' ] || [ $9 == 1 ]; then
40                force=true
41                echo "batch mode mit 'force' gesetzt."
42        fi
43fi
44
45# Testen, ob Variablen gesetzt sind
46
47if [ ! $table_input ]; then
48        error="${error}Keine Angabe zur Eingabetabelle gemacht!\r\n"
49fi
50if [ ! $table_output ]; then
51        error="${error}Keine Angabe zur Ausgabetabelle gemacht!\r\n"
52fi
53if [ ! $column_label ]; then
54        error="${error}Keine Angabe zur Spalte 'Widmung' gemacht!\r\n"
55fi
56if [ ! $minArea_outer ]; then
57        error="${error}Keine Angabe zur Mindestgröße der Außenpolygone!\r\n"
58fi
59if [ ! $minArea_inner ]; then
60        error="${error}Keine Angabe zur Mindestgröße der Innenpolygone!\r\n"
61fi
62if [ ! $simplDist_outer ]; then
63        error="${error}Keine Angabe zur Simplify-Distance fÃŒr die Außenpolygone!\r\n"
64fi
65if [ ! $simplDist_inner ]; then
66        error="${error}Keine Angabe zur Simplify-Distance fÃŒr die Innenpolygone!\r\n"
67fi
68if [ ! $RasterCellsize ]; then
69        error="${error}Keine Angabe zur Zellgröße (in km) fÃŒr die Verschneidung!\r\n"
70fi
71
72if [ $# -lt 9 ]; then
73        echo "Aufruf mit inkorrekter Anzahl an Parametern:"
74        echo -e $error #@ ToDo
75        exit 0
76fi
77
78# Festlegen der Datenbank-Verbindung
79echo "Teste PostGIS-Verbindung zu $dbname auf $dbuser@$dbhost:$dbport:"
80if [ $dbpass == false ]; then
81        CONN="psql --user=$dbuser $dbname --host=$dbhost --port=$dbport -w"
82else   
83        CONN="psql --user=$dbuser $dbname --host=$dbhost --port=$dbport --password=$dbpass"
84fi
85
86# Testen, ob die Eingangstabelle exisitiert
87QUERY="${CONN} -c 'SELECT true FROM $table_input LIMIT 1 OFFSET 0' --quiet"
88
89if eval $QUERY &> /dev/null;
90then
91        echo "Verbindung erfolgreich hergestellt, Eingabetabelle existiert."
92else # Kein Abruf von Tabelle möglich - DB-Konfig falsch oder Tabelle nicht existent
93        QUERY="${CONN} -c 'SELECT false' --quiet"
94        if eval $QUERY &> /dev/null;
95        then
96                echo "Fehler: Eingabetabelle $table_input existiert nicht."
97        else
98                echo "Fehler: Verbindung zum Server konnte nicht hergestellt werden!"
99                echo "Bitte Verbindungseinstellungen ÃŒberprÃŒfen."
100        fi
101        exit 0
102fi
103
104
105# Testen, ob die Ausgabetabelle bereits exisitiert - Abfrage: Überschreiben oder Abbruch?
106QUERY="${CONN} -c 'SELECT true FROM $table_output LIMIT 1 OFFSET 0' --quiet"
107
108if eval $QUERY &> /dev/null;
109then
110        echo "Ausgabetabelle existiert bereits!"
111       
112        response=false
113        if [ $force == true ]; then
114                response=true
115                QUERY="${CONN} -c 'DROP TABLE $table_output' --quiet"
116                eval $QUERY &> /dev/null;
117                echo "Tabelle $table_output gelöscht."
118        fi
119
120        while [ $response = false ]; do
121                read -p "[L]ösche Tabelle oder [A]bbruch: " var
122                if [ "$var" = 'L' ]; then
123                        response=true
124                        QUERY="${CONN} -c 'DROP TABLE $table_output' --quiet"
125                        eval $QUERY &> /dev/null;
126                        echo "Tabelle $table_output gelöscht."
127                elif [ "$var" = 'A' ]; then
128                        response=true
129                        echo "Abbruch durch Benutzer."
130                        exit 0
131                fi
132        done
133else # Kein Abruf von Tabelle möglich - DB-Konfig falsch oder Tabelle nicht existent
134        QUERY="${CONN} -c 'SELECT false' --quiet"
135        if eval $QUERY &> /dev/null;
136        then
137                echo "Alle Vorbereitungen zum Prozessieren sind abgeschlossen."
138        else
139                echo "Fehler: Verbindung zum Server konnte nicht hergestellt werden!"
140                echo "Bitte Verbindungseinstellungen ÃŒberprÃŒfen."
141        fi
142fi
143
144# NÀchstes "Fenster"
145if [ $force != true ]; then
146        read -p "DrÃŒcken Sie eine beliebige Taste zum Fortfahren: " > /dev/null
147        echo '##            ATKIS-Generalisierung :: Wrapper            ##'
148fi;
149
150if [ $dbpass == false ]; then
151        CONN="psql --user=$dbuser $dbname --host=$dbhost --port=$dbport -w"
152else   
153        CONN="psql --user=$dbuser $dbname --host=$dbhost --port=$dbport --password=$dbpass"
154fi
155
156################################# GENERALISIERUNG per se #################################
157
158time $CONN -c "DROP TABLE IF EXISTS temp0;
159        SELECT path[1] AS path, ST_MakePolygon(ST_ExteriorRing(geom)) AS wkb_geometry, $3::text
160        INTO temp0
161        FROM (
162                SELECT (ST_DumpRings( wkb_geometry )).*, $3
163                FROM $1
164        ) AS t1;
165        CREATE INDEX temp0_geomidx ON temp0 USING GIST ( wkb_geometry );
166        DROP SEQUENCE IF EXISTS temp0_geomid_seq;
167        CREATE SEQUENCE temp0_geomid_seq;
168        ALTER TABLE temp0 ADD COLUMN geomid INTEGER;
169        UPDATE temp0 SET geomid = nextval('temp0_geomid_seq');
170        ALTER TABLE temp0 ALTER COLUMN geomid SET DEFAULT nextval('temp0_geomid_seq');
171        --
172        DELETE FROM temp0 WHERE path = 0 AND ST_Area( wkb_geometry ) < $4; --
173        DELETE FROM temp0 WHERE path <> 0 AND ST_Area( wkb_geometry ) < $5; --
174        UPDATE temp0 SET wkb_geometry = ST_Buffer(
175                                                                                        ST_Buffer(
176                                                                                                        ST_SimplifyPreserveTopology( wkb_geometry, $6 )
177                                                                                        , SQRT( $6 ) )
178                                                                        , SQRT( $6 ) *-1 )
179        WHERE path = 0;
180        UPDATE temp0 SET wkb_geometry = ST_Buffer(
181                                                                                        ST_Buffer(
182                                                                                                        ST_SimplifyPreserveTopology( wkb_geometry, $7 )
183                                                                                        , SQRT( $7 ) )
184                                                                        , SQRT( $7 ) *-1 )
185        WHERE path <> 0;
186        INSERT INTO temp0
187        (wkb_geometry, path, $3)
188        SELECT
189                ST_Buffer(      ST_Buffer( wkb_geometry , 1.5 ), -1.5 ) AS wkb_geometry,
190                path, $3
191        FROM temp0
192        WHERE NOT ST_IsValid( wkb_geometry );
193        DELETE FROM temp0 WHERE NOT ST_IsValid( wkb_geometry );";
194
195
196time $CONN -c "ALTER TABLE temp0 ADD COLUMN relate VARCHAR;
197        UPDATE temp0 AS t SET relate = 'intersects'
198        WHERE EXISTS (
199                SELECT 1
200                        FROM map_landesflaeche_g0 AS rlp
201                        WHERE rlp.wkb_geometry && t.wkb_geometry
202                        GROUP BY t.wkb_geometry
203                        HAVING ST_Overlaps( ST_Union(rlp.wkb_geometry), t.wkb_geometry )
204        );
205        INSERT INTO temp0
206        (relate, wkb_geometry, path, $3)
207        SELECT 'clipped' AS relate, proc.wkb_geometry, path, $3
208        FROM (
209                SELECT path, $3, (ST_Dump(wkb_geometry)).geom AS wkb_geometry
210                FROM (
211                        SELECT ST_Intersection( lyr, rlp) AS wkb_geometry, path, $3
212                        FROM
213                        (
214                                SELECT path, $3, relate, wkb_geometry AS lyr FROM temp0
215                        ) AS w,
216                        (
217                                SELECT wkb_geometry AS rlp FROM map_landesflaeche
218                        ) AS l
219                        WHERE w.relate = 'intersects'
220                ) AS wrapper
221        ) AS proc;
222
223        DELETE FROM temp0 WHERE relate = 'intersects' OR ST_GeometryType( wkb_geometry ) IN ('ST_Point', 'ST_LineString');
224
225        INSERT INTO temp0
226        (relate, wkb_geometry, path, $3 )
227        SELECT relate, (ST_Dump( wkb_geometry )).geom AS wkb_geometry, path, $3
228        FROM temp0
229        WHERE ST_GeometryType( wkb_geometry ) = 'ST_MultiPolygon';
230        DELETE FROM temp0 WHERE ST_GeometryType( wkb_geometry ) = 'ST_MultiPolygon';
231        DROP TABLE IF EXISTS $2;
232
233                SELECT ST_MakePolygon( ST_ExteriorRing(o.wkb_geometry) , ST_Accum(ST_ExteriorRing(i.wkb_geometry)) ) AS wkb_geometry, o.$3
234                INTO $2
235                FROM temp0 AS o
236                INNER JOIN temp0 AS i
237                ON ST_Contains(o.wkb_geometry, i.wkb_geometry)
238                WHERE o.path = 0 AND NOT (o.wkb_geometry ~= i.wkb_geometry)
239                GROUP BY o.wkb_geometry, o.$3
240        UNION ALL
241                SELECT
242                t0.wkb_geometry, $3
243                FROM temp0 AS t0
244                WHERE NOT EXISTS( SELECT 1 FROM temp0 AS t1 WHERE ST_Contains(t0.wkb_geometry, t1.wkb_geometry) AND NOT (t0.wkb_geometry ~= t1.wkb_geometry) ) AND t0.path = 0;
245        DROP TABLE temp0;
246        CREATE INDEX $2_gidx ON $2 USING GIST ( wkb_geometry );
247        DROP SEQUENCE IF EXISTS $2_gid_seq;
248        CREATE SEQUENCE $2_gid_seq;
249        ALTER TABLE $2 ADD COLUMN gid INTEGER;
250        UPDATE $2 SET gid = nextval('$2_gid_seq');
251        ALTER TABLE $2 ALTER COLUMN gid SET DEFAULT nextval('$2_gid_seq');
252        UPDATE $2 SET wkb_geometry = ST_Buffer(ST_Buffer( wkb_geometry, 1), -1) WHERE NOT ST_IsValid( wkb_geometry );
253        UPDATE $2 SET wkb_geometry = (ST_Dump( wkb_geometry )).geom WHERE ST_GeometryType( wkb_geometry ) = 'ST_MultiPolygon';
254        REINDEX TABLE $2; "
Note: See TracBrowser for help on using the repository browser.