Mercurial > dive4elements > river
view flys-backend/doc/schema/postgresql-spatial.sql @ 4441:093f9333f66b
Fix logging statement
author | Björn Ricks <bjoern.ricks@intevation.de> |
---|---|
date | Wed, 07 Nov 2012 13:54:49 +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;