identifier handling

[cymed]

As we already discussed in QGEP, identifiers should be handled on the provider side. For nodes, this can be solved with a sequence, while for reaches and channels it gets a bit complicated. I have made the following code ready, which only works for singular reaches

CREATE OR REPLACE FUNCTION tww_app.update_reach_identifier(
	_obj_id text--,
--	_all boolean DEFAULT false
    )
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
BEGIN
UPDATE tww_od.wastewater_networkelement  
SET 
	identifier=foo.identifier
FROM (	
	WITH identifiers AS (
		SELECT
			re.obj_id,
			ne.identifier,
			unnest(ARRAY[
				ws_from.identifier||'-'|| ws_to.identifier,
				ne_from.identifier||'-'|| ws_to.identifier,
				rp_from.identifier||'-'|| ws_to.identifier,
				ws_from.identifier||'-'|| ne_to.identifier,
				ne_from.identifier||'-'|| ne_to.identifier,
				rp_from.identifier||'-'|| ne_to.identifier,
				ws_from.identifier||'-'|| rp_to.identifier,
				ne_from.identifier||'-'|| rp_to.identifier,
				rp_from.identifier||'-'|| rp_to.identifier
			]) as candidate,
			generate_series(1,9) as rnk
		FROM tww_od.reach re
			LEFT JOIN tww_od.wastewater_networkelement ne ON ne.obj_id=re.obj_id
			LEFT JOIN tww_od.reach_point rp_from ON rp_from.obj_id = re.fk_reach_point_from
			LEFT JOIN tww_od.wastewater_networkelement ne_from ON rp_from.fk_wastewater_networkelement = ne_from.obj_id
			LEFT JOIN tww_od.wastewater_structure ws_from ON ws_from.obj_id = ne_from.fk_wastewater_structure
			LEFT JOIN tww_od.reach re_from on re_from.obj_id = ne_from.obj_id

			LEFT JOIN tww_od.reach_point rp_to ON rp_to.obj_id = re.fk_reach_point_to
			LEFT JOIN tww_od.wastewater_networkelement ne_to ON rp_to.fk_wastewater_networkelement = ne_to.obj_id
			LEFT JOIN tww_od.wastewater_structure ws_to ON ws_to.obj_id = ne_to.fk_wastewater_structure
			LEFT JOIN tww_od.reach re_to on re_to.obj_id = ne_to.obj_id
		WHERE TRUE 
		)

	SELECT DISTINCT
	i.obj_id,
	i.identifier as identifier_old,
	first_value(candidate) OVER w as identifier,
	first_value(rnk) OVER w as rnk
	FROM identifiers i
	LEFT JOIN tww_od.wastewater_networkelement ne 
	  ON ne.identifier=i.candidate AND ne.obj_id <>i.obj_id
	WHERE ne.obj_id IS NULL AND candidate IS NOT NULL
	WINDOW w AS (PARTITION BY i.obj_id ORDER by rnk)
	ORDER BY rnk desc
)foo
WHERE 
	foo.obj_id=wastewater_networkelement.obj_id;
END
$BODY$;

For Channels, a similar logic could be used, but it needs a bit more work to return the correct start and end nodes

[urskaufmann]

I do not use node or manhole identifiers-combination for reaches or channels because the identifier should not change and on the other side you should update it, if you have a new manhole or a new node (or in case of Blindanschluss: a new identifier of the channel/reach below). I struggled more than once when I thought it's possible to use the channel-identifier from a geonis-dataset for connecting the ktv-data to the reach/channel. I think it does not work...