Working with GeoPackages and SQL

Much of the free data available on the Riverscapes Data Exchange are commonly provided in GeoPackage format. GeoPackages are a free, non-proprietary format for storing geospatial data. They replace (and are far superior to) ShapeFiles and are preferred over other geodatabase formats. 

Key features of GeoPackages:

• Single file format (no more missing projection files!)
• Can contain multiple layers in one GeoPackage.
• Can contain both vector and raster data.
• Include spatial reference, coordinate system information.
• Work with both open source QGIS and ESRI products.

Under the hood, GeoPackages are actually just SQLite databases. SQLite is the World's most deployed database technology. Think of it like the open source equivalent of Microsoft Access.

Why this is important is because you can open GeoPackages using freely available database software, run queries and use it like any other database. This is extremely useful for large datasets that can perform poorly in desktop GIS that is also trying to display the data spatially. Querying GeoPackages using SQL can be a faster way to filter data and interrogate the data without the burden of mapping display. Nowhere is this more helpful than analyzing Riverscapes when some databases run to tens of Gigabytes!

Using SQL Software with GeoPackages

There are lots of free SQL software for working with GeoPackages. We like the following:

• SQLite Studio
• DBBrowser for SQLite
• DBeaver

SQLite Studio is noteworthy because it can be download and used without installing the software. You simply download the software as a zip file, uncompress it and run the software executable. This doesn't require special permissions or administrative access to the computer on which you are trying to use the software. If this is desirable then visit the following link and choose the "portable" download for your operating system. For most users this will be Windows x64.

https://github.com/pawelsalawa/sqlitestudio/releases

If one of these software packages is still too much, then you can always resort to the SQLite command line interface. Use the DOS prompt or Shell terminal to run SQL commands without the burdon of any user interface at all! Download the "commandline tools" for your operating system from this site.