Databases and Data Management

 

Creating a Database of Fish Observations In and Around the George Washington Birthplace National Monument (GEWA) – A Comparison of PostGIS and ArcSDE

Problem Description

The main purpose of this exercise was to compare and contrast databases created in ArcSDE Enterprise Geodatabase and a Postgre database populated using PostGIS.  The goal was to see what options were different between the two databases and determine what database option would be the most useful under particular circumstances.  Additional goals of this exercise were to determine scenarios where using SQL would be more efficient then using a GUI and vice versa.

Analysis Procedures

The first step was to create the PostGIS database.  This was done by importing layers into a postgre database using PostGIS then viewing the data using QGIS.  Views using SQL were then created for viewing in QGIS and to answer specific questions about GEWA.  The process was similar to creating views in ArcSDE.  A connection within QGIS was made to the postgres database then layers were imported.

The specific attribute under investigation was identification of bird roosting sites within the GEWA area.  This required querying the observed roosting sites, linking them with latitude and longitude locations that were then plotted in QGIS as layers.  The second portion of this excersize was to identify locations where a bird species who eats white perch would be likely located.  These birds would therefore be located near white perch observations and near trees that are ideal for birds to roost.  To determine these locations buffers around tree and observations were created and where they intersected, these were noted as locations the bird species in question would be likely to inhabit.

Results

The end product was a postgre database with geometry populated by PostGIS and viewable in QGIS.  Figure 1 shows layers displayed in QGIS from the database.  Spatial SQL was used to create queries to identify an ideal location for observations of bird roosting sites.  Figure 2 shows the results of the SQL queries used to identify likely bird roosting trees and a subsequent observation deck to view the white perch on which the birds feed.

Figure 1. The above map is a screen capture from QGIS.  The larger circles show the number of different species observed at each site.  The map also shows the location of other features (like buildings, roads and fences) from within the park.

Figure 2.  The first image indicates that a white perch observation site would ideally be located at the visitor center.  The red highlighted building is the Visitor Center.  The yellow circles are trees within 200 meters of observation sites where white perch were observed.  The red dots over water are the white perch observation sites.  The second image shows the buildings that could prove detrimental to the bird’s feeding routine.  The pink polygons indicate buildings while the light blue transparent layer is a 200 meter buffer around the bird roosting trees.  The buildings plotted on top of the buffer could potentially impact the bird’s feeding behavior of white perch.  The yellow circles are the tree locations and purple dots the observation sites.

Reflection

Using spatial SQL allows for greater control over the functions, output and analysis a user undertakes while working on a problem.  I think SQL (and coding in general) requires a greater understanding of the computations that will take place.  It eliminates the need for a GUI, which often works as a sort of ‘middle man’ when working on a spatial problem.  I ended up using various QGIS tools via GUIs to solve the bird roosting question as I had problems with importing the cultural vegetation layer’s geometry (no errors on import but empty geom column).  GUIs are often times easier to manipulate and work with while again, coding requires an understanding of all the elements (file locations, output paths, tool parameters, unit conversions….) a problem considers.  Users of spatial SQL would need to have a good logical understanding of how to solve a problem and know exactly what results they need.  Another plus for using SQL and other languages would be that it allows for batch processing of data.

Some advantages of using GUIs would be quick visual analyses of intermediate results allowing for modifications of the analysis process.  GUIs also seem to be easier to navigate not requiring the user to learn a new language.  However, users are limited to the tools available in whatever software is being used.  A combination of the 2 methods as demonstrated in this assignment works quite well.  Using spatial SQL to create separate views proved a quicker method then doing several spatial selections in QGIS and then saving the selections as separate layers.  On the flip side, running the buffer tool and the intersect tool through QGIS ended up being useful as it allowed for a visual inspection of the layers as each tool ran.