swmm_vw_subcatchments

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

name

text

2147483647

√

null

raingage

varchar

2147483647

√

null

outlet

text

2147483647

√

null

area

float8

17,17

√

null

percimperv

numeric

√

null

width

float8

17,17

√

null

percslope

numeric

√

null

curblen

int4

√

null

snowpack

varchar

2147483647

√

null

description

text

2147483647

√

null

tag

varchar

√

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 concat(replace((obj_id)::text, ' '::text, '_'::text), '_', state) AS name,
    (concat('raingage@', replace((obj_id)::text, ' '::text, '_'::text)))::character varying AS raingage,
        CASE
            WHEN (state = 'rw_current'::text) THEN (fk_wastewater_networkelement_rw_current)::text
            WHEN (state = 'rw_planned'::text) THEN (fk_wastewater_networkelement_rw_planned)::text
            WHEN (state = 'ww_current'::text) THEN (fk_wastewater_networkelement_ww_current)::text
            WHEN (state = 'ww_planned'::text) THEN (fk_wastewater_networkelement_ww_planned)::text
            ELSE replace((obj_id)::text, ' '::text, '_'::text)
        END AS outlet,
        CASE
            WHEN (surface_area IS NULL) THEN (st_area(perimeter_geometry) / (10000)::double precision)
            WHEN (surface_area < 0.01) THEN (st_area(perimeter_geometry) / (10000)::double precision)
            ELSE ((surface_area)::numeric)::double precision
        END AS area,
        CASE
            WHEN (state = 'rw_current'::text) THEN discharge_coefficient_rw_current
            WHEN (state = 'rw_planned'::text) THEN discharge_coefficient_rw_planned
            WHEN (state = 'ww_current'::text) THEN discharge_coefficient_ww_current
            WHEN (state = 'ww_planned'::text) THEN discharge_coefficient_ww_planned
            ELSE (0)::numeric
        END AS percimperv,
        CASE
            WHEN (wn_geom IS NOT NULL) THEN ((st_maxdistance(wn_geom, st_exteriorring(perimeter_geometry)) + st_distance(wn_geom, st_exteriorring(perimeter_geometry))) / (2)::double precision)
            ELSE ((st_maxdistance(st_centroid(perimeter_geometry), st_exteriorring(perimeter_geometry)) + st_distance(st_centroid(perimeter_geometry), st_exteriorring(perimeter_geometry))) / (2)::double precision)
        END AS width,
    0.5 AS percslope,
    0 AS curblen,
    NULL::character varying AS snowpack,
        CASE
            WHEN (fk_wastewater_networkelement_ww_current IS NOT NULL) THEN
            CASE
                WHEN (waste_water_production_current IS NOT NULL) THEN concat('catchment_area: ', obj_id, ': DWF baseline is computed from waste_water_production_current')
                ELSE
                CASE
                    WHEN ((surface_area IS NOT NULL) AND (surface_area <> (0)::numeric)) THEN concat('catchment_area: ', obj_id, ': DWF baseline is computed from surface_area, population_density_current and a default production of 160 Litre / inhabitant /day')
                    ELSE concat('catchment_area: ', obj_id, ': DWF baseline is computed from the geometric area, population_density_current and a default production of 160 Litre / inhabitant /day')
                END
            END
            WHEN (fk_wastewater_networkelement_ww_planned IS NOT NULL) THEN
            CASE
                WHEN (waste_water_production_planned IS NOT NULL) THEN concat('catchment_area: ', obj_id, ': DWF baseline is computed from waste_water_production_planned')
                ELSE
                CASE
                    WHEN ((surface_area IS NOT NULL) AND (surface_area <> (0)::numeric)) THEN concat('catchment_area: ', obj_id, ': DWF baseline is computed from surface_area, population_density_planned and a default production of 160 Litre / inhabitant /day')
                    ELSE concat('catchment_area: ', obj_id, ': DWF baseline is computed from the geometric area, population_density_planned and a default production of 160 Litre / inhabitant /day')
                END
            END
            WHEN (fk_wastewater_networkelement_rw_current IS NOT NULL) THEN NULL::text
            WHEN (fk_wastewater_networkelement_rw_planned IS NOT NULL) THEN NULL::text
            ELSE NULL::text
        END AS description,
    obj_id AS tag,
    (st_curvetoline(perimeter_geometry))::geometry(Polygon,2056) AS geom,
        CASE
            WHEN ((state = 'rw_current'::text) OR (state = 'ww_current'::text)) THEN 'current'::text
            WHEN ((state = 'rw_planned'::text) OR (state = 'ww_planned'::text)) THEN 'planned'::text
            ELSE 'planned'::text
        END AS state,
        CASE
            WHEN (_function_hierarchic = ANY (ARRAY[5062, 5064, 5066, 5068, 5069, 5070, 5071, 5072, 5074])) THEN 'primary'::text
            ELSE 'secondary'::text
        END AS hierarchy,
    wn_obj_id AS obj_id
   FROM ( SELECT ca_1.obj_id,
            ca_1.direct_discharge_current,
            ca_1.direct_discharge_planned,
            ca_1.discharge_coefficient_rw_current,
            ca_1.discharge_coefficient_rw_planned,
            ca_1.discharge_coefficient_ww_current,
            ca_1.discharge_coefficient_ww_planned,
            ca_1.drainage_system_current,
            ca_1.drainage_system_planned,
            ca_1.identifier,
            ca_1.infiltration_current,
            ca_1.infiltration_planned,
            ca_1.perimeter_geometry,
            ca_1.population_density_current,
            ca_1.population_density_planned,
            ca_1.remark,
            ca_1.retention_current,
            ca_1.retention_planned,
            ca_1.runoff_limit_current,
            ca_1.runoff_limit_planned,
            ca_1.seal_factor_rw_current,
            ca_1.seal_factor_rw_planned,
            ca_1.seal_factor_ww_current,
            ca_1.seal_factor_ww_planned,
            ca_1.sewer_infiltration_water_production_current,
            ca_1.sewer_infiltration_water_production_planned,
            ca_1.surface_area,
            ca_1.waste_water_production_current,
            ca_1.waste_water_production_planned,
            ca_1.last_modification,
            ca_1.fk_dataowner,
            ca_1.fk_provider,
            ca_1.fk_wastewater_networkelement_rw_current,
            ca_1.fk_wastewater_networkelement_rw_planned,
            ca_1.fk_wastewater_networkelement_ww_planned,
            ca_1.fk_wastewater_networkelement_ww_current,
            ca_1.fk_special_building_rw_planned,
            ca_1.fk_special_building_rw_current,
            ca_1.fk_special_building_ww_planned,
            ca_1.fk_special_building_ww_current,
            wn.situation3d_geometry AS wn_geom,
            'rw_current'::text AS state,
            wn.obj_id AS wn_obj_id,
            wn._function_hierarchic
           FROM ((tww_od.catchment_area ca_1
             JOIN tww_od.wastewater_networkelement ne ON (((ne.obj_id)::text = (ca_1.fk_wastewater_networkelement_rw_current)::text)))
             LEFT JOIN tww_od.wastewater_node wn ON (((wn.obj_id)::text = (ne.obj_id)::text)))
        UNION ALL
         SELECT ca_1.obj_id,
            ca_1.direct_discharge_current,
            ca_1.direct_discharge_planned,
            ca_1.discharge_coefficient_rw_current,
            ca_1.discharge_coefficient_rw_planned,
            ca_1.discharge_coefficient_ww_current,
            ca_1.discharge_coefficient_ww_planned,
            ca_1.drainage_system_current,
            ca_1.drainage_system_planned,
            ca_1.identifier,
            ca_1.infiltration_current,
            ca_1.infiltration_planned,
            ca_1.perimeter_geometry,
            ca_1.population_density_current,
            ca_1.population_density_planned,
            ca_1.remark,
            ca_1.retention_current,
            ca_1.retention_planned,
            ca_1.runoff_limit_current,
            ca_1.runoff_limit_planned,
            ca_1.seal_factor_rw_current,
            ca_1.seal_factor_rw_planned,
            ca_1.seal_factor_ww_current,
            ca_1.seal_factor_ww_planned,
            ca_1.sewer_infiltration_water_production_current,
            ca_1.sewer_infiltration_water_production_planned,
            ca_1.surface_area,
            ca_1.waste_water_production_current,
            ca_1.waste_water_production_planned,
            ca_1.last_modification,
            ca_1.fk_dataowner,
            ca_1.fk_provider,
            ca_1.fk_wastewater_networkelement_rw_current,
            ca_1.fk_wastewater_networkelement_rw_planned,
            ca_1.fk_wastewater_networkelement_ww_planned,
            ca_1.fk_wastewater_networkelement_ww_current,
            ca_1.fk_special_building_rw_planned,
            ca_1.fk_special_building_rw_current,
            ca_1.fk_special_building_ww_planned,
            ca_1.fk_special_building_ww_current,
            wn.situation3d_geometry AS wn_geom,
            'rw_planned'::text AS state,
            wn.obj_id AS wn_obj_id,
            wn._function_hierarchic
           FROM ((tww_od.catchment_area ca_1
             JOIN tww_od.wastewater_networkelement ne ON (((ne.obj_id)::text = (ca_1.fk_wastewater_networkelement_rw_planned)::text)))
             LEFT JOIN tww_od.wastewater_node wn ON (((wn.obj_id)::text = (ne.obj_id)::text)))
        UNION ALL
         SELECT ca_1.obj_id,
            ca_1.direct_discharge_current,
            ca_1.direct_discharge_planned,
            ca_1.discharge_coefficient_rw_current,
            ca_1.discharge_coefficient_rw_planned,
            ca_1.discharge_coefficient_ww_current,
            ca_1.discharge_coefficient_ww_planned,
            ca_1.drainage_system_current,
            ca_1.drainage_system_planned,
            ca_1.identifier,
            ca_1.infiltration_current,
            ca_1.infiltration_planned,
            ca_1.perimeter_geometry,
            ca_1.population_density_current,
            ca_1.population_density_planned,
            ca_1.remark,
            ca_1.retention_current,
            ca_1.retention_planned,
            ca_1.runoff_limit_current,
            ca_1.runoff_limit_planned,
            ca_1.seal_factor_rw_current,
            ca_1.seal_factor_rw_planned,
            ca_1.seal_factor_ww_current,
            ca_1.seal_factor_ww_planned,
            ca_1.sewer_infiltration_water_production_current,
            ca_1.sewer_infiltration_water_production_planned,
            ca_1.surface_area,
            ca_1.waste_water_production_current,
            ca_1.waste_water_production_planned,
            ca_1.last_modification,
            ca_1.fk_dataowner,
            ca_1.fk_provider,
            ca_1.fk_wastewater_networkelement_rw_current,
            ca_1.fk_wastewater_networkelement_rw_planned,
            ca_1.fk_wastewater_networkelement_ww_planned,
            ca_1.fk_wastewater_networkelement_ww_current,
            ca_1.fk_special_building_rw_planned,
            ca_1.fk_special_building_rw_current,
            ca_1.fk_special_building_ww_planned,
            ca_1.fk_special_building_ww_current,
            wn.situation3d_geometry AS wn_geom,
            'ww_current'::text AS state,
            wn.obj_id AS wn_obj_id,
            wn._function_hierarchic
           FROM ((tww_od.catchment_area ca_1
             JOIN tww_od.wastewater_networkelement ne ON (((ne.obj_id)::text = (ca_1.fk_wastewater_networkelement_ww_current)::text)))
             LEFT JOIN tww_od.wastewater_node wn ON (((wn.obj_id)::text = (ne.obj_id)::text)))
        UNION ALL
         SELECT ca_1.obj_id,
            ca_1.direct_discharge_current,
            ca_1.direct_discharge_planned,
            ca_1.discharge_coefficient_rw_current,
            ca_1.discharge_coefficient_rw_planned,
            ca_1.discharge_coefficient_ww_current,
            ca_1.discharge_coefficient_ww_planned,
            ca_1.drainage_system_current,
            ca_1.drainage_system_planned,
            ca_1.identifier,
            ca_1.infiltration_current,
            ca_1.infiltration_planned,
            ca_1.perimeter_geometry,
            ca_1.population_density_current,
            ca_1.population_density_planned,
            ca_1.remark,
            ca_1.retention_current,
            ca_1.retention_planned,
            ca_1.runoff_limit_current,
            ca_1.runoff_limit_planned,
            ca_1.seal_factor_rw_current,
            ca_1.seal_factor_rw_planned,
            ca_1.seal_factor_ww_current,
            ca_1.seal_factor_ww_planned,
            ca_1.sewer_infiltration_water_production_current,
            ca_1.sewer_infiltration_water_production_planned,
            ca_1.surface_area,
            ca_1.waste_water_production_current,
            ca_1.waste_water_production_planned,
            ca_1.last_modification,
            ca_1.fk_dataowner,
            ca_1.fk_provider,
            ca_1.fk_wastewater_networkelement_rw_current,
            ca_1.fk_wastewater_networkelement_rw_planned,
            ca_1.fk_wastewater_networkelement_ww_planned,
            ca_1.fk_wastewater_networkelement_ww_current,
            ca_1.fk_special_building_rw_planned,
            ca_1.fk_special_building_rw_current,
            ca_1.fk_special_building_ww_planned,
            ca_1.fk_special_building_ww_current,
            wn.situation3d_geometry AS wn_geom,
            'ww_planned'::text AS state,
            wn.obj_id AS wn_obj_id,
            wn._function_hierarchic
           FROM ((tww_od.catchment_area ca_1
             JOIN tww_od.wastewater_networkelement ne ON (((ne.obj_id)::text = (ca_1.fk_wastewater_networkelement_ww_planned)::text)))
             LEFT JOIN tww_od.wastewater_node wn ON (((wn.obj_id)::text = (ne.obj_id)::text)))) ca;

swmm_vw_subcatchments

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views