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
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
def run_model():
# Get the information about the sensors
sens = sensors.sensors()
# Find the latest measurement time in the database
max_time = db.query('SELECT MAX(time) FROM measurements').fetchone()[0]
t = i2maps.datestring_to_datetime(str(max_time))
# Define the time step, in our case 1 hour
dt = datetime.timedelta(hours=1)
# Run the model for each time step for one week
# This is of course not very efficient, as we should rather first check
# if we have already computed the time step. But it is enough for
# illustration purposes.
for i in range(0, 24*7):
m = sensors.measurements(t)
t -= dt
data = [[sens[id]['geometry'].x, sens[id]['geometry'].y, sens[id]['elevation'], m[id]['t']] for id in m]
model.update(data, str(t))
print '%i of %i hours computed' % (i+1, 24*7)