#meshlium

Open data is exciting, and real-time open data is even exciting-er. But it's tricky: every step in the data gathering chain needs to be designed to support real-time publishing, or else the data goes rapidly stale. I've just finishing implementing a pilot project, publishing real-time open data for the City of Melbourne.

Collect the data A team of researchers led by Dr Jayavardhana Lakshminarasimha from the University of Melbourne School of Electrical and Electronic Engineering have installed around 10 Meshlium automated environment sensors, in Fitzroy Gardens and at the Docklands Library. These produce data every 30 seconds.

Locations of the environmental sensors, at the Docklands Library and in Fitzroy Gardens.

Store the data The team created a MySQL database on the NeCTAR Research Cloud, and wrote scripts to constantly write data into it. The next bit was up to me.

Aggregate the data Publishing data at such frequent intervals could hit Socrata's usage limits, and isn't useful for the average end user. So we aggregate first:

SELECT date_sub(timestamp,interval (minute(timestamp) %% %d ) * 60 + second(timestamp) second) AS timestamp_agg, convert(avg(convert(temperature, decimal(5,1))),decimal(5,1)) AS temp_avg, ..... FROM sensorReadings sr INNER JOIN sensor_info si on si.boardid = sr.boardid WHERE temperature <> '' GROUP BY sr.boardid, date_sub(timestamp,interval (minute(timestamp) %% %d) * 60 + second(timestamp) second) HAVING timestamp_agg > '%s' ORDER BY timestamp LIMIT %d''' % (interval_mins, interval_mins, latest_published, rowlimit)

Publish the data Publishing to Socrata is pretty easy using the SODA API. My script (available on Github) is in Python. There's no special Python API, but then you don't really need one, as it's a pretty conventional REST interface.

headers = { 'X-App-Token': config.app_token, 'Content-Type': 'application/json' } auth = ('[REDACTED USERNAME]', '[REDACTED PASSWORD]') r = requests.post(config.dataset, data=simplejson.dumps(rows), headers = headers, auth=auth) j = r.json() if r.status_code != 200: raise RuntimeError ( "%d Socrata error: %s" % (r.status_code, j['message']))

Repeat automatically

To make this happen automatically, we simply add the Python script to Cron, Linux's background task scheduler.

*/10 * * * * cd /home/ubuntu/sodasensor && ./publish.py --rows 100 >> publish.log

The final result The dataset in data.melbourne.gov.au has these fields: timestamp: the time of the start of the aggregated period

mac: MAC address of the sensor

boardtype: always 1 for environmental sensors, I think

boardid: ID of the sensor itself

temp_max/min/avg: temperature

light_max/min/avg: ambient light level

humidity_max/min/avg: humidity

model: ENV for environmental sensors

latitude/longitude: location, hard coded

elevation: height above ground, hard coded I believe

location: text description of where the sensor is

rowid: unique identifier, comprised of time and board ID.

See the data now: "Trial environmental sensor readings".