# HG changeset patch
# User Michael Stanko <mstanko@bfs.de>
# Date 1475046378 -7200
# Node ID 299f4ba86090a45311b5d6ece2dd0bc05fea1fe2
# Parent  37952c111f71bd1699c5288472ff04a25daece1a
extend docker envireoment to load geographical data of german comunities

diff -r 37952c111f71 -r 299f4ba86090 db_schema/Dockerfile
--- a/db_schema/Dockerfile	Wed Sep 28 08:48:04 2016 +0200
+++ b/db_schema/Dockerfile	Wed Sep 28 09:06:18 2016 +0200
@@ -25,7 +25,7 @@
 # Install packages
 #
 RUN apt-get update && \
-    apt-get install -y postgresql-9.4-postgis-2.1 postgis
+    apt-get install -y postgresql-9.4-postgis-2.1 postgis curl unzip
 
 #
 # Use user postgres to run the next commands
diff -r 37952c111f71 -r 299f4ba86090 db_schema/setup-db.sh
--- a/db_schema/setup-db.sh	Wed Sep 28 08:48:04 2016 +0200
+++ b/db_schema/setup-db.sh	Wed Sep 28 09:06:18 2016 +0200
@@ -77,4 +77,16 @@
 
     echo import lada test data
     psql $DB_CONNECT_STRING -d $DB_NAME -f $DIR/lada_data.sql
+
+    echo create user $ROLE_NAME
+    psql $DB_CONNECT_STRING -d $DB_NAME --command "CREATE SCHEMA geo AUTHORIZATION $ROLE_NAME"
+
+    TS="0101" 
+    cd /tmp
+    curl -O http://sg.geodatenzentrum.de/web_download/vg/vg250_${TS}/utm32s/shape/vg250_${TS}.utm32s.shape.ebenen.zip && \
+    unzip vg250_${TS}.utm32s.shape.ebenen.zip "*VG250_GEM*"
+    cd vg250_${TS}.utm32s.shape.ebenen/vg250_ebenen/
+    shp2pgsql VG250_GEM geo.gem_utm | psql $DB_CONNECT_STRING -d $DB_NAME
+    cd /tmp
+    rm -rf vg250_${TS}.utm32s.shape.ebenen*
 fi
diff -r 37952c111f71 -r 299f4ba86090 db_schema/stammdaten_schema.sql
--- a/db_schema/stammdaten_schema.sql	Wed Sep 28 08:48:04 2016 +0200
+++ b/db_schema/stammdaten_schema.sql	Wed Sep 28 09:06:18 2016 +0200
@@ -231,37 +231,6 @@
 
 ALTER SEQUENCE datensatz_erzeuger_id_seq OWNED BY datensatz_erzeuger.id;
 
-
-CREATE SEQUENCE de_vg_id_seq
-    START WITH 1
-    INCREMENT BY 1
-    NO MINVALUE
-    NO MAXVALUE
-    CACHE 1;
-
-CREATE TABLE de_vg (
-    id integer PRIMARY KEY DEFAULT nextval('de_vg_id_seq'::regclass),
-    use double precision,
-    rs character varying(12),
-    gf double precision,
-    rau_rs character varying(12),
-    gen character varying(50),
-    des character varying(75),
-    isn double precision,
-    bemerk character varying(75),
-    nambild character varying(16),
-    ags character varying(12),
-    rs_alt character varying(20),
-    wirksamkei date,
-    debkg_id character varying(16),
-    length numeric,
-    shape_area numeric,
-    geom public.geometry(MultiPolygon,4326)
-);
-
-ALTER SEQUENCE de_vg_id_seq OWNED BY de_vg.id;
-
-
 CREATE TABLE deskriptor_umwelt (
     id integer PRIMARY KEY,
     s00 integer NOT NULL,
@@ -809,9 +778,6 @@
     ADD CONSTRAINT umwelt_umwelt_bereich_key UNIQUE (umwelt_bereich);
 
 
-CREATE INDEX de_vg_geom_gist ON de_vg USING gist (geom);
-
-
 CREATE INDEX fts_stauts_kooin10001 ON staat USING btree (kda_id);