vw_catchment_area_totals_aggregated

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

obj_id

varchar

√

null

population

int4

√

null

surface_area

numeric

√

null

surface_imp

numeric

√

null

surface_red

numeric

√

null

sewer_infiltration_water

numeric

√

null

waste_water_production

numeric

√

null

population_dim

int4

√

null

surface_dim

numeric

√

null

surface_imp_dim

numeric

√

null

surface_red_dim

numeric

√

null

sewer_infiltration_water_dim

numeric

√

null

waste_water_production_dim

numeric

√

null

Relationships

Close relationships within degrees of separation

View Definition

SELECT ca_tot.obj_id,
    (round(ca_agg.population))::integer AS population,
    ca_agg.surface_area,
    ca_agg.surface_imp,
    ca_agg.surface_red,
    ca_agg.sewer_infiltration_water,
    ca_agg.waste_water_production,
    (round(ca_agg.population_dim))::integer AS population_dim,
    ca_agg.surface_dim,
    ca_agg.surface_imp_dim,
    ca_agg.surface_red_dim,
    ca_agg.sewer_infiltration_water_dim,
    ca_agg.waste_water_production_dim
   FROM (((tww_od.catchment_area_totals ca_tot
     LEFT JOIN tww_od.hydraulic_char_data hcd ON (((hcd.obj_id)::text = (ca_tot.fk_hydraulic_char_data)::text)))
     LEFT JOIN tww_od.wastewater_node wn ON (((hcd.fk_wastewater_node)::text = (wn.obj_id)::text)))
     LEFT JOIN ( WITH RECURSIVE log_card_tree AS (
                 SELECT log_card.obj_id,
                    log_card.fk_next_special_building,
                    ARRAY[(log_card.obj_id)::character varying] AS log_card_path
                   FROM tww_od.log_card
                  WHERE (log_card.fk_next_special_building IS NULL)
                UNION ALL
                 SELECT lc_1.obj_id,
                    lc_1.fk_next_special_building,
                    (lt.log_card_path || lc_1.obj_id)
                   FROM (tww_od.log_card lc_1
                     JOIN log_card_tree lt ON (((lc_1.fk_next_special_building)::text = (lt.obj_id)::text)))
                ), log_card_agg AS (
                 SELECT log_card_tree.obj_id AS child,
                    unnest(log_card_tree.log_card_path) AS parent
                   FROM log_card_tree
                ), ca AS (
                 SELECT catchment_area.obj_id,
                    catchment_area.fk_special_building_ww_current AS fk_log_card,
                    ((catchment_area.population_density_current)::numeric * catchment_area.surface_area) AS population,
                    catchment_area.surface_area,
                    ((catchment_area.surface_area * catchment_area.seal_factor_ww_current) / (100)::numeric) AS surface_imp,
                    ((catchment_area.surface_area * catchment_area.discharge_coefficient_ww_current) / (100)::numeric) AS surface_red,
                    catchment_area.sewer_infiltration_water_production_current AS sewer_infiltration_water,
                    catchment_area.waste_water_production_current AS waste_water_production,
                    0 AS population_dim,
                    0 AS surface_dim,
                    0 AS surface_imp_dim,
                    0 AS surface_red_dim,
                    0 AS sewer_infiltration_water_dim,
                    0 AS waste_water_production_dim
                   FROM tww_od.catchment_area
                  WHERE (catchment_area.fk_special_building_ww_current IS NOT NULL)
                UNION ALL
                 SELECT catchment_area.obj_id,
                    catchment_area.fk_special_building_rw_current AS fk_log_card,
                    0 AS population,
                        CASE
                            WHEN ((catchment_area.fk_special_building_rw_current)::text = (catchment_area.fk_special_building_ww_current)::text) THEN (0)::numeric
                            ELSE catchment_area.surface_area
                        END AS surface_area,
                    ((catchment_area.surface_area * catchment_area.seal_factor_rw_current) / (100)::numeric) AS surface_imp,
                    ((catchment_area.surface_area * catchment_area.discharge_coefficient_rw_current) / (100)::numeric) AS surface_red,
                    0 AS sewer_infiltration_water,
                    0 AS waste_water_production,
                    0 AS population_dim,
                    0 AS surface_dim,
                    0 AS surface_imp_dim,
                    0 AS surface_red_dim,
                    0 AS sewer_infiltration_water_dim,
                    0 AS waste_water_production_dim
                   FROM tww_od.catchment_area
                  WHERE (catchment_area.fk_special_building_rw_current IS NOT NULL)
                UNION ALL
                 SELECT catchment_area.obj_id,
                    catchment_area.fk_special_building_ww_planned AS fk_log_card,
                    0 AS population,
                    0 AS surface_area,
                    0 AS surface_imp,
                    0 AS surface_red,
                    0 AS sewer_infiltration_water,
                    0 AS waste_water_production,
                    ((catchment_area.population_density_planned)::numeric * catchment_area.surface_area) AS population_dim,
                    catchment_area.surface_area AS surface_dim,
                    ((catchment_area.surface_area * catchment_area.seal_factor_ww_planned) / (100)::numeric) AS surface_imp_dim,
                    ((catchment_area.surface_area * catchment_area.discharge_coefficient_ww_planned) / (100)::numeric) AS surface_red_dim,
                    0 AS sewer_infiltration_water_dim,
                    0 AS waste_water_production_dim
                   FROM tww_od.catchment_area
                  WHERE (catchment_area.fk_special_building_ww_planned IS NOT NULL)
                UNION ALL
                 SELECT catchment_area.obj_id,
                    catchment_area.fk_special_building_rw_planned AS fk_log_card,
                    0 AS population,
                    0 AS surface_area,
                    0 AS surface_imp,
                    0 AS surface_red,
                    0 AS sewer_infiltration_water,
                    0 AS waste_water_production,
                    0 AS population_dim,
                        CASE
                            WHEN ((catchment_area.fk_special_building_rw_planned)::text = (catchment_area.fk_special_building_ww_planned)::text) THEN (0)::numeric
                            ELSE catchment_area.surface_area
                        END AS surface_dim,
                    ((catchment_area.surface_area * catchment_area.seal_factor_rw_planned) / (100)::numeric) AS surface_imp_dim,
                    ((catchment_area.surface_area * catchment_area.discharge_coefficient_rw_planned) / (100)::numeric) AS surface_red_dim,
                    0 AS sewer_infiltration_water_dim,
                    0 AS waste_water_production_dim
                   FROM tww_od.catchment_area
                  WHERE (catchment_area.fk_special_building_rw_planned IS NOT NULL)
                ), ca_sums AS (
                 SELECT main_lc.obj_id,
                    main_lc.fk_pwwf_wastewater_node,
                    sum(ca.population) AS population,
                    sum(ca.surface_area) AS surface_area,
                    sum(ca.surface_imp) AS surface_imp,
                    sum(ca.surface_red) AS surface_red,
                    sum(ca.sewer_infiltration_water) AS sewer_infiltration_water,
                    sum(ca.waste_water_production) AS waste_water_production,
                    sum(ca.population_dim) AS population_dim,
                    sum(ca.surface_dim) AS surface_dim,
                    sum(ca.surface_imp_dim) AS surface_imp_dim,
                    sum(ca.surface_red_dim) AS surface_red_dim,
                    sum(ca.sewer_infiltration_water_dim) AS sewer_infiltration_water_dim,
                    sum(ca.waste_water_production_dim) AS waste_water_production_dim
                   FROM ((ca
                     LEFT JOIN tww_od.log_card lc_1 ON (((ca.fk_log_card)::text = (lc_1.obj_id)::text)))
                     LEFT JOIN tww_od.log_card main_lc ON (((main_lc.obj_id)::text = (lc_1.fk_main_structure)::text)))
                  GROUP BY main_lc.obj_id
                )
         SELECT lca.parent AS obj_id,
            lc.fk_pwwf_wastewater_node,
            sum(ca_sums_c.population) AS population,
            sum(ca_sums_p.surface_area) AS surface_area,
            sum(ca_sums_p.surface_imp) AS surface_imp,
            sum(ca_sums_p.surface_red) AS surface_red,
            sum(ca_sums_c.sewer_infiltration_water) AS sewer_infiltration_water,
            sum(ca_sums_c.waste_water_production) AS waste_water_production,
            sum(ca_sums_c.population_dim) AS population_dim,
            sum(ca_sums_p.surface_dim) AS surface_dim,
            sum(ca_sums_p.surface_imp_dim) AS surface_imp_dim,
            sum(ca_sums_p.surface_red_dim) AS surface_red_dim,
            sum(ca_sums_c.sewer_infiltration_water_dim) AS sewer_infiltration_water_dim,
            sum(ca_sums_c.waste_water_production_dim) AS waste_water_production_dim
           FROM (((log_card_agg lca
             LEFT JOIN tww_od.log_card lc ON (((lca.parent)::text = (lc.obj_id)::text)))
             LEFT JOIN ca_sums ca_sums_c ON (((ca_sums_c.obj_id)::text = (lca.child)::text)))
             LEFT JOIN ca_sums ca_sums_p ON (((ca_sums_p.obj_id)::text = (lca.parent)::text)))
          GROUP BY lca.parent, lc.fk_pwwf_wastewater_node) ca_agg ON (((ca_agg.fk_pwwf_wastewater_node)::text = (wn.obj_id)::text)));

vw_catchment_area_totals_aggregated

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views