Development of queries for a spatio-temporal database to estimate runoff changes in urbanized watersheds
ISBN 978-85-88783-11-9
Authors
1Schafer, A.; 2Richa, C.; 3Moreira, D.
1FEDERAL UNIVERSITY OF PAMPA Email: alexandro.schafer@unipampa.edu.br
2FEDERAL UNIVERSITY OF PAMPA Email: cesarhuegel@live.com
3SENAI CIMATEC Email: davidson.moreira@gmail.com
Abstract
The planning of the use and occupation of land seeking to minimize the impacts resulting from the alterations of the surface cover of the land regarding the capacity of drainage support in a watershed requires the checking of these alterations and the understanding of their impact on the hydrological response of the watershed. Geographic Information Systems (GIS), associated with hydrological models, are commonly used to perform these studies. Due to the difficulty to deal with the temporal component of GIS, spatio-temporal databases (STDB) have been increasingly used because they enable the management of spatio-temporal objects and provide functionality of queries to these objects. An important step in designing STDB is the development of these queries. This paper discusses the development of queries to a spatio-temporal database designed to assist the monitoring of the watershed characteristics (land cover, soil and waterproofing) and the simulation of the impact of their changes in terms of runoff. The STDB was implemented in the database management system of the PostgreSQL/PostGIS. The curve number method of the Natural Resource Conservation Service (NRCS-CN) was incorporated into STDB, and it enabled the estimation of the runoff for a given precipitation event. The entities defined in the conceptual modeling phase were grouped into entities with no spatial representation (with or without variation along time); entities with spatial data; and entities with spatio-temporal data (which variation in geometry and/or in the attribute). For the spatio-temporal modeling of the STDB, we adopted a combined approach: an approach based on the time to organize the changes in the characteristics of the watershed and an approach based on the object for the storage structure of spatio-temporal entities. With this structure, it was possible to develop queries based on entity, on time and on events. Queries to verify the characteristics of the watershed and to estimate runoff were performed using mathematical operators and construction aggregated functions of Structured Query Language (SQL). The equations of the NRCS-CN method were implemented in SQL, allowing estimate of runoff directly in the STDB. For entities with a temporal component, we developed queries for recover the version of a valid object at a given time or time interval. This development is exemplified based on the analysis of the evolution of four objects along time, all belonging to the same theme. We considered four instants in time, which gave origin to three time intervals. From this analysis, we defined all the possibilities to change the objects in STDB and we structured queries to recover a complete scenario of the theme at any moment, using temporal operators. We then present the development of queries to deal with the events stored in the database, with the states of a given entity, and with this entity changes along time. Using the queries developed for STDB, it was possible to recover the valid data to a certain place and at a certain scenario, to check the changes in the characteristics of the watershed and to relate them to changes in the direct runoff.