Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql-spatial.sql @ 1241:f68a0504dfb6

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Postgresql spatial scheme extended. flys-backend/trunk@2706 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Bjoern Schilberg <bjoern@intevation.de>
date Mon, 12 Sep 2011 13:20:05 +0000
parents 2ef8b67bd68a
children d6520d46edb7
comparison
equal deleted inserted replaced
1240:2ef8b67bd68a 1241:f68a0504dfb6
72 SELECT AddGeometryColumn('floodplain', 'geom', 31466, 'MULTIPOLYGON', 2); 72 SELECT AddGeometryColumn('floodplain', 'geom', 31466, 'MULTIPOLYGON', 2);
73 ALTER TABLE floodplain ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODPLAIN_ID_SEQ'); 73 ALTER TABLE floodplain ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODPLAIN_ID_SEQ');
74 74
75 75
76 -- Geodaesie/Hoehenmodelle/* 76 -- Geodaesie/Hoehenmodelle/*
77 -- XXX DGM in englisch DEM, um dies zu vereinheitlichen
78 CREATE SEQUENCE DEM_ID_SEQ; 77 CREATE SEQUENCE DEM_ID_SEQ;
79 CREATE TABLE dem ( 78 CREATE TABLE dem (
80 id int PRIMARY KEY NOT NULL, 79 id int PRIMARY KEY NOT NULL,
81 river_id int REFERENCES rivers(id), 80 river_id int REFERENCES rivers(id),
82 -- XXX Should we use the ranges table instead? 81 -- XXX Should we use the ranges table instead?
86 UNIQUE (river_id, lower, upper) 85 UNIQUE (river_id, lower, upper)
87 ); 86 );
88 ALTER TABLE dem ALTER COLUMN id SET DEFAULT NEXTVAL('DEM_ID_SEQ'); 87 ALTER TABLE dem ALTER COLUMN id SET DEFAULT NEXTVAL('DEM_ID_SEQ');
89 88
90 89
91 -- TODO 90 -- Hydrologie/Einzugsgebiete/EZG.shp
92 -- Flussachse+km/km 91 -- Hinweise zu ezg_saar.shp wird nicht importiert:
93 92 -- CLASS: Integer (8.0) KLAEREN: wir die benoetigt?
94 -- TODO 93 -- AREA: Real (19.8) laesst sich auch durch EZG.shp bestimmen
95 -- Hydrologie/Einzugsgebiete 94 -- PERIMETER: Real (19.8) laesst sich auch durch EZG.shp bestimmen
96 95
97 -- TODO 96 CREATE SEQUENCE CATCHMENT_ID_SEQ;
97 CREATE TABLE catchment (
98 id int PRIMARY KEY NOT NULL,
99 river_id int REFERENCES rivers(id),
100 "area" numeric,
101 "name" VARCHAR(80)
102 );
103 SELECT AddGeometryColumn('catchment','geom',31466,'POLYGON',2);
104 ALTER TABLE catchment ALTER COLUMN id SET DEFAULT NEXTVAL('CATCHMENT_ID_SEQ');
105
106 -- Hydrologie/Einzugsgebiete/b1-polygon.shp
107 -- Nur fuer saar vorhanden
108 -- IDEE alle Begrenzungspolygone in eine Tabelle.
109 CREATE SEQUENCE BOUNDARYPOLYS_ID_SEQ;
110 CREATE TABLE boundarypolys (
111 id int PRIMARY KEY NOT NULL,
112 river_id int REFERENCES rivers(id)
113 );
114 SELECT AddGeometryColumn('boundarypolys','geom',31466,'MULTIPOLYGON',2);
115 ALTER TABLE boundarypolys ALTER COLUMN id SET DEFAULT NEXTVAL('BOUNDARYPOLYS_ID_SEQ');
116
98 -- Hydrologie/HW-Schutzanlagen 117 -- Hydrologie/HW-Schutzanlagen
99 118 -- Wird nicht benoetigt, stattdessen verwenden wir
100 -- TODO 119 -- Gewaesser/Saar/Geodaesie/Linien/rohre-und-sperren.shp
120 -- hws.shp beinhaltet die Geometrien von:
121 -- HWS-Lisdorf.shp -- hws_anlage
122 -- HWS-Mettlach.shp -- maßnahme -> hws_anlage
123 -- HWS-Rehlingen.shp -- hw -> hws_anlage
124 -- HWS_Saarburg.shp -- höhe? bauart?
125 -- HWS-Schoden-Rhl-Pf.shp -- hws_anlage
126 -- HWS_Schoden.shp --höhe? bauart?
127 -- HWS-Serrig.shp --hws_anlage
128 -- CREATE SEQUENCE HWS_EZG_ID_SEQ;
129 -- CREATE TABLE hws (
130 -- id int PRIMARY KEY NOT NULL,
131 -- oid int,
132 -- river_id int REFERENCES rivers(id),
133 -- hws_facility VARCHAR(40),
134 -- typ VARCHAR(254)
135 -- );
136 -- SELECT AddGeometryColumn('hws','geom',31466,'MULTILINESTRING',2);
137 -- ALTER TABLE hw ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ');
138
101 -- Hydrologie/Hydr. Grenzen/Linien 139 -- Hydrologie/Hydr. Grenzen/Linien
102 140 -- BfG/boeschung_*.shp
103 -- TODO 141 CREATE SEQUENCE BANKS_ID_SEQ;
142 CREATE TABLE banks (
143 id int PRIMARY KEY NOT NULL,
144 river_id int REFERENCES rivers(id)
145 );
146 SELECT AddGeometryColumn('banks','geom',31466,'MULTILINESTRING',2);
147 ALTER TABLE banks ALTER COLUMN id SET DEFAULT NEXTVAL('BANKS_ID_SEQ');
148
149 -- BfG/hauptoeff_*.shp
150 CREATE SEQUENCE MAINSPANS_ID_SEQ;
151 CREATE TABLE mainspans(
152 id int PRIMARY KEY NOT NULL,
153 river_id int REFERENCES rivers(id)
154 );
155 SELECT AddGeometryColumn('mainspans','geom',31466,'MULTILINESTRING',2);
156 ALTER TABLE mainspans ALTER COLUMN id SET DEFAULT NEXTVAL('MAINSPANS_ID_SEQ');
157
158 -- BfG/MNQ-*.shp
159 CREATE SEQUENCE MNQ_ID_SEQ;
160 CREATE TABLE mnq (gid serial PRIMARY KEY,
161 id int PRIMARY KEY NOT NULL,
162 river_id int REFERENCES rivers(id),
163 haltung varchar(16)
164 );
165 SELECT AddGeometryColumn('mnq', 'the_geom',31466,'MULTIPOLYGON',2);
166 ALTER TABLE mnq ALTER COLUMN id SET DEFAULT NEXTVAL('MNQ_ID_SEQ');
167
168 -- BfG/modellgrenze*.shp
169 CREATE SEQUENCE MODELBOUNDARY_ID_SEQ;
170 CREATE TABLE modelboundary (
171 id int PRIMARY KEY NOT NULL,
172 river_id int REFERENCES rivers(id)
173 );
174 SELECT AddGeometryColumn('modelboundary','geom',31466,'MULTILINESTRING',2);
175 ALTER TABLE modelboundary ALTER COLUMN id SET DEFAULT NEXTVAL('MODELBOUNDARY_ID_SEQ');
176
177 -- TODO: Klaeren ob benoetigt, da einzel Geometrien in Tabelle vorland.
178 -- BfG/saar-sld-vorland.shp
179
180 -- BfG/uferlinie.shp
181 CREATE SEQUENCE SHORELINE_ID_SEQ;
182 CREATE TABLE shoreline(
183 id int PRIMARY KEY NOT NULL,
184 river_id int REFERENCES rivers(id)
185 );
186 SELECT AddGeometryColumn('shoreline','geom',31466,'MULTILINESTRING',2);
187 ALTER TABLE shoreline ALTER COLUMN id SET DEFAULT NEXTVAL('SHORELINE_ID_SEQ');
188
189 -- BfG/vorland_*.shp
190 CREATE SEQUENCE FORELAND_ID_SEQ;
191 CREATE TABLE foreland(
192 id int PRIMARY KEY NOT NULL,
193 river_id int REFERENCES rivers(id)
194 );
195 SELECT AddGeometryColumn('foreland','geom',31466,'MULTILINESTRING',2);
196 ALTER TABLE foreland ALTER COLUMN id SET DEFAULT NEXTVAL('FORELANDS_ID_SEQ');
197
198
104 -- Hydrologie/Streckendaten 199 -- Hydrologie/Streckendaten
105 200 -- pegellage_saar.shp
106 -- TODO 201 CREATE SEQUENCE LEVELPOSITION_ID_SEQ;
202 CREATE TABLE levelposition (
203 id int PRIMARY KEY NOT NULL,
204 river_id int REFERENCES rivers(id),
205 x numeric(10,0),
206 y numeric(10,0),
207 name varchar(254)
208 );
209 SELECT AddGeometryColumn('levelposition','geom','31466','POINT',2);
210 ALTER TABLE levelposition ALTER COLUMN id SET DEFAULT NEXTVAL('LEVELPOSITION_ID_SEQ');
211
107 -- Hydrologie/UeSG/Berechnung 212 -- Hydrologie/UeSG/Berechnung
108 213 -- Berechnung/Aktuell/BfG
109 -- TODO 214 CREATE SEQUENCE COMPUTATIONS_BFG_ID_SEQ;
215 CREATE TABLE computations_bfg (
216 id int PRIMARY KEY NOT NULL,
217 river_id int REFERENCES rivers(id),
218 section varchar(254),
219 area float8,
220 perimeter float8
221 );
222 SELECT AddGeometryColumn('computations_bfg','geom','31466','MULTIPOLYGON',2);
223 ALTER TABLE computations_bfg ALTER COLUMN id SET DEFAULT NEXTVAL('COMPUTATIONS_BFG_ID_SEQ');
224
225 -- Berechnung/Aktuell/Land
226 CREATE SEQUENCE COMPUTATIONS_COUNTRY_ID_SEQ;
227 CREATE TABLE computations_country(
228 id int PRIMARY KEY NOT NULL,
229 river_id int REFERENCES rivers(id),
230 text varchar(254)
231 );
232 SELECT AddGeometryColumn('computations_contry','geom','31466','MULTILINESTRING',2);
233 ALTER TABLE computations_country ALTER COLUMN id SET DEFAULT NEXTVAL('COMPUTATIONS_COUNTRY_ID_SEQ');
234
235
110 -- Hydrologie/UeSG/Messung 236 -- Hydrologie/UeSG/Messung
237 CREATE SEQUENCE MEASUREMENTS_ID_SEQ;
238 CREATE TABLE measurements (
239 id int PRIMARY KEY NOT NULL,
240 river_id int REFERENCES rivers(id),
241 year varchar(254),
242 oid varchar(40)
243 );
244 SELECT AddGeometryColumn('measurement','geom','31466','MULTILINESTRING',2);
245 ALTER TABLE measurements ALTER COLUMN id SET DEFAULT NEXTVAL('MEASUREMENTS_ID_SEQ');
111 246
112 END; 247 END;
http://dive4elements.wald.intevation.org