The PostgreSQL extension for Hydrographic Applications Project (PgHydro) represents the first innovation of intelligence as an extension of spatial database management systems for use in water resources management that uses the sub-catchment network model and the logic elements present in the Pfafstetter basin coding system. PgHydro aims for an add-on implementation of 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 anyone dealing 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 simpler 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 management of the multiuser edition.
Last year, the pgh_raster was developed, which allows the insertion of the digital elevation model and products derived from it, such as drainage direction and flow accumulation. Functions allow retrieving the downstream or upstream pixel given a position as well as the elevation profile along a given specific geometry.
Another extension developed for pghydro was the pgh_hgm, a hydrogeomorphometric extension used to calculate information such as minimum and maximum elevation, slope, concentration time, time travel, and others related to drainage geometry and digital elevation. The pghydro project is officially and widely used by the National Water and Sanitation Agency of Brazil as a reference for the Brazilian Water Resources Management.