ingo@1230: BEGIN;
ingo@1230: 
hans@1238: -- Geodaesie/Flussachse+km/achse
ingo@1230: CREATE TABLE river_axes (
ingo@1230:     id       SERIAL 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@1230: 
ingo@1230: 
hans@1238: -- Geodaesie/Querprofile/*
ingo@1230: CREATE TABLE cross_section_tracks (
ingo@1230:     id       SERIAL 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@1230: 
ingo@1230: 
hans@1238: -- Geodaesie/Linien/rohre-und-spreen
ingo@1230: CREATE TABLE lines (
ingo@1230:     id       SERIAL 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@1230: -- 'kind':
ingo@1230: -- 0: ROHR1
ingo@1230: -- 1: DAMM
ingo@1230: 
ingo@1230: 
hans@1238: -- Geodaesie/Bauwerke/Wehre.shp
ingo@1230: CREATE TABLE buildings (
ingo@1230:     id       SERIAL 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@1230: 
ingo@1230: 
hans@1238: -- Geodaesie/Festpunkte/Festpunkte.shp
ingo@1230: CREATE TABLE fixpoints (
ingo@1230:     id       SERIAL 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@1230: 
ingo@1236: 
hans@1238: -- Hydrologie/Hydr. Grenzen/talaue.shp
ingo@1237: CREATE TABLE floodplain (
ingo@1237:     id       SERIAL PRIMARY KEY NOT NULL,
ingo@1237:     river_id int REFERENCES rivers(id)
ingo@1237: );
ingo@1237: SELECT AddGeometryColumn('floodplain', 'geom', 31466, 'MULTIPOLYGON', 2);
ingo@1237: 
ingo@1237: 
hans@1238: -- Geodaesie/Hoehenmodelle/*
ingo@1236: CREATE TABLE dgm (
ingo@1236:     id       SERIAL 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@1236: 
hans@1238: 
hans@1238: -- TODO
hans@1238: -- Flussachse+km/km
hans@1238: 
hans@1238: -- TODO
hans@1238: -- Hydrologie/Einzugsgebiete
hans@1238: 
hans@1238: -- TODO
hans@1238: -- Hydrologie/HW-Schutzanlagen
hans@1238: 
hans@1238: -- TODO
hans@1238: -- Hydrologie/Hydr. Grenzen/Linien
hans@1238: 
hans@1238: -- TODO
hans@1238: -- Hydrologie/Streckendaten
hans@1238: 
hans@1238: -- TODO
hans@1238: -- Hydrologie/UeSG/Berechnung
hans@1238: 
hans@1238: -- TODO
hans@1238: -- Hydrologie/UeSG/Messung
hans@1238: 
ingo@1230: END;