To develop open data publishing as an ongoing council capability, you may want to automate the process of extracting and uploading data. Exactly how you do this depends a lot on what GIS tools you use (ArcGIS, MapInfo, open source tools), platforms (Windows, Mac), whether you have access to servers to automatically run extraction jobs, and finally whether you are comfortable using the command line and perhaps writing scripts.

In general terms, here’s what the process looks like.

1. Develop an extraction/transformation script

First, you will need to manually develop the exact steps that need to be carried out for a given dataset. This must be a script, batch file or FME Workspace that can be run by a computer with no human interaction. For example, if your (trivial) SQL query is “SELECT geometry,name FROM wards;” then your batch file might be a text file called WARDS.BAT:

ogr2ogr -f GeoJSON wards.json "PG:host=gis dbname=gis" -sql "SELECT geometry,name FROM wards"

2. Run it once to generate the first output file

If you’re using Windows, in a Command Prompt, run the batch file:

> WARDS.BAT

3. Create a dataset in data.gov.au

See How to Publish Data.

4. Upload the file as a new resource.

The final part of the resource’s URL is the resource ID. Make a note of it:

Screenshot 2016-01-11 23.37.17.png

5. Update the script to upload to the resource

Our script above generates a GeoJSON file. Now it needs to upload it to data.gov.au. We can use the ckan-upload tool described below, so we add a line to the script:

python2 ckan-upload.py --apikey 123451234 --resource http://data.gov.au/dataset/wards/resource/6a582739-8a20-4b99-ac9e-db6294524191 wards.geojson

6. Schedule the script for automatic updates

Once the script is working, you’ll want it to run on schedule, perhaps once a week. If you’re using a desktop Windows computer, you could use the Windows Task Scheduler to run the script every Friday morning.

FME-CKAN Connector

FME is a commercial data extraction and conversion tool used by many councils. The FME-CKAN Connector has been developed by Ballarat and Greater Geelong City Councils to automate uploading data to data.gov.au.

→ See the FME-CKAN connector page for download.

Open source tools

A free alternative is to use open source tools. There are two parts:

Extract/transform

OGR2OGR is a command line tool which can convert spatial data from one format to another. You can use it to extract data from a database, perform SQL queries to change attribute names, then export to a format such as CSV or GeoJSON:

ogr2ogr -f GeoJSON OpentownWasteZones.json \
 "PG:host=gis.opentown.vic.gov.au dbname=gis user=ubuntu password=toomanysecrets" \ 
 -sql "select zone_name AS name,rub_day,2 AS rub_weeks,rub_start \
 from garbage_zones"

More information here, and you can ask questions at gis.stackexchange.com.