Back in December, I wrote a blog on Managing Data Exports With SQLite. That article covers some basic relational database concepts, with particular regard to managing complex Fulcrum data exports. While we continue to receive support inquiries regarding best practices for managing exported field data, I thought I would continue this topic and provide some additional options for slicing and dicing your data with GDAL/OGR.

Geospatial Data Abstraction Library (GDAL)

GDAL is an extremely popular open source library for reading and writing raster and vector geospatial data formats. This library is used by many leading geospatial software packages and is the foundation of several GIS stacks. GDAL typically refers to the raster utilities of the library while OGR refers to the vector utilities.

Since Fulcrum exports data in a variety of standard vector formats, we will be focusing on the incredibly useful ogr2ogr utility.

Installing GDAL/OGR

GDAL is a command line utility that has no real graphical user interface. As such, it is excellent for scripting common transformation tasks. If you have QGIS installed, the GDAL library is already available. The path to the GDAL utility will vary depending on your operating system.

Mabox has great documentation for setting up GDAL on Mac OS X and Linux. If you are on Windows, your best bet is to install GDAL via OSGeo4W.

Working with GDAL/OGR

Once you’ve got it installed, working with GDAL is relatively easy. Pop open a terminal window and enter the following command gdalinfo --version. If everything is setup properly, it should return the version you have installed- GDAL 1.11.1, released 2014/09/24.

Fulcrum ogr2ogr Cheat Sheet

I’ve put together the following Cheat Sheet specifically designed to help with some common tasks you may want to perform on your Fulcrum data exports. The examples below use the following Fire Hydrant GeoJSON file, which is pulled from a Fulcrum data share.

Convert GeoJSON to Shapefile

ogr2ogr -f "ESRI Shapefile" fire_hydrants.shp fire_hydrants.geojson

Convert GeoJSON to Shapefile reprojected to EPSG:3857 (web mercator)

ogr2ogr -f "ESRI Shapefile" fire_hydrants_3857.shp fire_hydrants.geojson -t_srs "EPSG:3857"

Convert GeoJSON to KML (defining hydrant_type as the KML name)

ogr2ogr -f "KML" fire_hydrants.kml fire_hydrants.geojson -dsco NameField=hydrant_type

Remove Unwanted Attributes and Reorder Attribute Column Position

ogr2ogr -f "GeoJSON" fire_hydrants_lite.geojson fire_hydrants.geojson -select id_tag,diameter,status,hydrant_type,position,photo_url

Convert GeoJSON to CSV With Coordinates and Select Only Hydrants Where hydrant_type = Standpipe

ogr2ogr -f "CSV" -lco GEOMETRY=AS_XY standpipe_hydrants.csv fire_hydrants.geojson -where 'hydrant_type = "Standpipe"'

Conclusion

This overview merely scratches the surface of the capabilities of GDAL/OGR, but it demonstrates the utility of a simple command line interface for working with basic geodata tasks and scripting common processes. Hopefully these examples will help you make the most of your valuable field data! If you have any tips or tricks you’d like to share with the Fulcrum community, join the conversation @fulcrumapp.

‍