Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql-spatial.sql @ 1259:54365104835c 2.5

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
merged flys-backend/2.5
author Thomas Arendsen Hein <thomas@intevation.de>
date Fri, 28 Sep 2012 12:14:18 +0200
parents 3ebc0a7d6793
children b28ab244a77d
comparison
equal deleted inserted replaced
1190:f514894ec2fd 1259:54365104835c
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 -- Geodaesie/Querprofile/*
15 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
16 CREATE TABLE cross_section_tracks (
17 id int PRIMARY KEY NOT NULL,
18 river_id int REFERENCES rivers(id),
19 km NUMERIC NOT NULL,
20 z NUMERIC NOT NULL DEFAULT 0
21 );
22 SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31466, 'LINESTRING', 2);
23 ALTER TABLE cross_section_tracks ALTER COLUMN id SET DEFAULT NEXTVAL('CROSS_SECTION_TRACKS_ID_SEQ');
24
25
26 -- Geodaesie/Linien/rohre-und-spreen
27 CREATE SEQUENCE LINES_ID_SEQ;
28 CREATE TABLE lines (
29 id int PRIMARY KEY NOT NULL,
30 river_id int REFERENCES rivers(id),
31 kind int NOT NULL DEFAULT 0,
32 z NUMERIC DEFAULT 0
33 );
34 SELECT AddGeometryColumn('lines', 'geom', 31466, 'LINESTRING', 4);
35 ALTER TABLE lines ALTER COLUMN id SET DEFAULT NEXTVAL('LINES_ID_SEQ');
36 -- 'kind':
37 -- 0: ROHR1
38 -- 1: DAMM
39
40
41 -- Geodaesie/Bauwerke/Wehre.shp
42 CREATE SEQUENCE BUILDINGS_ID_SEQ;
43 CREATE TABLE buildings (
44 id int PRIMARY KEY NOT NULL,
45 river_id int REFERENCES rivers(id),
46 name VARCHAR(50)
47 );
48 SELECT AddGeometryColumn('buildings', 'geom', 31466, 'LINESTRING', 2);
49 ALTER TABLE buildings ALTER COLUMN id SET DEFAULT NEXTVAL('BUILDINGS_ID_SEQ');
50
51
52 -- Geodaesie/Festpunkte/Festpunkte.shp
53 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
54 CREATE TABLE fixpoints (
55 id int PRIMARY KEY NOT NULL,
56 river_id int REFERENCES rivers(id),
57 x int,
58 y int,
59 km NUMERIC NOT NULL,
60 HPGP VARCHAR(2)
61 );
62 SELECT AddGeometryColumn('fixpoints', 'geom', 31466, 'POINT', 2);
63 ALTER TABLE fixpoints ALTER COLUMN id SET DEFAULT NEXTVAL('FIXPOINTS_ID_SEQ');
64
65
66 -- Hydrologie/Hydr. Grenzen/talaue.shp
67 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
68 CREATE TABLE floodplain (
69 id int PRIMARY KEY NOT NULL,
70 river_id int REFERENCES rivers(id)
71 );
72 SELECT AddGeometryColumn('floodplain', 'geom', 31466, 'MULTIPOLYGON', 2);
73 ALTER TABLE floodplain ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODPLAIN_ID_SEQ');
74
75
76 -- Geodaesie/Hoehenmodelle/*
77 CREATE SEQUENCE DEM_ID_SEQ;
78 CREATE TABLE dem (
79 id int PRIMARY KEY NOT NULL,
80 river_id int REFERENCES rivers(id),
81 -- XXX Should we use the ranges table instead?
82 lower NUMERIC,
83 upper NUMERIC,
84 path VARCHAR(256),
85 UNIQUE (river_id, lower, upper)
86 );
87 ALTER TABLE dem ALTER COLUMN id SET DEFAULT NEXTVAL('DEM_ID_SEQ');
88
89
90 -- 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;
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/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
128 -- Hydrologie/Hydr. Grenzen/Linien
129
130
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;
http://dive4elements.wald.intevation.org