[114] | 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; " |
---|