Mercurial > dive4elements > river
view flys-backend/doc/schema/postgresql-spatial.sql @ 4380:19754e5227c8
Add facets for fixanalysis data from the datacage
Add facets for fixanalysis data from the datacage to the bed difference height
year, bed difference year and bed difference epoch output modes.
author | Björn Ricks <bjoern.ricks@intevation.de> |
---|---|
date | Fri, 02 Nov 2012 15:54:41 +0100 |
parents | f5912365619c |
children | 28f992c0f937 |
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, name VARCHAR(64), path VARCHAR(256) ); SELECT AddGeometryColumn('river_axes', 'geom', 31467, '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, name VARCHAR(64), path VARCHAR(256) ); SELECT AddGeometryColumn('river_axes_km', 'geom', 31467, '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, name VARCHAR(64), path VARCHAR(256) ); SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31467, '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, name VARCHAR(64), path VARCHAR(256) ); SELECT AddGeometryColumn('lines', 'geom', 31467, 'LINESTRING', 3); 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), path VARCHAR(256) ); SELECT AddGeometryColumn('buildings', 'geom', 31467, '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), name VARCHAR(64), path VARCHAR(256) ); SELECT AddGeometryColumn('fixpoints', 'geom', 31467, '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), name VARCHAR(64), path VARCHAR(256) ); SELECT AddGeometryColumn('floodplain', 'geom', 31467, '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? name VARCHAR(64), lower NUMERIC, upper NUMERIC, year_from VARCHAR(32) NOT NULL, year_to VARCHAR(32) NOT NULL, projection VARCHAR(32) NOT NULL, elevation_state VARCHAR(32), format VARCHAR(32), border_break BOOLEAN NOT NULL DEFAULT FALSE, resolution VARCHAR(16), description VARCHAR(256), path VARCHAR(256) ); 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), path VARCHAR(256) ); SELECT AddGeometryColumn('catchment','geom',31467,'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), name VARCHAR(64), path VARCHAR(256) ); SELECT AddGeometryColumn('hws','geom',31467,'LINESTRING',2); ALTER TABLE hws ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ'); -- --Hydrologie/UeSG -- -- 'kind' can be one of: -- 200 = Messung -- 111 = Berechnung->Aktuell->BfG -- 112 = Berechnung->Aktuell->Land -- 121 = Berechnung->Potenziell->BfG -- 122 = Berechnung->Potenziell->Land -- CREATE SEQUENCE FLOODMAPS_ID_SEQ; CREATE TABLE floodmaps ( id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id), name varchar(64) NOT NULL, kind int NOT NULL, diff real, count int, area real, perimeter real, path VARCHAR(256) ); SELECT AddGeometryColumn('floodmaps', 'geom', 31467, 'MULTIPOLYGON', 2); ALTER TABLE floodmaps DROP CONSTRAINT enforce_geotype_geom; ALTER TABLE floodmaps ADD CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POLYGON'::text OR geometrytype(geom) = 'MULTIPOLYGON'::text); ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ'); CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ; CREATE TABLE hydr_boundaries ( id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id), name VARCHAR(255), kind int, path VARCHAR(256) ); SELECT AddGeometryColumn('hydr_boundaries','geom',31467,'LINESTRING',3); ALTER TABLE hydr_boundaries ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_ID_SEQ'); CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ; CREATE TABLE hydr_boundaries_poly ( id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id), name VARCHAR(255), kind int, path VARCHAR(256) ); SELECT AddGeometryColumn('hydr_boundaries_poly','geom',31467,'POLYGON',3); ALTER TABLE hydr_boundaries_poly ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_POLY_ID_SEQ'); CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ; CREATE TABLE gauge_location ( id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id), name VARCHAR(255), path VARCHAR(256) ); SELECT AddGeometryColumn('gauge_location','geom',31467,'POINT',2); ALTER TABLE gauge_location ALTER COLUMN id SET DEFAULT NEXTVAL('GAUGE_LOCATION_ID_SEQ'); COMMIT;