Fulcrum customers often have a need to maintain a local version of the data they collect with our platform, in their database system of choice. With our export tool, it’s simple to pull out extracts in formats like CSV, shapefile, SQLite, and even PostGIS or GeoPackage. What this doesn’t allow, though, is an automatable way to keep a local version of data on your own server. You’d have to extract data manually on some schedule and append new stuff to existing tables you’ve already got.
A while back we built and released a tool called Fulcrum Desktop, with the goal of alleviating this problem. It’s an open source command line utility that harnesses our API to synchronize content from your Fulcrum account into a local database. It supports PostgreSQL (with PostGIS), Microsoft SQL Server, and even GeoPackage.
Other than the primary advantage of providing a way to clone your data to your own system, one of the cool things you can do with Desktop is easily make your data available to your GIS users in a tool like QGIS. It also has a plugin architecture to support other cool things like:
- Media management — syncing photos, videos, audio, signatures
- S3 — storing media files in your own Amazon S3 bucket
- Reports — Generating PDF reports
If you have the Fulcrum Developer Pack with your account, you have access to all of the APIs, so you need that to get Desktop set up (though it is available on the free trial tier). The quickest way to get up and running with Fulcrum Desktop is to check out the releases page on GitHub and download the latest version for your platform of choice (Windows, macOS, or Linux). With a simple drag-and-drop you can have the command line tool installed and ready for use.
But here I wanted to cover another utility we’ve built called fulcrum-sync, which makes it easy to set up Desktop using Docker. This is great for version management, syncing data for multiple organizations, and overall simplifying dependencies and local library management. With Docker “containerizing” the installation, you don’t have to worry about conflicting libraries or fiddle with your local setup. All of the FD installation is segmented to its own container. This utility also makes it easier to install and manage FD plugins.
Here’s a quick overview of how to get it set up. The goal of this short tutorial is to show how to get Fulcrum syncing to a PostgreSQL database, connected in QGIS, and to add a data layer to a QGIS project.
Getting Set Up
First things first, you’ll need a couple of things:
- Docker Desktop — you’ll need this installed and running for your platform to support and run containers.
- Fulcrum API key — from the Settings in your Fulcrum account, API tab.
- GitHub Desktop — or standard git, Desktop just makes it easier to clone and set up
Next you’ll want to clone the fulcrum-sync repository, then open a command line prompt to that directory.
Syncing an Account
First you’ll want to set up a local Postgres database to which you’ll sync your Fulcrum data. These steps will create the database, add PostGIS, and create a custom schema for user-friendly views (more on that in a second):
psql -c “CREATE DATABASE fulcrum_data;”
psql -d fulcrum_data -c “CREATE EXTENSION postgis;”
psql -d fulcrum_data -c “CREATE SCHEMA fulcrum_views;”
This example sets up the Docker environment with Fulcrum Desktop, then syncs data from my organization account into my local PostgreSQL database:
git clone git@github.com:fulcrumapp/fulcrum-sync.git
cd fulcrum-sync
FULCRUM_ORG=”Omega Labs”
FULCRUM_TOKEN=”api_token_here”
FULCRUM_PG_ENABLED=yes
FULCRUM_PG_HOST=docker.for.mac.host.internal
FULCRUM_PG_USER=$USER
FULCRUM_PG_DATABASE=fulcrum_data
FULCRUM_PG_ARGS=”–no-pg-prefix –pg-schema-views fulcrum_views”
./run
Those parameters tell FD the Fulcrum organization name, your API token, and desired Postgres database name. When run the first time, you’ll see Docker downloading all of the dependency containers, then creating your database (with PostGIS extensions), and syncing the data for the account.
After a few minutes (depending on the amount of data in your Fulcrum account), you’ll have a database called fulcrum_data in Postgres that contains all of the apps and records from your account. You’ll also have a /data directory in that repository with a SQLite version of all your Fulcrum data.
Fulcrum Desktop creates many tables by default to represent various pieces of data. Combining the custom fulcrum_views schema above with the FULCRUM_PG_ARGS setting will create nice user-friendly views for working with your records, photos, and changeset history.
If you want to connect and sync regularly, like in a scheduled task, it’s useful to set these parameters as global environment variables in your path. I do this by creating a hidden .fulcrum-desktop settings file with the parameters setup (see this example). You’d just need to load this into your shell profile to make sure the variables are included in your path.
Connecting to the Database in QGIS
I’m using QGIS 3.4 in this example, but all versions support PostGIS datasources. In the Browser panel, you’ll see PostGIS as a source type, so right-click there and select “New Connection.”
Entering the settings above will get your connection setup working. If you have your own database server running already, you may have to change some of the parameters to get connected.
Once connected, you should see the public schema populated with tables for each Fulcrum app in your account, plus tables for other things like roles, memberships, choice lists, and others that you can use perform JOINs on to connect up with other tables. Changesets, for example, store a record for each edit event in your account — useful for analyzing activity over time.
After I adding the “Utility Pole Inspection” app’s table into the QGIS canvas, I can perform any analysis or symbology changes just like I can with other GIS data sources. Here I’ve made some minor symbology changes to classify utility poles by the “pole type” field available in the data:
From here we can do geoprocessing functions, combine with other data, and publish maps. What makes this a powerful tool is the ability to keep things up to date. When changes happen in Fulcrum — like edits to data or new inspections being collected — a re-sync of Fulcrum Desktop updates all of my Postgres tables accordingly. So if I have a QGIS map project set up for regular analysis or QA, simply refreshing the data layer after syncing will update my map.
If you’re already using the Fulcrum API, check out Fulcrum Desktop for a simpler way to manage a local version of your data. While our export options are powerful, storing everything in your own PostgreSQL or SQL Server environment gives you the full range of options for integrating your data into your own systems, custom applications, or enterprise GIS platform.