Small Hydro Resource Mapping in Indonesia GIS DATABASE - USER MANUAL November 2016 This report was prepared by GESTO, AQUALOGUS and INDONESIA HYDRO CONSULT, under contract to The World Bank. This is an interim output from the Energy Resource Mapping and Geospatial Planning Indonesia [Project ID: P145273]. This activity is funded and supported by the Energy Sector Management Assistance Program (ESMAP), a multi-donor trust fund administered by The World Bank, under a global initiative on Renewable Energy Resource Mapping. Further details on the initiative can be obtained from the ESMAP website. The user manual describes the essential information for the users to make full use of the GIS Database for national information on Small Hydropower Planning. The document includes a description of the system functions and capabilities, contingencies and alternate modes of operation, and step-by-step procedures for system access and use. This output has been internally peer-reviewed and it will be published via The - please refer to the corresponding country page. Copyright © 2016 THE WORLD BANK Washington DC 20433 Telephone: +1-202-473-1000 Internet: www.worldbank.org The World Bank, comprising the International Bank for Reconstruction and Development (IBRD) and the International Development Association (IDA), is the commissioning agent and copyright holder for this publication. However, this work is a product of the consultants listed, and not of World Bank staff. The findings, interpretations, and conclusions expressed in this work do not necessarily reflect the views of The World Bank, its Board of Executive Directors, or the governments they represent. The World Bank does not guarantee the accuracy of the data included in this work and accept no responsibility for any consequence of their use. The boundaries, colors, denominations, and other information shown on any map in this work do not imply any judgment on the part of The World Bank concerning the legal status of any territory or the endorsement or acceptance of such boundaries. The material in this work is subject to copyright. Because The World Bank encourages dissemination of its knowledge, this work may be reproduced, in whole or in part, for non-commercial purposes as long as full attribution to this work is given. Any queries on rights and licenses, including subsidiary rights, should be addressed to World Bank Publications, The World Bank Group, 1818 H Street NW, Washington, DC 20433, USA; fax: +1-202-522-2625; e-mail: pubrights@worldbank.org. Furthermore, the ESMAP Program Manager would appreciate receiving a copy of the publication that uses this publication for its source sent in care of the address above, or to esmap@worldbank.org. SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANNING INDONESIA GIS DATABASE USER’S MANUAL November 2016 SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANING - INDONESIA GIS Database User’s Manual TABLE OF CONTENTS 1 FOREWORD ................................................................................................................................... 1 1.1 OBJECTIVES ............................................................................................................................................. 1 1.2 SYNOPSIS................................................................................................................................................ 1 2 INTRODUCTION ............................................................................................................................. 3 2.1 CONTEXT ................................................................................................................................................ 3 2.2 INSTALLATION REQUIREMENTS .................................................................................................................... 3 3 INSTALLATION ............................................................................................................................... 4 4 GIS DATABASE ............................................................................................................................... 8 4.1 CONTEXT ................................................................................................................................................ 8 4.2 QGIS INTERFACE ..................................................................................................................................... 8 4.2.1 Initial screen............................................................................................................................... 8 4.2.2 Menu bar ................................................................................................................................... 9 4.2.3 Tool Bar...................................................................................................................................... 9 4.2.4 Map Legend ............................................................................................................................. 10 4.2.5 Map view ................................................................................................................................. 10 4.2.6 Status Bar ................................................................................................................................ 10 4.3 GIS DATABASE ...................................................................................................................................... 11 4.3.1 Login ........................................................................................................................................ 13 4.3.2 Database Administration ......................................................................................................... 15 4.3.3 Background Maps .................................................................................................................... 32 4.3.4 Base Maps ............................................................................................................................... 33 4.3.5 PLN Small Hydro Database ...................................................................................................... 36 4.4 BACKUP SYSTEM .................................................................................................................................... 42 i ID.2016.R.005.0 SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANING - INDONESIA GIS Database User’s Manual FIGURES Figure 3.1 – Welcome to the Small Hydropower Database Setup. ................................................................... 4 Figure 3.2 – License Agreement. ....................................................................................................................... 4 Figure 3.3 – Install Location. .............................................................................................................................. 5 Figure 3.4 – Choose Components. ..................................................................................................................... 5 Figure 3.5 – Completing Setup. ......................................................................................................................... 6 Figure 3.6 – SHP installation folder. .................................................................................................................. 6 Figure 3.7 – SHP Permissions............................................................................................................................. 7 Figure 4.1 – QGIS Interface. ............................................................................................................................... 8 Figure 4.2 – GIS Database Icons. ..................................................................................................................... 11 Figure 4.3 – Levels of permissions ................................................................................................................... 12 Figure 4.4 – Login dialog (left). Database Settings dialog (right). .................................................................... 13 Figure 4.5 – Login messages dialog: Login Successful (left). Wrong username or password (right). .............................................................................................................................................................. 13 Figure 4.6 – User interface after login as Admin user. .................................................................................... 14 Figure 4.7 – Logout dialog (left). Logout massage dialog (right). .................................................................... 14 Figure 4.8 – Database Administration tool. ..................................................................................................... 15 Figure 4.9 – Users Administration dialog. ....................................................................................................... 16 Figure 4.10 – Create User dialog. .................................................................................................................... 17 Figure 4.11 – Create User success message. ................................................................................................... 17 Figure 4.12 – Edit User dialog. ......................................................................................................................... 18 Figure 4.13 – Edit User success message. ........................................................................................................ 19 Figure 4.14 – Delete User dialog. .................................................................................................................... 19 Figure 4.15 – Delete User success message. ................................................................................................... 19 Figure 4.16 – Edit ‘Public’ view dialog. ............................................................................................................ 20 Figure 4.17 – Edit ‘Public’ view success message. ........................................................................................... 21 Figure 4.18 – Import data: select input file window. ...................................................................................... 22 Figure 4.19 – Coordinate Reference System Selector dialog. ......................................................................... 23 Figure 4.20 – Import data to new table dialog. ............................................................................................... 24 Figure 4.21 – Import data to existing table dialog........................................................................................... 25 Figure 4.22 – Successful import (left). Fail to import messages (right). .......................................................... 26 ii ID.2016.R.005.0 SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANING - INDONESIA GIS Database User’s Manual Figure 4.23 – Export data dialog. ..................................................................................................................... 27 Figure 4.24 – Export data: save file window.................................................................................................... 27 Figure 4.25 – Export table success message. .................................................................................................. 27 Figure 4.26 – Edit table dialog. ........................................................................................................................ 28 Figure 4.27 – Edit ‘Public’ view success message. ........................................................................................... 28 Figure 4.28 – Delete table dialog. .................................................................................................................... 29 Figure 4.29 – Delete table success message. .................................................................................................. 29 Figure 4.30 – Restore Settings dialog. ............................................................................................................. 30 Figure 4.31- Confirmation Windows: Create a new restore point (left), Restore the database from a backup (middle) and Restore the database from the existing Restore Point (right). .......................... 31 Figure 4.32 – Background Maps tool. .............................................................................................................. 32 Figure 4.33 – Base Maps tool: before download files (left); after download files (right). .............................. 34 Figure 4.34 – PLN Small Hydro Database tool. ................................................................................................ 36 Figure 4.35 – Add projects success message. .................................................................................................. 37 Figure 4.36 – Default Filters dialog. ................................................................................................................. 38 Figure 4.37 – Set default filters success message. .......................................................................................... 38 Figure 4.38 – Default filters example. ............................................................................................................. 39 Figure 4.39 – Custom Filters dialog. ................................................................................................................ 39 Figure 4.40 – Layer added to canvas information (left). Warning messages: expression has an error (middle) and expression has no result (right). ....................................................................................... 40 Figure 4.41 – Custom Filters example. ............................................................................................................ 41 TABLES Table 4.1 – Menu Bar options. .......................................................................................................................... 9 Table 4.2 – GIS Database: data structure. ....................................................................................................... 11 Table 4.3 – Importing options depending on the input file format. ................................................................ 22 Table 4.4 - Importing options for Existing Tables depending on the input file geometry and target geometry. ............................................................................................................................................. 25 Table 4.5 – Public_shapes schema: existing tables and corresponding layers symbols .................................. 44 Table 4.6 – Projects schema: existing tables and corresponding layers symbols ............................................ 45 ANNEXES Annex I – Data base structure and layers layout iii ID.2016.R.005.0 SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANING - INDONESIA GIS Database User’s Manual 1 FOREWORD 1.1 OBJECTIVES The objective of this document is to describe all essential information for the user to make full use of the GIS Database for national information on Small Hydropower Planning. This GIS Database was developed by the Consultant under the Small Hydropower Mapping and Improved Geospatial Electrification Planning in Indonesia Project. This User’s Manual includes a description of the system functions and capabilities, contingencies and alternate modes of operation, and step-by-step procedures for system access and use. 1.2 SYNOPSIS Project title: Small Hydropower Mapping and Improved Geospatial Electrification Planning in Indonesia Project Number: Selection No. 1125330 Country: Indonesia Project objectives: Facilitate and improve the planning and investment process for small hydro development in both grid and isolated systems through: a) Building up a central database on small hydro at national scale and validating the mapping of small hydro in NTT, Maluku, Maluku Utara and Sulawesi b) Improved electrification planning by integrating small hydro potential for the provinces of NTT, Maluku, Maluku Utara and Sulawesi into the planning process Expected results:  National Small Hydro Resource Database with statistics and relevant Geographic Information Systems (GIS) data  Small Hydro Mapping Report outlining national information on small hydro in Indonesia, including maps of existing, planned and potential schemes  A report/annex on small hydro potential in NTT, Maluku, Maluku Utara and Sulawesi for both grid expansion and isolated systems  Workshop and introductory training in the small hydro GIS database  A list of prioritized small hydro development sites in NTT, Maluku, Maluku Utara and Sulawesi, including recommendations for further studies, investigation and monitoring to support the development of the schemes 1 ID.2016.R.005.0 SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANING - INDONESIA GIS Database User’s Manual  Recommendations on improvement of the existing Geospatial Electrification Planning tool to take into account small hydro potential  Input data on small hydro potential for the least cost geospatial electrification planning for NTT, Maluku, Maluku Utara and Sulawesi  Update final reporting coordinated with the parallel contract to give a complete description of the Small Hydropower Mapping and Improved Geospatial Electrification Planning process in Indonesia  Final Workshop  Training on Small Hydro GIS database and Small Hydro Potential Evaluation Contract signing date: February 12, 2014 Project duration after final methodology approval: 22 months 2 ID.2016.R.005.0 SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANING - INDONESIA GIS Database User’s Manual 2 INTRODUCTION 2.1 CONTEXT The Geographic Information System (GIS) Database for Small Hydropower Planning was developed by the Consultant for small hydropower mapping and improved geospatial electrification planning in Indonesia based on PLN specific necessities and is currently hosted in PLN’s server. The main purpose of building up this central database on Small Hydropower (nationwide level) is to facilitate and improve the planning process of Small Hydropower in both grid and isolated systems in Indonesia. The central GIS database for national information on Small Hydropower Planning (SHP Database) was developed in PostgreSQL which is an open source object-relational database management system (ORDBMS). This ORDBMS has a PostGIS extension which allows GIS objects to be stored in the database. The visualization and management tools of this GIS Database are supported by a user- friendly open source Geographic Information System – QGIS. To provide customized options for easier and intuitive usage of the existing data in this database, a package of QGIS Plugins (version QGIS Essen 2.14) was developed, Erro! A origem da referência não foi encontrada.. WGS84 (World Geodetic System 84) is the adopted coordinate system by the GIS database for national information on Small Hydropower Planning, therefore all saved and visualized data are in this coordinate system. 2.2 INSTALLATION REQUIREMENTS QGIS doesn’t have a formal system requirement per se. However we recommend the following installation requirements for proper use and installation of the software:  Processor: Intel I3 2 cores @2GHz (or superior)  RAM: 4 GB (or superior)  Hard Disk: 320 GB or superior (2GB minimum of free space)  Operation System: Windows 7 or Windows 8  Access to PLN server 3 ID.2016.R.005.0 SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANING - INDONESIA GIS Database User’s Manual 3 INSTALLATION The SHP Database installation process is simple and intuitive. For installing this application just follow the indications shown in this tutorial. This software is available for Windows 7 and Windows 8 and it is similar in the two operating systems mentioned before. Nevertheless each of them has its particularities, so in this manual it will be shown how to install this application step-by-step in these two operating systems. To start the installation process, double click on the installation file: SHPDatabase.exe. After you execute this file a new window will came up (Figure 3.1), where you should click Next to continue. Figure 3.1 – Welcome to the Small Hydropower Database Setup. Now you can read and agree to the terms and conditions of this software (Figure 3.2) and then press I Agree. During the installation you can cancel or go back to the previous menu at any point of the installation process. Figure 3.2 – License Agreement. 4 ID.2016.R.005.0 SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANING - INDONESIA GIS Database User’s Manual This software will be installed by default in the folder C:\Program Files (x86)\SHPDatabase, however you can change it if you want (Figure 3.3), just be aware of the installation requirements (hard disk space). In case your operating system is Windows 8 the folder where the software is installed is not optional – it has to be installed in C:\SHPDatabase. Figure 3.3 – Install Location. Now you can start the core installation process by pressing Install (Figure 3.4). Figure 3.4 – Choose Components. Wait while SHP installs and when the process ends, click on Finish. Your computer will reboot to make sure this process it concluded and all functions are available to function. 5 ID.2016.R.005.0 SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANING - INDONESIA GIS Database User’s Manual Figure 3.5 – Completing Setup. For security reasons it is recommended that the software is installed by the System Administrator. Go to your installation folder through Windows Explorer (C:\Program Files (x86)\SHPDatabase or C:\SHPDatabase), Figure 3.6, right click with the mouse on the folder “SHPDatabase” and then go to “Properties”. Figure 3.6 – SHP installation folder. Next you must choose the tab “Security” and select your PC group of users on the first part of the window “Group and user names”. Then click on “Edit”, probably it will require the PC Administrator credentials, so please login the credentials in order to continue and successfully end this installation process. Then select the option “Full Control” (Figure 3.7). Finally press “OK” and the SHPDatabase is ready for operation. 6 ID.2016.R.005.0 SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANING - INDONESIA GIS Database User’s Manual Figure 3.7 – SHP Permissions. 7 ID.2016.R.005.0 SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANING - INDONESIA GIS Database User’s Manual 4 GIS DATABASE 4.1 CONTEXT As previously mentioned, the GIS Database is supported by a user-friendly open source Geographic Information System – QGIS. Therefore we will present the QGIS Interface, all existing GIS database tools and the backup system. 4.2 QGIS INTERFACE 4.2.1 INITIAL SCREEN When QGIS starts, you are presented with the user interface as shown in the Figure 4.11. Figure 4.1 – QGIS Interface. The interface it’s divided into five areas: 1 Your window decorations and icons may appear different depending on your window manager and on your selected shortcuts tools bars. In this image/workstation the package of QGIS Plugins developed for PLN in the project Small Hydropower Mapping and Improved Geospatial Electrification Planning in Indonesia is already installed. 8 ID.2016.R.005.0 SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANING - INDONESIA GIS Database User’s Manual 1. Menu Bar 2. Tools Bar 3. Map Legend 4. Map View 5. Status Bar These five areas are described in more detail in the following sections. 4.2.2 MENU BAR The “Menu Bar” provides access to various QGIS features using a standard hierarchical menu. This bar has ten options and its tools are described in Table 4.1. Table 4.1 – Menu Bar options. This button contains options for managing QGIS projects, such as: Create, Edit, Open, 1 Project Save, etc. This button contains options for managing features, such as: Add, Move, Delete, 2 Edit Rotate, etc. This button provides access to different options on viewing layers’ contents towards 3 View map navigation. This button contains options for managing layers, such as: Add (from different sources), 4 Layer Edit, Labeling, Query, etc. 5 Settings This button contains options for Configure Shortcuts, Options, Projects Properties, etc. This button provides access to the option: Manage and Install Plugins and to the 6 Plugins Python Console. This button provides access to: Analysis Tools, Geoprocessing Tools, Geometry Tools, 7 Vector Data Management Tools, etc. This button provides access to the Raster Calculator that allows you to perform 8 Raster calculations on the basis of existing raster pixel values. 9 Processing This button contains options for: Graphical Model, History and log, Results viewer, etc. 10 Help This button contains options for: Help Contents, Check Version, etc. Although most menu options have a corresponding tool and vice-versa, the menus are not organized exactly like the toolbars. 4.2.3 TOOL BAR The “Tool Bar” provides access to most of the same functions as the menus, plus additional tools for interacting with the map. 9 ID.2016.R.005.0 SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANING - INDONESIA GIS Database User’s Manual Every menu bar can be moved around according to your needs. Additionally, every menu bar can be switched off using your right mouse button context menu, holding the mouse over the toolbars. 4.2.4 MAP LEGEND The “Map Legend” area lists all the layers in the project. The checkbox in each legend entry can be used to show or hide the layer. A layer can be selected and dragged up or down in the legend to change the Z-ordering. Z-ordering means that layers listed nearer the top of the legend are drawn over layers listed lower down in the legend. 4.2.5 MAP VIEW The “Map View” area is where the maps are displayed. The map displayed in this window will depend on the layers that have been loaded. The map view can be panned, shifting the focus of the map display to another region, and it can be zoomed in and out. Tip: Zooming the Map with the Mouse Wheel You can use the mouse wheel to zoom in and out on the map. Place the mouse cursor inside the map area and roll the wheel forward (away from you) to zoom in and backwards (towards you) to zoom out. The zoom is centered on the mouse cursor position. 4.2.6 STATUS BAR The “Status Bar” shows the actual coordinates of the pointer (decimal degrees) as the mouse pointer is moved across the map canvas. This coordinates are composed by Latitude and Longitude (X, Y) on World Geodetic System 1984 (WGS84). Next to the status bar is displayed the scale of current visualization of the map view. If there’s need to zoom in or zoom out, the scale selector allows the user to choose between predefined scales from 1:500 to 1:1000000. The user can also make use of the scroll button to zoom in or zoom out. 10 ID.2016.R.005.0 SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANING - INDONESIA GIS Database User’s Manual 4.3 GIS DATABASE The GIS Database for national information on Small Hydropower Planning has been built based on PostgreSQL and a package of QGIS Plugins has been developed to support its visualization. The purpose of this package is to facilitate and improve the planning process of small hydro in both grid and isolated systems. The core data on PostgreSQL is essential to the consistency of the SHP Database, so it is important to understand how the database is structured. All data is held in three main schemas that contain distinct types of information and each schema support different types of geometry (Table 4.2), for more detail of all tables available in each schema see Annex 1. Table 4.2 – GIS Database: data structure. Geometry type enabled Schema designation Information type Points Lines Polygons No geometry db_admin User’s and permissions × × ×  projects SHP projects  × × × public_shapes Auxiliary public data     All tool operations are made according to this data organization, so when users add new tables to these schemas it is important that they are aware of how it is structured, in order to maintain the schema’s data organization and hence the well-functioning of this package. Figure 4.2 – GIS Database Icons. The previously mentioned package of QGIS Plugins is build-up on five tools (Figure 4.2):  Login  Database Administration  Background Maps  Base Maps  PLN Small Hydro Database Note: When QGIS is started all the package buttons are locked, except the Login tool button. Therefore you must login first, to be able to use the GIS Database and the above mentioned tools. 11 ID.2016.R.005.0 SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANING - INDONESIA GIS Database User’s Manual For security reasons, the access to the GIS Database is based on distinct levels of permissions, which aims to restrict the access to sensitive and confidential data. Therefore it was defined four permission types: Admin – The user can create, edit and delete other users (setting the user permission and PLN Wilayah). This user has access to all information in the database so he can view and edit all projects and auxiliary layers, as well. This user can also import and export the existing information in the database. It is the Admin responsibility to choose which Projects information the Public users can have access and change it whenever is needed. It is also the Admin responsibility to create a Restore Point and if needed Restore the database from an available backup or from the restore point. Edit – The user can view and edit all projects or only the projects that belong to the user PLN Wilayah and add/remove to the map canvas all auxiliary layers as the user needs. View – The user can view all projects or only the projects that belong to the user PLN Wilayah and add/remove to the map canvas all auxiliary layers as the user needs. Public – The user can add/remove to the map canvas all auxiliary layers as the user needs. Nevertheless the user can only view some projects and some details of those projects (chosen by the Admin user). As the previous profiles, the Public user has access to all projects or only to the projects data of the corresponding PLN Wilayah. Translating this in package tools, this means that the user has access to: Admin user – Login, Background Maps, Base Maps, Database Administration and PLN Small Hydro Database Edit user – Login, Background Maps, Base Maps and PLN Small Hydro Database View user – Login, Background Maps, Base Maps and PLN Small Hydro Database Public user – Login, Background Maps, Base Maps and PLN Small Hydro Database (with some limitations) unrestricted restricted Figure 4.3 – Levels of permissions 12 ID.2016.R.005.0 SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANING - INDONESIA GIS Database User’s Manual 4.3.1 LOGIN Login The Login tool allows the user to login and logout to the GIS Database. 1) Login To login you must click on this tool icon and this will bring up a new window (Figure 4.4 - left) where you must fill the username and password boxes and when this action is completed click [Login]. You can also press [Exit] to close the Login dialog. Figure 4.4 – Login dialog (left). Database Settings dialog (right). This will bring up a new window (Figure 4.5) with the message “Login Successful” in case the credentials are correct and the tool successfully established a connection to the server or with the message “Wrong username or password” in case the credentials aren’t correct. Figure 4.5 – Login messages dialog: Login Successful (left). Wrong username or password (right). In case you want to edit the server port and IP where the GIS Database is located, you should press [Settings], edit the ‘Server’/ ‘Port’ boxes and click [Save]. This action will only affect the PC where you at. It should be noted that the edition of the database settings will be locked after you login to the database, therefore you need to edit these settings before you continue to login. As mentioned before, when QGIS is started all the other tools are locked, thus when the user log in to the GIS Database, the tools will be unlocked (depending on the user permission profile the Database Administration may or may not be unlocked). Additionally some graphical decorations in the map canvas (Figure 4.6) are set for visualization support, such as: north arrow (top right), copyright label (bottom right), graphical scale bar (bottom left) and coordinate system (status bar). 13 ID.2016.R.005.0 SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANING - INDONESIA GIS Database User’s Manual Figure 4.6 – User interface after login as Admin user. Now your GIS Database workstation for national information on Small Hydropower Database is ready for visualization and edition. Note: The user accounts definition/creation is a task that belongs only to Admin users, so if you do not have an account you need to contact an Admin user to create it. 2) Logout To logout from the GIS database you must press [Logout]. This will bring up a new window with the message “Logout Successful” notifying that your session is ended and finally the SHP database window will be closed. You can also press [Exit] to close the Logout dialog. Note: In case you need to reset or change your user password or any detail on your user account, please contact an Admin user. This also applies to Admin users (for security reasons), in this is case these user must contact other Admin user to make any changes on their user account. Figure 4.7 – Logout dialog (left). Logout massage dialog (right). 14 ID.2016.R.005.0 SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANING - INDONESIA GIS Database User’s Manual 4.3.2 DATABASE ADMINISTRATION Database Administration The Database Administration tool provides several features to manage all the data in the GIS Database. This tool allows you to connect to the GIS Database and to create, delete and edit tables and the ‘Public’ view as well. You can also import and export tables into your spatial database. This tool menu has seven buttons:  Users  Edit ‘Public’ view  Import  Export  Edit  Delete  Restore Settings These seven options are described in more detail in the following sections. Figure 4.8 – Database Administration tool. Note: This tool will only be unlocked to Admin users therefore only this group of users will be able to manage the GIS Database for national information on Small Hydropower Planning. 1) User In this option you can add, edit and remove users thus defining their permission, name, password, PLN Wilayah, etc. 15 ID.2016.R.005.0 SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANING - INDONESIA GIS Database User’s Manual To have access to this option click on [Users] in the main Database Administration dialog (Figure 4.8). This action will bring up a new window (Figure 4.9) where you can manage the existing users and where you can see the ID, username, name, PLN Wilayah and permission of all other users. To create a new user, click in [Add new user]. This action will bring up a new window (Figure 4.10) where you must fill all the necessary information associated with the user, such as:  User  Name  Password/ Confirm Password  Permission  PLN Wilayah Figure 4.9 – Users Administration dialog. The ‘User’ field must be fill with the username that you intend to assign to the new user, nevertheless username has some characters restrictions, so when choosing usernames for users accounts, consider the following:  Letters (a – z and A – Z), numbers (0 – 9), dashes (-), underscores (_), and dot (.) can be used  Accents (`, ´, etc.) or accented letters (à, é, etc.) can’t be used  Dash, underscore or dot can't be used multiple times  Dash, underscore and dot can't be next to each other  Dash, underscore and dot can't be at the end or start of a username On the next field ‘Name’ you must write the complete name of the new user. The ‘Password’ field along with the ‘User’ field will be used as credentials for login into the system, however the password field has also some character restrictions, so when choosing passwords for users accounts, consider the following:  Any combination of ASCII characters can be used  Must contain a minimum of 1 character 16 ID.2016.R.005.0 SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANING - INDONESIA GIS Database User’s Manual Note that we recommend the password have at least 8 characters long and a mixture of: upper case letters, lower case letters, digits and symbols (dash, underscore or dot). The ‘Confirm Password’ field is used only for validation purposes, so make sure the ‘Password’/ ’Confirm Password’ fields are filled with the exactly same characters. The next field ‘Permission’ is where you should assign which permission profile the user will have (Admin, Edit, View or Public – for details see section 4.2). In the ‘PLN Wilayah’ field you must select the PLN Wilayah that this user will have access. The combo box content will change according to the permission previous selected. If you previously select the Admin user, the PLN Wilayah combo box will be filled a utomatically with “All” because this type of users can access to all PLN Wilayah’s data. If you selected any other type of permission profile this combo box will be filled with “All” and all PLN Wilayah short names. If you intend to provide access to all PLN Wilayah, you must choose the option ‘All’ or if you intend to provide access to one PLN Wilayah, you must choose the option with name of the PLN Wilayah, considering that it’s the only PLN Wilayah that the user will have data access to . Figure 4.10 – Create User dialog. To close the dialog and cancel the action: press [< Add Layer -> Add Delimited Text Layer), they are not prepared for you to extract the point/line/polygon coordinates directly from the CSV file when you open it (in Excel for example). 5) Edit In this option you can add, edit and delete features of any existing table (with or without geospatial information) in the public_shapes or projects schema. To have access to these options: click [Edit Table] in the main dialog. This action will bring up a new window (Figure 4.26) where you can select which schema and table you intend to edit. Figure 4.26 – Edit table dialog. To edit the table, first select the schema where the table belongs, the list of tables in the ‘Table’ combo box will change depending on which schema you choose. To close the dialog and cancel the action: press [< Toggle Editing and then right click on the layer -> Open Attribute Table. The table is now ready for editing. When you finish your edition please press (on the Attribute table) and your changes will be saved. It should be noted that all changes made are permanent and reflected in the GIS Database. 28 ID.2016.R.005.0 SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANING - INDONESIA GIS Database User’s Manual Note: In order to ensure the well-functioning of the export and import options, characters like commas, semicolons and quotation marks shouldn’t be used in any line/column of any table. Therefore it is import to avoid the use of these characters when adding new data or when editing. 6) Delete In this option you can delete any existing table in the public_shapes or projects schema from the GIS Database. To have access to these options click on [Delete Table] button in the main Database Administration dialog. This action will bring up a new window (Figure 4.28) where you can select which schema and table you intend to erase from the GIS database. In this option you will only be able to delete some of the tables in the public_shapes schema. The other tables related to Admin. Boundaries, Electric grid and Roads and Environment (see Annex I), are considered essential to the well-functioning of this tool, therefore you will not be able to delete them. Figure 4.28 – Delete table dialog. To delete the table first select the schema where the table belongs. The list of tables in the ‘Table’ combo box will change depending on which schema you choose. The [Done] button will be unlocked only when you have selected the schema and table that you want to edit. To close the dialog and cancel the action: press [< Save As, before you select any restore option. The restore process take approximately one minute, after the SHP window closes you can restart the program, login again and verify that the database is now in the exact state it was in when the backup/restore point was created. 31 ID.2016.R.005.0 SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANING - INDONESIA GIS Database User’s Manual 4.3.3 BACKGROUND MAPS Background Maps The Background Maps tool allows the user to choose an online worldwide map to be displayed as background map towards facilitate data visualization and especially planning. These maps will provide you a detailed and updated source of geographic data. This tool provides you access to four online maps:  Google Satellite  Open Street Maps  Bing Roads  Bing Aerial These four maps have some similar information, however they have distinct features, hence you should find which map fits the best to your planning workstation. Figure 4.32 – Background Maps tool. To display a background map, use the combo box (Figure 4.32) in order to select the map that you want to display in the map canvas. This action will add the selected layer to your workstation. These maps will always appear on the bottom of the layers tree of you visualization. To remove the layer from the workstation use the combo box and select . 32 ID.2016.R.005.0 SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANING - INDONESIA GIS Database User’s Manual 4.3.4 BASE MAPS Base Maps The Base Maps tool allows you to customize your planning workstation with relevant auxiliary geographic information for Small Hydropower Planning. This tool is designed for adding four types of data: 1. Administrative Boundaries  Islands  PLN Wilayah  Provinces  Regencies/Cities  NTT MMU Sula Districts  NTT MMU Villages 2. Electric Grid  Power Stations  Substations  Transmission Lines 3. Roads and Environment  Roads  River Network  River Basins  Geologic Map  Geologic Faults  Rainfall Gauge  Runoff Gauge  Forest Areas 4. Topographic Map Grid As previously mentioned this tool is designed for adding specific types of data that seemed fit to a planning workstation, nevertheless you can add any other data from the database that you find useful and/or relevant. To have access to all features in this tool, first you must click on the [Download] button. This will download all base layers to your PC, towards the improvement of the performance of data visualization. During the download process it will appear a progress bar showing the progress of the download, when this action is finished the progress bar will disappear and you can start adding layers to your workstation. 33 ID.2016.R.005.0 SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANING - INDONESIA GIS Database User’s Manual Considering that most users will only have access to projects data of their own PLN Wilayah this tool provides a Filter by Island option that will influence the Administrative Boundaries, the Electric Grid and the Roads and Environment layers. To filter by Island, select which Island you want to restrict your data in the ‘Island Filter’ combo box. When the selection in the combo box is changed, all the Administrative Boundaries, Electric Grid, Roads and Environment layers are removed and the respective checkboxes are unchecked/removed, so it is recommended that you choose the filter first and then select which layers you want to add to your workstation to avoid having to select all the layers again if you had already selected them. By default the combo box is set as “All”, meaning that you will be able to see the data from all island s. To add to your workstation an Administrative Boundaries or Topographic Map Grid use the corresponding combo box and select the layer that you intend to add to your visualization. To remove the layers from the workstation use the combo box to select . To add to your workstation any Electric Grid, Roads, Environment, etc. layers, check the corresponding box. In these two types of data it is possible to have all layers displayed in the map canvas at the same time. To remove the layer from your map canvas, uncheck the corresponding box by clicking again in it. To add other maps from the GIS Database use the ‘Other Maps’ combo box, select the table you want to add and then press [Add Map]. Some of the tables in the public_shapes schema can be added in other options, therefore, in this specific option you will only be able to add the tables that are not in the previous options Towards an intuitive and easy visualization and understanding of all data added to your workstation, most layers have associated: symbols, scale visibility and forms (presented in Annex I). These layouts can be changed at any time. Figure 4.33 – Base Maps tool: before download files (left); after download files (right). 34 ID.2016.R.005.0 SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANING - INDONESIA GIS Database User’s Manual When you’re consulting the represented data in map canvas (by selecting for example), you are presented with a dialog that lets you visualize/edit the attributes for that feature. However, this dialog is not, by default, very intuitive. Therefore when the data is load to the map canvas it is associated to a defined layout that includes a customized intuitive form, to change the associated form: right click on the layer -> Properties -> Fields. You can also set another symbol to the layer in right click on the layer -> Properties -> Styles and change the scale visibility in right click on the layer -> Properties -> General -> Scale dependent visibility. 35 ID.2016.R.005.0 SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANING - INDONESIA GIS Database User’s Manual 4.3.5 PLN SMALL HYDRO DATABASE PLN Small Hydro Database The PLN Small Hydro Database tool provides you access to the existing projects on your GIS Database. In this tool you can add and filter the spatial tables from the GIS Database to your workstation. This will complete your workstation composition and you will be ready for start small hydropower planning. This tool menu has three buttons:  Add Projects  Default Filters  Custom Filters To be able to click in one of this buttons you need to use the ‘Table’ combo box to select which projects table you intend to add to your map canvas or filter. After this selection the three buttons will be unlocked. To close the menu dialog press [Exit]. Any project in the database need three base information’s: the unique identifier (id), the location (geom) and the PLN Wilayah short name (pln_wilayah_short) where the project belongs. For that reason these information/columns are considered mandatory for all tables in the projects schema and if any table in this schema does not have these three columns it will not appear as an option for add to your planning workstation. Figure 4.34 – PLN Small Hydro Database tool. Note: As previously mentioned, Public users have some limitations on which projects they can have access to. These limitations are defined by the creation of public views, therefore for Public users, in this combo box will show only the tables that have public views created. The [Default Filters] and [Custom Filters] buttons will be locked in three different scenarios: (i) the logged in user has Public permission, (ii) the selected table do not have any project at all or do not have 36 ID.2016.R.005.0 SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANING - INDONESIA GIS Database User’s Manual any project in the user PLN Wilayah (iii) the selected table do not have the mandatory fields (status, ipp_pln, inst_cap_mw, province). For that reason it is recommended that in this case you set your Projects layer filter in right click on the layer -> Filter. 1) Add Projects This option will add to your workstation the spatial table previously selected in the combo box. To add the Projects layer to the map canvas, first select which table you want to add and then click on [Add Projects] button. This action will bring up a new window notifying that the layer is added to your map canvas (Figure 4.35) and then press [Close] and you can continue your tasks. Figure 4.35 – Add projects success message. Note: To Admin and Edit users this option provide you access to editing your Projects data and therefore add new projects, delete the existing, change their status, etc. 2) Default Filters This option allows you to set some default filters in your Projects layer that will facilitate the visualization of your data. To apply a default filter to the Projects layer, first select which table you want to add/visualize and then click on [Default Filters] button. This action will bring up a new window (Figure 4.36) and in this window you can now set your default filters. The Default Filters option supports two different types of filters:  Location filter  PLN Wilayah  Province  Attribute filter  Size  Developer  Current Phase To filter by PLN Wilayah use the ‘PLN Wilayah’ combo box and select which PLN Wilayah you want to add to your map canvas by selecting the corresponding PLN Wilayah short name. In case you don’t 37 ID.2016.R.005.0 SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANING - INDONESIA GIS Database User’s Manual intend to set any filter by PLN Wilayah just select “All”. If you are an Edit or View user, this combo box will be locked and will be filled with your PLN Wilayah (defined when your user account was created by the Admin user). It should be noted that only the PLN Wilayah with projects, in the selected table, will appear in the combo box. In the ‘Province’ combo box will appear the existing provinces, with projects, in the PLN Wilayah, previously selected, or all provinces in case you have selected “All” in the ‘PLN Wilayah’ combo box. Only after you have selected both combo boxes (‘PLN Wilayah’ and ‘Province’) the [Done] button will be unlocked. In the attribute filters all radio buttons are checked by default. If you intend to exclude a determined type of information you must uncheck the corresponding radio button or check it if you intend to include it instead. You can apply any combination of these five filters. Figure 4.36 – Default Filters dialog. It is important to notice that the [Done] button will be locked if you uncheck all radio buttons associated to an attribute, thus you need to have at least one radio button checked for attribute to continue (to avoid applying filters that will result in no data). Figure 4.37 – Set default filters success message. To apply the filter press [Done], this action will remove the current Projects layer that you may have on your map canvas and will be added a new one with the selected filters. To cancel the operation press [Exit]. Once you pressed [Done] the filtered layer will be added to your workstation and will bring up a 38 ID.2016.R.005.0 SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANING - INDONESIA GIS Database User’s Manual new window notifying that the layer is added to your map canvas (Figure 4.37), press [Close] and you can continue your tasks. Example: Add to the map canvas all the small hydro projects currently in the planning phase that were developed by PLN in Java Barat province that belongs to DISJABAR PLN Wilayah. Figure 4.38 – Default filters example. 3) Custom Filters This option allows you to set custom filters in your Projects layer to facilitate your data visualization and planning. The purpose of this option is similar to the Default Filters option nevertheless, in Custom Filter, you will be able to apply filters without restrictions and with any required complexity by executing queries. Therefore you will be able to execute SQL queries against your projects database schema and then view the spatial output for queries by adding the results to your workstation as a query layer. Figure 4.39 – Custom Filters dialog. 39 ID.2016.R.005.0 SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANING - INDONESIA GIS Database User’s Manual To apply a custom filter in your Projects layer, first select which table you want to add/visualize and then click on [Custom Filters] button. This action will bring up a new window (Figure 4.39) that it is divided in four sections: the Column section (1), the Values section (2), the Operators section (3) and the Expression section (4). The Column section contains, in the combo box, all existing columns of the projects table so you will be able to search through any of them. To add an attribute/column to the expression, use the combo box (to select the column) and click [Add]. You can use the various fields, values and operators to construct the expression/query. The Values section lists the distinct values of an attribute/column. To list all possible values of an attribute, select the attribute in the Columns combo box and click [Add]. To add a value to the expression box, double click its name in the Values list. The Operators section contains all usable operators. To add an operator to the expression box, click in the appropriate button. Relational operators ( = , != , > , < , ≥ , ≤ ) and logical operators (AND, OR) are also available. The Expression section is where your filter expression will appear. The expression is editable nevertheless is recommended that you built the expression through the push buttons and list and not through typing. If you want to delete part or all the expression you can select the part of the expression that you intend to delete and press backspace/delete. To apply the filter press [Done], this action will remove the current Projects layer that you may have on your map canvas and will be added a new one with the selected filters. To cancel the operation press [Exit].Once you click [Done] this will bring up a new window indicating if the expression is valid or not (Figure 4.40), if the expression created by you is invalid because has no results, will appear a warning window with the message “This expression has no results. Please try again”. On the other hand if the expression is invalid because has syntax errors, will appear a warning window with the message “This expression has an error. Please try again”. And finally if the expression is valid will appear the information window with the message “Layer added to canvas”. In case your query turns out to be valid just press [Close]. On the other and if the query turns out to be invalid press [Close], delete your expression (or part it) and please try again. Figure 4.40 – Layer added to canvas information (left). Warning messages: expression has an error (middle) and expression has no result (right). 40 ID.2016.R.005.0 SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANING - INDONESIA GIS Database User’s Manual Example: Add to the map canvas all the projects with inst. capacity under 2 MW, developed after 1990 in the province of Bali. Figure 4.41 – Custom Filters example. 41 ID.2016.R.005.0 SMALL HYDROPOWER MAPPING AND IMPROVED GEOSPATIAL ELECTRIFICATION PLANING - INDONESIA GIS Database User’s Manual 4.4 BACKUP SYSTEM For assure the security and integrity of all data in the GIS database, it is scheduled an automatic backup system. This backup system will allow you to restore the database to a previous day in case of need, for example, in case of data loss, etc. Therefore every week it is created a backup file, so the Admin user can restore the database in case it seems needed. The backup files created every week will be stored in the server during a period of three months (sixty days), after that the files will be automatically deleted. 42 ID.2016.R.005.0 ANNEX 1 – DATA BASE STRUCTURE AND LAYERS LAYOUT Table 4.5 – Public_shapes schema: existing tables and corresponding layers symbols Table name Description Symbol Scale Visibility (Max. – Min.) admin_00_islands* Island 1:2.000.000 – 1:0 admin_01_provinces* Provinces None admin_02_regencies* Regencies and Cities None admin_03_ntt_mmu_sula_districts NTT MMU Sula Districts None admin_04_ntt_mmu_villages NTT MMU Villages None admin_PLN_wilayahs* PLN Wilayah None elect_generation* Power Stations 1:2 000 000 – 1:0 elect_grid* Transmission Lines 1:8 000 000 – 1:0 elect_substations* Substations 1:2 000 000 – 1:0 gen_roads* Roads 1:8 000 000 – 1:0 geo_map* Geologic Map 1:16 000 000 – 1:0 geo_fault* Geologic Faults 1:4 000 000 – 1:0 hydro_rainfallgauges* Rainfall Gauge 1:2 000 000 – 1:0 hydro_runoffgauges* Runoff Gauge 1:2 000 000 – 1:0 gen_forest_areas* Forest Areas 1:16 000 000 – 1:0 hydro_riverbasins* River Basins 1:16 000 000 – 1:0 hydro_rivers* Rivers 1:16 000 000 – 1:0 hydro_water_bodies* Water Bodies 1:16 000 000 – 1:0 Table 4.6 – Projects schema: existing tables and corresponding layers symbols Table name Description Symbol Scale Visibility (Max. – Min.) comprehensive_compilation* Comprehensive compilation None list_most_promissing_sites* List of most promising sites None pln_shp_planning* PLN SHP planning None * Tables considered essential to this tool, therefore they cannot be deleted. GESTO ENERGIA S.A. Av. Cáceres Monteiro, nº 10, 1º Sul 1495-131 Algés, Portugal T. +351 211 579 899 F. +351 211 540 900 www.gestoenergy.com