Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql-spatial.sql @ 5214:b46df0609276

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
add lookup tables for floodplain and cross section tracks
author Tom Gottfried <tom@intevation.de>
date Mon, 11 Mar 2013 11:43:44 +0100
parents 908848b74d7e
children 170f514a4f29
comparison
equal deleted inserted replaced
5213:76818dc2c937 5214:b46df0609276
34 SELECT AddGeometryColumn('river_axes_km', 'geom', 31467, 'POINT', 2); 34 SELECT AddGeometryColumn('river_axes_km', 'geom', 31467, 'POINT', 2);
35 ALTER TABLE river_axes_km ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_KM_ID_SEQ'); 35 ALTER TABLE river_axes_km ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_KM_ID_SEQ');
36 36
37 37
38 --Geodaesie/Querprofile/QP-Spuren/qps.shp 38 --Geodaesie/Querprofile/QP-Spuren/qps.shp
39 CREATE TABLE cross_section_track_kinds(
40 id int PRIMARY KEY NOT NULL,
41 name VARCHAR(64)
42 );
43 INSERT INTO cross_section_track_kinds(id, name) VALUES (0, 'Sonstige');
44 INSERT INTO cross_section_track_kinds(id, name) VALUES (1, 'Aktuell');
45
39 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; 46 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
40 CREATE TABLE cross_section_tracks ( 47 CREATE TABLE cross_section_tracks (
41 id int PRIMARY KEY NOT NULL, 48 id int PRIMARY KEY NOT NULL,
42 river_id int REFERENCES rivers(id) ON DELETE CASCADE, 49 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
50 kind_id int REFERENCES cross_section_track_kinds(id) NOT NULL DEFAULT 0,
43 km FLOAT8 NOT NULL, 51 km FLOAT8 NOT NULL,
44 z FLOAT8 NOT NULL DEFAULT 0, 52 z FLOAT8 NOT NULL DEFAULT 0,
45 name VARCHAR(64), 53 name VARCHAR(64),
46 path VARCHAR(256) 54 path VARCHAR(256)
47 ); 55 );
76 SELECT AddGeometryColumn('fixpoints', 'geom', 31467, 'POINT', 2); 84 SELECT AddGeometryColumn('fixpoints', 'geom', 31467, 'POINT', 2);
77 ALTER TABLE fixpoints ALTER COLUMN id SET DEFAULT NEXTVAL('FIXPOINTS_ID_SEQ'); 85 ALTER TABLE fixpoints ALTER COLUMN id SET DEFAULT NEXTVAL('FIXPOINTS_ID_SEQ');
78 86
79 87
80 -- Hydrologie/Hydr. Grenzen/talaue.shp 88 -- Hydrologie/Hydr. Grenzen/talaue.shp
89 CREATE TABLE floodplain_kinds(
90 id int PRIMARY KEY NOT NULL,
91 name VARCHAR(64)
92 );
93 INSERT INTO floodplain_kinds(id, name) VALUES (0, 'Sonstige');
94 INSERT INTO floodplain_kinds(id, name) VALUES (1, 'Aktuell');
95
81 CREATE SEQUENCE FLOODPLAIN_ID_SEQ; 96 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
82 CREATE TABLE floodplain ( 97 CREATE TABLE floodplain (
83 id int PRIMARY KEY NOT NULL, 98 id int PRIMARY KEY NOT NULL,
84 river_id int REFERENCES rivers(id) ON DELETE CASCADE, 99 river_id int REFERENCES rivers(id) ON DELETE CASCADE,
100 kind_id int REFERENCES floodplain_kinds(id) NOT NULL DEFAULT 0,
85 name VARCHAR(64), 101 name VARCHAR(64),
86 path VARCHAR(256) 102 path VARCHAR(256)
87 ); 103 );
88 SELECT AddGeometryColumn('floodplain', 'geom', 31467, 'POLYGON', 2); 104 SELECT AddGeometryColumn('floodplain', 'geom', 31467, 'POLYGON', 2);
89 ALTER TABLE floodplain ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODPLAIN_ID_SEQ'); 105 ALTER TABLE floodplain ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODPLAIN_ID_SEQ');
http://dive4elements.wald.intevation.org