swmm_vw_conduits

Columns

Related columns Constraint Comments

Search:

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

name

varchar

√

null

fromnode

varchar

2147483647

√

null

tonode

varchar

2147483647

√

null

length

float8

17,17

√

null

roughness

float8

17,17

√

null

inletoffset

numeric

√

null

outletoffset

numeric

√

null

initflow

int4

√

null

maxflow

int4

√

null

description

text

2147483647

√

null

Showing 1 to 10 of 15 entries

Relationships

Close relationships within degrees of separation

View Definition

SELECT re.obj_id AS name,
    COALESCE(from_wn.obj_id, (concat('from_node@', re.obj_id))::character varying) AS fromnode,
    COALESCE(to_wn.obj_id, (concat('to_node@', re.obj_id))::character varying) AS tonode,
        CASE
            WHEN ((re.length_effective <= 0.01) AND (st_length(re.progression3d_geometry) <= (0.01)::double precision)) THEN (0.01)::double precision
            WHEN ((re.length_effective <= 0.01) AND (st_length(re.progression3d_geometry) >= (0.01)::double precision)) THEN st_length(re.progression3d_geometry)
            WHEN ((re.length_effective IS NULL) AND (st_length(re.progression3d_geometry) <= (0.01)::double precision)) THEN (0.01)::double precision
            WHEN ((re.length_effective IS NULL) AND (st_length(re.progression3d_geometry) >= (0.01)::double precision)) THEN st_length(re.progression3d_geometry)
            ELSE (re.length_effective)::double precision
        END AS length,
        CASE
            WHEN (re.coefficient_of_friction IS NOT NULL) THEN ((1)::double precision / (re.coefficient_of_friction)::double precision)
            WHEN ((re.coefficient_of_friction IS NULL) AND (re.wall_roughness IS NOT NULL)) THEN
            CASE
                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
                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)
                ELSE (0.01)::double precision
            END
            WHEN ((re.coefficient_of_friction IS NULL) AND (re.wall_roughness IS NULL)) THEN
            CASE
                WHEN (re.swmm_default_coefficient_of_friction IS NOT NULL) THEN ((1)::double precision / (re.swmm_default_coefficient_of_friction)::double precision)
                ELSE (0.01)::double precision
            END
            ELSE (0.01)::double precision
        END AS roughness,
    COALESCE((rp_from.level - from_wn.bottom_level), (0)::numeric) AS inletoffset,
    COALESCE((rp_to.level - to_wn.bottom_level), (0)::numeric) AS outletoffset,
    0 AS initflow,
    0 AS maxflow,
    concat_ws(';'::text, ws.identifier,
        CASE
            WHEN (re.coefficient_of_friction IS NOT NULL) THEN '1 / K_Strickler, 1/re.coefficient_of_friction is used as roughness'::text
            WHEN ((re.coefficient_of_friction IS NULL) AND (re.wall_roughness IS NOT NULL)) THEN
            CASE
                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
                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
                ELSE 'Default value 0.01 is used as roughness'::text
            END
            WHEN ((re.coefficient_of_friction IS NULL) AND (re.wall_roughness IS NULL)) THEN
            CASE
                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')
                ELSE 'Default value 0.01 is used as roughness'::text
            END
            ELSE 'Default value 0.01 is used as roughness'::text
        END,
        CASE
            WHEN (vl_pp.vsacode = 3350) THEN
            CASE
                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')
                ELSE NULL::text
            END
            WHEN (vl_pp.vsacode = 3351) THEN
            CASE
                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')
                ELSE NULL::text
            END
            WHEN (vl_pp.vsacode = 3352) THEN
            CASE
                WHEN (pp.height_width_ratio IS NOT NULL) THEN
                CASE
                    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')
                    ELSE NULL::text
                END
                WHEN (pp.height_width_ratio IS NULL) THEN
                CASE
                    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')
                    ELSE concat('Reach', re.obj_id, ': arch profile with default value of 1 as height_width_ratio and with known clear_height value')
                END
                ELSE NULL::text
            END
            WHEN (vl_pp.vsacode = 3353) THEN
            CASE
                WHEN (pp.height_width_ratio IS NOT NULL) THEN
                CASE
                    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')
                    ELSE NULL::text
                END
                WHEN (pp.height_width_ratio IS NULL) THEN
                CASE
                    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')
                    ELSE concat('Reach', re.obj_id, ': rectangular profile with default value of 1 as height_width_ratio and with known clear_height value')
                END
                ELSE NULL::text
            END
            WHEN (vl_pp.vsacode = 3354) THEN
            CASE
                WHEN (pp.height_width_ratio IS NOT NULL) THEN
                CASE
                    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')
                    ELSE NULL::text
                END
                WHEN (pp.height_width_ratio IS NULL) THEN
                CASE
                    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')
                    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')
                END
                ELSE NULL::text
            END
            WHEN (vl_pp.vsacode = 3355) THEN concat('Reach', re.obj_id, ': custom profile to be defined in SWMM')
            ELSE NULL::text
        END,
        CASE
            WHEN (to_wn.obj_id IS NULL) THEN 'Blind connection, the destination node must be edited'::text
            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
            ELSE NULL::text
        END) AS description,
    cfhy.value_en AS tag,
    (st_curvetoline(st_force3d(re.progression3d_geometry)))::geometry(LineStringZ,2056) AS geom,
        CASE
            WHEN (ws.status = ANY (ARRAY[7959, 6529, 6526])) THEN 'planned'::text
            ELSE 'current'::text
        END AS state,
        CASE
            WHEN (cfhi.vsacode = ANY (ARRAY[5062, 5064, 5066, 5068, 5069, 5070, 5071, 5072, 5074])) THEN 'primary'::text
            ELSE 'secondary'::text
        END AS hierarchy,
    re.obj_id
   FROM (((((((((((tww_od.reach re
     LEFT JOIN tww_od.pipe_profile pp ON (((pp.obj_id)::text = (re.fk_pipe_profile)::text)))
     LEFT JOIN tww_vl.pipe_profile_profile_type vl_pp ON ((pp.profile_type = vl_pp.code)))
     LEFT JOIN tww_od.wastewater_networkelement ne ON (((ne.obj_id)::text = (re.obj_id)::text)))
     LEFT JOIN tww_od.wastewater_structure ws ON (((ws.obj_id)::text = (ne.fk_wastewater_structure)::text)))
     LEFT JOIN tww_od.reach_point rp_from ON (((rp_from.obj_id)::text = (re.fk_reach_point_from)::text)))
     LEFT JOIN tww_od.reach_point rp_to ON (((rp_to.obj_id)::text = (re.fk_reach_point_to)::text)))
     LEFT JOIN tww_od.wastewater_node from_wn ON (((from_wn.obj_id)::text = (rp_from.fk_wastewater_networkelement)::text)))
     LEFT JOIN tww_od.wastewater_node to_wn ON (((to_wn.obj_id)::text = (rp_to.fk_wastewater_networkelement)::text)))
     LEFT JOIN tww_od.channel ch ON (((ch.obj_id)::text = (ws.obj_id)::text)))
     LEFT JOIN tww_vl.channel_function_hierarchic cfhi ON ((cfhi.code = ch.function_hierarchic)))
     LEFT JOIN tww_vl.channel_function_hydraulic cfhy ON ((cfhy.code = ch.function_hydraulic)))
  WHERE (ws.status = ANY (ARRAY[6530, 6533, 8493, 6529, 6526, 7959]));

 
SELECT re.obj_id AS name,
    COALESCE(from_wn.obj_id, (concat('from_node@', re.obj_id))::character varying) AS fromnode,
    COALESCE(to_wn.obj_id, (concat('to_node@', re.obj_id))::character varying) AS tonode,
        CASE
            WHEN ((re.length_effective <= 0.01) AND (st_length(re.progression3d_geometry) <= (0.01)::double precision)) THEN (0.01)::double precision
            WHEN ((re.length_effective <= 0.01) AND (st_length(re.progression3d_geometry) >= (0.01)::double precision)) THEN st_length(re.progression3d_geometry)
            WHEN ((re.length_effective IS NULL) AND (st_length(re.progression3d_geometry) <= (0.01)::double precision)) THEN (0.01)::double precision
            WHEN ((re.length_effective IS NULL) AND (st_length(re.progression3d_geometry) >= (0.01)::double precision)) THEN st_length(re.progression3d_geometry)
            ELSE (re.length_effective)::double precision
        END AS length,
        CASE
            WHEN (re.coefficient_of_friction IS NOT NULL) THEN ((1)::double precision / (re.coefficient_of_friction)::double precision)
            WHEN ((re.coefficient_of_friction IS NULL) AND (re.wall_roughness IS NOT NULL)) THEN
            CASE
                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
                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)
                ELSE (0.01)::double precision
            END
            WHEN ((re.coefficient_of_friction IS NULL) AND (re.wall_roughness IS NULL)) THEN
            CASE
                WHEN (re.swmm_default_coefficient_of_friction IS NOT NULL) THEN ((1)::double precision / (re.swmm_default_coefficient_of_friction)::double precision)
                ELSE (0.01)::double precision
            END
            ELSE (0.01)::double precision
        END AS roughness,
    COALESCE((rp_from.level - from_wn.bottom_level), (0)::numeric) AS inletoffset,
    COALESCE((rp_to.level - to_wn.bottom_level), (0)::numeric) AS outletoffset,
    0 AS initflow,
    0 AS maxflow,
    concat_ws(';'::text, ws.identifier,
        CASE
            WHEN (re.coefficient_of_friction IS NOT NULL) THEN '1 / K_Strickler, 1/re.coefficient_of_friction is used as roughness'::text
            WHEN ((re.coefficient_of_friction IS NULL) AND (re.wall_roughness IS NOT NULL)) THEN
            CASE
                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
                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
                ELSE 'Default value 0.01 is used as roughness'::text
            END
            WHEN ((re.coefficient_of_friction IS NULL) AND (re.wall_roughness IS NULL)) THEN
            CASE
                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')
                ELSE 'Default value 0.01 is used as roughness'::text
            END
            ELSE 'Default value 0.01 is used as roughness'::text
        END,
        CASE
            WHEN (vl_pp.vsacode = 3350) THEN
            CASE
                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')
                ELSE NULL::text
            END
            WHEN (vl_pp.vsacode = 3351) THEN
            CASE
                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')
                ELSE NULL::text
            END
            WHEN (vl_pp.vsacode = 3352) THEN
            CASE
                WHEN (pp.height_width_ratio IS NOT NULL) THEN
                CASE
                    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')
                    ELSE NULL::text
                END
                WHEN (pp.height_width_ratio IS NULL) THEN
                CASE
                    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')
                    ELSE concat('Reach', re.obj_id, ': arch profile with default value of 1 as height_width_ratio and with known clear_height value')
                END
                ELSE NULL::text
            END
            WHEN (vl_pp.vsacode = 3353) THEN
            CASE
                WHEN (pp.height_width_ratio IS NOT NULL) THEN
                CASE
                    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')
                    ELSE NULL::text
                END
                WHEN (pp.height_width_ratio IS NULL) THEN
                CASE
                    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')
                    ELSE concat('Reach', re.obj_id, ': rectangular profile with default value of 1 as height_width_ratio and with known clear_height value')
                END
                ELSE NULL::text
            END
            WHEN (vl_pp.vsacode = 3354) THEN
            CASE
                WHEN (pp.height_width_ratio IS NOT NULL) THEN
                CASE
                    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')
                    ELSE NULL::text
                END
                WHEN (pp.height_width_ratio IS NULL) THEN
                CASE
                    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')
                    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')
                END
                ELSE NULL::text
            END
            WHEN (vl_pp.vsacode = 3355) THEN concat('Reach', re.obj_id, ': custom profile to be defined in SWMM')
            ELSE NULL::text
        END,
        CASE
            WHEN (to_wn.obj_id IS NULL) THEN 'Blind connection, the destination node must be edited'::text
            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
            ELSE NULL::text
        END) AS description,
    cfhy.value_en AS tag,
    (st_curvetoline(st_force3d(re.progression3d_geometry)))::geometry(LineStringZ,2056) AS geom,
        CASE
            WHEN (ws.status = ANY (ARRAY[7959, 6529, 6526])) THEN 'planned'::text
            ELSE 'current'::text
        END AS state,
        CASE
            WHEN (cfhi.vsacode = ANY (ARRAY[5062, 5064, 5066, 5068, 5069, 5070, 5071, 5072, 5074])) THEN 'primary'::text
            ELSE 'secondary'::text
        END AS hierarchy,
    re.obj_id
   FROM (((((((((((tww_od.reach re
     LEFT JOIN tww_od.pipe_profile pp ON (((pp.obj_id)::text = (re.fk_pipe_profile)::text)))
     LEFT JOIN tww_vl.pipe_profile_profile_type vl_pp ON ((pp.profile_type = vl_pp.code)))
     LEFT JOIN tww_od.wastewater_networkelement ne ON (((ne.obj_id)::text = (re.obj_id)::text)))
     LEFT JOIN tww_od.wastewater_structure ws ON (((ws.obj_id)::text = (ne.fk_wastewater_structure)::text)))
     LEFT JOIN tww_od.reach_point rp_from ON (((rp_from.obj_id)::text = (re.fk_reach_point_from)::text)))
     LEFT JOIN tww_od.reach_point rp_to ON (((rp_to.obj_id)::text = (re.fk_reach_point_to)::text)))
     LEFT JOIN tww_od.wastewater_node from_wn ON (((from_wn.obj_id)::text = (rp_from.fk_wastewater_networkelement)::text)))
     LEFT JOIN tww_od.wastewater_node to_wn ON (((to_wn.obj_id)::text = (rp_to.fk_wastewater_networkelement)::text)))
     LEFT JOIN tww_od.channel ch ON (((ch.obj_id)::text = (ws.obj_id)::text)))
     LEFT JOIN tww_vl.channel_function_hierarchic cfhi ON ((cfhi.code = ch.function_hierarchic)))
     LEFT JOIN tww_vl.channel_function_hydraulic cfhy ON ((cfhy.code = ch.function_hydraulic)))
  WHERE (ws.status = ANY (ARRAY[6530, 6533, 8493, 6529, 6526, 7959]));

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views