1
SELECT re.obj_id AS name,
2
COALESCE(from_wn.obj_id, (concat('from_node@', re.obj_id))::character varying) AS fromnode,
3
COALESCE(to_wn.obj_id, (concat('to_node@', re.obj_id))::character varying) AS tonode,
4
CASE
5
WHEN ((re.length_effective <= 0.01) AND (st_length(re.progression3d_geometry) <= (0.01)::double precision)) THEN (0.01)::double precision
6
WHEN ((re.length_effective <= 0.01) AND (st_length(re.progression3d_geometry) >= (0.01)::double precision)) THEN st_length(re.progression3d_geometry)
7
WHEN ((re.length_effective IS NULL) AND (st_length(re.progression3d_geometry) <= (0.01)::double precision)) THEN (0.01)::double precision
8
WHEN ((re.length_effective IS NULL) AND (st_length(re.progression3d_geometry) >= (0.01)::double precision)) THEN st_length(re.progression3d_geometry)
9
ELSE (re.length_effective)::double precision
10
END AS length,
11
CASE
12
WHEN (re.coefficient_of_friction IS NOT NULL) THEN ((1)::double precision / (re.coefficient_of_friction)::double precision)
13
WHEN ((re.coefficient_of_friction IS NULL) AND (re.wall_roughness IS NOT NULL)) THEN
14
CASE
15
WHEN (re.clear_height IS NOT NULL) THEN (('1'::numeric / ((((('4'::numeric * sqrt(9.81)))::double precision * power((('32'::numeric)::double precision / ((re.clear_height)::double precision / ('1000'::numeric)::double precision)), (('1'::numeric)::double precision / ('6'::numeric)::double precision))) * log((((3.71)::double precision * ((re.clear_height)::double precision / ('1000'::numeric)::double precision)) / ((re.wall_roughness / '1000'::numeric))::double precision))))::numeric(7,4)))::double precision
16
WHEN ((re.clear_height IS NULL) AND (re.swmm_default_coefficient_of_friction IS NOT NULL)) THEN ((1)::double precision / (re.swmm_default_coefficient_of_friction)::double precision)
17
ELSE (0.01)::double precision
18
END
19
WHEN ((re.coefficient_of_friction IS NULL) AND (re.wall_roughness IS NULL)) THEN
20
CASE
21
WHEN (re.swmm_default_coefficient_of_friction IS NOT NULL) THEN ((1)::double precision / (re.swmm_default_coefficient_of_friction)::double precision)
22
ELSE (0.01)::double precision
23
END
24
ELSE (0.01)::double precision
25
END AS roughness,
26
COALESCE((rp_from.level - from_wn.bottom_level), (0)::numeric) AS inletoffset,
27
COALESCE((rp_to.level - to_wn.bottom_level), (0)::numeric) AS outletoffset,
28
0 AS initflow,
29
0 AS maxflow,
30
concat_ws(';'::text, ws.identifier,
31
CASE
32
WHEN (re.coefficient_of_friction IS NOT NULL) THEN '1 / K_Strickler, 1/re.coefficient_of_friction is used as roughness'::text
33
WHEN ((re.coefficient_of_friction IS NULL) AND (re.wall_roughness IS NOT NULL)) THEN
34
CASE
35
WHEN (re.clear_height IS NOT NULL) THEN 'The approximation of 1 / K_Strickler is computed from re.wall_roughness using an approximation formula.'::text
36
WHEN ((re.clear_height IS NULL) AND (re.swmm_default_coefficient_of_friction IS NOT NULL)) THEN 'The default value stored in reach.swmm_default_coefficient_of_friction is used'::text
37
ELSE 'Default value 0.01 is used as roughness'::text
38
END
39
WHEN ((re.coefficient_of_friction IS NULL) AND (re.wall_roughness IS NULL)) THEN
40
CASE
41
WHEN (re.swmm_default_coefficient_of_friction IS NOT NULL) THEN concat('The default value stored in reach.swmm_default_coefficient_of_friction is used')
42
ELSE 'Default value 0.01 is used as roughness'::text
43
END
44
ELSE 'Default value 0.01 is used as roughness'::text
45
END,
46
CASE
47
WHEN (vl_pp.vsacode = 3350) THEN
48
CASE
49
WHEN ((re.clear_height = 0) OR (re.clear_height IS NULL)) THEN concat('Reach', re.obj_id, ': circular profile with default value of 0.1m as clear_height')
50
ELSE NULL::text
51
END
52
WHEN (vl_pp.vsacode = 3351) THEN
53
CASE
54
WHEN ((re.clear_height = 0) OR (re.clear_height IS NULL)) THEN concat('Reach', re.obj_id, ': egg profile with default value of 0.1m as clear_height')
55
ELSE NULL::text
56
END
57
WHEN (vl_pp.vsacode = 3352) THEN
58
CASE
59
WHEN (pp.height_width_ratio IS NOT NULL) THEN
60
CASE
61
WHEN ((re.clear_height = 0) OR (re.clear_height IS NULL)) THEN concat('Reach', re.obj_id, ': arch profile with known height_width_ratio value and with default value of 0.1m as clear_height')
62
ELSE NULL::text
63
END
64
WHEN (pp.height_width_ratio IS NULL) THEN
65
CASE
66
WHEN ((re.clear_height = 0) OR (re.clear_height IS NULL)) THEN concat('Reach', re.obj_id, ': arch profile with default value of 1 as height_width_ratio and with default value of 0.1m as clear_height')
67
ELSE concat('Reach', re.obj_id, ': arch profile with default value of 1 as height_width_ratio and with known clear_height value')
68
END
69
ELSE NULL::text
70
END
71
WHEN (vl_pp.vsacode = 3353) THEN
72
CASE
73
WHEN (pp.height_width_ratio IS NOT NULL) THEN
74
CASE
75
WHEN ((re.clear_height = 0) OR (re.clear_height IS NULL)) THEN concat('Reach', re.obj_id, ': rectangular profile with known height_width_ratio value and with default value of 0.1m as clear_height')
76
ELSE NULL::text
77
END
78
WHEN (pp.height_width_ratio IS NULL) THEN
79
CASE
80
WHEN ((re.clear_height = 0) OR (re.clear_height IS NULL)) THEN concat('Reach', re.obj_id, ': rectangular profile with default value of 1 as height_width_ratio and with default value of 0.1m as clear_height')
81
ELSE concat('Reach', re.obj_id, ': rectangular profile with default value of 1 as height_width_ratio and with known clear_height value')
82
END
83
ELSE NULL::text
84
END
85
WHEN (vl_pp.vsacode = 3354) THEN
86
CASE
87
WHEN (pp.height_width_ratio IS NOT NULL) THEN
88
CASE
89
WHEN ((re.clear_height = 0) OR (re.clear_height IS NULL)) THEN concat('Reach', re.obj_id, ': parabolic profile with known height_width_ratio value, default value of 0.1m as clear_height and no code value')
90
ELSE NULL::text
91
END
92
WHEN (pp.height_width_ratio IS NULL) THEN
93
CASE
94
WHEN ((re.clear_height = 0) OR (re.clear_height IS NULL)) THEN concat('Reach', re.obj_id, ': parabolic profile with default value of 1 as height_width_ratio, with default value of 0.1m as clear_height and no code value')
95
ELSE concat('Reach', re.obj_id, ': parabolic profile with default value of 1 as height_width_ratio, with known clear_height value and no code value')
96
END
97
ELSE NULL::text
98
END
99
WHEN (vl_pp.vsacode = 3355) THEN concat('Reach', re.obj_id, ': custom profile to be defined in SWMM')
100
ELSE NULL::text
101
END,
102
CASE
103
WHEN (to_wn.obj_id IS NULL) THEN 'Blind connection, the destination node must be edited'::text
104
WHEN ((from_wn.obj_id IS NULL) AND (to_wn.obj_id IS NOT NULL)) THEN 'No from node, a junction was automatically created for the export.'::text
105
ELSE NULL::text
106
END) AS description,
107
cfhy.value_en AS tag,
108
(st_curvetoline(st_force3d(re.progression3d_geometry)))::geometry(LineStringZ,2056) AS geom,
109
CASE
110
WHEN (ws.status = ANY (ARRAY[7959, 6529, 6526])) THEN 'planned'::text
111
ELSE 'current'::text
112
END AS state,
113
CASE
114
WHEN (cfhi.vsacode = ANY (ARRAY[5062, 5064, 5066, 5068, 5069, 5070, 5071, 5072, 5074])) THEN 'primary'::text
115
ELSE 'secondary'::text
116
END AS hierarchy,
117
re.obj_id
118
FROM (((((((((((tww_od.reach re
119
LEFT JOIN tww_od.pipe_profile pp ON (((pp.obj_id)::text = (re.fk_pipe_profile)::text)))
120
LEFT JOIN tww_vl.pipe_profile_profile_type vl_pp ON ((pp.profile_type = vl_pp.code)))
121
LEFT JOIN tww_od.wastewater_networkelement ne ON (((ne.obj_id)::text = (re.obj_id)::text)))
122
LEFT JOIN tww_od.wastewater_structure ws ON (((ws.obj_id)::text = (ne.fk_wastewater_structure)::text)))
123
LEFT JOIN tww_od.reach_point rp_from ON (((rp_from.obj_id)::text = (re.fk_reach_point_from)::text)))
124
LEFT JOIN tww_od.reach_point rp_to ON (((rp_to.obj_id)::text = (re.fk_reach_point_to)::text)))
125
LEFT JOIN tww_od.wastewater_node from_wn ON (((from_wn.obj_id)::text = (rp_from.fk_wastewater_networkelement)::text)))
126
LEFT JOIN tww_od.wastewater_node to_wn ON (((to_wn.obj_id)::text = (rp_to.fk_wastewater_networkelement)::text)))
127
LEFT JOIN tww_od.channel ch ON (((ch.obj_id)::text = (ws.obj_id)::text)))
128
LEFT JOIN tww_vl.channel_function_hierarchic cfhi ON ((cfhi.code = ch.function_hierarchic)))
129
LEFT JOIN tww_vl.channel_function_hydraulic cfhy ON ((cfhy.code = ch.function_hydraulic)))
130
WHERE (ws.status = ANY (ARRAY[6530, 6533, 8493, 6529, 6526, 7959]));