tww
.tww_app
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
ft_vw_tww_wastewater_structure_update()
Parameters
Name
Type
Mode
IN
Definition
DECLARE dx float; dy float; BEGIN UPDATE tww_od.cover co SET brand = NEW.co_brand , cover_shape = NEW.co_shape , diameter = NEW.co_diameter , fastening = NEW.co_fastening , level = NEW.co_level , material = NEW.co_material , positional_accuracy = NEW.co_positional_accuracy -- , situation3d_geometry = NEW.co_situation3d_geometry , sludge_bucket = NEW.co_sludge_bucket , venting = NEW.co_venting WHERE obj_id = OLD.co_obj_id; UPDATE tww_od.structure_part sp SET fk_dataowner = NEW.fk_dataowner , fk_provider = NEW.fk_provider -- , fk_wastewater_structure = NEW.co_fk_wastewater_structure , identifier = NEW.co_identifier , last_modification = NEW.last_modification , remark = NEW.co_remark , renovation_demand = NEW.co_renovation_demand WHERE obj_id = OLD.co_obj_id; UPDATE tww_od.wastewater_structure ws SET obj_id = NEW.obj_id -- , _bottom_label = NEW._bottom_label -- , _cover_label = NEW._cover_label -- , _depth = NEW._depth -- , _input_label = NEW._input_label -- , _label = NEW._label -- , _output_label = NEW._output_label , accessibility = NEW.accessibility , condition_score = NEW.condition_score , contract_section = NEW.contract_section -- , detail_geometry3d_geometry = NEW.detail_geometry3d_geometry , elevation_determination = NEW.elevation_determination , financing = NEW.financing , fk_dataowner = NEW.fk_dataowner -- , fk_main_cover = NEW.fk_main_cover -- , fk_main_wastewater_node = NEW.fk_main_wastewater_node , fk_operator = NEW.fk_operator , fk_owner = NEW.fk_owner , fk_provider = NEW.fk_provider , gross_costs = NEW.gross_costs , identifier = NEW.identifier , inspection_interval = NEW.inspection_interval -- , last_modification = NEW.last_modification , location_name = NEW.location_name , records = NEW.records , remark = NEW.remark , renovation_necessity = NEW.renovation_necessity , replacement_value = NEW.replacement_value , rv_base_year = NEW.rv_base_year , rv_construction_type = NEW.rv_construction_type , status = NEW.status , status_survey_year = NEW.status_survey_year , structure_condition = NEW.structure_condition , subsidies = NEW.subsidies , urgency_figure = NEW.urgency_figure , year_of_construction = NEW.year_of_construction , year_of_replacement = NEW.year_of_replacement WHERE obj_id = OLD.obj_id; UPDATE tww_od.wastewater_node wn SET -- _function_hierarchic = NEW.wn__function_hierarchic -- _status = NEW.wn__status -- _usage_current = NEW.wn__usage_current backflow_level_current = NEW.wn_backflow_level_current , bottom_level = NEW.wn_bottom_level , elevation_accuracy = NEW.wn_elevation_accuracy , fk_hydr_geometry = NEW.wn_fk_hydr_geometry , function_node_amelioration = NEW.wn_function_node_amelioration -- , situation3d_geometry = NEW.wn_situation3d_geometry , wwtp_number = NEW.wn_wwtp_number WHERE obj_id = OLD.wn_obj_id; UPDATE tww_od.wastewater_networkelement ne SET fk_dataowner = NEW.wn_fk_dataowner , fk_provider = NEW.wn_fk_provider , fk_wastewater_structure = NEW.wn_fk_wastewater_structure , identifier = NEW.wn_identifier , last_modification = NEW.wn_last_modification , remark = NEW.wn_remark WHERE obj_id = OLD.wn_obj_id; IF OLD.ws_type <> NEW.ws_type THEN CASE WHEN OLD.ws_type <> 'unknown' THEN BEGIN EXECUTE FORMAT('DELETE FROM tww_od.%I WHERE obj_id = %L',OLD.ws_type,OLD.obj_id); END; END CASE; CASE WHEN NEW.ws_type = ANY(ARRAY['manhole','special_structure','discharge_point','infiltration_installation','drainless_toilet','wwtp_structure','small_treatment_plant']) THEN BEGIN EXECUTE FORMAT('INSERT INTO tww_od.%I(obj_id) VALUES (%L)',NEW.ws_type,OLD.obj_id); END; END CASE; END IF; CASE WHEN NEW.ws_type = 'manhole' THEN UPDATE tww_od.manhole ws SET -- _orientation = NEW.ma__orientation amphibian_exit = NEW.ma_amphibian_exit , dimension1 = NEW.ma_dimension1 , dimension2 = NEW.ma_dimension2 , function = NEW.ma_function , material = NEW.ma_material , possibility_intervention = NEW.ma_possibility_intervention , surface_inflow = NEW.ma_surface_inflow WHERE obj_id = OLD.obj_id; WHEN NEW.ws_type = 'special_structure' THEN UPDATE tww_od.special_structure ss SET amphibian_exit = NEW.ss_amphibian_exit , bypass = NEW.ss_bypass , emergency_overflow = NEW.ss_emergency_overflow , function = NEW.ss_function , possibility_intervention = NEW.ss_possibility_intervention , stormwater_tank_arrangement = NEW.ss_stormwater_tank_arrangement , upper_elevation = NEW.ss_upper_elevation WHERE obj_id = OLD.obj_id; WHEN NEW.ws_type = 'discharge_point' THEN UPDATE tww_od.discharge_point dp SET highwater_level = NEW.dp_highwater_level , relevance = NEW.dp_relevance , terrain_level = NEW.dp_terrain_level , upper_elevation = NEW.dp_upper_elevation , water_course_number = NEW.dp_water_course_number , water_course_segment_canton = NEW.dp_water_course_segment_canton , waterlevel_hydraulic = NEW.dp_waterlevel_hydraulic WHERE obj_id = OLD.obj_id; WHEN NEW.ws_type = 'infiltration_installation' THEN UPDATE tww_od.infiltration_installation ii SET absorption_capacity = NEW.ii_absorption_capacity , defects = NEW.ii_defects , dimension1 = NEW.ii_dimension1 , dimension2 = NEW.ii_dimension2 , distance_to_aquifer = NEW.ii_distance_to_aquifer , effective_area = NEW.ii_effective_area , emergency_overflow = NEW.ii_emergency_overflow , filling_material = NEW.ii_filling_material , fk_dss15_aquifer = NEW.ii_fk_dss15_aquifer , kind = NEW.ii_kind , labeling = NEW.ii_labeling , seepage_utilization = NEW.ii_seepage_utilization , upper_elevation = NEW.ii_upper_elevation , vehicle_access = NEW.ii_vehicle_access , watertightness = NEW.ii_watertightness WHERE obj_id = OLD.obj_id; ELSE -- do nothing END CASE; -- Cover geometry has been moved IF NOT ST_Equals( OLD.situation3d_geometry, NEW.situation3d_geometry) THEN dx = ST_X(NEW.situation3d_geometry) - ST_X(OLD.situation3d_geometry); dy = ST_Y(NEW.situation3d_geometry) - ST_Y(OLD.situation3d_geometry); -- Move wastewater node as well -- comment: TRANSLATE((ST_MakePoint(500, 900, 'NaN')), 10, 20, 0) would return NaN NaN NaN - so we have this workaround UPDATE tww_od.wastewater_node WN SET situation3d_geometry = ST_SetSRID( ST_MakePoint( ST_X(ST_TRANSLATE(ST_MakePoint(ST_X(WN.situation3d_geometry), ST_Y(WN.situation3d_geometry)), dx, dy )), ST_Y(ST_TRANSLATE(ST_MakePoint(ST_X(WN.situation3d_geometry), ST_Y(WN.situation3d_geometry)), dx, dy )), ST_Z(WN.situation3d_geometry)), 2056 ) WHERE obj_id IN ( SELECT obj_id FROM tww_od.wastewater_networkelement WHERE fk_wastewater_structure = NEW.obj_id ); -- Move reach point node as well UPDATE tww_od.reach_point RP SET situation3d_geometry = ST_SetSRID( ST_MakePoint( ST_X(ST_TRANSLATE(ST_MakePoint(ST_X(RP.situation3d_geometry), ST_Y(RP.situation3d_geometry)), dx, dy )), ST_Y(ST_TRANSLATE(ST_MakePoint(ST_X(RP.situation3d_geometry), ST_Y(RP.situation3d_geometry)), dx, dy )), ST_Z(RP.situation3d_geometry)), 2056 ) WHERE obj_id IN ( SELECT RP.obj_id FROM tww_od.reach_point RP LEFT JOIN tww_od.wastewater_networkelement NE ON RP.fk_wastewater_networkelement = NE.obj_id WHERE NE.fk_wastewater_structure = NEW.obj_id ); -- Move covers UPDATE tww_od.cover CO SET situation3d_geometry = ST_SetSRID( ST_MakePoint( ST_X(ST_TRANSLATE(ST_MakePoint(ST_X(CO.situation3d_geometry), ST_Y(CO.situation3d_geometry)), dx, dy )), ST_Y(ST_TRANSLATE(ST_MakePoint(ST_X(CO.situation3d_geometry), ST_Y(CO.situation3d_geometry)), dx, dy )), ST_Z(CO.situation3d_geometry)), 2056 ) WHERE obj_id IN ( SELECT obj_id FROM tww_od.structure_part WHERE fk_wastewater_structure = NEW.obj_id ); -- Move reach(es) as well UPDATE tww_od.reach RE SET progression3d_geometry = ST_ForceCurve (ST_SetPoint( ST_CurveToLine (RE.progression3d_geometry ), 0, -- SetPoint index is 0 based, PointN index is 1 based. ST_SetSRID( ST_MakePoint( ST_X(ST_TRANSLATE(ST_MakePoint(ST_X(ST_PointN(RE.progression3d_geometry, 1)), ST_Y(ST_PointN(RE.progression3d_geometry, 1))), dx, dy )), ST_Y(ST_TRANSLATE(ST_MakePoint(ST_X(ST_PointN(RE.progression3d_geometry, 1)), ST_Y(ST_PointN(RE.progression3d_geometry, 1))), dx, dy )), ST_Z(ST_PointN(RE.progression3d_geometry, 1))), 2056 ) ) ) WHERE fk_reach_point_from IN ( SELECT RP.obj_id FROM tww_od.reach_point RP LEFT JOIN tww_od.wastewater_networkelement NE ON RP.fk_wastewater_networkelement = NE.obj_id WHERE NE.fk_wastewater_structure = NEW.obj_id ); UPDATE tww_od.reach RE SET progression3d_geometry = ST_ForceCurve( ST_SetPoint( ST_CurveToLine( RE.progression3d_geometry ), ST_NumPoints(RE.progression3d_geometry) - 1, ST_SetSRID( ST_MakePoint( ST_X(ST_TRANSLATE(ST_MakePoint(ST_X(ST_EndPoint(RE.progression3d_geometry)), ST_Y(ST_EndPoint(RE.progression3d_geometry))), dx, dy )), ST_Y(ST_TRANSLATE(ST_MakePoint(ST_X(ST_EndPoint(RE.progression3d_geometry)), ST_Y(ST_EndPoint(RE.progression3d_geometry))), dx, dy )), ST_Z(ST_PointN(RE.progression3d_geometry, 1))), 2056 ) ) ) WHERE fk_reach_point_to IN ( SELECT RP.obj_id FROM tww_od.reach_point RP LEFT JOIN tww_od.wastewater_networkelement NE ON RP.fk_wastewater_networkelement = NE.obj_id WHERE NE.fk_wastewater_structure = NEW.obj_id ); END IF; RETURN NEW; END;