ingo@1230: BEGIN;
ingo@1230: 
hans@1238: -- Geodaesie/Flussachse+km/achse
ingo@1240: CREATE SEQUENCE RIVER_AXES_ID_SEQ;
ingo@1230: CREATE TABLE river_axes (
ingo@1240:     id       int PRIMARY KEY NOT NULL,
ingo@1230:     river_id int REFERENCES rivers(id),
ingo@1230:     kind     int             NOT NULL DEFAULT 0
ingo@1230: );
ingo@1230: SELECT AddGeometryColumn('river_axes', 'geom', 31466, 'LINESTRING', 2);
ingo@1240: ALTER TABLE river_axes ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_ID_SEQ');
ingo@1230: 
ingo@1230: 
hans@1238: -- Geodaesie/Querprofile/*
ingo@1240: CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
ingo@1230: CREATE TABLE cross_section_tracks (
ingo@1240:     id       int PRIMARY KEY NOT NULL,
ingo@1230:     river_id int REFERENCES rivers(id),
ingo@1230:     km       NUMERIC NOT NULL,
ingo@1230:     z        NUMERIC NOT NULL DEFAULT 0
ingo@1230: );
ingo@1230: SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31466, 'LINESTRING', 2);
ingo@1240: ALTER TABLE cross_section_tracks ALTER COLUMN id SET DEFAULT NEXTVAL('CROSS_SECTION_TRACKS_ID_SEQ');
ingo@1230: 
ingo@1230: 
hans@1238: -- Geodaesie/Linien/rohre-und-spreen
ingo@1240: CREATE SEQUENCE LINES_ID_SEQ;
ingo@1230: CREATE TABLE lines (
ingo@1240:     id       int PRIMARY KEY NOT NULL,
ingo@1230:     river_id int REFERENCES rivers(id),
ingo@1230:     kind     int             NOT NULL DEFAULT 0,
ingo@1230:     z        NUMERIC DEFAULT 0
ingo@1230: );
ingo@1230: SELECT AddGeometryColumn('lines', 'geom', 31466, 'LINESTRING', 4);
ingo@1240: ALTER TABLE lines ALTER COLUMN id SET DEFAULT NEXTVAL('LINES_ID_SEQ');
ingo@1230: -- 'kind':
ingo@1230: -- 0: ROHR1
ingo@1230: -- 1: DAMM
ingo@1230: 
ingo@1230: 
hans@1238: -- Geodaesie/Bauwerke/Wehre.shp
ingo@1240: CREATE SEQUENCE BUILDINGS_ID_SEQ;
ingo@1230: CREATE TABLE buildings (
ingo@1240:     id       int PRIMARY KEY NOT NULL,
ingo@1230:     river_id int REFERENCES rivers(id),
ingo@1230:     name     VARCHAR(50)
ingo@1230: );
ingo@1230: SELECT AddGeometryColumn('buildings', 'geom', 31466, 'LINESTRING', 2);
ingo@1240: ALTER TABLE buildings ALTER COLUMN id SET DEFAULT NEXTVAL('BUILDINGS_ID_SEQ');
ingo@1230: 
ingo@1230: 
hans@1238: -- Geodaesie/Festpunkte/Festpunkte.shp
ingo@1240: CREATE SEQUENCE FIXPOINTS_ID_SEQ;
ingo@1230: CREATE TABLE fixpoints (
ingo@1240:     id       int PRIMARY KEY NOT NULL,
ingo@1230:     river_id int REFERENCES rivers(id),
ingo@1230:     x        int,
ingo@1230:     y        int,
ingo@1230:     km       NUMERIC NOT NULL,
ingo@1230:     HPGP     VARCHAR(2)
ingo@1230: );
ingo@1230: SELECT AddGeometryColumn('fixpoints', 'geom', 31466, 'POINT', 2);
ingo@1240: ALTER TABLE fixpoints ALTER COLUMN id SET DEFAULT NEXTVAL('FIXPOINTS_ID_SEQ');
ingo@1230: 
ingo@1236: 
hans@1238: -- Hydrologie/Hydr. Grenzen/talaue.shp
ingo@1240: CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
ingo@1237: CREATE TABLE floodplain (
ingo@1240:     id       int PRIMARY KEY NOT NULL,
ingo@1237:     river_id int REFERENCES rivers(id)
ingo@1237: );
ingo@1237: SELECT AddGeometryColumn('floodplain', 'geom', 31466, 'MULTIPOLYGON', 2);
ingo@1240: ALTER TABLE floodplain ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODPLAIN_ID_SEQ');
ingo@1237: 
ingo@1237: 
hans@1238: -- Geodaesie/Hoehenmodelle/*
ingo@1240: CREATE SEQUENCE DEM_ID_SEQ;
ingo@1240: CREATE TABLE dem (
ingo@1240:     id       int PRIMARY KEY NOT NULL,
ingo@1236:     river_id int REFERENCES rivers(id),
ingo@1236:     -- XXX Should we use the ranges table instead?
ingo@1236:     lower    NUMERIC,
ingo@1236:     upper    NUMERIC,
ingo@1236:     path     VARCHAR(256),
ingo@1236:     UNIQUE (river_id, lower, upper)
ingo@1236: );
ingo@1240: ALTER TABLE dem ALTER COLUMN id SET DEFAULT NEXTVAL('DEM_ID_SEQ');
ingo@1236: 
hans@1238: 
bjoern@1241: -- Hydrologie/Einzugsgebiete/EZG.shp
bjoern@1241:    -- Hinweise zu ezg_saar.shp wird nicht importiert:
bjoern@1241:    -- CLASS: Integer (8.0) KLAEREN: wir die benoetigt?
bjoern@1241:    -- AREA: Real (19.8) laesst sich auch durch EZG.shp bestimmen
bjoern@1241:    -- PERIMETER: Real (19.8) laesst sich auch durch EZG.shp bestimmen
hans@1238: 
bjoern@1241: CREATE SEQUENCE CATCHMENT_ID_SEQ;
bjoern@1241: CREATE TABLE catchment (
bjoern@1241:     id int PRIMARY KEY NOT NULL,
bjoern@1241:     river_id int REFERENCES rivers(id),
bjoern@1241:     "area" numeric,
bjoern@1241:     "name" VARCHAR(80)
bjoern@1241: );
bjoern@1241: SELECT AddGeometryColumn('catchment','geom',31466,'POLYGON',2);
bjoern@1241: ALTER TABLE catchment ALTER COLUMN id SET DEFAULT NEXTVAL('CATCHMENT_ID_SEQ');
bjoern@1241: 
bjoern@1241: -- Hydrologie/Einzugsgebiete/b1-polygon.shp
bjoern@1241:    -- Nur fuer saar vorhanden
bjoern@1241:    -- IDEE alle Begrenzungspolygone in eine Tabelle.
bjoern@1241: CREATE SEQUENCE BOUNDARYPOLYS_ID_SEQ;
bjoern@1241: CREATE TABLE boundarypolys (
bjoern@1241:     id int PRIMARY KEY NOT NULL,
bjoern@1241:     river_id int REFERENCES rivers(id)
bjoern@1241: );
bjoern@1241: SELECT AddGeometryColumn('boundarypolys','geom',31466,'MULTIPOLYGON',2);
bjoern@1241: ALTER TABLE boundarypolys ALTER COLUMN id SET DEFAULT NEXTVAL('BOUNDARYPOLYS_ID_SEQ');
bjoern@1241: 
bjoern@1241: -- Hydrologie/HW-Schutzanlagen
bjoern@1241: -- Wird nicht benoetigt, stattdessen verwenden wir
bjoern@1241: -- Gewaesser/Saar/Geodaesie/Linien/rohre-und-sperren.shp
bjoern@1241:     -- hws.shp beinhaltet die Geometrien von:
bjoern@1241:         -- HWS-Lisdorf.shp -- hws_anlage
bjoern@1241:         -- HWS-Mettlach.shp -- maßnahme -> hws_anlage
bjoern@1241:         -- HWS-Rehlingen.shp -- hw -> hws_anlage
bjoern@1241:         -- HWS_Saarburg.shp -- höhe? bauart?
bjoern@1241:         -- HWS-Schoden-Rhl-Pf.shp  -- hws_anlage
bjoern@1241:         -- HWS_Schoden.shp --höhe? bauart?
bjoern@1241:         -- HWS-Serrig.shp --hws_anlage
bjoern@1241: -- CREATE SEQUENCE HWS_EZG_ID_SEQ;
bjoern@1241: -- CREATE TABLE hws (
bjoern@1241: --     id int PRIMARY KEY NOT NULL,
bjoern@1241: --     oid int,
bjoern@1241: --     river_id int REFERENCES rivers(id),
bjoern@1241: --     hws_facility VARCHAR(40),
bjoern@1241: --     typ VARCHAR(254)
bjoern@1241: -- );
bjoern@1241: -- SELECT AddGeometryColumn('hws','geom',31466,'MULTILINESTRING',2);
bjoern@1241: -- ALTER TABLE hw ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ');
bjoern@1241: 
hans@1238: -- Hydrologie/Hydr. Grenzen/Linien
bjoern@1241:    -- BfG/boeschung_*.shp 
bjoern@1241: CREATE SEQUENCE BANKS_ID_SEQ;
bjoern@1241: CREATE TABLE banks (
bjoern@1241:     id int PRIMARY KEY NOT NULL,
bjoern@1241:     river_id int REFERENCES rivers(id)
bjoern@1241: );
bjoern@1241: SELECT AddGeometryColumn('banks','geom',31466,'MULTILINESTRING',2);
bjoern@1241: ALTER TABLE banks ALTER COLUMN id SET DEFAULT NEXTVAL('BANKS_ID_SEQ');
hans@1238: 
bjoern@1241:    -- BfG/hauptoeff_*.shp
bjoern@1241: CREATE SEQUENCE MAINSPANS_ID_SEQ;
bjoern@1241: CREATE TABLE mainspans(
bjoern@1241:     id int PRIMARY KEY NOT NULL,
bjoern@1241:     river_id int REFERENCES rivers(id)
bjoern@1241: );
bjoern@1241: SELECT AddGeometryColumn('mainspans','geom',31466,'MULTILINESTRING',2);
bjoern@1241: ALTER TABLE mainspans ALTER COLUMN id SET DEFAULT NEXTVAL('MAINSPANS_ID_SEQ');
bjoern@1241: 
bjoern@1241:    -- BfG/MNQ-*.shp
bjoern@1241: CREATE SEQUENCE MNQ_ID_SEQ;
bjoern@1241: CREATE TABLE mnq (gid serial PRIMARY KEY,
bjoern@1241:     id int PRIMARY KEY NOT NULL,
bjoern@1241:     river_id int REFERENCES rivers(id),
bjoern@1241:     haltung varchar(16)
bjoern@1241: );
bjoern@1241: SELECT AddGeometryColumn('mnq', 'the_geom',31466,'MULTIPOLYGON',2);
bjoern@1241: ALTER TABLE mnq ALTER COLUMN id SET DEFAULT NEXTVAL('MNQ_ID_SEQ');
bjoern@1241: 
bjoern@1241:    -- BfG/modellgrenze*.shp
bjoern@1241: CREATE SEQUENCE MODELBOUNDARY_ID_SEQ;
bjoern@1241: CREATE TABLE modelboundary (
bjoern@1241:     id int PRIMARY KEY NOT NULL,
bjoern@1241:     river_id int REFERENCES rivers(id)
bjoern@1241: );
bjoern@1241: SELECT AddGeometryColumn('modelboundary','geom',31466,'MULTILINESTRING',2);
bjoern@1241: ALTER TABLE modelboundary ALTER COLUMN id SET DEFAULT NEXTVAL('MODELBOUNDARY_ID_SEQ');
bjoern@1241: 
bjoern@1241:    -- TODO: Klaeren ob benoetigt, da einzel Geometrien in Tabelle vorland.
bjoern@1241:    -- BfG/saar-sld-vorland.shp
bjoern@1241: 
bjoern@1241:    -- BfG/uferlinie.shp
bjoern@1241: CREATE SEQUENCE SHORELINE_ID_SEQ;
bjoern@1241: CREATE TABLE shoreline(
bjoern@1241:     id int PRIMARY KEY NOT NULL,
bjoern@1241:     river_id int REFERENCES rivers(id)
bjoern@1241: );
bjoern@1241: SELECT AddGeometryColumn('shoreline','geom',31466,'MULTILINESTRING',2);
bjoern@1241: ALTER TABLE shoreline ALTER COLUMN id SET DEFAULT NEXTVAL('SHORELINE_ID_SEQ');
bjoern@1241: 
bjoern@1241:    -- BfG/vorland_*.shp
bjoern@1241: CREATE SEQUENCE FORELAND_ID_SEQ;
bjoern@1241: CREATE TABLE foreland(
bjoern@1241:     id int PRIMARY KEY NOT NULL,
bjoern@1241:     river_id int REFERENCES rivers(id)
bjoern@1241: );
bjoern@1241: SELECT AddGeometryColumn('foreland','geom',31466,'MULTILINESTRING',2);
bjoern@1241: ALTER TABLE foreland ALTER COLUMN id SET DEFAULT NEXTVAL('FORELANDS_ID_SEQ');
bjoern@1241: 
bjoern@1241: 
hans@1238: -- Hydrologie/Streckendaten
bjoern@1241:     -- pegellage_saar.shp
bjoern@1241: CREATE SEQUENCE LEVELPOSITION_ID_SEQ;
bjoern@1241: CREATE TABLE levelposition (
bjoern@1241:     id int PRIMARY KEY NOT NULL,
bjoern@1241:     river_id int REFERENCES rivers(id),
bjoern@1241:     x numeric(10,0),
bjoern@1241:     y numeric(10,0),
bjoern@1241:     name varchar(254)
bjoern@1241: );
bjoern@1241: SELECT AddGeometryColumn('levelposition','geom','31466','POINT',2);
bjoern@1241: ALTER TABLE levelposition ALTER COLUMN id SET DEFAULT NEXTVAL('LEVELPOSITION_ID_SEQ');
hans@1238: 
hans@1238: -- Hydrologie/UeSG/Berechnung
bjoern@1241:    -- Berechnung/Aktuell/BfG
bjoern@1241: CREATE SEQUENCE COMPUTATIONS_BFG_ID_SEQ;
bjoern@1241: CREATE TABLE computations_bfg (
bjoern@1241:     id int PRIMARY KEY NOT NULL,
bjoern@1241:     river_id int REFERENCES rivers(id),
bjoern@1241:     section varchar(254),
bjoern@1241:     area float8,
bjoern@1241:     perimeter float8
bjoern@1241: );
bjoern@1241: SELECT AddGeometryColumn('computations_bfg','geom','31466','MULTIPOLYGON',2);
bjoern@1241: ALTER TABLE computations_bfg ALTER COLUMN id SET DEFAULT NEXTVAL('COMPUTATIONS_BFG_ID_SEQ');
hans@1238: 
bjoern@1241:    -- Berechnung/Aktuell/Land
bjoern@1241: CREATE SEQUENCE COMPUTATIONS_COUNTRY_ID_SEQ;
bjoern@1241: CREATE TABLE computations_country(
bjoern@1241:     id int PRIMARY KEY NOT NULL,
bjoern@1241:     river_id int REFERENCES rivers(id),
bjoern@1241:     text varchar(254)
bjoern@1241: );
bjoern@1241: SELECT AddGeometryColumn('computations_contry','geom','31466','MULTILINESTRING',2);
bjoern@1241: ALTER TABLE computations_country ALTER COLUMN id SET DEFAULT NEXTVAL('COMPUTATIONS_COUNTRY_ID_SEQ');
bjoern@1241: 
bjoern@1241: 
hans@1238: -- Hydrologie/UeSG/Messung
bjoern@1241: CREATE SEQUENCE MEASUREMENTS_ID_SEQ;
bjoern@1241: CREATE TABLE measurements (
bjoern@1241:     id int PRIMARY KEY NOT NULL,
bjoern@1241:     river_id int REFERENCES rivers(id),
bjoern@1241:     year varchar(254),
bjoern@1241:     oid varchar(40)
bjoern@1241: );
bjoern@1241: SELECT AddGeometryColumn('measurement','geom','31466','MULTILINESTRING',2);
bjoern@1241: ALTER TABLE measurements ALTER COLUMN id SET DEFAULT NEXTVAL('MEASUREMENTS_ID_SEQ');
hans@1238: 
ingo@1230: END;