What is a Spatially Enabled Database
A Spatial Enabled Database (or spatial database) infers some sort of management system that is specialised to work with spatial data. There are indeed such systems, but a well organized set of files in folders is essentially similar to single users Databases. A set of folders with gis data is not as cool sounding as what ESRI calls a GeoDatabase however, so we differentiate user files with software driven data structures and database management systems.
The use of a Spatial Database for GIS software is not a new thing – although it is often perceived that way. Relational Database Management Systems (RDBMS) – often just called Realtional Database, have been supporting the web and the corporate world for a long time.
An example of a file based relational type of functioning geodatabase is the data model ESRI followed for years – the “coverage”. You can always hear a collective sigh of reminiscence from old time GIS folks – see (https://www.youtube.com/watch?v=0b04pKO_698) – there is profanity in the clip however (often the case when working in GIS). This coverage model structued data into specific folders and placed files that related to each other amongst these folders. There are plenty of these coverages clanging around our data stores in the GIS Lab (just look for folders name “info” (hence ArcInfo).
In this lab we will quickly look at some GeoDatabase, and then spend more time working with a spatially enabled RDBMS that has been around for some time (as well as having a Prince George connection) – PostgreSQL/PostGIS. The following links reference GeoDatabases ESRI definition -
Wikipedia - http://desktop.arcgis.com/en/arcmap/10.4/manage-data/geodatabases/what-is-a-geodatabase.htm
ESRI definition - https://en.wikipedia.org/wiki/Spatial_database
ESRI comparison of GeoDatabase types
Lets have a quick look at ESRI File Based GeoDatabases
Create a ESRI File Based GeoDatabase
Open up ArcCatalog and navigate to your newly created folder for this lab (perhaps called geodatabase…)
- Right click in the directory and create a new file based geodatabase (New –> File Geodatabase)
- Now right click on this newly created database and add whatever spatial data you want (Import –> Feature Class (Single) – fill in the required fields in the feature class to feature class panel – navigate to a shape file somewhere – give this layer a name to be used in the geodatabase
- You can also create new features by right clicking (New –> Feature Class) – fill in the fields in the new feature class wizard – this includes setting the feature type (i.e. polygon) – the projection (I chose UTM 10N ) – the vertical projection if you chose a z type layer (I chose NAD 83)
- You can now bring in the geodatabase into ArcMap
Sharing the data Using the regular file explorer, navigate to where you created the file based GeoDatabase – it should be a folder ending with .gdb (i.e. k:\emmons\geog413\geodatabase\testesri.gdb). If you look inside this folder, you will see a variety of files used to add structure and expand the use of the layers that have been added to the database (i.e, topology, relationships between layers, annotation..). This folder can be shared with other GIS users. It is often zipped up and emailed to people for them to expand and use it with their GIS software.
A couple OGC (http://www.opengeospatial.org/) standard GeoDatabases
For the following sections, we are going to use the Linux desktop (and a virtual machine to complete the lab). We may use the data created in the following sections in ArcMap, so keep Osmotar running – just minimize its window.
Spatialite is a spatial extension to SQLite that adds spatial abilities to the portability of this database system. It works simularly to ESRI file based system in that data is stored in you personal workspace (saved as a single file), but funtions more like a RDMS internally. SQlite is the common format used for storing data for small devices such as cell phones. Spatialite is a version of SQLite that follows the Simple Feature Specification for SQL to model data in a relational system (Scott will explain in his guest lecture. As most of the Open Source Software used in the GIS Lab follows OGC standards, each piece of software works well with each other. We are going to use QGIS for the next sections of the lab.
Starting your own Virtual Machine
- Using the file browser on the Linux descktop, navigate to /home/labs/geog413/data/geodatabase
- Right click on the “Geog 413/613 VM” file and copy it
- Move your mouse to the Desktop and Paste the file
- Double click (or right click –> run) to launch the program
- If it does not work – go to the next step
Virtual Machine not starting (bumming Scott out as to why it will not start sometimes)..
- Go the Menu panel button and then navigate to Administration the Oracle VM Virtualbox (Menu –> Administration–> Oracle VM Virtualbox)
- Highlight (Click once) the machine in the right panel “geog413_2017“
- Click on the Settings tab
- click on Network
- Click OK
- Click setting again
- Click network again
- Click OK
- Click on the Desktop icon you copied above
Once the Virtual Machine starts, you should get a login screen. Use the login drop down to select Other, then login the same as you do the lab machines.
QGIS and Spatial Lite
Open up QGIS in your virtual machine (Menu (bottom left) –> Education –> QGIS. When it opens you should have an interface that looks somewhat similar to ArcGIS. Scott will be illustrating,so if your interface looks different that his – let him know.
To add layers to QGIS, you can find the type of data you want to load under the layer –> add layer in the main menu (top of QGIS) or use the quick access icons on the left.
To add a shape file:
- Layer –> Add Vector Layer –> navigate to the shape file in the “add vector layer” panel
To add a raster file:
- Layer –> Add Raster Layer –> navigate to the file as above
Creating a Spatialite database
You should have a panel called Browser in the QGIS interface, if you don’t have the browser interface:
- View –> Panels –> Browser Panel
Inside the Browser Panel, there is a listing of types of data to load (similar to ArcCatalog). In that panel there is a entry for Spatialite. You can create a Spatialite database by:
- Right click Spatailite –> Create Database –> navigate to where you want to store it –> Save
- Now if you click the arrow to the left of the Spatialite entry, there should be a connection to this new database
Adding data to your Spatialite database – using a plugin
Plugins are a great way of adding functionality to QGIS, we are going to add a couple today – lets start with the DB Manger Plugin by:
- Plugins –> Manage Plugins –> Settings –> click on all the boxes in the panel (should be three of them) – don’t hit close yet
- Go to the All Tab –> Search –> type DB –> click once of DB Manager –> Install Plugin (bottom right corner)
- Click close
Adding data to your new database
Open a shape file from your home folder, or from the Geog413 folder into QGIS (if you have not already done so). We should check to see what projection the shape file(s) are in before we place them into a database. To see the projection of the shape file we have loaded we:
- Right ckick the shape file layer –> Properties –> General tab –> read the projection information in the Coordinate Reference System (pay attention to the EPSG number (i.e EPSG:3005 – BC Albers, or EPSG 26910: UTM Zone 10N)
- Now add it to the database by –> Database in main menu –> DB Manger –> DB Manager –> Navigate down the Spatialite tree (entry) to see your new database –> highlight it by clicking once
- Table menu –> import layer/file –> use the image below as a guide to the options you may want to use for you layer
You can now add the layer you have in the database, by adding a spatialite layer under the layer menu or the spatialite icon (as you did with our shape file).
The DB Manager can be used to create an empty layer to add features to (similar to the ESRI method for adding new layers). Spatialite GUI can be used to see inside a database and load shape files, it is accessed by:
- menu –> programming –> Spatialite gui
Spatially enabled Enterprise Relational Database Management System
There are may OGC standards compliant RDMS that are spatially enabled (such as MSSQL and Oracle spatial), but the original spatial extension for a RDMS was created by a Victoria - with one of the company’s head honchos from Prince George. We have been using the PostGIS extension for PostgreSQL in the GIS Lab since the early years of application (when Scott first set up ninkasi..)
The reason we are using a Virtual Machine is to make use of server side applications. Although the RDBMS can be installed on desktop computers (including windows), Scott does not have it installed on the lab machines. He created a virtual machine for everyone to share instead. This means we can have all the tools we need.
QGIS and PostGIS have been around about the same length of time (as is MapServer), and have both worked well together. We will QGIS to populate and work with PostGIS, but first we are going to look inside PostgreSQL and create a database to work with. We are going to use PGAdmin3 to create a database by:
- Starting PGAdmin –> menu –> programming –> PGAdmin
- Create a new connection file–> add server (or hit the plug icon)
- Fill in the fields similar to the image below (Scott will tell you the password)
We now have a connection to the server – we need to create a database for us to work with by
- Expand the server connection (click the + sign or double click the sever connection
- right click on the database –> New Database
- Name the database (i.e. yourname_db) –> set the owner to geog413
- Add PostGIS extension (no need to do this if you can use the template database – i.e. geog413template)
- Use the definition tab and select geog413template as the template database
- expand the database tree for your database
- navigate to extensions (if you did not use the template databases above)
- right click extensions –> new extension
- use the drop down at the name field –> find PostGIS –> click OK
PostGIS and QGIS
As with Spatialite, you can create a connection to the database you just created in PGAdmin by using the DB Manager. Layers can be loaded in via layers – add PostGIS layer. Try the following steps.
- Add a layer to the PostGIS database (you can even load from the spatialite layers)
- Style the layer
- Styling is accessible by right clicking the layer -> properties –> sytle
- you can also right click an existing layer –> style –> copy style –> right click new layer –> style –> paste style
- Use the style panel for the layer to save the styling (bottom of the panel) to the database itself
Looking at the database itself
Play around with PGAdmin to see the table(s) you have just created. You can see there are other tables that exist to help manage the layers that have been added. As with any relational database, relationships between tables (the fields within them), indexing, constraints, triggers and other functionality is all available to be used with the layers present.
The real power of PostGIS
One of the most powerful features of PostGIS is the spatial operations it bring to the database. We will use PostGIS to perform some GIS functions on a set of data by (Scott will help out):
Add and Create layers in PostGIS
- Load the landcover shapefile from /home/labs/geog413/data/watershed into PostGIS (without loading it into QGIS first)
- Create another polygon layer in PostGIS through the DB Manager
- Add both the landcover and new polygon layer into QGIS
- Draw one or more polygons in the new polygon layer and save edits
Add another processing Plugin
- Add a plugin called “PostGIS Geoprocessing Tools”
- access this plugin by –> Processing –> Toolbox
GeoProcessing in the Database
Use the functions in this toolbox to:
- Buffer the new polygon layer to create a new layer in PostGIS
- Clip the landcover with this new layer
- Dissolve the newly clipped landcover layer using an appropriate field (perhaps plu_label).
Commands for working with databases – When PGAdmin is not working
We found in this lab that there were some issues with PGAdmin and the management of databases and extensions. We have a base database we can work with (lab_data), but in case you wish to create more databases, you can use the following commands. You can also hit the web and look up more help with Postgresql (the database management system that PostGIS is an extension within). You may also want to clean up the database and reset your PGAdmin session as below:
Clearing up PGAdmin Settings
- In PGAdmin – right click on the databases we created in the lab –> delete/drop – make sure you the keep lab-data and geog414template databases
- Close PGAdmin
- Open a terminal (it will open in your home directory by default) and type –> rm -r .pga* (this will remove the folders created when you configured your database connections)
- Clearing PGAdmin settings (along with changes Scott made to the lab computers) should have the software working properly
Working with PostGIS using command line – when PGAdmin does not work
- Create a database from the command line: createdb -O geog413 -T geog413template -U geog413 -W databasenameyouarecreating – it should ask you password
- or you can connect to postgres by: psql -d lab-Data -U geog413 -W –> it will ask for your password –> then create a database by typing: create database databasenameyouarecreating template geog413template
Setting up a Network System in PostGIS – PGRoute
You may have worked with network analysis in ArcGIS, and today we are going to take database look at how this same kind of work can be accomplished.
Start a new QGIS project and add perform the following steps:
- Add in a plugin called “Quick Map Services”
- Once the plugin is added in click Web –> Quick Map Services –> Settings –> More Services tab –> Get Contributed Pack
- Add in a plugin called OSMDownloader
- Add in a plugin called PGRoutingLayer
- Web –> Quick Map services –> OSM –> OSM Standard
- Zoom to an area anywhere in the world. Probably at a fairly large scale. For instance Scott zoomed into Manchester England at a scale of 1:100000 to 1:200000
- Change the projection of the map to unprojected lat lon – EPSG 4326 (bottom right – enable on the fly projection – set to 4326)
- Vector –> Open Street Map –> Download Data
- Download the data using the canvas extent –> same file with the extension .osm
Load data into PostGIS as a network – PGRoute
- Create a new DataBase for your routing layer – i.e. Manchester using PGAdmin and use the geog413template s the template for the new database.
- Using the command shell (everyone’s favourite) navigate to the folder where you downloaded the osm file (HINT: File Browser –> right click –> Open Terminal here
- use the following command to load your data, but substitute your database name, DataBase user, Password and file name appropriately
osm2pgrouting -f manchester.osm -h localhost -U DatabaseUser -d manchester -W password --conf=/usr/share/osm2pgrouting/mapconfig_for_cars.xml
- Once the command is finished type “more thenameofyourfile” This should show the contents of the file in your terminal
- From last Lab, What type of file is this? It is a text file, but what type of text file?
Running Network Analysis
- Make a connection to the new database by:
- Adding a PopstGIS Layer (click the elephant)
- Create a new connection
- Name your connection
- set the host to: localhost
- set the database to your new osm database(i.e. Scott’s was manchester)
- type in the user name (geog413) and the password
- Make Sure you click the save button for both the user and the password – otherwise the pgrouting plugin will not fine the database
- Load in the ways and the ways_vertices
- Once the data is loaded in, check out the attributes of the ways layer.
- Click Database –> PGRouting –> PGRouting Layer
- You now have a panel to play with routing with a set of functions (we are going to test driving distance)
- Set up a travel area by using the following settings (exampled with Scott’s Manchester DB:
- Function: Driving Distance
- edge table: ways
- geometry: the_geom
- id: gid
- We will pick the source ID from the map
- We can play around with the different distances
Scratching the Surface
We have only begun exploring how GeoDatabases are used in GIS and Remote Sensing. If you are interested in using any of the software you used today in your project, speak with Scott about some of the applications that make use of spatial databases.
Close down the Virtual Machine before you log off your desktop. Log off of osmotar as well.