Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql-spatial.sql @ 1238:35740d675866

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added mappings to existing data in file system (based on river Saar); added TODOs for missing tables/mappings flys-backend/trunk@2629 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Hans Plum <hans.plum@intevation.de>
date Thu, 01 Sep 2011 09:38:05 +0000
parents 774dff3ee546
children 07882568cef8
comparison
equal deleted inserted replaced
1237:774dff3ee546 1238:35740d675866
1 BEGIN; 1 BEGIN;
2 2
3 -- Geodaesie/Flussachse+km/achse
3 CREATE TABLE river_axes ( 4 CREATE TABLE river_axes (
4 id SERIAL PRIMARY KEY NOT NULL, 5 id SERIAL PRIMARY KEY NOT NULL,
5 river_id int REFERENCES rivers(id), 6 river_id int REFERENCES rivers(id),
6 kind int NOT NULL DEFAULT 0 7 kind int NOT NULL DEFAULT 0
7 ); 8 );
8 SELECT AddGeometryColumn('river_axes', 'geom', 31466, 'LINESTRING', 2); 9 SELECT AddGeometryColumn('river_axes', 'geom', 31466, 'LINESTRING', 2);
9 10
10 11
12 -- Geodaesie/Querprofile/*
11 CREATE TABLE cross_section_tracks ( 13 CREATE TABLE cross_section_tracks (
12 id SERIAL PRIMARY KEY NOT NULL, 14 id SERIAL PRIMARY KEY NOT NULL,
13 river_id int REFERENCES rivers(id), 15 river_id int REFERENCES rivers(id),
14 km NUMERIC NOT NULL, 16 km NUMERIC NOT NULL,
15 z NUMERIC NOT NULL DEFAULT 0 17 z NUMERIC NOT NULL DEFAULT 0
16 ); 18 );
17 SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31466, 'LINESTRING', 2); 19 SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31466, 'LINESTRING', 2);
18 20
19 21
22 -- Geodaesie/Linien/rohre-und-spreen
20 CREATE TABLE lines ( 23 CREATE TABLE lines (
21 id SERIAL PRIMARY KEY NOT NULL, 24 id SERIAL PRIMARY KEY NOT NULL,
22 river_id int REFERENCES rivers(id), 25 river_id int REFERENCES rivers(id),
23 kind int NOT NULL DEFAULT 0, 26 kind int NOT NULL DEFAULT 0,
24 z NUMERIC DEFAULT 0 27 z NUMERIC DEFAULT 0
27 -- 'kind': 30 -- 'kind':
28 -- 0: ROHR1 31 -- 0: ROHR1
29 -- 1: DAMM 32 -- 1: DAMM
30 33
31 34
35 -- Geodaesie/Bauwerke/Wehre.shp
32 CREATE TABLE buildings ( 36 CREATE TABLE buildings (
33 id SERIAL PRIMARY KEY NOT NULL, 37 id SERIAL PRIMARY KEY NOT NULL,
34 river_id int REFERENCES rivers(id), 38 river_id int REFERENCES rivers(id),
35 name VARCHAR(50) 39 name VARCHAR(50)
36 ); 40 );
37 SELECT AddGeometryColumn('buildings', 'geom', 31466, 'LINESTRING', 2); 41 SELECT AddGeometryColumn('buildings', 'geom', 31466, 'LINESTRING', 2);
38 42
39 43
44 -- Geodaesie/Festpunkte/Festpunkte.shp
40 CREATE TABLE fixpoints ( 45 CREATE TABLE fixpoints (
41 id SERIAL PRIMARY KEY NOT NULL, 46 id SERIAL PRIMARY KEY NOT NULL,
42 river_id int REFERENCES rivers(id), 47 river_id int REFERENCES rivers(id),
43 x int, 48 x int,
44 y int, 49 y int,
46 HPGP VARCHAR(2) 51 HPGP VARCHAR(2)
47 ); 52 );
48 SELECT AddGeometryColumn('fixpoints', 'geom', 31466, 'POINT', 2); 53 SELECT AddGeometryColumn('fixpoints', 'geom', 31466, 'POINT', 2);
49 54
50 55
51 -- Talaue 56 -- Hydrologie/Hydr. Grenzen/talaue.shp
52 CREATE TABLE floodplain ( 57 CREATE TABLE floodplain (
53 id SERIAL PRIMARY KEY NOT NULL, 58 id SERIAL PRIMARY KEY NOT NULL,
54 river_id int REFERENCES rivers(id) 59 river_id int REFERENCES rivers(id)
55 ); 60 );
56 SELECT AddGeometryColumn('floodplain', 'geom', 31466, 'MULTIPOLYGON', 2); 61 SELECT AddGeometryColumn('floodplain', 'geom', 31466, 'MULTIPOLYGON', 2);
57 62
58 63
64 -- Geodaesie/Hoehenmodelle/*
59 CREATE TABLE dgm ( 65 CREATE TABLE dgm (
60 id SERIAL PRIMARY KEY NOT NULL, 66 id SERIAL PRIMARY KEY NOT NULL,
61 river_id int REFERENCES rivers(id), 67 river_id int REFERENCES rivers(id),
62 -- XXX Should we use the ranges table instead? 68 -- XXX Should we use the ranges table instead?
63 lower NUMERIC, 69 lower NUMERIC,
64 upper NUMERIC, 70 upper NUMERIC,
65 path VARCHAR(256), 71 path VARCHAR(256),
66 UNIQUE (river_id, lower, upper) 72 UNIQUE (river_id, lower, upper)
67 ); 73 );
68 74
75
76 -- TODO
77 -- Flussachse+km/km
78
79 -- TODO
80 -- Hydrologie/Einzugsgebiete
81
82 -- TODO
83 -- Hydrologie/HW-Schutzanlagen
84
85 -- TODO
86 -- Hydrologie/Hydr. Grenzen/Linien
87
88 -- TODO
89 -- Hydrologie/Streckendaten
90
91 -- TODO
92 -- Hydrologie/UeSG/Berechnung
93
94 -- TODO
95 -- Hydrologie/UeSG/Messung
96
69 END; 97 END;
http://dive4elements.wald.intevation.org