One of my valued clients is not a biology firm. They are a consulting firm in an entirely different industry. While they have their own internal GIS shop, I provide support for their overflow when they get busy. There are some challenges because their GIS is based on ESRI products and I work exclusively in open source software. None of those challenges are insurmountable, however, and my open source tools give me some efficiency advantages. This post will summarize how I use open source tools to quickly and efficiently provide data they need.
I store data in a PostgreSQL database with the PostGIS extension. The PostGIS extension allows you to store geospatial data in PostgreSQL tables and use spatial SQL to manipulate and query the data in those tables. If you plan things out correctly, you can also automate a lot of processes that might otherwise take quite awhile to do manually each time. Since I use a MacBook Pro, I run Postgres easily using Postgres.app, which sets up a database instance that can be easily started and stopped with an icon on my desktop’s menu bar. I do most of my database management in pgAdmin 4. pgAdmin 4 allows you to easily create databases, queries, and “views” of the data.
While I have several databases that serve their own purposes, my primary database for consulting work is called “California”. The database is loaded with state-specific data culled from various local, state, and federal agencies. I have biological data such as sensitive species occurrence records, ranges for all wildlife species in the state, statewide vegetation data, Natural Communities Conservation Plan areas, historical fire perimeters, etc. I also have non-biological data such as land ownership, county boundaries, roads, soils, etc.
Key to all of this, at least for my needs, is a “projects” table. I maintain a projects table for my biological projects, and a separate projects table for my non-biological projects. The projects table contains a geometry column that stores the project limits, with additional columns for the project and client name, acreage (calculated automatically from the geometry column), project date, etc. The projects table is the basis for all database queries to get data for my clients.
Creating a Database View in QGIS
I use QGIS for all of my final mapping needs. QGIS also contains an excellent database manager that is often more convenient to use than the tools provided by pgAdmin. Here, I will create a database view based upon the projects table. A view is an SQL query that is permanently saved as a standalone table. The view table updates itself when the underlying table that the view is based on is updated.
Step 1 – connect to the “California” database in DB Manager.
Step 2 – select the “projects” table in the database tree.
Step 3 – open the SQL window.
Step 4 – test the SQL statement. For this exercise, I want to generate a derived table with polygons that are half-mile buffers from the boundaries in the “projects” table, with additional columns for the project name and the unique id or row key. To do this, we’ll use the following SQL statement in the query box:
SELECT id, project_name, st_buffer(geom, 804.672) AS geom FROM projects;
So, let’s explain the above SQL statement. With this statement we are SELECTing the id, and project_name FROM the projects table. We are also SELECTing the geometry (geom) column, but using a buffer function (st_buffer) on it, which also requires you to give it the distance of the buffer. The data is stored in projection EPSG: 3310, which is in meters. As 804.672 meters is a half mile, that is what we use for the buffer size. The query would call the new column “st_buffer” by default unless we rename it, so I rename it to the standard “geom” name used for geometry columns in PostGIS. Thus, we get “st_buffer(geom, 804.672) AS geom”.
Step 5 – After clicking “Execute”, we’ll get a table of data. The geometry column as text doesn’t tell us much though, so we’ll execute it again with “Load as New Layer” selected so we can visualize the output on a map, selecting “id” as the field with unique values, “geom” as the geometry column, and “buffer_half_mile” as the layer name:
The screenshot above shows one linear project in green with the new half mile buffer in purple. We have the same result for the other projects as well. As this is what we want, we’re ready to save the query permanently as a view. QGIS makes this easy for us. We just go back to the DB Manager tab (which still has out original work from the previous steps in it) and instead of pushing the “Execute” button, we push the “Create View” button. This creates what is essentially a new virtual table with half mile buffers for all projects. It will update automatically when new projects are added.
Part 2 will show how to use OpenOffice Base to build a database interface for querying and reporting.
Learn more about using PostGIS with PostGIS Essentials: