Network analysis capabilities

[vpicavet]

It would be interesting to be able to run some analytics on the water network. This would be a set of PostGIS queries computing statistics or specific indicators for the network.

A GUI could be designed on top of these queries allowing the user to set parameters, pick objects and more.

[m-kuhn]

FYI, over at QGEP we have built this with python(networkx library) but a database side is also worth evaluating. We have already built some interactive tools to work with the network analysis tool.

[haubourg]

We also started on SQL views to show differents topological errors on the fly.

[bogdanvaduva]

Here is an example of valve to close (i did not included district_node table which contains source nodes)

-- FUNCTION: qwat_od.fn_valve_to_close(integer)

-- DROP FUNCTION IF EXISTS qwat_od.fn_valve_to_close(integer);

CREATE OR REPLACE FUNCTION qwat_od.fn_valve_to_close( var_nod integer) RETURNS TABLE(seq integer, valve_id integer, valve_geometry geometry, pipe_id integer, fk_node integer) LANGUAGE 'plpgsql' COST 100 VOLATILE PARALLEL UNSAFE ROWS 1000

AS $BODY$ DECLARE r record; DECLARE var_node integer = 0; DECLARE var_seq integer = 0; BEGIN FOR r IN ( SELECT CASE WHEN v1.id IS NULL THEN v2.id ELSE v1.id END as id, CASE WHEN v1.id IS NULL THEN v2.geometry ELSE v1.geometry END as geometry, tbl.seq,qwat_od.pipe_reference.fk_pipe,tbl.fk_node FROM ( WITH a AS ( select qwat_od.fn_pipe_path(var_nod, dn.fk_node) as path,dn.fk_node from ( select from qwat_od.district_node where fk_district=( select id from qwat_od.district where ST_Within((SELECT geometry FROM qwat_od.node WHERE id=var_nod),geometry)) ) as dn ) SELECT (path::record).,a.fk_node FROM a ORDER BY fk_node,seq) as tbl LEFT JOIN qwat_od.pipe_reference ON qwat_od_pipe_reference.id=tbl.edge LEFT JOIN qwat_od.valve as v1 ON qwat_od_pipe_reference.fk_pipe=v1.fk_pipe LEFT JOIN qwat_od.valve as v2 ON tbl.edge=v2.fk_pipe WHERE CASE WHEN v1.id IS NULL THEN v2.geometry ELSE v1.geometry END IS NOT NULL ) LOOP IF (var_node = 0) THEN var_node = r.fk_node; ELSE IF (var_node != r.fk_node) THEN var_node = 0; var_seq = 0; END IF; END IF; IF (var_seq = 0) THEN IF (var_seq != -1) THEN var_seq = r.seq; END IF; ELSE IF (var_seq != r.seq) THEN var_seq = -1; END IF; END IF; --IF (var_seq > 0) THEN seq := r.seq; valve_id := r.id; valve_geometry := r.geometry; pipe_id := r.fk_pipe; fk_node := r.fk_node; RETURN NEXT; --END IF; END LOOP; END;

$BODY$;

CREATE OR REPLACE FUNCTION qwat_od.fn_pipe_path( var_nod_a integer, var_nod_b integer) RETURNS TABLE(seq integer, path_seq integer, nod bigint, edge bigint, cost double precision, agg_cost double precision, geometry geometry, path text) LANGUAGE 'sql' COST 100 VOLATILE PARALLEL UNSAFE ROWS 1000

AS $BODY$ SELECT path.*,geom.geometry,geom.json FROM pgr_dijkstra(' select id, fk_node_a as source, fk_node_b as target, CASE WHEN qwat_od.fn_element_valve_status(fk_pipe) THEN -1 ELSE COALESCE(st_length(qwat_od_vw_pipe_reference.geometry),1) END as cost, CASE WHEN qwat_od.fn_element_valve_status(fk_pipe) THEN -1 ELSE COALESCE(st_length(qwat_od_vw_pipe_reference.geometry),1) END as reverse_cost from qwat_od.vw_pipe_reference',var_nod_a,var_nod_b) as path LEFT JOIN ( SELECT id,geometry,ST_AsGeoJSON(geometry) as json FROM qwat_od.vw_pipe_reference ) as geom ON geom.id=path.edge $BODY$;

CREATE OR REPLACE FUNCTION qwat_od.fn_element_valve_status( var_pipe_id integer) RETURNS boolean LANGUAGE 'sql' COST 100 VOLATILE PARALLEL UNSAFE AS $BODY$ SELECT bool_or(closed) as closed FROM qwat_od.valve WHERE fk_pipe=var_pipe_id GROUP by fk_pipe $BODY$;

CREATE OR REPLACE VIEW qwat_od.vw_pipe_reference AS SELECT sp.id, sp.fk_node_a, sp.fk_node_b, sp.geometry, sp.fk_pipe FROM qwat_od.pipe_reference sp UNION SELECT p.id, p.fk_node_a, p.fk_node_b, p.geometry, p.id AS fk_pipe FROM qwat_od.pipe p WHERE NOT (p.id IN ( SELECT pipe_reference.fk_pipe FROM qwat_od.pipe_reference pipe_reference));

[bogdanvaduva]

Regarding Detect which subscribers must be notified

There are some elements that you have to consider like starting node (you have an non oriented graph).You can consider NetworkX for that. I do have a tool that does that but uses JAVA so ... I can only show a working example to who is interested.

[ponceta]

https://github.com/qwat/qwat-data-model/pull/345