forked from QGEP/datamodel
-
Notifications
You must be signed in to change notification settings - Fork 1
/
05_data_model_extensions.sql
53 lines (47 loc) · 3.53 KB
/
05_data_model_extensions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
-- table wastewater_structure is extended to hold additional attributes necessary for symbology reasons
-- extended attributes are started with an underscore
-- _usage_current is necessary for coloring the wastewater_structure/cover symbols
-- _function_hierarchic is necessary for scale-based filtering (display minor wastewater_structures only at larger scales)
-- _orientation is necessary for certain symbols (e.g. oil separator)
-- TABLE wastewater_structure
ALTER TABLE qgep_od.wastewater_structure ADD COLUMN _usage_current integer;
COMMENT ON COLUMN qgep_od.wastewater_structure._usage_current IS 'not part of the VSA-DSS data model
added solely for QGEP
has to be updated by triggers';
ALTER TABLE qgep_od.wastewater_structure ADD COLUMN _function_hierarchic integer;
COMMENT ON COLUMN qgep_od.wastewater_structure._function_hierarchic IS 'not part of the VSA-DSS data model
added solely for QGEP
has to be updated by triggers';
ALTER TABLE qgep_od.manhole ADD COLUMN _orientation numeric;
COMMENT ON COLUMN qgep_od.manhole._orientation IS 'not part of the VSA-DSS data model
added solely for QGEP';
ALTER TABLE qgep_od.wastewater_structure ADD COLUMN _label text;
COMMENT ON COLUMN qgep_od.wastewater_structure._label IS 'not part of the VSA-DSS data model
added solely for QGEP';
-- this column is an extension to the VSA data model and puts the _function_hierarchic in order
ALTER TABLE qgep_vl.channel_function_hierarchic ADD COLUMN order_fct_hierarchic smallint;
UPDATE qgep_vl.channel_function_hierarchic SET order_fct_hierarchic=5 WHERE code=5062;
UPDATE qgep_vl.channel_function_hierarchic SET order_fct_hierarchic=7 WHERE code=5063;
UPDATE qgep_vl.channel_function_hierarchic SET order_fct_hierarchic=6 WHERE code=5064;
UPDATE qgep_vl.channel_function_hierarchic SET order_fct_hierarchic=8 WHERE code=5065;
UPDATE qgep_vl.channel_function_hierarchic SET order_fct_hierarchic=10 WHERE code=5066;
UPDATE qgep_vl.channel_function_hierarchic SET order_fct_hierarchic=13 WHERE code=5067;
UPDATE qgep_vl.channel_function_hierarchic SET order_fct_hierarchic=1 WHERE code=5068;
UPDATE qgep_vl.channel_function_hierarchic SET order_fct_hierarchic=3 WHERE code=5069;
UPDATE qgep_vl.channel_function_hierarchic SET order_fct_hierarchic=2 WHERE code=5070;
UPDATE qgep_vl.channel_function_hierarchic SET order_fct_hierarchic=4 WHERE code=5071;
UPDATE qgep_vl.channel_function_hierarchic SET order_fct_hierarchic=9 WHERE code=5072;
UPDATE qgep_vl.channel_function_hierarchic SET order_fct_hierarchic=12 WHERE code=5073;
UPDATE qgep_vl.channel_function_hierarchic SET order_fct_hierarchic=11 WHERE code=5074;
UPDATE qgep_vl.channel_function_hierarchic SET order_fct_hierarchic=14 WHERE code=5075;
-- this column is an extension to the VSA data model and puts the _usage_current in order
ALTER TABLE qgep_vl.channel_usage_current ADD COLUMN order_usage_current smallint;
UPDATE qgep_vl.channel_usage_current SET order_usage_current=5 WHERE code = 4514;
UPDATE qgep_vl.channel_usage_current SET order_usage_current=3 WHERE code = 4516;
UPDATE qgep_vl.channel_usage_current SET order_usage_current=7 WHERE code = 4518;
UPDATE qgep_vl.channel_usage_current SET order_usage_current=6 WHERE code = 4520;
UPDATE qgep_vl.channel_usage_current SET order_usage_current=2 WHERE code = 4522;
UPDATE qgep_vl.channel_usage_current SET order_usage_current=4 WHERE code = 4524;
UPDATE qgep_vl.channel_usage_current SET order_usage_current=1 WHERE code = 4526;
UPDATE qgep_vl.channel_usage_current SET order_usage_current=8 WHERE code = 4571;
UPDATE qgep_vl.channel_usage_current SET order_usage_current=9 WHERE code = 5322;