Пример #1
0
def timeline(sensor_id):
    """
    Returns all measurements for one particular sensor.
    """
    timeline = db.dict_query('''SELECT time, temperature FROM measurements
        WHERE sensor_id = ? ORDER BY time''', [sensor_id])
    return timeline
Пример #2
0
def population_density(year=None):
    if year is None:
        rows = db.query("""SELECT MAX(pop_year) FROM cantons_pop""")
        year = rows.fetchone()[0]
    pop_dens = db.dict_query("""SELECT cantons_pop.abbr, pop_year, 
            pop, pop / area_ha AS pop_dens
        FROM cantons_pop JOIN cantons ON cantons_pop.abbr = cantons.abbr
        WHERE pop_year = ?""", (year,))
    # Note that in a Sqlite query the placeholder for a variable value is ?
    # (it would be %s in Postgres)
    return pop_dens
Пример #3
0
def sensors():
    """
    Returns the ID, name, elevation and point geometry of all sensors.
    """
    sensors = db.dict_query('SELECT id, geometry, name, elevation FROM sensors')
    # Sensors are in WGS84 (EPSG:4326) in the database.
    # We need to transform the coordinates to Google Mercator (EPSG:900913),
    # which is the SRS of the OpenLayers map
    for c in sensors:
        sensors[c]['geometry'].set_srid(4326)
        sensors[c]['geometry'].transform(900913)
    return sensors
Пример #4
0
def measurements(time=None, time_window=30):
    """
    Returns all available measurements at a given time (None means latest
    measurements), plus/minus a time window (30 minutes by default).
    """
    if time is None:    # Query the database to get time of latest measurement
        time = db.query('SELECT MAX(time) FROM measurements').fetchone()[0]
    from_time = time - timedelta(minutes=time_window)
    to_time = time + timedelta(minutes=time_window)
    measurements = db.dict_query('''SELECT sensor_id, time, 
        AVG(temperature) AS t FROM measurements WHERE time > ? AND time < ?
        GROUP BY sensor_id''', [from_time, to_time])
    return measurements
Пример #5
0
def cantons():
    cantons = db.dict_query("""SELECT abbr, the_geom FROM cantons""")
    return cantons