Working with Riverscapes IGO (scrape) Projects

Created by Jordan Gilbert, Modified on Thu, 10 Oct at 2:46 PM by Jordan Gilbert

Riverscapes IGO projects, also referred to as 'scrapes' or 'syntheses' consist of igo points attributed with model output attributes (typically from Riverscapes Metric Engine). The purpose of a scrape is that it allows for assessment of model outputs over a much larger spatial extent than individual model outputs, which are packaged at the HUC10 watershed scale. The Riverscapes IGO project contains a geopackage, which enables interaction using GIS, as well as with database software as a geopackage is a form of SQLite database. We recommend SQLiteStudio as it is free, powerful, and easy to deploy (https://sqlitestudio.pl/). Opening the geopackage as a database in this type of software allows a user to do two things, which are highlighted here:

Calculate summary metrics over an area of interest constrained by some set of 'filters'.
Create spatial views of subsets of the data to interact with in desktop GIS.

To open a geopackage in SQLiteStudio, open SQLiteStudio and click on the 'Add a database' button:

When opening a filetype other than typical database files (e.g., a geopackage) you need to change the 'save as type' when navigating to the .gpkg file:

After adding the geopackage, you can connect to it in order to interact with it:

To write queries to interact with the database, use the 'Open SQL Editor' button:

With an editor open, you can now write and execute queries on the database/geopackage. To run queries, use the execute query button. This will run either the most recent query typed in your editor, or the one you have highlighted.

Often a task may involve coming up riverscape metrics over a specific area. For example, within the area for which the scrape I'm working with is produced, I may want to know the area of riparian vegetation cover along perennial riverscapes on forest service lands. A single, simple query can be used in SQLiteStudio to produce this value:

SELECT SUM(RCAT_IGO_prop_riparian * segment_area) FROM igos WHERE rme_dgo_ownership = 'USFS' AND fcode IN (46006, 55800, 33600)

This query is adding up the product of the proportion of riverscape that is riparian and the area of riverscape to produce an area (meters squared) of riparian, and using the 'WHERE' clause to do this where ownership is forest service and NHD FCode is perennial.

At a certain scale (e.g., HUC2 or US State), it is possible to open the geopackage in GIS, but because of the quantity of geometries, operations are slow, or cause the software to crash. For example, the scrape for Colorado contains over 4.1 million IGO points.

In such cases, SQLiteStudio can be used to generate a new spatial view consisting of a subset of the full dataset. A query creating a new view is executed, followed by two queries that add the view, in spatial form, to the geopackage contents.

In the example above, the new view is named 'vw_blm_peren' because the 'WHERE' clause selecting data is selecting BLM ownership and perennial FCodes. That same name should then be used in the following two queries adding it to the geopackage. After each of the three queries is executed, the geopackage will contain a new spatial view that can be accessed in GIS. In this example, the filtering resulted in a subset of the original data consisting of 28000 points, which the GIS software can easily handle. This dataset can then be used for subsequent analysis using desktop GIS.