In the latest release of QGIS (2.2 “Valmiera”), the free and open source desktop GIS software, there’s a great new feature to support one-to-many data relationships. Since we recently launched parent-child & repeatable records in Fulcrum, we wanted to write up a quick overview on how you can use QGIS with data collected in Fulcrum to do visualization and analysis for free on the desktop. Read on to learn how to create data relationships and do more using your data in a GIS.
Using repeatable sections in Fulcrum comes with a number of advantages. It makes it easy to create field collection apps that allow one-to-many feature relationships — like inspecting numerous rooms within a building, or recording multiple observations at a stream gauge or construction site over time. When exporting these types of data from Fulcrum, you’ll get multiple files (or tables) representing each feature type you’ve collected. This requires creating a link between tables to analyze or report on the data. In my example, I’ll use the same Park Inventory survey from a previous post to demonstrate how to export your data from Fulcrum, load it up in QGIS, and create a relationship between the parent and child data items for browsing within the attribute table. You’ll need to install QGIS 2.2 on your platform of choice to get started.
Export Data from Fulcrum
QGIS natively supports all of Fulcrum’s open data formats, including CSV, shapefile, SQLite/SpatiaLite, and geojson. For this guide, I’ll be using SQLite, a file-based database format that’s well-supported and simple to work with. I’ve exported my Park Inventory survey data to a SQLite database, which includes independent tables for the park survey with information about the park itself, and park features asset locations and condition reports for assets like benches, tables, shelters, and other amenities1.
Once the data is downloaded and ready, drag it into the QGIS workspace to load up the tables.
Loading into QGIS
With your dataset loaded in QGIS, and the layers selected to display (park_inventory and park_features), zooming in on an area shows my park record as a blue diamond, and each asset tagged as orange triangles. Also helpful here is the QGIS OpenLayers plugin which allows you to load basemap layers from web sources (Bing, Google, and others) for reference, but you can also load your own data or imagery if needed. I have several parks and their assets mapped, and I want to be able to join the park data to each asset record for reporting, so I can make queries on things like “all picnic tables by jurisdiction” to see what needs to be repaired and where. This is where relates come in.
Creating the Relationship
Next you need to go into your Project Properties under the File menu, and pick the Relations tab at the bottom. Select Add Relation to bring up the configuration dialog to set up the link. Give the relation link a name, then set your child and parent features — in my example, I select the park_features and park_inventory layers, respectively. With those selected, you need to pick a shared data column to match between the layers to form the relationship. The child data from repeatable sections in Fulcrum contains a parent_id column, which matches the fulcrum_id of the parent item2. Select your two Referencing Fields and click OK.
With the relationship established, use the QGIS Identify tool to select one of the parent items. When the record’s data is displayed in the results window, expand the Actions list and click View Feature Form to see the full attributes for the feature. Scroll all the way down to the bottom and you’ll see a sub-table containing the related child items for that feature. Swapping between the full attribute table and the form view with the buttons on the right, I can now see a list of that park location’s assets, as well as select an individual asset to view its details.
In addition to relations, also check out how to perform table joins in QGIS. In the example above, that would make it easy to append the park details to each individual asset record. All of these things are also possible using other data formats within ArcGIS, like Fulcrum’s file geodatabase or shapefile export formats. It’s also worth looking at Matthias Kuhn’s post on how to work with relations in the latest QGIS release. Check out what’s possible with these capabilities in viewing and mapping your Fulcrum data, and let us know how it works for you.
Notes
- Also note that if you include photos in your export, you’ll also see separate tables for photo data. This can also be useful because Fulcrum records photo locations (geotags) independently from the record data. ↩
- You’ll see several ID columns in Fulcrum. The ‘fulcrum_id’ is unique for the record, ‘parent_id’ references the immediate parent, and ‘fulcrum_record_id’ references the root record (if you have nested child items). ↩