1 | #!/bin/bash |
---|
2 | |
---|
3 | source 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 |
---|
14 | table_input=$1 |
---|
15 | # $2= auszugebende Tabelle |
---|
16 | table_output=$2 |
---|
17 | # $3= in welcher Spalte wird die Widmung vorgehalten? (kann NULL-Werte enthalten) |
---|
18 | column_label=$3 |
---|
19 | # Generalisierungsparameter: |
---|
20 | # $4= MindestgröÃe fÃŒr AuÃenpolygone |
---|
21 | minArea_outer=$4 |
---|
22 | # $5= MindestgröÃe fÃŒr Innenpolygono |
---|
23 | minArea_inner=$5 |
---|
24 | # $6= SimplifyDistance fÃŒr AuÃenpolygone |
---|
25 | simplDist_outer=$6 |
---|
26 | # $7= SimplifyDistance fÃŒr Innenpolygone |
---|
27 | simplDist_inner=$7 |
---|
28 | # $8= RasterCellsize fÃŒr Perimter-Verschneidungen (siehe Doku) |
---|
29 | rasterCellsize=$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 | |
---|
37 | force=false |
---|
38 | if [ $9 ]; then |
---|
39 | if [ $9 == 'true' ] || [ $9 == 1 ]; then |
---|
40 | force=true |
---|
41 | echo "batch mode mit 'force' gesetzt." |
---|
42 | fi |
---|
43 | fi |
---|
44 | |
---|
45 | # Testen, ob Variablen gesetzt sind |
---|
46 | |
---|
47 | if [ ! $table_input ]; then |
---|
48 | error="${error}Keine Angabe zur Eingabetabelle gemacht!\r\n" |
---|
49 | fi |
---|
50 | if [ ! $table_output ]; then |
---|
51 | error="${error}Keine Angabe zur Ausgabetabelle gemacht!\r\n" |
---|
52 | fi |
---|
53 | if [ ! $column_label ]; then |
---|
54 | error="${error}Keine Angabe zur Spalte 'Widmung' gemacht!\r\n" |
---|
55 | fi |
---|
56 | if [ ! $minArea_outer ]; then |
---|
57 | error="${error}Keine Angabe zur MindestgröÃe der AuÃenpolygone!\r\n" |
---|
58 | fi |
---|
59 | if [ ! $minArea_inner ]; then |
---|
60 | error="${error}Keine Angabe zur MindestgröÃe der Innenpolygone!\r\n" |
---|
61 | fi |
---|
62 | if [ ! $simplDist_outer ]; then |
---|
63 | error="${error}Keine Angabe zur Simplify-Distance fÃŒr die AuÃenpolygone!\r\n" |
---|
64 | fi |
---|
65 | if [ ! $simplDist_inner ]; then |
---|
66 | error="${error}Keine Angabe zur Simplify-Distance fÃŒr die Innenpolygone!\r\n" |
---|
67 | fi |
---|
68 | if [ ! $RasterCellsize ]; then |
---|
69 | error="${error}Keine Angabe zur ZellgröÃe (in km) fÃŒr die Verschneidung!\r\n" |
---|
70 | fi |
---|
71 | |
---|
72 | if [ $# -lt 9 ]; then |
---|
73 | echo "Aufruf mit inkorrekter Anzahl an Parametern:" |
---|
74 | echo -e $error #@ ToDo |
---|
75 | exit 0 |
---|
76 | fi |
---|
77 | |
---|
78 | # Festlegen der Datenbank-Verbindung |
---|
79 | echo "Teste PostGIS-Verbindung zu $dbname auf $dbuser@$dbhost:$dbport:" |
---|
80 | if [ $dbpass == false ]; then |
---|
81 | CONN="psql --user=$dbuser $dbname --host=$dbhost --port=$dbport -w" |
---|
82 | else |
---|
83 | CONN="psql --user=$dbuser $dbname --host=$dbhost --port=$dbport --password=$dbpass" |
---|
84 | fi |
---|
85 | |
---|
86 | # Testen, ob die Eingangstabelle exisitiert |
---|
87 | QUERY="${CONN} -c 'SELECT true FROM $table_input LIMIT 1 OFFSET 0' --quiet" |
---|
88 | |
---|
89 | if eval $QUERY &> /dev/null; |
---|
90 | then |
---|
91 | echo "Verbindung erfolgreich hergestellt, Eingabetabelle existiert." |
---|
92 | else # 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 |
---|
102 | fi |
---|
103 | |
---|
104 | |
---|
105 | # Testen, ob die Ausgabetabelle bereits exisitiert - Abfrage: Ãberschreiben oder Abbruch? |
---|
106 | QUERY="${CONN} -c 'SELECT true FROM $table_output LIMIT 1 OFFSET 0' --quiet" |
---|
107 | |
---|
108 | if eval $QUERY &> /dev/null; |
---|
109 | then |
---|
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 |
---|
133 | else # 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 |
---|
142 | fi |
---|
143 | |
---|
144 | # NÀchstes "Fenster" |
---|
145 | if [ $force != true ]; then |
---|
146 | read -p "DrÃŒcken Sie eine beliebige Taste zum Fortfahren: " > /dev/null |
---|
147 | echo '## ATKIS-Generalisierung :: Wrapper ##' |
---|
148 | fi; |
---|
149 | |
---|
150 | if [ $dbpass == false ]; then |
---|
151 | CONN="psql --user=$dbuser $dbname --host=$dbhost --port=$dbport -w" |
---|
152 | else |
---|
153 | CONN="psql --user=$dbuser $dbname --host=$dbhost --port=$dbport --password=$dbpass" |
---|
154 | fi |
---|
155 | |
---|
156 | ################################# GENERALISIERUNG per se ################################# |
---|
157 | |
---|
158 | time $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 | |
---|
196 | time $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; " |
---|