FOSS4G 2022 general tracks

The PostgreSQL extension for Hydrographic Applications Project (PgHydro) represents the first innovation of intelligence as an extension on spatial database management systems for use in water resources management that uses the subcatchment network model and the logic elements present in the Pfafstetter basin coding system. PgHydro aims for an add-on implementation on a spatial database management system performed by a series of tables, queries, functions or views that can be used individually to assist in water resources decision making. These objects are the hydrography core of the intelligence system developed using free open source software that can be used by any person who deals with water resources management. To this end, this new conceptual model was implemented in the object-relational spatial database management system PostgreSQL/PostGIS respecting the integrity constraints related to the geometry of the mapped objects. These user-defined constraints respect the logical objects based on the Pfafstetter basin coding system and integrity constraints linked to the spatial relationships between objects, which follow the ISO SQL/MM specifications. The main advantage of using the pghydro extension is the possibility to process large datasets and complex queries using a more simple hydrography model and the tools and languages already available in spatial database management systems that work as a framework for the future development of new extensions related to water resources.
The pghydro functionalities can be run using a GUI developed in a QGIS plugin called PgHydro Tools. After the physical implementation of the pgHydro Scheme in the spatial database management system, the construction of the Pfafsteter hydrography dataset is started using the hydrography objects that make up the pgHydro Tools. The construction of this base is divided into seven stages: 1) Creation of the spatial database and creation of the pghydro extension; 2) insertion of the drainage lines and the drainage areas in the spatial database; 3) verification of the consistency of the drainage network geometries and topologies; 4) verification of the consistency of the drainage areas geometries and topologies; 5) verification of the consistency of topology between the drainage network and the drainage areas; 6) Pfafstetter basin coding and other information, finally; 7) export of the final Pfafstetter hydrography dataset. Optional steps are the systematization of river names and the multiuser edition management.
The pghydro project is officially and widely used by the National Water and Sanitation Agency of Brazil as a reference for the Water Resources Management of Brazil.