Riverscapes Consortium

Database Driven Tools

Post from @philpbaileynar


Background

We are increasingly relying on databases in our riverscapes tools. Remember that the “I” in GIS stands for information. So if you don’t know how to use a database you can’t really call yourself a GIS expert! Still not convinced? ESRI’s first product back in the 1970s was called ArcInfo. The “Arc” referred to geometry while the “Info” literally referred to a tabular database. Yet, most GIS users (including most Riverscape Scientists) don’t regularlly use databases directly. So not knowing databases means your skills are 40 years out of date! Here’s how you can turn it all around…

There are several popular brands of databases, some of which you might have heard of - MySQL, Oracle, SQLite, Postgres, SQLServer, Access. They differ in lots of ways, but what they all share in common is that you interact with data using something called the Structured Query Language (SQL). Despite a few SQL nuances all the aforementioned database brands implement the same SQL standard for retrieving and managing data. The bottom line… You need to learn the SQL language. Here are some resources…

Resources

For Book Lovers

For Movie Lovers

PostGIS

Introduction to PostGIS (a geospatial extension to postgres)

SQL

Introduction to SQL (this 3hr video course uses MySQL but I like Mosh, the presenter)

For Practical Types

Software

The two types of open source database that we use are:

  • SQLite - useful when you just want to store data on your computer and use it yourself.
  • Postgres - hosted on a server and accessible by multiple people.

If you’re just starting out then I recommend SQLite and the O’Reilly book above. Note that SQLite itself does not have a user interface. It’s literally just a file format for storing data. You will need a piece of software to open and work with SQLite files. For this I recommend DataGrip. It’s free with a university email address.

What about GIS?

Basic SQL is not geospatial. I recommend learning the SQL language before delving further into geospatial databases. But if you’re keen then you should know that we are using the open source geopackage to store GIS data on desktop computers (no more ShapeFiles!) and the PostGIS extension to serve GIS data from online servers.

Not convinced? As of November 2020 the outputs of the following tools are already geopackage SQLite databases:

We will soon migrate BRAT to output a SQLite geopackage and stop using ShapeFiles. The new reach typing tool for the Mississippi, as well as GNAT will also use databases. Our reporting technology also relies on SQLite.


Summary

In summary… If you plan on working with riverscapes tools and data, it would be in your best interest to learn SQL.