Mercurial > dive4elements > river
diff backend/doc/schema/postgresql-spatial.sql @ 8943:71b17f731762
Same data types, precision and scale as in Oracle (as far as possible)
author | mschaefer |
---|---|
date | Tue, 13 Mar 2018 09:42:41 +0100 |
parents | cc53aae06303 |
children | f89fb9e9abad |
line wrap: on
line diff
--- a/backend/doc/schema/postgresql-spatial.sql Wed Mar 07 17:36:04 2018 +0100 +++ b/backend/doc/schema/postgresql-spatial.sql Tue Mar 13 09:42:41 2018 +0100 @@ -1,7 +1,10 @@ BEGIN; +--FIXME: Adjust precision and scale to reasonable numbers +--FIXME: Make precision and scale equal for all km columns in the database + CREATE TABLE axis_kinds( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, name VARCHAR(64) ); INSERT INTO axis_kinds(id, name) VALUES (0, 'Unbekannt'); @@ -11,9 +14,9 @@ -- 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) ON DELETE CASCADE, - kind_id int REFERENCES axis_kinds(id) NOT NULL DEFAULT 0, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, + kind_id NUMERIC(9,0) REFERENCES axis_kinds(id) NOT NULL DEFAULT 0, name VARCHAR(64), path VARCHAR(256) ); @@ -25,10 +28,10 @@ -- 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) ON DELETE CASCADE, - km FLOAT8 NOT NULL, - fedstate_km FLOAT8, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, + km NUMERIC(7,3) NOT NULL, + fedstate_km NUMERIC(7,3), name VARCHAR(64), path VARCHAR(256) ); @@ -38,7 +41,7 @@ --Geodaesie/Querprofile/QP-Spuren/qps.shp CREATE TABLE cross_section_track_kinds( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, name VARCHAR(64) ); INSERT INTO cross_section_track_kinds(id, name) VALUES (0, 'Sonstige'); @@ -46,11 +49,11 @@ CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; CREATE TABLE cross_section_tracks ( - id int PRIMARY KEY NOT NULL, - river_id int REFERENCES rivers(id) ON DELETE CASCADE, - kind_id int REFERENCES cross_section_track_kinds(id) NOT NULL DEFAULT 0, - km FLOAT8 NOT NULL, - z FLOAT8 NOT NULL DEFAULT 0, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, + kind_id NUMERIC(9,0) REFERENCES cross_section_track_kinds(id) NOT NULL DEFAULT 0, + km NUMERIC(16,12) NOT NULL, + z NUMERIC(16,12) NOT NULL DEFAULT 0, name VARCHAR(64), path VARCHAR(256) ); @@ -58,7 +61,7 @@ ALTER TABLE cross_section_tracks ALTER COLUMN id SET DEFAULT NEXTVAL('CROSS_SECTION_TRACKS_ID_SEQ'); CREATE TABLE building_kinds( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, name VARCHAR(64) ); INSERT INTO building_kinds(id, name) VALUES (0, 'Sonstige'); @@ -69,12 +72,12 @@ -- Geodaesie/Bauwerke CREATE SEQUENCE BUILDINGS_ID_SEQ; CREATE TABLE buildings ( - id int PRIMARY KEY NOT NULL, - river_id int REFERENCES rivers(id) ON DELETE CASCADE, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, description VARCHAR(256), -- Name taken from attributes, name VARCHAR(256), -- The layername - km FLOAT8, - kind_id int REFERENCES building_kinds(id) NOT NULL DEFAULT 0, + km NUMERIC(15,11), + kind_id NUMERIC(9,0) REFERENCES building_kinds(id) NOT NULL DEFAULT 0, path VARCHAR(256) ); SELECT AddGeometryColumn('buildings', 'geom', 31467, 'LINESTRING', 2); @@ -84,11 +87,11 @@ -- Geodaesie/Festpunkte/Festpunkte.shp CREATE SEQUENCE FIXPOINTS_ID_SEQ; CREATE TABLE fixpoints ( - id int PRIMARY KEY NOT NULL, - river_id int REFERENCES rivers(id) ON DELETE CASCADE, - x FLOAT8, - y FLOAT8, - km FLOAT8 NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, + x NUMERIC(15,11), + y NUMERIC(15,11), + km NUMERIC(15,11) NOT NULL, HPGP VARCHAR(64), name VARCHAR(64), path VARCHAR(256) @@ -99,7 +102,7 @@ -- Hydrologie/Hydr. Grenzen/talaue.shp CREATE TABLE floodplain_kinds( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, name VARCHAR(64) ); INSERT INTO floodplain_kinds(id, name) VALUES (0, 'Sonstige'); @@ -107,9 +110,9 @@ CREATE SEQUENCE FLOODPLAIN_ID_SEQ; CREATE TABLE floodplain ( - id int PRIMARY KEY NOT NULL, - river_id int REFERENCES rivers(id) ON DELETE CASCADE, - kind_id int REFERENCES floodplain_kinds(id) NOT NULL DEFAULT 0, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, + kind_id NUMERIC(9,0) REFERENCES floodplain_kinds(id) NOT NULL DEFAULT 0, name VARCHAR(64), path VARCHAR(256) ); @@ -120,16 +123,16 @@ -- Geodaesie/Hoehenmodelle/* CREATE SEQUENCE DEM_ID_SEQ; CREATE TABLE dem ( - id int PRIMARY KEY NOT NULL, - river_id int REFERENCES rivers(id) ON DELETE CASCADE, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, name VARCHAR(64), - range_id INT REFERENCES ranges(id) ON DELETE CASCADE, - time_interval_id INT REFERENCES time_intervals(id), + range_id NUMERIC(9,0) REFERENCES ranges(id) ON DELETE CASCADE, + time_interval_id NUMERIC(9,0) REFERENCES time_intervals(id), projection VARCHAR(32), - srid int NOT NULL, + srid NUMERIC(9,0) NOT NULL, elevation_state VARCHAR(32), format VARCHAR(32), - border_break BOOLEAN NOT NULL DEFAULT FALSE, + border_break NUMERIC(1,0) NOT NULL DEFAULT 0, resolution VARCHAR(16), description VARCHAR(256), path VARCHAR(256) NOT NULL @@ -139,7 +142,7 @@ -- Static lookup tables for Hochwasserschutzanlagen CREATE TABLE hws_kinds ( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, kind VARCHAR(64) NOT NULL ); INSERT INTO hws_kinds (id, kind) VALUES (1, 'Durchlass'); @@ -147,7 +150,7 @@ INSERT INTO hws_kinds (id, kind) VALUES (3, 'Graben'); CREATE TABLE fed_states ( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, name VARCHAR(23) NOT NULL ); INSERT INTO fed_states (id, name) VALUES (1, 'Bayern'); @@ -170,19 +173,19 @@ --Hydrologie/HW-Schutzanlagen/*Linien.shp CREATE SEQUENCE HWS_LINES_ID_SEQ; CREATE TABLE hws_lines ( - id int PRIMARY KEY NOT NULL, - ogr_fid int, - kind_id int REFERENCES hws_kinds(id) DEFAULT 2, - fed_state_id int REFERENCES fed_states(id), - river_id int REFERENCES rivers(id) ON DELETE CASCADE, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + ogr_fid NUMERIC(9,0), + kind_id NUMERIC(9,0) REFERENCES hws_kinds(id) DEFAULT 2, + fed_state_id NUMERIC(9,0) REFERENCES fed_states(id), + river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, name VARCHAR(256), path VARCHAR(256), - official INT DEFAULT 0, + official NUMERIC(9,0) DEFAULT 0, agency VARCHAR(256), range VARCHAR(256), - shore_side INT DEFAULT 0, + shore_side NUMERIC(9,0) DEFAULT 0, source VARCHAR(256), - status_date TIMESTAMP, + status_date TIMESTAMP(0), description VARCHAR(256) ); SELECT AddGeometryColumn('hws_lines', 'geom', 31467, 'MULTILINESTRING', 3); @@ -193,25 +196,25 @@ --Hydrologie/HW-Schutzanlagen/*Punkte.shp CREATE SEQUENCE HWS_POINTS_ID_SEQ; CREATE TABLE hws_points ( - id int PRIMARY KEY NOT NULL, - ogr_fid int, - kind_id int REFERENCES hws_kinds(id) DEFAULT 2, - fed_state_id int REFERENCES fed_states(id), - river_id int REFERENCES rivers(id) ON DELETE CASCADE, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + ogr_fid NUMERIC(9,0), + kind_id NUMERIC(9,0) REFERENCES hws_kinds(id) DEFAULT 2, + fed_state_id NUMERIC(9,0) REFERENCES fed_states(id), + river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, name VARCHAR, path VARCHAR, - official INT DEFAULT 0, + official NUMERIC(9,0) DEFAULT 0, agency VARCHAR, range VARCHAR, - shore_side INT DEFAULT 0, + shore_side NUMERIC(9,0) DEFAULT 0, source VARCHAR, status_date VARCHAR, description VARCHAR, - freeboard FLOAT8, - dike_km FLOAT8, - z FLOAT8, - z_target FLOAT8, - rated_level FLOAT8 + freeboard NUMERIC(19,5), + dike_km NUMERIC(19,5), + z NUMERIC(19,5), + z_target NUMERIC(19,5), + rated_level NUMERIC(19,5) ); SELECT AddGeometryColumn('hws_points', 'geom', 31467, 'POINT', 2); @@ -220,7 +223,7 @@ -- --Hydrologie/UeSG CREATE TABLE floodmap_kinds ( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, name varchar(64) NOT NULL ); INSERT INTO floodmap_kinds VALUES (200, 'Messung'); @@ -231,14 +234,14 @@ CREATE SEQUENCE FLOODMAPS_ID_SEQ; CREATE TABLE floodmaps ( - id int PRIMARY KEY NOT NULL, - river_id int REFERENCES rivers(id) ON DELETE CASCADE, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, name varchar(64) NOT NULL, - kind int NOT NULL REFERENCES floodmap_kinds(id), - diff FLOAT8, - count int, - area FLOAT8, - perimeter FLOAT8, + kind NUMERIC(9,0) NOT NULL REFERENCES floodmap_kinds(id), + diff NUMERIC(19,5), + count NUMERIC(9,0), + area NUMERIC(19,5), + perimeter NUMERIC(19,5), waterbody varchar(64), path VARCHAR(256), source varchar(64) @@ -247,7 +250,7 @@ ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ'); CREATE TABLE sectie_kinds ( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, name VARCHAR(64) NOT NULL ); INSERT INTO sectie_kinds (id, name) VALUES (0, 'nicht berücksichtigt'); @@ -256,7 +259,7 @@ INSERT INTO sectie_kinds (id, name) VALUES (3, 'Vorland'); CREATE TABLE sobek_kinds ( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, name VARCHAR(64) NOT NULL ); INSERT INTO sobek_kinds (id, name) VALUES (0, 'nicht berücksichtigt'); @@ -264,7 +267,7 @@ INSERT INTO sobek_kinds (id, name) VALUES (2, 'nicht durchströmt'); CREATE TABLE boundary_kinds ( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, name VARCHAR(64) NOT NULL ); INSERT INTO boundary_kinds (id, name) VALUES (0, 'Unbekannt'); @@ -274,12 +277,12 @@ CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ; CREATE TABLE hydr_boundaries ( - id int PRIMARY KEY NOT NULL, - river_id int REFERENCES rivers(id) ON DELETE CASCADE, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, name VARCHAR(255), - kind int REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0, - sectie int REFERENCES sectie_kinds(id), - sobek int REFERENCES sobek_kinds(id), + kind NUMERIC(9,0) REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0, + sectie NUMERIC(9,0) REFERENCES sectie_kinds(id), + sobek NUMERIC(9,0) REFERENCES sobek_kinds(id), path VARCHAR(256) ); SELECT AddGeometryColumn('hydr_boundaries','geom',31467,'MULTILINESTRING',3); @@ -288,12 +291,12 @@ CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ; CREATE TABLE hydr_boundaries_poly ( - id int PRIMARY KEY NOT NULL, - river_id int REFERENCES rivers(id) ON DELETE CASCADE, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, name VARCHAR(255), - kind int REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0, - sectie int REFERENCES sectie_kinds(id), - sobek int REFERENCES sobek_kinds(id), + kind NUMERIC(9,0) REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0, + sectie NUMERIC(9,0) REFERENCES sectie_kinds(id), + sobek NUMERIC(9,0) REFERENCES sobek_kinds(id), path VARCHAR(256) ); SELECT AddGeometryColumn('hydr_boundaries_poly','geom',31467,'MULTIPOLYGON',3); @@ -301,7 +304,7 @@ CREATE TABLE jetty_kinds( - id int PRIMARY KEY NOT NULL, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, name VARCHAR(64) ); INSERT INTO jetty_kinds VALUES (0, 'Buhnenkopf'); @@ -310,10 +313,10 @@ CREATE SEQUENCE JETTIES_ID_SEQ; CREATE TABLE jetties ( - id int PRIMARY KEY NOT NULL, - river_id int REFERENCES rivers(id) ON DELETE CASCADE, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, path VARCHAR(256), - kind_id int REFERENCES jetty_kinds(id), + kind_id NUMERIC(9,0) REFERENCES jetty_kinds(id), km FLOAT8, z FLOAT8 ); @@ -322,13 +325,13 @@ CREATE SEQUENCE FLOOD_MARKS_ID_SEQ; CREATE TABLE flood_marks ( - id int PRIMARY KEY NOT NULL, - river_id int REFERENCES rivers(id) ON DELETE CASCADE, + id NUMERIC(9,0) PRIMARY KEY NOT NULL, + river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE, path VARCHAR(256), - km FLOAT8, - z FLOAT8, + km NUMERIC(7,3), + z NUMERIC(16,12), location VARCHAR(64), - year int + year NUMERIC(4,0) ); SELECT AddGeometryColumn('flood_marks','geom',31467,'POINT',2); ALTER TABLE flood_marks ALTER COLUMN id SET DEFAULT NEXTVAL('FLOOD_MARKS_ID_SEQ');