Mercurial > dive4elements > river
view flys-backend/doc/schema/postgresql-spatial.sql @ 2336:60c722dc0304
Added right to create views.
flys-backend/trunk@2796 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author | Bjoern Schilberg <bjoern@intevation.de> |
---|---|
date | Wed, 21 Sep 2011 10:12:00 +0000 |
parents | 3ebc0a7d6793 |
children | b28ab244a77d |
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'); -- Geodaesie/Querprofile/* 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(50) ); 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, 'MULTIPOLYGON', 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 -- Hinweise zu ezg_saar.shp wird nicht importiert: -- CLASS: Integer (8.0) KLAEREN: wir die benoetigt? -- AREA: Real (19.8) laesst sich auch durch EZG.shp bestimmen -- PERIMETER: Real (19.8) laesst sich auch durch EZG.shp bestimmen CREATE SEQUENCE CATCHMENT_ID_SEQ; CREATE TABLE catchment ( id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id), "area" numeric, "name" VARCHAR(80) ); SELECT AddGeometryColumn('catchment','geom',31466,'POLYGON',2); ALTER TABLE catchment ALTER COLUMN id SET DEFAULT NEXTVAL('CATCHMENT_ID_SEQ'); -- Hydrologie/HW-Schutzanlagen -- Wird nicht benoetigt, stattdessen verwenden wir -- Gewaesser/Saar/Geodaesie/Linien/rohre-und-sperren.shp -- hws.shp beinhaltet die Geometrien von: -- HWS-Lisdorf.shp -- hws_anlage -- HWS-Mettlach.shp -- maßnahme -> hws_anlage -- HWS-Rehlingen.shp -- hw -> hws_anlage -- HWS_Saarburg.shp -- höhe? bauart? -- HWS-Schoden-Rhl-Pf.shp -- hws_anlage -- HWS_Schoden.shp --höhe? bauart? -- HWS-Serrig.shp --hws_anlage -- CREATE SEQUENCE HWS_EZG_ID_SEQ; -- CREATE TABLE hws ( -- id int PRIMARY KEY NOT NULL, -- oid int, -- river_id int REFERENCES rivers(id), -- hws_facility VARCHAR(40), -- typ VARCHAR(254) -- ); -- SELECT AddGeometryColumn('hws','geom',31466,'MULTILINESTRING',2); -- ALTER TABLE hw ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ'); -- Hydrologie/Hydr. Grenzen/Linien -- Hydrologie/Streckendaten -- pegellage_saar.shp CREATE SEQUENCE LEVELPOSITION_ID_SEQ; CREATE TABLE levelposition ( id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id), x numeric(10,0), y numeric(10,0), name varchar(254) ); SELECT AddGeometryColumn('levelposition','geom','31466','POINT',2); ALTER TABLE levelposition ALTER COLUMN id SET DEFAULT NEXTVAL('LEVELPOSITION_ID_SEQ'); -- Hydrologie/UeSG/Berechnung -- Berechnung/Aktuell/BfG CREATE SEQUENCE COMPUTATIONS_BFG_ID_SEQ; CREATE TABLE computations_bfg ( id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id), section varchar(254), area float8, perimeter float8 ); SELECT AddGeometryColumn('computations_bfg','geom','31466','MULTIPOLYGON',2); ALTER TABLE computations_bfg ALTER COLUMN id SET DEFAULT NEXTVAL('COMPUTATIONS_BFG_ID_SEQ'); -- Berechnung/Aktuell/Land CREATE SEQUENCE COMPUTATIONS_COUNTRY_ID_SEQ; CREATE TABLE computations_country( id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id), text varchar(254) ); SELECT AddGeometryColumn('computations_contry','geom','31466','MULTILINESTRING',2); ALTER TABLE computations_country ALTER COLUMN id SET DEFAULT NEXTVAL('COMPUTATIONS_COUNTRY_ID_SEQ'); -- Hydrologie/UeSG/Messung CREATE SEQUENCE MEASUREMENTS_ID_SEQ; CREATE TABLE measurements ( id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id), year varchar(254), oid varchar(40) ); SELECT AddGeometryColumn('measurement','geom','31466','MULTILINESTRING',2); ALTER TABLE measurements ALTER COLUMN id SET DEFAULT NEXTVAL('MEASUREMENTS_ID_SEQ'); END;