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