Mercurial > dive4elements > river
view flys-backend/doc/schema/postgresql-spatial.sql @ 2360:9df06b88c079
Added model class for relation 'river_axes_km'.
flys-backend/trunk@3133 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author | Ingo Weinzierl <ingo.weinzierl@intevation.de> |
---|---|
date | Tue, 01 Nov 2011 15:30:38 +0000 |
parents | b8e01307c138 |
children | a8f408fdf58c |
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 int NOT NULL DEFAULT 0, 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, oid int, river_id int REFERENCES rivers(id), hws_facility VARCHAR(256), typ VARCHAR(256) ); SELECT AddGeometryColumn('hws','geom',31466,'LINESTRING',2); ALTER TABLE hw ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ'); END;