Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql-spatial.sql @ 2339:b28ab244a77d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Harmonized oracle and postgresql spatial schema. flys-backend/trunk@2817 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Bjoern Schilberg <bjoern@intevation.de>
date Fri, 23 Sep 2011 10:42:25 +0000
parents 3ebc0a7d6793
children c4f090a00178
comparison
equal deleted inserted replaced
2338:e69ef36290ae 2339:b28ab244a77d
9 ); 9 );
10 SELECT AddGeometryColumn('river_axes', 'geom', 31466, 'LINESTRING', 2); 10 SELECT AddGeometryColumn('river_axes', 'geom', 31466, 'LINESTRING', 2);
11 ALTER TABLE river_axes ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_ID_SEQ'); 11 ALTER TABLE river_axes ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_ID_SEQ');
12 12
13 13
14 -- Geodaesie/Querprofile/* 14 -- TODO
15 -- Geodaesie/Flussachse+km/km.shp
16
17
18 --Geodaesie/Querprofile/QP-Spuren/qps.shp
15 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; 19 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
16 CREATE TABLE cross_section_tracks ( 20 CREATE TABLE cross_section_tracks (
17 id int PRIMARY KEY NOT NULL, 21 id int PRIMARY KEY NOT NULL,
18 river_id int REFERENCES rivers(id), 22 river_id int REFERENCES rivers(id),
19 km NUMERIC NOT NULL, 23 km NUMERIC NOT NULL,
41 -- Geodaesie/Bauwerke/Wehre.shp 45 -- Geodaesie/Bauwerke/Wehre.shp
42 CREATE SEQUENCE BUILDINGS_ID_SEQ; 46 CREATE SEQUENCE BUILDINGS_ID_SEQ;
43 CREATE TABLE buildings ( 47 CREATE TABLE buildings (
44 id int PRIMARY KEY NOT NULL, 48 id int PRIMARY KEY NOT NULL,
45 river_id int REFERENCES rivers(id), 49 river_id int REFERENCES rivers(id),
46 name VARCHAR(50) 50 name VARCHAR(256)
47 ); 51 );
48 SELECT AddGeometryColumn('buildings', 'geom', 31466, 'LINESTRING', 2); 52 SELECT AddGeometryColumn('buildings', 'geom', 31466, 'LINESTRING', 2);
49 ALTER TABLE buildings ALTER COLUMN id SET DEFAULT NEXTVAL('BUILDINGS_ID_SEQ'); 53 ALTER TABLE buildings ALTER COLUMN id SET DEFAULT NEXTVAL('BUILDINGS_ID_SEQ');
50 54
51 55
86 ); 90 );
87 ALTER TABLE dem ALTER COLUMN id SET DEFAULT NEXTVAL('DEM_ID_SEQ'); 91 ALTER TABLE dem ALTER COLUMN id SET DEFAULT NEXTVAL('DEM_ID_SEQ');
88 92
89 93
90 -- Hydrologie/Einzugsgebiete/EZG.shp 94 -- Hydrologie/Einzugsgebiete/EZG.shp
91 -- Hinweise zu ezg_saar.shp wird nicht importiert:
92 -- CLASS: Integer (8.0) KLAEREN: wir die benoetigt?
93 -- AREA: Real (19.8) laesst sich auch durch EZG.shp bestimmen
94 -- PERIMETER: Real (19.8) laesst sich auch durch EZG.shp bestimmen
95
96 CREATE SEQUENCE CATCHMENT_ID_SEQ; 95 CREATE SEQUENCE CATCHMENT_ID_SEQ;
97 CREATE TABLE catchment ( 96 CREATE TABLE catchment (
98 id int PRIMARY KEY NOT NULL, 97 id int PRIMARY KEY NOT NULL,
99 river_id int REFERENCES rivers(id), 98 river_id int REFERENCES rivers(id),
100 "area" numeric, 99 area NUMERIC,
101 "name" VARCHAR(80) 100 name VARCHAR(256)
102 ); 101 );
103 SELECT AddGeometryColumn('catchment','geom',31466,'POLYGON',2); 102 SELECT AddGeometryColumn('catchment','geom',31466,'POLYGON',2);
104 ALTER TABLE catchment ALTER COLUMN id SET DEFAULT NEXTVAL('CATCHMENT_ID_SEQ'); 103 ALTER TABLE catchment ALTER COLUMN id SET DEFAULT NEXTVAL('CATCHMENT_ID_SEQ');
105 104
106 -- Hydrologie/HW-Schutzanlagen
107 -- Wird nicht benoetigt, stattdessen verwenden wir
108 -- Gewaesser/Saar/Geodaesie/Linien/rohre-und-sperren.shp
109 -- hws.shp beinhaltet die Geometrien von:
110 -- HWS-Lisdorf.shp -- hws_anlage
111 -- HWS-Mettlach.shp -- maßnahme -> hws_anlage
112 -- HWS-Rehlingen.shp -- hw -> hws_anlage
113 -- HWS_Saarburg.shp -- höhe? bauart?
114 -- HWS-Schoden-Rhl-Pf.shp -- hws_anlage
115 -- HWS_Schoden.shp --höhe? bauart?
116 -- HWS-Serrig.shp --hws_anlage
117 -- CREATE SEQUENCE HWS_EZG_ID_SEQ;
118 -- CREATE TABLE hws (
119 -- id int PRIMARY KEY NOT NULL,
120 -- oid int,
121 -- river_id int REFERENCES rivers(id),
122 -- hws_facility VARCHAR(40),
123 -- typ VARCHAR(254)
124 -- );
125 -- SELECT AddGeometryColumn('hws','geom',31466,'MULTILINESTRING',2);
126 -- ALTER TABLE hw ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ');
127 105
128 -- Hydrologie/Hydr. Grenzen/Linien 106 --Hydrologie/HW-Schutzanlagen/hws.shp
107 CREATE SEQUENCE HWS_ID_SEQ;
108 CREATE TABLE hws (
109 id int PRIMARY KEY NOT NULL,
110 oid int,
111 river_id int REFERENCES rivers(id),
112 hws_facility VARCHAR(256),
113 typ VARCHAR(256)
114 );
115 SELECT AddGeometryColumn('hws','geom',31466,'MULTILINESTRING',2);
116 ALTER TABLE hw ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ');
129 117
130 118
131 -- Hydrologie/Streckendaten
132 -- pegellage_saar.shp
133 CREATE SEQUENCE LEVELPOSITION_ID_SEQ;
134 CREATE TABLE levelposition (
135 id int PRIMARY KEY NOT NULL,
136 river_id int REFERENCES rivers(id),
137 x numeric(10,0),
138 y numeric(10,0),
139 name varchar(254)
140 );
141 SELECT AddGeometryColumn('levelposition','geom','31466','POINT',2);
142 ALTER TABLE levelposition ALTER COLUMN id SET DEFAULT NEXTVAL('LEVELPOSITION_ID_SEQ');
143
144 -- Hydrologie/UeSG/Berechnung
145 -- Berechnung/Aktuell/BfG
146 CREATE SEQUENCE COMPUTATIONS_BFG_ID_SEQ;
147 CREATE TABLE computations_bfg (
148 id int PRIMARY KEY NOT NULL,
149 river_id int REFERENCES rivers(id),
150 section varchar(254),
151 area float8,
152 perimeter float8
153 );
154 SELECT AddGeometryColumn('computations_bfg','geom','31466','MULTIPOLYGON',2);
155 ALTER TABLE computations_bfg ALTER COLUMN id SET DEFAULT NEXTVAL('COMPUTATIONS_BFG_ID_SEQ');
156
157 -- Berechnung/Aktuell/Land
158 CREATE SEQUENCE COMPUTATIONS_COUNTRY_ID_SEQ;
159 CREATE TABLE computations_country(
160 id int PRIMARY KEY NOT NULL,
161 river_id int REFERENCES rivers(id),
162 text varchar(254)
163 );
164 SELECT AddGeometryColumn('computations_contry','geom','31466','MULTILINESTRING',2);
165 ALTER TABLE computations_country ALTER COLUMN id SET DEFAULT NEXTVAL('COMPUTATIONS_COUNTRY_ID_SEQ');
166
167
168 -- Hydrologie/UeSG/Messung
169 CREATE SEQUENCE MEASUREMENTS_ID_SEQ;
170 CREATE TABLE measurements (
171 id int PRIMARY KEY NOT NULL,
172 river_id int REFERENCES rivers(id),
173 year varchar(254),
174 oid varchar(40)
175 );
176 SELECT AddGeometryColumn('measurement','geom','31466','MULTILINESTRING',2);
177 ALTER TABLE measurements ALTER COLUMN id SET DEFAULT NEXTVAL('MEASUREMENTS_ID_SEQ');
178
179 END; 119 END;
http://dive4elements.wald.intevation.org