Accessing Sensor Data

We encourage people to make use of the sensor data that we have been saving to our database since 2007. Our database contains over 31 million measurements.

We have been saving the following measurements:

  • Air Pressure
  • Air Temperature
  • Relative Humidity
  • Wind Direction
  • Wind Speed (avg., min., & max.)
  • Rain Amount
  • Rain Duration
  • Rain Intensity
  • CO2 Density (parts-per-million)
  • Sound Level (decibels)

The frequency of measurement varies per sensor from sub-minute samples to multi-minute.

We ask that you reference the CitySense Project ( http://citysense.net) in any publications that result from use of this data.

Web-based Query

We have created a web-page to  query the sensor database. We appreciate any feedback on making the form more useful.

Raw SQL Access

If you are more adventurous and like to write your own SQL queries read-on.

We've created the citysenseDB database in mySql on citysense.bbn.com. Currently you can connect to it over port 8080 from the outside world via a mysql client. This may change as we try to make sure the database is secure. 

mysql -u guest -P 8080 -p -h citysense.bbn.com citysenseDB
password: enter 'citysense'

Example Queries

With the current schema is quite easy to construct powerful queries. For example to display the average value for each measurement during a specific time period you can use the following query: 

SELECT Name, AVG(Value), AVG(Timestamp), COUNT(Value) FROM RealMeasurements, SensorOutputTable WHERE sensor_output_id=id
       AND Timestamp>'2007-04-30' AND Timestamp<'2007-05-01' GROUP BY Name;

This query shows the average value for each distinct measurement Name (from the RealMeasurements? table) sampled during the time period midnight 4/30 to midnight 5/1. It also displays the average time of measurements of a given Name as well the number of samples recorded for each measurement Name. The output from the query is shown below: 

+------------------+-----------------+---------------------------+--------------+
| Name             | AVG(Value)      | AVG(Timestamp)            | COUNT(Value) |
+------------------+-----------------+---------------------------+--------------+
| airPressure      |   1005.09218107 | 20070430088150.8398437500 |          486 |
| airTemperature   |  13.52366255144 | 20070430088150.8398437500 |          486 |
| directionAvg     | 208.53611111111 | 20070430084172.0820312500 |         3960 |
| directionMax     | 203.20656565657 | 20070430084172.0820312500 |         3960 |
| directionMin     | 211.45934343434 | 20070430084172.0820312500 |         3960 |
| relativeHumidity | 65.044238683128 | 20070430088150.8398437500 |          486 |
| speedAvg         | 1.4300252525253 | 20070430084172.0820312500 |         3960 |
| speedMax         | 1.6692171717172 | 20070430084172.0820312500 |         3960 |
| speedMin         | 1.1942929292929 | 20070430084172.0820312500 |         3960 |
+------------------+-----------------+---------------------------+--------------+
9 rows in set (0.45 sec)

The schema we use in the database is shown below:

Attachments