Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql-spatial.sql @ 3962:d609fd83310a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
merged flys-backend
author Thomas Arendsen Hein <thomas@intevation.de>
date Fri, 28 Sep 2012 12:15:04 +0200
parents f5912365619c
children 28f992c0f937
comparison
equal deleted inserted replaced
3938:c0cab28ba1ea 3962:d609fd83310a
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 name VARCHAR(64),
10 path VARCHAR(256)
11 );
12 SELECT AddGeometryColumn('river_axes', 'geom', 31467, 'LINESTRING', 2);
13 ALTER TABLE river_axes ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_ID_SEQ');
14
15
16 -- TODO: TestMe.
17 -- Geodaesie/Flussachse+km/km.shp
18 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ;
19 CREATE TABLE river_axes_km (
20 id int PRIMARY KEY NOT NULL,
21 river_id int REFERENCES rivers(id),
22 km NUMERIC NOT NULL,
23 name VARCHAR(64),
24 path VARCHAR(256)
25 );
26 SELECT AddGeometryColumn('river_axes_km', 'geom', 31467, 'POINT', 2);
27 ALTER TABLE river_axes_km ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_KM_ID_SEQ');
28
29
30 --Geodaesie/Querprofile/QP-Spuren/qps.shp
31 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
32 CREATE TABLE cross_section_tracks (
33 id int PRIMARY KEY NOT NULL,
34 river_id int REFERENCES rivers(id),
35 km NUMERIC NOT NULL,
36 z NUMERIC NOT NULL DEFAULT 0,
37 name VARCHAR(64),
38 path VARCHAR(256)
39 );
40 SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31467, 'LINESTRING', 2);
41 ALTER TABLE cross_section_tracks ALTER COLUMN id SET DEFAULT NEXTVAL('CROSS_SECTION_TRACKS_ID_SEQ');
42
43
44 -- Geodaesie/Linien/rohre-und-spreen
45 CREATE SEQUENCE LINES_ID_SEQ;
46 CREATE TABLE lines (
47 id int PRIMARY KEY NOT NULL,
48 river_id int REFERENCES rivers(id),
49 kind VARCHAR(16) NOT NULL,
50 z NUMERIC DEFAULT 0,
51 name VARCHAR(64),
52 path VARCHAR(256)
53 );
54 SELECT AddGeometryColumn('lines', 'geom', 31467, 'LINESTRING', 3);
55 ALTER TABLE lines ALTER COLUMN id SET DEFAULT NEXTVAL('LINES_ID_SEQ');
56 -- 'kind':
57 -- 0: ROHR1
58 -- 1: DAMM
59
60
61 -- Geodaesie/Bauwerke/Wehre.shp
62 CREATE SEQUENCE BUILDINGS_ID_SEQ;
63 CREATE TABLE buildings (
64 id int PRIMARY KEY NOT NULL,
65 river_id int REFERENCES rivers(id),
66 name VARCHAR(256),
67 path VARCHAR(256)
68 );
69 SELECT AddGeometryColumn('buildings', 'geom', 31467, 'LINESTRING', 2);
70 ALTER TABLE buildings ALTER COLUMN id SET DEFAULT NEXTVAL('BUILDINGS_ID_SEQ');
71
72
73 -- Geodaesie/Festpunkte/Festpunkte.shp
74 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
75 CREATE TABLE fixpoints (
76 id int PRIMARY KEY NOT NULL,
77 river_id int REFERENCES rivers(id),
78 x int,
79 y int,
80 km NUMERIC NOT NULL,
81 HPGP VARCHAR(2),
82 name VARCHAR(64),
83 path VARCHAR(256)
84 );
85 SELECT AddGeometryColumn('fixpoints', 'geom', 31467, 'POINT', 2);
86 ALTER TABLE fixpoints ALTER COLUMN id SET DEFAULT NEXTVAL('FIXPOINTS_ID_SEQ');
87
88
89 -- Hydrologie/Hydr. Grenzen/talaue.shp
90 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
91 CREATE TABLE floodplain (
92 id int PRIMARY KEY NOT NULL,
93 river_id int REFERENCES rivers(id),
94 name VARCHAR(64),
95 path VARCHAR(256)
96 );
97 SELECT AddGeometryColumn('floodplain', 'geom', 31467, 'POLYGON', 2);
98 ALTER TABLE floodplain ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODPLAIN_ID_SEQ');
99
100
101 -- Geodaesie/Hoehenmodelle/*
102 CREATE SEQUENCE DEM_ID_SEQ;
103 CREATE TABLE dem (
104 id int PRIMARY KEY NOT NULL,
105 river_id int REFERENCES rivers(id),
106 -- XXX Should we use the ranges table instead?
107 name VARCHAR(64),
108 lower NUMERIC,
109 upper NUMERIC,
110 year_from VARCHAR(32) NOT NULL,
111 year_to VARCHAR(32) NOT NULL,
112 projection VARCHAR(32) NOT NULL,
113 elevation_state VARCHAR(32),
114 format VARCHAR(32),
115 border_break BOOLEAN NOT NULL DEFAULT FALSE,
116 resolution VARCHAR(16),
117 description VARCHAR(256),
118 path VARCHAR(256)
119 );
120 ALTER TABLE dem ALTER COLUMN id SET DEFAULT NEXTVAL('DEM_ID_SEQ');
121
122
123 -- Hydrologie/Einzugsgebiete/EZG.shp
124 CREATE SEQUENCE CATCHMENT_ID_SEQ;
125 CREATE TABLE catchment (
126 id int PRIMARY KEY NOT NULL,
127 river_id int REFERENCES rivers(id),
128 area NUMERIC,
129 name VARCHAR(256),
130 path VARCHAR(256)
131 );
132 SELECT AddGeometryColumn('catchment','geom',31467,'POLYGON',2);
133 ALTER TABLE catchment ALTER COLUMN id SET DEFAULT NEXTVAL('CATCHMENT_ID_SEQ');
134
135
136 --Hydrologie/HW-Schutzanlagen/hws.shp
137 CREATE SEQUENCE HWS_ID_SEQ;
138 CREATE TABLE hws (
139 id int PRIMARY KEY NOT NULL,
140 river_id int REFERENCES rivers(id),
141 hws_facility VARCHAR(256),
142 type VARCHAR(256),
143 name VARCHAR(64),
144 path VARCHAR(256)
145 );
146 SELECT AddGeometryColumn('hws','geom',31467,'LINESTRING',2);
147 ALTER TABLE hws ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ');
148
149
150 --
151 --Hydrologie/UeSG
152 --
153 -- 'kind' can be one of:
154 -- 200 = Messung
155 -- 111 = Berechnung->Aktuell->BfG
156 -- 112 = Berechnung->Aktuell->Land
157 -- 121 = Berechnung->Potenziell->BfG
158 -- 122 = Berechnung->Potenziell->Land
159 --
160 CREATE SEQUENCE FLOODMAPS_ID_SEQ;
161 CREATE TABLE floodmaps (
162 id int PRIMARY KEY NOT NULL,
163 river_id int REFERENCES rivers(id),
164 name varchar(64) NOT NULL,
165 kind int NOT NULL,
166 diff real,
167 count int,
168 area real,
169 perimeter real,
170 path VARCHAR(256)
171 );
172 SELECT AddGeometryColumn('floodmaps', 'geom', 31467, 'MULTIPOLYGON', 2);
173 ALTER TABLE floodmaps DROP CONSTRAINT enforce_geotype_geom;
174 ALTER TABLE floodmaps ADD CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POLYGON'::text OR geometrytype(geom) = 'MULTIPOLYGON'::text);
175 ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ');
176
177
178 CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ;
179 CREATE TABLE hydr_boundaries (
180 id int PRIMARY KEY NOT NULL,
181 river_id int REFERENCES rivers(id),
182 name VARCHAR(255),
183 kind int,
184 path VARCHAR(256)
185 );
186 SELECT AddGeometryColumn('hydr_boundaries','geom',31467,'LINESTRING',3);
187 ALTER TABLE hydr_boundaries ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_ID_SEQ');
188
189
190 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ;
191 CREATE TABLE hydr_boundaries_poly (
192 id int PRIMARY KEY NOT NULL,
193 river_id int REFERENCES rivers(id),
194 name VARCHAR(255),
195 kind int,
196 path VARCHAR(256)
197 );
198 SELECT AddGeometryColumn('hydr_boundaries_poly','geom',31467,'POLYGON',3);
199 ALTER TABLE hydr_boundaries_poly ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_POLY_ID_SEQ');
200
201
202 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ;
203 CREATE TABLE gauge_location (
204 id int PRIMARY KEY NOT NULL,
205 river_id int REFERENCES rivers(id),
206 name VARCHAR(255),
207 path VARCHAR(256)
208 );
209 SELECT AddGeometryColumn('gauge_location','geom',31467,'POINT',2);
210 ALTER TABLE gauge_location ALTER COLUMN id SET DEFAULT NEXTVAL('GAUGE_LOCATION_ID_SEQ');
211
212 COMMIT;
http://dive4elements.wald.intevation.org