# HG changeset patch # User Bjoern Schilberg # Date 1315833605 0 # Node ID f68a0504dfb631ba910567a788e7054ff2ec9f58 # Parent 2ef8b67bd68aa892b4b45bca770e5eeb17887f5c Postgresql spatial scheme extended. flys-backend/trunk@2706 c6561f87-3c4e-4783-a992-168aeb5c3f6f diff -r 2ef8b67bd68a -r f68a0504dfb6 flys-backend/ChangeLog --- a/flys-backend/ChangeLog Fri Sep 02 13:49:46 2011 +0000 +++ b/flys-backend/ChangeLog Mon Sep 12 13:20:05 2011 +0000 @@ -1,3 +1,12 @@ +2011-09-12 Bjoern Schilberg + + * doc/schema/postgresql-spatial.sql: Added initial schemas for + Hydrologie/Einzugsgebiete, Hydrologie/HW-Schutzanlagen, Hydrologie/Hydr. + Grenzen/Linien, BfG/hauptoeff_*.shp, BfG/MNQ-*.shp, + BfG/modellgrenze*.shp, BfG/uferlinie.shp, BfG/vorland_*.shp, + Hydrologie/Streckendaten, Hydrologie/UeSG/Berechnung, + Hydrologie/UeSG/Messung + 2011-09-02 Ingo Weinzierl * doc/schema/postgresql-spatial.sql: Removed SERIALs from schema. diff -r 2ef8b67bd68a -r f68a0504dfb6 flys-backend/doc/schema/postgresql-spatial.sql --- a/flys-backend/doc/schema/postgresql-spatial.sql Fri Sep 02 13:49:46 2011 +0000 +++ b/flys-backend/doc/schema/postgresql-spatial.sql Mon Sep 12 13:20:05 2011 +0000 @@ -74,7 +74,6 @@ -- Geodaesie/Hoehenmodelle/* --- XXX DGM in englisch DEM, um dies zu vereinheitlichen CREATE SEQUENCE DEM_ID_SEQ; CREATE TABLE dem ( id int PRIMARY KEY NOT NULL, @@ -88,25 +87,161 @@ ALTER TABLE dem ALTER COLUMN id SET DEFAULT NEXTVAL('DEM_ID_SEQ'); --- TODO --- Flussachse+km/km - --- TODO --- Hydrologie/Einzugsgebiete - --- TODO --- Hydrologie/HW-Schutzanlagen +-- 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 --- TODO +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/Einzugsgebiete/b1-polygon.shp + -- Nur fuer saar vorhanden + -- IDEE alle Begrenzungspolygone in eine Tabelle. +CREATE SEQUENCE BOUNDARYPOLYS_ID_SEQ; +CREATE TABLE boundarypolys ( + id int PRIMARY KEY NOT NULL, + river_id int REFERENCES rivers(id) +); +SELECT AddGeometryColumn('boundarypolys','geom',31466,'MULTIPOLYGON',2); +ALTER TABLE boundarypolys ALTER COLUMN id SET DEFAULT NEXTVAL('BOUNDARYPOLYS_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 + -- BfG/boeschung_*.shp +CREATE SEQUENCE BANKS_ID_SEQ; +CREATE TABLE banks ( + id int PRIMARY KEY NOT NULL, + river_id int REFERENCES rivers(id) +); +SELECT AddGeometryColumn('banks','geom',31466,'MULTILINESTRING',2); +ALTER TABLE banks ALTER COLUMN id SET DEFAULT NEXTVAL('BANKS_ID_SEQ'); --- TODO + -- BfG/hauptoeff_*.shp +CREATE SEQUENCE MAINSPANS_ID_SEQ; +CREATE TABLE mainspans( + id int PRIMARY KEY NOT NULL, + river_id int REFERENCES rivers(id) +); +SELECT AddGeometryColumn('mainspans','geom',31466,'MULTILINESTRING',2); +ALTER TABLE mainspans ALTER COLUMN id SET DEFAULT NEXTVAL('MAINSPANS_ID_SEQ'); + + -- BfG/MNQ-*.shp +CREATE SEQUENCE MNQ_ID_SEQ; +CREATE TABLE mnq (gid serial PRIMARY KEY, + id int PRIMARY KEY NOT NULL, + river_id int REFERENCES rivers(id), + haltung varchar(16) +); +SELECT AddGeometryColumn('mnq', 'the_geom',31466,'MULTIPOLYGON',2); +ALTER TABLE mnq ALTER COLUMN id SET DEFAULT NEXTVAL('MNQ_ID_SEQ'); + + -- BfG/modellgrenze*.shp +CREATE SEQUENCE MODELBOUNDARY_ID_SEQ; +CREATE TABLE modelboundary ( + id int PRIMARY KEY NOT NULL, + river_id int REFERENCES rivers(id) +); +SELECT AddGeometryColumn('modelboundary','geom',31466,'MULTILINESTRING',2); +ALTER TABLE modelboundary ALTER COLUMN id SET DEFAULT NEXTVAL('MODELBOUNDARY_ID_SEQ'); + + -- TODO: Klaeren ob benoetigt, da einzel Geometrien in Tabelle vorland. + -- BfG/saar-sld-vorland.shp + + -- BfG/uferlinie.shp +CREATE SEQUENCE SHORELINE_ID_SEQ; +CREATE TABLE shoreline( + id int PRIMARY KEY NOT NULL, + river_id int REFERENCES rivers(id) +); +SELECT AddGeometryColumn('shoreline','geom',31466,'MULTILINESTRING',2); +ALTER TABLE shoreline ALTER COLUMN id SET DEFAULT NEXTVAL('SHORELINE_ID_SEQ'); + + -- BfG/vorland_*.shp +CREATE SEQUENCE FORELAND_ID_SEQ; +CREATE TABLE foreland( + id int PRIMARY KEY NOT NULL, + river_id int REFERENCES rivers(id) +); +SELECT AddGeometryColumn('foreland','geom',31466,'MULTILINESTRING',2); +ALTER TABLE foreland ALTER COLUMN id SET DEFAULT NEXTVAL('FORELANDS_ID_SEQ'); + + -- 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'); --- TODO -- 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'); --- TODO + -- 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;