swmm_vw_junctions

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

name

varchar

2147483647

√

null

invertelev

numeric

√

null

maxdepth

numeric

√

null

initdepth

float8

17,17

√

null

surchargedepth

float8

17,17

√

null

pondedarea

float8

17,17

√

null

description

text

2147483647

√

null

tag

text

2147483647

√

null

geom

geometry

2147483647

√

null

state

text

2147483647

√

null

hierarchy

text

2147483647

√

null

obj_id

varchar

√

null

Relationships

Close relationships within degrees of separation

View Definition

SELECT wn.obj_id AS name,
    COALESCE(wn.bottom_level, (0)::numeric) AS invertelev,
    (co.level - wn.bottom_level) AS maxdepth,
    NULL::double precision AS initdepth,
    NULL::double precision AS surchargedepth,
    NULL::double precision AS pondedarea,
    ws.identifier AS description,
    concat_ws(','::text, 'manhole', mf.value_en) AS tag,
    wn.situation3d_geometry AS geom,
        CASE
            WHEN (ws_st.vsacode = ANY (ARRAY[7959, 6529, 6526])) THEN 'planned'::text
            ELSE 'current'::text
        END AS state,
        CASE
            WHEN (ch_fh.vsacode = ANY (ARRAY[5062, 5064, 5066, 5068, 5069, 5070, 5071, 5072, 5074])) THEN 'primary'::text
            ELSE 'secondary'::text
        END AS hierarchy,
    wn.obj_id
   FROM (((((((tww_od.manhole ma
     LEFT JOIN tww_od.wastewater_structure ws ON (((ws.obj_id)::text = (ma.obj_id)::text)))
     LEFT JOIN tww_od.wastewater_networkelement we ON (((we.fk_wastewater_structure)::text = (ws.obj_id)::text)))
     LEFT JOIN tww_od.wastewater_node wn ON (((wn.obj_id)::text = (we.obj_id)::text)))
     LEFT JOIN tww_od.cover co ON (((ws.fk_main_cover)::text = (co.obj_id)::text)))
     LEFT JOIN tww_vl.manhole_function mf ON ((ma.function = mf.code)))
     LEFT JOIN tww_vl.wastewater_structure_status ws_st ON ((ws_st.code = ws.status)))
     LEFT JOIN tww_vl.channel_function_hierarchic ch_fh ON ((ch_fh.code = wn._function_hierarchic)))
  WHERE ((wn.obj_id IS NOT NULL) AND (ws_st.vsacode = ANY (ARRAY[6530, 6533, 8493, 6529, 6526, 7959])))
UNION
 SELECT wn.obj_id AS name,
    COALESCE(wn.bottom_level, (0)::numeric) AS invertelev,
    (co.level - wn.bottom_level) AS maxdepth,
    NULL::double precision AS initdepth,
    NULL::double precision AS surchargedepth,
    NULL::double precision AS pondedarea,
    ws.identifier AS description,
    concat_ws(','::text, 'special_structure', ss_fu.value_en) AS tag,
    wn.situation3d_geometry AS geom,
        CASE
            WHEN (ws_st.vsacode = ANY (ARRAY[7959, 6529, 6526])) THEN 'planned'::text
            ELSE 'current'::text
        END AS state,
        CASE
            WHEN (ch_fh.vsacode = ANY (ARRAY[5062, 5064, 5066, 5068, 5069, 5070, 5071, 5072, 5074])) THEN 'primary'::text
            ELSE 'secondary'::text
        END AS hierarchy,
    wn.obj_id
   FROM (((((((tww_od.special_structure ss
     LEFT JOIN tww_od.wastewater_structure ws ON (((ws.obj_id)::text = (ss.obj_id)::text)))
     LEFT JOIN tww_od.wastewater_networkelement we ON (((we.fk_wastewater_structure)::text = (ws.obj_id)::text)))
     LEFT JOIN tww_od.wastewater_node wn ON (((wn.obj_id)::text = (we.obj_id)::text)))
     LEFT JOIN tww_od.cover co ON (((ws.fk_main_cover)::text = (co.obj_id)::text)))
     LEFT JOIN tww_vl.special_structure_function ss_fu ON ((ss_fu.code = ss.function)))
     LEFT JOIN tww_vl.wastewater_structure_status ws_st ON ((ws_st.code = ws.status)))
     LEFT JOIN tww_vl.channel_function_hierarchic ch_fh ON ((ch_fh.code = wn._function_hierarchic)))
  WHERE ((wn.obj_id IS NOT NULL) AND (ws_st.vsacode = ANY (ARRAY[6530, 6533, 8493, 6529, 6526, 7959])) AND (ss_fu.vsacode <> ALL (ARRAY[6397, 6398, 3348, 336, 6478, 3673, 3674, 5574, 3675, 3676, 5575, 5576, 3677])))
UNION
 SELECT COALESCE(from_wn.obj_id, (concat('from_node@', re.obj_id))::character varying) AS name,
    COALESCE(from_wn.bottom_level, (0)::numeric) AS invertelev,
    0 AS maxdepth,
    NULL::double precision AS initdepth,
    NULL::double precision AS surchargedepth,
    NULL::double precision AS pondedarea,
    COALESCE(from_wn.obj_id, (concat('from_node@', re.obj_id))::character varying) AS description,
    'junction without structure'::text AS tag,
    COALESCE(from_wn.situation3d_geometry, st_startpoint(re.progression3d_geometry)) AS geom,
        CASE
            WHEN (ws_st.vsacode = ANY (ARRAY[7959, 6529, 6526])) THEN 'planned'::text
            ELSE 'current'::text
        END AS state,
        CASE
            WHEN (ch_fh.vsacode = ANY (ARRAY[5062, 5064, 5066, 5068, 5069, 5070, 5071, 5072, 5074])) THEN 'primary'::text
            ELSE 'secondary'::text
        END AS hierarchy,
    COALESCE(from_wn.obj_id, re.obj_id) AS obj_id
   FROM (((((((((tww_od.reach re
     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.wastewater_node from_wn ON (((from_wn.obj_id)::text = (rp_from.fk_wastewater_networkelement)::text)))
     LEFT JOIN tww_od.channel ch ON (((ch.obj_id)::text = (ws.obj_id)::text)))
     LEFT JOIN tww_od.wastewater_networkelement we ON (((from_wn.obj_id)::text = (we.obj_id)::text)))
     LEFT JOIN tww_od.wastewater_structure ws_node ON (((we.fk_wastewater_structure)::text = (ws_node.obj_id)::text)))
     LEFT JOIN tww_vl.wastewater_structure_status ws_st ON ((ws_st.code = ws.status)))
     LEFT JOIN tww_vl.channel_function_hierarchic ch_fh ON ((ch_fh.code = ch.function_hierarchic)))
  WHERE ((ws_st.vsacode = ANY (ARRAY[6530, 6533, 8493, 6529, 6526, 7959])) AND (ws_node.* IS NULL))
UNION
 SELECT COALESCE(to_wn.obj_id, (concat('to_node@', re.obj_id))::character varying) AS name,
    COALESCE(to_wn.bottom_level, (0)::numeric) AS invertelev,
    0 AS maxdepth,
    NULL::double precision AS initdepth,
    NULL::double precision AS surchargedepth,
    NULL::double precision AS pondedarea,
    COALESCE(to_wn.obj_id, (concat('to_node@', re.obj_id))::character varying) AS description,
    'junction without structure'::text AS tag,
    COALESCE(to_wn.situation3d_geometry, st_endpoint(re.progression3d_geometry)) AS geom,
        CASE
            WHEN (ws_st.vsacode = ANY (ARRAY[7959, 6529, 6526])) THEN 'planned'::text
            ELSE 'current'::text
        END AS state,
        CASE
            WHEN (ch_fh.vsacode = ANY (ARRAY[5062, 5064, 5066, 5068, 5069, 5070, 5071, 5072, 5074])) THEN 'primary'::text
            ELSE 'secondary'::text
        END AS hierarchy,
    COALESCE(to_wn.obj_id, re.obj_id) AS obj_id
   FROM (((((((((tww_od.reach re
     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_to ON (((rp_to.obj_id)::text = (re.fk_reach_point_to)::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_od.wastewater_networkelement we ON (((to_wn.obj_id)::text = (we.obj_id)::text)))
     LEFT JOIN tww_od.wastewater_structure ws_node ON (((we.fk_wastewater_structure)::text = (ws_node.obj_id)::text)))
     LEFT JOIN tww_vl.wastewater_structure_status ws_st ON ((ws_st.code = ws.status)))
     LEFT JOIN tww_vl.channel_function_hierarchic ch_fh ON ((ch_fh.code = ch.function_hierarchic)))
  WHERE ((ws_st.vsacode = ANY (ARRAY[6530, 6533, 8493, 6529, 6526, 7959])) AND (ws_node.* IS NULL));

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views