SOCIO-ECONOMIC AND ENVIRONMENTAL SIG-DATABASE DESIGN: AN APPLICATION TO THE ARARICÁ COUNTY MANAGEMENT IN SOUTHERN BRAZIL M.A.F. Hansena, M. Tsaob, O. G. Wöhl Coelhoc a INPE/CRS, Instituto Nacional de Pesquisas Espaciais, Santa Maria/ RS, Brazil- mafhansen@gmail.com IQLS/UNILASALLE, Instituto de Química La Salle/Centro Universitário La Salle, Canoas/RS, Brazil tsao@unilasalle.edu.br c PPGeo/UNISINOS, Programa de Pós-Graduação em Geologia/Universidade do Vale do Rio dos Sinos, São Leopoldo/RS, Brazil- osmar@unisinos.br b Commission VI, WG VI/3 KEY WORDS: GIS, Database, Environment, Economy, Analysis, Management ABSTRACT: The design of a multipurpose database, mainly as supporting tool for decision makers and public administrators, needs to consider social, economics and environmental factors, which relationships can be better understood when distributed and analyzed along the geographical space. Heading the purpose of identifying land use conflicts and to build up sustainability indicators, spatial and nonspatial data were stored in a WEB-database by using the SPRING system, a GIS/RS software created by the Brazilian National Institute for Aerospace Research - INPE. An expert system, named ZEEBRA, which consider several criteria concerning the natural carrying capacity and human pressure over the environment, was used to analyze the stored information, as well as producing thematic status indexes and to point out the efficiency or lack of public administration actions. This article describes the conceptualization, design and implementation of a regional GIS database, presenting its application to the Araricá County in southern Brazil 1. INTRODUCTION Large quantities of environmental and socio-economic data are currently generated and gathered from different sources within the Rio dos Sinos Valley, mainly of them remaining without public access for further analysis and applications. Aiming to organize and using such information in environmental and socio-economic evaluation and planning, a common project, named DATASinos was carried out by two universities, UNISINOS- Universidade do Vale do Rio dos Sinos and UNILASALLE- Centro Universitário La Salle. The financial support was provided by the Regional Council for Development of Rio dos Sinos Valley, the CONSINOS- Conselho Regional de Desenvolvimento do Vale do Rio dos Sinos, and by the government research foundation, FAPERGS-Fundação Estadual de Amparo à Pesquisa do Estado do Rio Grande do Sul. Despite its main goal of retrieving stored spatial and non-spatial data, as well using it to assess environmental and socioeconomic dynamics, the DATASinos project also aim to support future uses as teaching applications of GIS/RS in basic and high schools, as well as public health and risks assessment. In order to allow public access and data extraction by expert systems for each thematic application, WEB facilities were implemented. The present paper describes the design and implementation of the DATASinos SIG-database, as well as its first application to environmental and socio-economic assessment, a study case of the Araricá County. This city is, located at -29o 36’ 49’’ South and 50o 55’ 30’ West geographical coordinates (Figure 1) in Rio Grande do Sul State – Brazil. Araricá has 4,781 inhabitants distributed in 35 km2 (IBGE,2007), with 87% of them living in the urban area (IPEADATA, 2000). The leather’s industry is the main economic activity and the annual “per capita” income is around U$ 4.400,00 (FEE, 2005). 2. CRITERIA AND CONCEPTUAL MODEL The DATASinos database modeling was based on a multipurpose approach, comprising specific needs in several correlated areas, as public administration and decision-makers supporting, socio-economic and environmental research and also in basic and high schools teaching. With a wide range of applications, the database efficiency should be provided by a minimum amount and quality of the stored information, as well a SIG system for spatial data retrieving and some expert systems for specific analysis and data handling. Concerning all aimed goals and access facilities, a model to bring up-to-date and running a thematic database is shown in Figure 2. The database comprises rough data typically derived from four sources: (a) maps and remote sensing products; (b) field observations; (c) laboratory analyses and (d) social and economic features. At this stage, just after rough data entering, the available information consist of (1) point observations or determinations, (2) spatial thematic frames or imagery (3) non spatial data, normally as tables or graphics. Before the thematic data generation, the working dataset must be organized, analyzed and updated, using database facilities. The resulting information should be labeled respectively, according its nature and predefined access keys. 1415 The International Archives of the Photogrammetry, Remote Sensing and Spatial Information Sciences. Vol. XXXVII. Part B7. Beijing 2008 conceptual model of the environmental and socio-economic database (Figure 3). Database structure Data gathering Data sources/ Processes/Products Maps and sattelite images Nonspatial attributes Field monitorin Laborator y analyses Data organization Socio-economic and environmental geographic database structure Thematic data generation Multipurpose and multitemporal data analysis Thematic access rules (WEB) Figure 1. Location of CONSINOS area, including the Araricá County – RS / Brazil (source: DATASinos, 2006) Applications Former Data Rough Data Gathering Field Monitoring 3. Environment al research Public health DATABASE DESIGN AND IMPLEMENTATION As already pointed, the relational database is composed by three main information blocks, concerning the environmental, social and economic data. The major structural components of each block are illustrated in Figure 4. More details of the relational database, as entities, relationships, tables, maps and, glossary of terms for data surveying are presented in the WEB page (http://www.datasinos.unisinos.br). (WEB Access) Expert Application Systems Figure 2. Thematic WEB Database flowchart Once the information is already organized and, if it’s the case, spatially linked, new thematic data can be generated for further utilization. That is allowed by combining data from different sources for mapping purposes, as well as by multitemporal analysis of spatial and non-spatial data Finally, expert applications can be performed over the database structure by direct information extraction or WEB facilities using. All criteria and functions are summarized in the Basic and high school Figure 3. Conceptual model of the environmental and socioeconomic SIG-WEB Database Multitemporal Analysis Thematic Data Generation Environment al managing For implementation it was used the SIG-database system SPRING, which was produced by INPE, and also a MDBS (Management Database System) named MySQL, which data query works with a PHP programming language. Later, for easier data handling, a Content Management System (CMS) named MAMBO (http://www.mambo-foundation.org ) was used. Data query facilities, like data retrieving by keywords or filtering were provided by MAMBO, which outputs are tables. Looking forward to a large utilization by municipalities and general users, all systems used are free of charge. 1416 The International Archives of the Photogrammetry, Remote Sensing and Spatial Information Sciences. Vol. XXXVII. Part B7. Beijing 2008 The gotten answers were used to find out environmental (A), Social (S) and Economics (E) status indexes for each theme. Depending on the major area (A, S or E), specific weights were assigned to the standardized answers. Compiling the product of each answer value plus its weight, scores were assigned to the answers, being the worse and best scores respectively represented by zero and two values. Conception, Project and Implementation Climate Relie Geology and soils Vegetation and Fauna Environment al Data Finally, by arithmetical average calculation, three status indexes (A, S, E) were assigned to each theme, with the same scale value (0 to 2) and its corresponding meaning. The indexes and its statistical confidence (C) to the Araricá county are presented in Table 1. Surface water Groundwater City and Industries Demography Social Data Social Indicators Public Security Health Indicators Economic Data Employment Number of business organization Number of public institutions Water resources Land resources Health and sanitation Climate Influences Industry and Energy Economy and infrastructure Biotic aspects Demography and education Protected areas and tourism Institutional action 10 Major area A S E 1.10 1.16 1.19 1.16 1.26 1.32 1.07 1.04 1.00 1.00 1.30 1.10 1.00 0.92 0.77 C (%) 81.6 100 100 83.3 100 1.11 1.11 1.09 100 0.65 1.00 0.94 77.3 0.64 0.91 0.91 100 0.73 0.80 0.80 93.8 0.93 1.02 1.02 100 Table 1: Status indexes (A, S, E) and its statistical confidence (C) to the Araricá county in year 2007. According Schubart (2000), vulnerability indexes can be used to evaluate natural resources availability, as forests, water, soils, minerals and raw materials. Otherwise, with the same concept and application, potentiality indexes may be used to assess public and private benefits of land occupation and renewable resources using. For potentiality and vulnerability evaluation of Araricá, considering simultaneously the social pressure and economic dynamics, the indexes (A, S, E) were used to find out an integrated diagnosis and to highlight regional characteristics and limiting factors of sustainability. Figure 4: Structure of the major database blocks. 4. 01 02 03 04 05 06 09 Infrastructure and public services Economical Activities Theme 07 08 Educational Indicators Public Finances N EXPERT SYSTEM APPLICATION: THE CASE STUDY OF ARARICA COUNTY An environmental and socio-economic analysis was performed by using a expert system named ZEEBRA (Hansen, 2001), which was built based on the C++ programming language and the Microsoft®Access frame. A retrieving process of selected information from the DATASinos database was done in order to fill up a questionnaire provided by the expert system. An amount of 239 questions were answered, covering a wide range of ten correlated themes: surface and groundwater (38); geology and soils (19), economy and industry (45), industry and energy (13), demography and education (22); health and sanitation (27); biotic aspects (22); climate (12); tourism and environmental special areas (16) and institutional actions (45). The answers were standardized with three common options for all questions, each option with a standard value. The amount of answers obtained has direct influence over the statistical confidence (C) for each theme. In order to get an integrated view of all aspects (A, S, E), both vulnerability and potentiality indexes were used respectively as “x” and “y” coordinates of a bi-dimensional diagram. The plotting coordinates of the mentioned diagram were obtained by applying the equations presented below (1), which was applied once for each analyzed theme. 1417 x= ⎧ ⎪ ⎪ f ( A, S , E ) = 1.5 * ⎨1 + ⎪ ⎪⎩ tan ⎛ S − A ⎞⎤ ⎫ ⎢ ⎝ 100 − E ⎟⎠⎥ ⎪⎪ ⎣ ⎦ ⎬ −1 tan ( Kx ) ⎪ ⎪⎭ −1 ⎡ Kx * ⎜ (1) The International Archives of the Photogrammetry, Remote Sensing and Spatial Information Sciences. Vol. XXXVII. Part B7. Beijing 2008 y = vulnerability of some interrelated themes can be found (see Table 2). ⎡ (E − A − S ) ⎤ ⎫ ⎧ ⎢ ⎥ ⎪ tan − 1 ⎢ Ky * ⎥⎪ 2 ⎢ ⎥⎪ ⎪ 100 ⎢ ⎥ ⎢⎣ ⎥⎦ ⎪ ⎪ f ( A, S , E ) = 2.5 * ⎨1 + ⎬ tan − 1 ( Ky ) ⎪ ⎪ ⎪ ⎪ ⎪ ⎪ ⎭ ⎩ where x , y = Vulnerability and potentiality coordinates A = Environmental index S = Social index E = Economic index Kx = Vulnerability constant Ky = Potentiality constant The diagram proposed by Hansen & Lanna (2001) has respectively three and five classes of vulnerability and potentiality, producing fifteen combined classes, as shown in Figure 5. Very Low 5 Recuperation/ Consolidation Recuperation/ Consolidation/ Recuperation Transformation / Consolidation Protection /Consolidation Recuperation / Consolidatio n/ Transformation Protection/ Transformation Protection low 4 Medium High Conservation / Protection Transformation/ Conservation Very High 1 Expansion/ Conservation Expansion Low Conservation Medium 1 High 2 Very Low High Very Low Low High High Low High Table 2: Potentiality and vulnerability of each theme analyzed to Araricá county in year 2007. Considering the proposal of Hansen & Lanna (2001), as presented in Figure 5, the final classes given in Table 2 are showing a low to very low potentiality and high vulnerability of natural resources (land, water and biotic aspects), which indicates a demand for recuperation actions. By the way, in order to get successfully recuperation programs, specific education activities are essential to engage population in such programming. Besides, there is also a high vulnerability of the municipal education system, which needs recuperation measures too. At this point, since the factors involved are distributed on the geographical space and changing over time, a deeper analysis should consider spatial and temporal factors. In this way, a regional analysis, for all municipalities of CONSINOS can be made by using the multitemporal stored data and the GIS facilities of the SPRING database system. 0 0 Vulnerability High High Low/Medium High Low Medium At this stage, it is possible to conjecture about an unbalance between socio-environmental and economic features. That is to say, the technique of analysis is starting to show up a probably disconnection, between economic rising, social needs and natural resources conservation. In other words, there are probably some land use conflicts within the study area. 2 Expansion/ Transformation Potentiality Low Very Low High High Very High High As can be expected, by doing an overview of environmental and social indexes, the diagram of Hansen & Lanna (2001) is also showing a lack of institutional actions in the Araricá county. In the other hand, in spite of institutional weakness, environmental impacts and social problems, the economic growing is going very well, showing very high potentiality and low vulnerability. 3 Potentiality Theme Water resources Land resources Health and sanitation Climate Influences Industry and Energy Economy and infrastructure Demography and education Biotic aspects Protected areas and tourism Institutional action 3 Vulnerability 5. Figure 5. Potentiality x Vulnerability Diagram (source: Hansen & Lanna, 2001) Depending on the combined class (Figure 5) determined for each theme, by plotting potentiality and vulnerability coordinates, some administration actions (protection, recuperation, etc.) are indicated in the diagram Performing an integrated analysis to the Araricá County, based on the bi-dimensional diagram, a perception of potentiality and CONCLUSIONS The DATASinos SIG-database, in spite of being an initial data frame to support spatial and multitemporal analysis of CONSINOS area, is already showing some environmental impacts and socio-economic unbalances, which can head administration actions. There is a shortage of natural resources, as shown by the technique of analysis proposed by Hansen & Lanna (2001), indicating a demand for recuperation measures. A similar 1418 The International Archives of the Photogrammetry, Remote Sensing and Spatial Information Sciences. Vol. XXXVII. Part B7. Beijing 2008 situation was found for the educational system of Araricá, which has also a high vulnerability. Despite of depleting natural resources and the educational system weakness, the economy has high potentiality and low vulnerability. It could indicate a disconnection between economic rising, environmental conservation and improvement of social conditions, pointing at the same time a lack of institutional actions. To visualize the relationships of all parameters distributed on the geographic space, the SPRING system facilities could be used for spatial and multitemporal analysis. The DATASinos is showing to be a good tool to support socioeconomic and environmental analysis, as well as to help decision makers and municipality administrators. REFERENCES Subsistema Baixo Camaquã. 648 p. Tese de Doutorado Instituto de Pesquisas Hidráulicas, Universidade Federal do Rio Grande do Sul, Porto Alegre, 2001. HANSEN, M.A.F., LANNA, A.E.L. 2001. Zonas e zoneamento ecológico-econômico na gestão dos recursos hídricos. In: PROGRAMA DE ZONEAMENTO ECOLÓGICO ECONÔMICO: Diretrizes Metodológicas para o Zoneamento Ecológico-Econômico do Brasil. Ministério do meio Ambiente. Secretaria de Políticas para o Desenvolvimento Sustentável. Brasília. DF. 20 p. CD-ROM. IBGE,2007. Instituto Brasileiro de Geografia e Estatística. Porto Alegre, RS. http://www.ibge.gov.br. (accessed 03 Apr 2007). IPEADATA, 2000. Instituto de Pesquisa Econômica Aplicada. http://www.ipeadata.gov.br. (accessed, 05 Sep 2006). MAMBO, 2007. Mambo Foundation Incorporation. http://www.mambo-foundation.org (accessed 28 Mar. 2007). DATASinos, 2006. Projeto DATASinos-Sistema de informação e análise sócio-econômico-ambiental para o COREDE Vale do rio dos Sinos. http://www.datasinos.unisinos.br. (accessed 08 Apr. 2008). SCHUBART, H. O. R., 2000. O zoneamento ecológicoeconômico e a gestão dos recursos hídricos. Brasília: Ministério do Meio Ambiente, Secretaria de Recursos Hídricos, 2000, p. 155-175. FEE, 2005. Fundação de Economia e Estatística. http://www.fee.rs.gov.br (accessed 18 May. 2006). SPRING, 2003. Sistema de Informações Georeferenciadas. Instituto Nacional de Pesquisas Espaciais, INPE. http://www.dpi.inpe.br/spring/ (accessed 15 Mar 2006). HANSEN, M. A. F. 2001. Proposta de indicadores de sustentabilidade visando a síntese do diagnóstico ambiental, social e econômico para bacias hidrográficas. Estudo de caso: 1419 The International Archives of the Photogrammetry, Remote Sensing and Spatial Information Sciences. Vol. XXXVII. Part B7. Beijing 2008 1420