Giswater uses PostgreSQL database to store all the data of your projects. It means network data, and also options data and result data.
When Giswater read the result file generated by the EPA software, Giswater store all this information into a specific tables configured to do this. In the case of a project of Water Supply the tables used by Giswater to store the result data are: rpt_node, rpt_arc, rpt_energy_usage, rpt_hydraulic_status and finally the catalog of results is stored into rpt_result_cat. When we create other simulation, and other and other again, the result data is stored in the same tables.
As the same time, Giswater only shows a single result into GIS software in order to make easy the analysis of this information. To do this, Giswater uses a ‘filter’ before show the results tables into the GIS software. Here you can see an example applied to arcs of water supply (arc minimum and arc maximum values)
CREATE OR REPLACE VIEW “SCHEMA_NAME”.v_rpt_arc AS
SELECT arc.arc_id,
result_selection.result_id,
max(rpt_arc.flow) AS max_flow,
min(rpt_arc.flow) AS min_flow,
max(rpt_arc.vel) AS max_vel,
min(rpt_arc.vel) AS min_vel,
max(rpt_arc.headloss) AS max_headloss,
min(rpt_arc.headloss) AS min_headloss,
max(rpt_arc.setting) AS max_setting,
min(rpt_arc.setting) AS min_setting,
max(rpt_arc.reaction) AS max_reaction,
min(rpt_arc.reaction) AS min_reaction,
max(rpt_arc.ffactor) AS max_ffactor,
min(rpt_arc.ffactor) AS min_ffactor,
arc.the_geom
FROM “SCHEMA_NAME”.arc
JOIN “SCHEMA_NAME”.rpt_arc ON rpt_arc.arc_id::text = arc.arc_id::text
JOIN “SCHEMA_NAME”.result_selection ON rpt_arc.result_id::text = result_selection.result_id::text
GROUP BY arc.arc_id, result_selection.result_id, arc.the_geom
ORDER BY arc.arc_id;
As you can see, there are two interesting questions in this sql expression;
– Only data from one result is showed (using JOIN between result_selection and data)
– Only maximun and minimum values are showed using GROUP BY and the functions of max(…) and min(…)
Questions:
1) Would you like show all time values, not only maximum or minimum values?
2) Would you like show all result values, not only data from one simulation?
It’s easy to do. Follow the next instructions:
Only you must do is to create new data views into database and then proceed to call this views from GIS interface. To create new data views into database you can use SQL file launcher of Giswater. To do this, you must to write a new SQL text file -using notepad++ or similar, with UTF-8 without BOOM codification- and define the creation of the views into it. Then you must launch this sql file using SQL file launcher of Giswater (Note: When you use SQL file launcher of Giswater, the SCHEMA_NAME label is replaced by the name of the schema your are working in).
If you are interested to solve the first question (show all time values from a single simulation) you must write into the sql text file the next SQL expressión:
CREATE VIEW “SCHEMA_NAME”.”v_rpt_all2_arc” AS
SELECT rpt_arc.id,
arc.arc_id,
result_selection.result_id,
rpt_arc.flow,
rpt_arc.vel,
rpt_arc.headloss,
rpt_arc.setting,
rpt_arc.reaction,
rpt_arc.ffactor,
arc.the_geom
FROM ((SCHEMA_NAME.arc
JOIN SCHEMA_NAME.rpt_arc ON (((rpt_arc.arc_id)::text = (arc.arc_id)::text)))
JOIN SCHEMA_NAME.result_selection ON (((rpt_arc.result_id)::text = (result_selection.result_id)::text)))
ORDER BY arc.arc_id;
As you can see, the constriction of only one result from result_selector has been deleted, and by this way all maximum and minimum values from all results will be shown on the view. Only you must do is proceed to call this view from GIS interface -using ‘Add PostGIS layers’ buttom- and enjoy the results.
On the other hand If you are interestet on the second question (show all maximum and minimum values from all simulation) you must write into the sql text file the next SQL expressión:
CREATE OR REPLACE VIEW “SCHEMA_NAME”.v_rpt_all2_arc AS
SELECT arc.arc_id,
result_selection.result_id,
max(rpt_arc.flow) AS max_flow,
min(rpt_arc.flow) AS min_flow,
max(rpt_arc.vel) AS max_vel,
min(rpt_arc.vel) AS min_vel,
max(rpt_arc.headloss) AS max_headloss,
min(rpt_arc.headloss) AS min_headloss,
max(rpt_arc.setting) AS max_setting,
min(rpt_arc.setting) AS min_setting,
max(rpt_arc.reaction) AS max_reaction,
min(rpt_arc.reaction) AS min_reaction,
max(rpt_arc.ffactor) AS max_ffactor,
min(rpt_arc.ffactor) AS min_ffactor,
arc.the_geom
FROM “SCHEMA_NAME”.arc
JOIN “SCHEMA_NAME”.rpt_arc ON rpt_arc.arc_id::text = arc.arc_id::text
GROUP BY arc.arc_id, result_selection.result_id, arc.the_geom
ORDER BY arc.arc_id;
As you can see, the constriction of only one result from result_selector has been deleted, and by this way all maximum and minimum values from all simulations will be shown on the view. Only you must do is proceed to call this view from GIS interface -using ‘Add PostGIS layers’ buttom- and enjoy the results.
Using the same procedure, you can create new views from node results data table, and you can use diferent combinations of SQL expressions in order to achieve your goals to improve your result data analysis.