Mercurial > dive4elements > river
view flys-backend/doc/schema/postgresql-spatial.sql @ 2796:f3b4e32b5db6
Improved the db schema for storing existing floodmaps.
flys-backend/trunk@4125 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author | Ingo Weinzierl <ingo.weinzierl@intevation.de> |
---|---|
date | Tue, 06 Mar 2012 11:54:41 +0000 |
parents | 918877ee70e7 |
children | e41d03bf9807 |
line wrap: on
line source
BEGIN; -- Geodaesie/Flussachse+km/achse CREATE SEQUENCE RIVER_AXES_ID_SEQ; CREATE TABLE river_axes ( id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id), kind int NOT NULL DEFAULT 0 ); SELECT AddGeometryColumn('river_axes', 'geom', 31466, 'LINESTRING', 2); ALTER TABLE river_axes ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_ID_SEQ'); -- TODO: TestMe. -- Geodaesie/Flussachse+km/km.shp CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ; CREATE TABLE river_axes_km ( id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id), km NUMERIC NOT NULL ); SELECT AddGeometryColumn('river_axes_km', 'geom', 31466, 'POINT', 2); ALTER TABLE river_axes_km ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_KM_ID_SEQ'); --Geodaesie/Querprofile/QP-Spuren/qps.shp CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; CREATE TABLE cross_section_tracks ( id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id), km NUMERIC NOT NULL, z NUMERIC NOT NULL DEFAULT 0 ); SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31466, 'LINESTRING', 2); ALTER TABLE cross_section_tracks ALTER COLUMN id SET DEFAULT NEXTVAL('CROSS_SECTION_TRACKS_ID_SEQ'); -- Geodaesie/Linien/rohre-und-spreen CREATE SEQUENCE LINES_ID_SEQ; CREATE TABLE lines ( id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id), kind VARCHAR(16) NOT NULL, z NUMERIC DEFAULT 0 ); SELECT AddGeometryColumn('lines', 'geom', 31466, 'LINESTRING', 4); ALTER TABLE lines ALTER COLUMN id SET DEFAULT NEXTVAL('LINES_ID_SEQ'); -- 'kind': -- 0: ROHR1 -- 1: DAMM -- Geodaesie/Bauwerke/Wehre.shp CREATE SEQUENCE BUILDINGS_ID_SEQ; CREATE TABLE buildings ( id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id), name VARCHAR(256) ); SELECT AddGeometryColumn('buildings', 'geom', 31466, 'LINESTRING', 2); ALTER TABLE buildings ALTER COLUMN id SET DEFAULT NEXTVAL('BUILDINGS_ID_SEQ'); -- Geodaesie/Festpunkte/Festpunkte.shp CREATE SEQUENCE FIXPOINTS_ID_SEQ; CREATE TABLE fixpoints ( id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id), x int, y int, km NUMERIC NOT NULL, HPGP VARCHAR(2) ); SELECT AddGeometryColumn('fixpoints', 'geom', 31466, 'POINT', 2); ALTER TABLE fixpoints ALTER COLUMN id SET DEFAULT NEXTVAL('FIXPOINTS_ID_SEQ'); -- Hydrologie/Hydr. Grenzen/talaue.shp CREATE SEQUENCE FLOODPLAIN_ID_SEQ; CREATE TABLE floodplain ( id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id) ); SELECT AddGeometryColumn('floodplain', 'geom', 31466, 'POLYGON', 2); ALTER TABLE floodplain ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODPLAIN_ID_SEQ'); -- Geodaesie/Hoehenmodelle/* CREATE SEQUENCE DEM_ID_SEQ; CREATE TABLE dem ( id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id), -- XXX Should we use the ranges table instead? lower NUMERIC, upper NUMERIC, path VARCHAR(256), UNIQUE (river_id, lower, upper) ); ALTER TABLE dem ALTER COLUMN id SET DEFAULT NEXTVAL('DEM_ID_SEQ'); -- Hydrologie/Einzugsgebiete/EZG.shp CREATE SEQUENCE CATCHMENT_ID_SEQ; CREATE TABLE catchment ( id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id), area NUMERIC, name VARCHAR(256) ); SELECT AddGeometryColumn('catchment','geom',31466,'POLYGON',2); ALTER TABLE catchment ALTER COLUMN id SET DEFAULT NEXTVAL('CATCHMENT_ID_SEQ'); --Hydrologie/HW-Schutzanlagen/hws.shp CREATE SEQUENCE HWS_ID_SEQ; CREATE TABLE hws ( id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id), hws_facility VARCHAR(256), type VARCHAR(256) ); SELECT AddGeometryColumn('hws','geom',31466,'LINESTRING',2); ALTER TABLE hws ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ'); --Hydrologie/UeSG CREATE SEQUENCE FLOODMAPS_ID_SEQ; CREATE TABLE floodmaps ( id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id), kind int NOT NULL, diff NUMERIC, count int, area NUMERIC, perimeter NUMERIC ); ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ'); CREATE SEQUENCE POLYGONS_SEQ; CREATE TABLE polygons ( id int PRIMARY KEY NOT NULL ); SELECT AddGeometryColumn('polygons', 'geom', 31466, 'POLYGON', 2); ALTER TABLE polygons ALTER COLUMN id SET DEFAULT NEXTVAL('POLYGONS_SEQ'); CREATE SEQUENCE FLOODMAP_GEOMS_SEQ; CREATE TABLE floodmap_geoms ( id int PRIMARY KEY NOT NULL, floodmaps_id int NOT NULL REFERENCES floodmaps(id), polygons_id int NOT NULL REFERENCES polygons(id) ); ALTER TABLE floodmap_geoms ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAP_GEOMS_SEQ'); END;