At our core, Spatial Networks is a geography company. From building geospatial technology products to collecting, organizing, and analyzing geodata, we eat, drink, and breathe geography. It’s sobering to learn that, for many of our customers, Fulcrum often provides their first exposure to the wonderful world of Geographic Information Systems (GIS).
We don’t build traditional GIS tools, but so much of field data collection revolves around location. Location provides tremendous context for analysis and helps to literally visualize trends and identify outliers. Whether you are monitoring light pollution on the beaches of Florida, supporting homeless outreach on the streets of Rhode Island, or helping to get your cousin elected in Cleveland, location leverage is always advantageous.
GIS can be an intimidating subject, with countless learning resources, strange acronyms, and complex software packed with unfamiliar icons. The abundance of geospatial data formats only adds to the challenge. GIS merges various specialty domains, including spatial reference systems, map projections, cartography, relational databases, scripting, and statistics. I aim to provide an approachable introduction to help beginners work with location-based data using free tools.
Understanding location
In the GIS world, location is represented by geometry, the spatial component of geographic features. For practical purposes, vector geometries typically consist of points, lines, or polygons, defined by one or more interconnected vertices. A vertex is simply a position in space defined by an X, Y (optional Z) axis. Fulcrum currently supports point geometries, defined by a single pair of X, Y coordinates – longitude and latitude in decimal degree format.
If you have a spreadsheet of addresses you’d like to import into Fulcrum, you can use GIS software or a web-based service to geocode the addresses, which will calculate the coordinates, generally based on street networks or building footprints.
Working with geospatial data formats
Geospatial data can come in a variety of formats, some of which are open, human readable, and familiar (CSV, GeoJSON) but many are not (shapefile, geodatabase, coverage). Fulcrum supports importing data in CSV and shapefile format. If you have a simple app without any repeatable fields, you can simply upload a single CSV file with latitude and longitude columns and your data will be mapped. Shapefiles actually consist of several files all with the same name and different extensions. These files all need to be zipped up before they can be imported into Fulcrum. If you’ve got a complicated Fulcrum app with nested levels of repeatables, you will need to make sure your import files have linked fields which can be related during the import.
If your data is not in CSV or shapefile format or you need to convert coordinates from another coordinate reference system, I highly recommend the open source GDAL translator library. The ogr2ogr utility which is a part of GDAL can be used to convert formats and transform/reproject coordinates. Ogr2ogr supports a wide variety of vector data formats and will write to both CSV and shapefile.
SQLite + GeoPackage
Whether you’re preparing data for import, running quality checks during collection, or analyzing final results, you’ll need the right tools. While spreadsheet software like Excel works for simple datasets, you’ll need more robust tools for relational and geospatial data. However, don’t worry—despite many examples, powerful tools aren’t always complicated or expensive!
A while back I made the case for Managing Data Exports with SQLite. Fast forward a few years and SQLite is even more prevalent in the geospatial industry. It’s popular because it’s simple, open, cross-platform, and incredibly powerful. SQLite-based GIS formats have been around for a while, in the form of MBTiles and SpatiaLite, but the recent formalization of the GeoPackage standard and its adoption across major GIS platforms has solidified SQLite as the go-to technology for data exchange across desktop and mobile platforms.
So now that we know SQLite is simple, powerful, and broadly utilized, let’s jump right in and see how we can start leveraging this technology to begin asking some location-based questions from our data. The simplicity of working with SQLite databases is probably one of its biggest attractions. As a serverless database, there are no installation, setup, or administrative procedures required. The database engine is embedded in the programs that use it and the process that wants to access the database reads and writes directly from the database files on disk. Databases can be emailed, copied across the file system, and trashed when you are finished with them – just like any other standalone file, but very much unlike most database systems.
Tools for working with SQLite
My go-to tool for working with SQLite databases is the excellent DB Browser for SQLite. This freely available, open source, cross-platform utility provides a clean Graphical User Interface for creating, viewing, editing, and querying SQLite database files. This is strictly a database utility and while it can support spatial querying, there is no mapping functionality with this tool. If you need to view your field data on a map, I recommend using QGIS, however, you can do a lot of spatial analysis without ever needing to view a map.
In order to support all the spatial functionality of our GeoPackage or SpatiaLite databases, we need to “spatially enable” DB Browser by loading the SpatiaLite extension. We’ve made this step easy by providing a full set of SpatiaLite binaries for macOS, Linux, and Windows, which are available for download at this GitHub repo.
Fortunately, DB Browser for SQLite makes it easy to load persistent extensions. Once you’ve downloaded the binary file for your operating system, from within the DB Browser menu toolbar, go to ‘DB Browser for SQLite -> Preferences’ and click on the ‘Extensions’ tab. Click the puzzle piece button with the green plus and browse out to the file you just downloaded. Windows users, see here for additional notes on setting this up.
You can confirm that the SpatiaLite extension is properly installed by executing the following SQL command to return the SpatiaLite version:
If it returns a version, you should be good to go. Congratulations, you’ve now got a spatially enabled SQLite utility ready for querying!
Creating a GeoPackage with reference data
For this exercise we will be investigating data collected from our internal “GeoBeers” Fulcrum app. GeoBeers is a simple app for logging and sharing where we’ve enjoyed our favorite beverages. This app also integrates with our Slack communication platform to post photos directly to a channel for immediate sharing, so it’s a fun way to use Fulcrum! While these records are geotagged with latitude and longitude coordinates, we are not storing any additional address or location information, but I’m interested in seeing how these records are distributed across states. What state has the most records? Which beer type is most represented in each state? You can see where we are going with this, but we first need some reference data to determine the state boundaries.
Steps to make a GeoPackage
While GeoPackage is a relatively new format, most downloadable data is available in shapefile format. I’m going to download the latest state boundary shapefile from the US Census Bureau available here.
- Unzip the downloaded file and you will see several files named tl_2017_us_state, all with different extensions (more on that here).
- Create a GeoPackage database using ogr2ogr to import the states shapefile as a new table: ogr2ogr -f GPKG -nln states -t_srs EPSG:4326 working.gpkg tl_2017_us_state.shp
- The -f GPKG option defines the output format as GeoPackage.
- The -nln states option sets the table name to states (default is the shapefile name).
- The -t_srs EPSG:4326 option sets to spatial reference system to World Geodetic System 1984 (Global system of latitude & longitude coordinates used by GPS). This will “reproject” the coordinates from EPSG:4269 to EPSG:4326.
- The result is a GeoPackage database file name working.gpkg with a table named states holding our state boundary polygons.
- Download Fulcrum data in CSV format and unzip the downloaded file. If you open the file in a spreadsheet program you should see _latitude & _longitude columns holding the point coordinates.
- Import this data into the GeoPackage database we just created with the following ogr2ogr command: ogr2ogr -f GPKG -update -nln geobeers -a_srs EPSG:4326 -oo X_POSSIBLE_NAMES=_longitude -oo Y_POSSIBLE_NAMES=_latitude working.gpkg geobeers.csv
- The -update option prevents a new GeoPackage file from being created.
- The -a_srs EPSG:4326 option assigns the spatial reference system to WGS 84. For CSV we have to assign the SRS since unlike shapefiles, it is not defined anywhere in the file.
- The -oo X_POSSIBLE_NAMES & -oo Y_POSSIBLE_NAMES options directly specify the potential names of the columns that can contain X/longitude and Y/latitude.
Working with goespatial data: Simple spatial analysis
We now have a GeoPackage database with our Fulcrum GeoBeers point data and state boundary reference data as tables, ready for some spatial analysis!
Let’s begin with some standard SQL to get a better understanding of our data. If you are new to SQL or SQLite, you may want to review this handy document: SQL As Understood By SQLite.
How many GeoBeers records do we have?
What are the most popular beer types?
What is the average rating, grouped by beer type?
Who’s a morning drinker? Caveat: we use this app for testing and demo purposes 🙂
Incorporating a database when working with geospatial data
Now that we have a better understanding of our data, let’s try to incorporate our states table to start writing some spatial queries. Note that GeoPackage database files created by org2ogr (and Fulcrum) are raw GeoPackages, with the geometry stored in a slightly different binary format than SpatiaLite, but starting with version 4.2.0, SpatiaLite supports working with the GeoPackage format.
This may sound incredibly confusing, but the bottom line is that in order to work with GeoPackage databases from the SpatiaLite plugin, we need to first run the following command: SELECT EnableGpkgAmphibiousMode(); This command basically tells SpatiaLite to work natively with GeoPackage geometry, removing the need to explicitly call the appropriate format conversion functions such as GeomFromGPB() or CastAutomagic().
SpatiaLite generally follows the OGC Simple Feature specification for SQL, but you can review all the supported SQL functions here.
First let’s enable GeoPackage amphibious mode to make writing spatial queries easier:
Now let’s take a look at the Well-Known Text representation of our geometries.
Notice they are all POINT geometries
Notice they are a mix of POLYGON and MULTIPOLYGON geometries
Next find the top 10 largest states by calculated area:
Okay, now we are ready to perform a spatial join, an operation used to combine two or more datasets with respect to a spatial relationship. Let’s join our GeoBeers points with our states’ boundaries to see which state each record is contained by.
So which state has the most records?
What are the top beer types grouped by state?
Which records are located outside of the United States? In this process, we collect states’ geometries into a single geometry and test which points fall outside.
Working with geospatial data: conclusions
We’ve just scratched the surface of GIS and spatial SQL, but I hope this post clarified some concepts. It also introduced useful tools for working with your data. Many resources exist to expand your knowledge in this area. We highly recommend the following online sources for further learning.
- Boundless Introduction to PostGIS – Specific to PostGIS, but includes many useful concepts and functions
- SpatiaLite Functions – Reference list of available functions
- Ogr2ogr Format Conversion Documentation – Documentation and detailed options
- GDAL/OGR Vector Formats – Handy links to all the vector data formats supported
In conclusion, GeoPackage, based on SQLite technology, is a great format for storing and working with geospatial data. Free and open source tools such as GDAL/OGR and DB Browser for SQLite with the SpatiaLite extension provide mechanisms for importing field data and running spatial queries for analysis. Once you break your dependency on spreadsheets and start working with geospatial data, you can truly experience the benefits of location leverage!