Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql-spatial.sql @ 3344:cb376f48dd37 2.8

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
merged flys-backend/2.8
author Thomas Arendsen Hein <thomas@intevation.de>
date Fri, 28 Sep 2012 12:14:36 +0200
parents e41d03bf9807
children 883dd49d5e74
comparison
equal deleted inserted replaced
3318:dbe2f85bf160 3344:cb376f48dd37
1 BEGIN;
2
3 -- Geodaesie/Flussachse+km/achse
4 CREATE SEQUENCE RIVER_AXES_ID_SEQ;
5 CREATE TABLE river_axes (
6 id int PRIMARY KEY NOT NULL,
7 river_id int REFERENCES rivers(id),
8 kind int NOT NULL DEFAULT 0
9 );
10 SELECT AddGeometryColumn('river_axes', 'geom', 31466, 'LINESTRING', 2);
11 ALTER TABLE river_axes ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_ID_SEQ');
12
13
14 -- TODO: TestMe.
15 -- Geodaesie/Flussachse+km/km.shp
16 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ;
17 CREATE TABLE river_axes_km (
18 id int PRIMARY KEY NOT NULL,
19 river_id int REFERENCES rivers(id),
20 km NUMERIC NOT NULL
21 );
22 SELECT AddGeometryColumn('river_axes_km', 'geom', 31466, 'POINT', 2);
23 ALTER TABLE river_axes_km ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_KM_ID_SEQ');
24
25
26 --Geodaesie/Querprofile/QP-Spuren/qps.shp
27 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
28 CREATE TABLE cross_section_tracks (
29 id int PRIMARY KEY NOT NULL,
30 river_id int REFERENCES rivers(id),
31 km NUMERIC NOT NULL,
32 z NUMERIC NOT NULL DEFAULT 0
33 );
34 SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31466, 'LINESTRING', 2);
35 ALTER TABLE cross_section_tracks ALTER COLUMN id SET DEFAULT NEXTVAL('CROSS_SECTION_TRACKS_ID_SEQ');
36
37
38 -- Geodaesie/Linien/rohre-und-spreen
39 CREATE SEQUENCE LINES_ID_SEQ;
40 CREATE TABLE lines (
41 id int PRIMARY KEY NOT NULL,
42 river_id int REFERENCES rivers(id),
43 kind VARCHAR(16) NOT NULL,
44 z NUMERIC DEFAULT 0
45 );
46 SELECT AddGeometryColumn('lines', 'geom', 31466, 'LINESTRING', 4);
47 ALTER TABLE lines ALTER COLUMN id SET DEFAULT NEXTVAL('LINES_ID_SEQ');
48 -- 'kind':
49 -- 0: ROHR1
50 -- 1: DAMM
51
52
53 -- Geodaesie/Bauwerke/Wehre.shp
54 CREATE SEQUENCE BUILDINGS_ID_SEQ;
55 CREATE TABLE buildings (
56 id int PRIMARY KEY NOT NULL,
57 river_id int REFERENCES rivers(id),
58 name VARCHAR(256)
59 );
60 SELECT AddGeometryColumn('buildings', 'geom', 31466, 'LINESTRING', 2);
61 ALTER TABLE buildings ALTER COLUMN id SET DEFAULT NEXTVAL('BUILDINGS_ID_SEQ');
62
63
64 -- Geodaesie/Festpunkte/Festpunkte.shp
65 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
66 CREATE TABLE fixpoints (
67 id int PRIMARY KEY NOT NULL,
68 river_id int REFERENCES rivers(id),
69 x int,
70 y int,
71 km NUMERIC NOT NULL,
72 HPGP VARCHAR(2)
73 );
74 SELECT AddGeometryColumn('fixpoints', 'geom', 31466, 'POINT', 2);
75 ALTER TABLE fixpoints ALTER COLUMN id SET DEFAULT NEXTVAL('FIXPOINTS_ID_SEQ');
76
77
78 -- Hydrologie/Hydr. Grenzen/talaue.shp
79 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
80 CREATE TABLE floodplain (
81 id int PRIMARY KEY NOT NULL,
82 river_id int REFERENCES rivers(id)
83 );
84 SELECT AddGeometryColumn('floodplain', 'geom', 31466, 'POLYGON', 2);
85 ALTER TABLE floodplain ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODPLAIN_ID_SEQ');
86
87
88 -- Geodaesie/Hoehenmodelle/*
89 CREATE SEQUENCE DEM_ID_SEQ;
90 CREATE TABLE dem (
91 id int PRIMARY KEY NOT NULL,
92 river_id int REFERENCES rivers(id),
93 -- XXX Should we use the ranges table instead?
94 lower NUMERIC,
95 upper NUMERIC,
96 path VARCHAR(256),
97 UNIQUE (river_id, lower, upper)
98 );
99 ALTER TABLE dem ALTER COLUMN id SET DEFAULT NEXTVAL('DEM_ID_SEQ');
100
101
102 -- Hydrologie/Einzugsgebiete/EZG.shp
103 CREATE SEQUENCE CATCHMENT_ID_SEQ;
104 CREATE TABLE catchment (
105 id int PRIMARY KEY NOT NULL,
106 river_id int REFERENCES rivers(id),
107 area NUMERIC,
108 name VARCHAR(256)
109 );
110 SELECT AddGeometryColumn('catchment','geom',31466,'POLYGON',2);
111 ALTER TABLE catchment ALTER COLUMN id SET DEFAULT NEXTVAL('CATCHMENT_ID_SEQ');
112
113
114 --Hydrologie/HW-Schutzanlagen/hws.shp
115 CREATE SEQUENCE HWS_ID_SEQ;
116 CREATE TABLE hws (
117 id int PRIMARY KEY NOT NULL,
118 river_id int REFERENCES rivers(id),
119 hws_facility VARCHAR(256),
120 type VARCHAR(256)
121 );
122 SELECT AddGeometryColumn('hws','geom',31466,'LINESTRING',2);
123 ALTER TABLE hws ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ');
124
125
126 --
127 --Hydrologie/UeSG
128 --
129 -- 'kind' can be one of:
130 -- 200 = Messung
131 -- 111 = Berechnung->Aktuell->BfG
132 -- 112 = Berechnung->Aktuell->Land
133 -- 121 = Berechnung->Potenziell->BfG
134 -- 122 = Berechnung->Potenziell->Land
135 --
136 CREATE SEQUENCE FLOODMAPS_SEQ;
137 CREATE FUNCTION floodmaps_id_func() RETURNS trigger AS $floodmaps_id_func$
138 BEGIN
139 NEW.id := nextval('floodmaps_seq');
140 RETURN NEW;
141 END;
142 $floodmaps_id_func$ LANGUAGE plpgsql;
143
144 CREATE TABLE floodmaps (
145 id int PRIMARY KEY NOT NULL,
146 river_id int REFERENCES rivers(id),
147 name varchar(64) NOT NULL,
148 kind int NOT NULL,
149 diff real,
150 count int,
151 area real,
152 perimeter real
153 );
154 SELECT AddGeometryColumn('floodmaps', 'geom', 31466, 'MULTIPOLYGON', 2);
155 ALTER TABLE floodmaps DROP CONSTRAINT enforce_geotype_geom;
156 ALTER TABLE floodmaps ADD CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POLYGON'::text OR geometrytype(geom) = 'MULTIPOLYGON'::text);
157 ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_SEQ');
158
159 CREATE TRIGGER floodmaps_id_trigger BEFORE INSERT OR UPDATE ON floodmaps
160 FOR EACH ROW EXECUTE PROCEDURE floodmaps_id_func();
161 END;
http://dive4elements.wald.intevation.org