Skip to content

@marians /database.sql
Created

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
Snapshot of the script I wrote to generate http://vimeo.com/26157684
CREATE TABLE `stations` (
`id` varchar(9) COLLATE latin1_general_ci NOT NULL,
`postalcode` varchar(5) COLLATE latin1_general_ci NOT NULL,
`name` varchar(255) COLLATE latin1_general_ci NOT NULL,
`longitude` decimal(5,2) NOT NULL,
`latitude` decimal(5,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='BfS Sensor Stations';
CREATE TABLE `values_2h` (
`station_id` varchar(9) NOT NULL,
`datetime_utc` datetime NOT NULL,
`dose` decimal(6,3) NOT NULL,
`status` tinyint(4) NOT NULL,
UNIQUE KEY `uniq` (`station_id`,`datetime_utc`),
KEY `station_id` (`station_id`),
KEY `datetime_utc` (`datetime_utc`),
KEY `status` (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='2 hour unverified values';
INSERT IGNORE INTO `stations` (`id`, `postalcode`, `name`, `longitude`, `latitude`)
VALUES ('010010001', '24941', 'Flensburg', 9.38, 54.78);
INSERT IGNORE INTO `values_2h` (`station_id`, `datetime_utc`, `dose`, `status`)
VALUES ('010010001', '2011-06-15 00:00:00', 0.078, 0);
#!/bin/python
# -*- coding: utf-8 -*-
from mpl_toolkits.basemap import Basemap
import matplotlib.pyplot as plt
import numpy as np
import sys
import MySQLdb
import math
from time import sleep
import pygame
from pygame.locals import *
import pygame.gfxdraw
import gradients
# globals
title = "My Game"
screen=None
clock = None
framerate = 25
black = [ 0, 0, 0]
white = [255,255,255]
almostwhite = [230,230,230]
blue = [ 0, 0,255]
green = [ 0,255, 0]
red = [255, 0, 0]
data = None
dates = None
date_cursor = 0
dotradius = 4
value_color_cache = {}
class MySQLDataStore:
def __init__(self, dbname, host='localhost', user='root', password=''):
try:
self.conn = MySQLdb.connect (host = host, user = user, passwd = password, db = dbname)
self.cursor = self.conn.cursor(MySQLdb.cursors.DictCursor)
except MySQLdb.Error, e:
print "Error %d: %s" % (e.args[0], e.args[1])
sys.exit (1)
def get_rows(self, sql):
try:
self.cursor.execute(sql)
rows = []
while (1):
row = self.cursor.fetchone()
if row == None:
break
rows.append(row)
return rows
except MySQLdb.Error, e:
print "Error %d: %s" % (e.args[0], e.args[1])
class WeatherData(MySQLDataStore):
def __init__(self, dbname, host='localhost', user='root', password=''):
MySQLDataStore.__init__(self, dbname, host, user, password)
self.read_stations()
def read_stations(self):
stations = self.get_rows('SELECT * FROM weather_stations')
self.stations = {}
for station in stations:
self.stations[int(station['id'])] = station
def get_values(self, date, station_id):
return self.get_rows('SELECT * FROM weather WHERE station_id="'+ str(station_id) +'" AND date="'+ date.strftime('%Y%m%d') +'"')[0]
class RadiationData(MySQLDataStore):
"""Access to radiation data via the local MySQL database"""
def __init__(self, dbname, host='localhost', user='root', password=''):
MySQLDataStore.__init__(self, dbname, host, user, password)
self.read_stats()
self.init_map()
self.read_stations()
#self.read_values()
def init_map(self):
self.bmap = Basemap(width=1200000, # width of map in meters
height=900000, # height of map in meters
projection='aeqd',
lat_0=51.1, lon_0=9.9, # center point of map
resolution='l',
area_thresh=10000.)
def read_stats(self):
stats = self.get_rows('SELECT MIN(dose) AS mindose, AVG(dose) AS avgdose, STD(dose) AS stddevdose, MAX(dose) AS maxdose FROM values_2h')[0]
self.mindose = float(stats['mindose'])
self.avgdose = float(stats['avgdose'])
self.stddevdose = float(stats['stddevdose'])
self.maxdose = float(stats['maxdose'])
def read_stations(self):
stations_flat = self.get_rows('SELECT id, latitude, longitude FROM stations')
self.stations = {}
self.stations_min_x = None
self.stations_min_y = None
self.stations_max_x = 0
self.stations_max_y = 0
for station in stations_flat:
x,y = self.bmap(station['longitude'], station['latitude'])
self.stations[station['id']] = {
'lat': station['latitude'],
'long': station['longitude'],
'x': x,
'y': y
}
if self.stations_min_x is None:
self.stations_min_x = x
if self.stations_min_y is None:
self.stations_min_y = x
self.stations_min_x = min(self.stations_min_x, x)
self.stations_min_y = min(self.stations_min_y, y)
self.stations_max_x = max(self.stations_max_x, x)
self.stations_max_y = max(self.stations_max_y, y)
self.stations_xspan = self.stations_max_x - self.stations_min_x
self.stations_yspan = self.stations_max_y - self.stations_min_y
print "Horizontal span:", self.stations_xspan, "meters"
print "Vertical span:", self.stations_yspan, "meters"
def set_dimensions(self, width, height):
smallside = min(width, height) * 0.9
largedimension = max(self.stations_xspan, self.stations_yspan)
self.position_factor = largedimension / smallside
self.screen_height = height
print "Resolution: ", self.position_factor, "meters per pixel"
# set colorfactor
#print "span between min", self.mindose, "and 3x average", (3 * self.avgdose), "is", (3 * self.avgdose - self.mindose)
self.colorfactor = 1.0 / ( 2 * self.avgdose - self.mindose )
print "colorfactor:", self.colorfactor
def get_dates(self, limit=None):
sql = 'SELECT DISTINCT datetime_utc FROM values_2h '
if limit is not None:
sql = sql + ' WHERE datetime_utc >= "2011-07-06 02:00:00"'
sql = sql + ' ORDER BY datetime_utc'
rows = self.get_rows(sql)
dates = []
for row in rows:
dates.append(row['datetime_utc'])
return dates
def get_values_for_date(self, date):
rows = self.get_rows('SELECT station_id, dose FROM values_2h WHERE datetime_utc = "'+ date.strftime('%Y-%m-%d %H-%M-%S') +'"')
values = {}
for row in rows:
values[row['station_id']] = row['dose']
return values
def value_color(self, val):
global value_color_cache
color = None
strval = str(val)
if strval not in value_color_cache:
scaled_value = (float(val) - self.mindose) * self.colorfactor * 255.0
color = int(min(scaled_value, 255.0))
value_color_cache[strval] = color
else:
color = value_color_cache[strval]
return [color, color, color, 255]
def scale_position(self, x, y):
return [(x - self.stations_min_x)/self.position_factor, self.screen_height-((y - self.stations_min_y)/self.position_factor)-(self.screen_height*0.15)]
def average(self, values):
return float(sum(values) / len(values))
def median(self, values):
return np.percentile(values, 50.0, None)
def quartile1(self, values):
return np.percentile(values, 25.0, None)
def quartile3(self, values):
return np.percentile(values, 75.0, None)
def percentile95(self, values):
return np.percentile(values, 95.0, None)
class MyGame:
"""The Main PyMan Class - This class handles the main
initialization and creating of the Game."""
def __init__(self, width=1024, height=768):
global screen, clock, title, data, dates
"""Initialize Game"""
data = RadiationData('radiationgermany')
dates = data.get_dates()
pygame.init()
clock=pygame.time.Clock()
self.width = width
self.height = height
self.screen = pygame.display.set_mode((self.width, self.height))
self.wdata = WeatherData('radiationgermany')
data.set_dimensions(self.width, self.height)
pygame.display.set_caption(title)
def MainLoop(self):
"""This is the Main Loop of the Game"""
global clock, black, green, white, blue, red, almostwhite, data, dates, date_cursor, dotradius
myriad_light_big = pygame.font.Font('fonts/myriad/MyriadPro-Light.otf', 38)
myriad_regular_big = pygame.font.Font('fonts/myriad/MyriadPro-Regular.otf', 38)
myriad_bold_big = pygame.font.Font('fonts/myriad/MyriadPro-Bold.otf', 38)
myriad_light_small = pygame.font.Font('fonts/myriad/MyriadPro-Light.otf', 18)
myriad_light_tiny = pygame.font.Font('fonts/myriad/MyriadPro-Light.otf', 14)
# explanation text
text1 = 'Displaying gamma radiation dose values over time,'
text2 = 'brighter colors indicate higher dose values.'
infoline1 = myriad_light_small.render(text1.decode('utf-8').encode('latin'), 1, [200, 200, 200])
infoline2 = myriad_light_small.render(text2.decode('utf-8').encode('latin'), 1, [200, 200, 200])
# imprint text
text1 = "Visualization by Marian Steinbach - www.sendung.de"
text2 = "Data courtesy of Bundesamt für Strahlenschutz (bfs) - www.bfs.de"
imprint1 = myriad_light_small.render(text1.decode('utf-8').encode('latin'), 1, [200, 200, 200])
imprint2 = myriad_light_small.render(text2.decode('utf-8').encode('latin'), 1, [200, 200, 200])
# scale gradient
scalegradient_height = 150.0
scalegradient = gradients.vertical((10, int(scalegradient_height)), (255,255,255,255), (0,0,0,255))
lastframe_values = {}
while 1:
for event in pygame.event.get():
if event.type == pygame.QUIT:
sys.exit()
elif event.type == KEYDOWN:
# escape
if event.key == 27:
sys.exit()
datestring = dates[date_cursor].strftime('%Y-%m-%d %H:%M')
daystring = dates[date_cursor].strftime('%Y%m%d')
# fetch values
values = data.get_values_for_date(dates[date_cursor])
# 10400 = Düsseldorf,
#weather = self.wdata.get_values(dates[date_cursor], '10400');
#if weather['precipitation_height'] > 0:
# print daystring, "precipitation_height", weather['precipitation_height']
rawvalues = []
for val in values.values():
rawvalues.append(float(val))
# calculate average value
#average = data.average(rawvalues)
#averagelabel_yoffset = int(scalegradient_height - (scalegradient_height * (average - data.mindose)))
#averagetext = "Average: " + ("%.3f" % average) + " µSv/h"
#averagelabel = myriad_light_tiny.render(averagetext.decode('utf-8').encode('latin'), 1, [200, 200, 200])
# min label
minlabel_yoffset = int(scalegradient_height)
minlabeltext = "Min: " + ("%.3f" % data.mindose) + " µSv/h"
minlabel = myriad_light_tiny.render(minlabeltext.decode('utf-8').encode('latin'), 1, [200, 200, 200])
# median label
median = data.median(rawvalues);
medianlabel_yoffset = int(scalegradient_height - (scalegradient_height * (median - data.mindose) * data.colorfactor))
mediantext = "Median: " + ("%.3f" % median) + " µSv/h"
medianlabel = myriad_light_tiny.render(mediantext.decode('utf-8').encode('latin'), 1, [200, 200, 200])
perc25 = data.quartile1(rawvalues)
perc75 = data.quartile3(rawvalues)
perc95 = data.percentile95(rawvalues)
perc25label_yoffset = int(scalegradient_height - (scalegradient_height * (perc25 - data.mindose) * data.colorfactor))
perc75label_yoffset = int(scalegradient_height - (scalegradient_height * (perc75 - data.mindose) * data.colorfactor))
perc95label_yoffset = int(scalegradient_height - (scalegradient_height * (perc95 - data.mindose) * data.colorfactor))
perc25text = "1. Quartile: " + ("%.3f" % perc25) + " µSv/h"
perc75text = "3. Quartile: " + ("%.3f" % perc75) + " µSv/h"
perc95text = "95th Percentile: " + ("%.3f" % perc95) + " µSv/h"
perc25label = myriad_light_tiny.render(perc25text.decode('utf-8').encode('latin'), 1, [200, 200, 200])
perc75label = myriad_light_tiny.render(perc75text.decode('utf-8').encode('latin'), 1, [200, 200, 200])
perc95label = myriad_light_tiny.render(perc95text.decode('utf-8').encode('latin'), 1, [200, 200, 200])
# background
self.screen.fill([47, 40, 54])
# draw date label
#datelabel = myriad_light_big.render(datestring, 1, [200, 200, 200])
daylabel = myriad_bold_big.render(dates[date_cursor].strftime('%B %d, %Y'), 1, [200, 200, 200])
hourlabel = myriad_light_big.render(dates[date_cursor].strftime('%Hh'), 1, [200, 200, 200])
self.screen.blit(daylabel, [int(self.width * 0.55), int(self.height * 0.2)])
self.screen.blit(hourlabel, [int(self.width * 0.55), int(self.height * 0.2)+50])
# explanation
self.screen.blit(infoline1, [int(self.width * 0.55), int(self.height * 0.7)])
self.screen.blit(infoline2, [int(self.width * 0.55), int(self.height * 0.7)+30])
# draw info text
self.screen.blit(imprint1, [int(self.width * 0.55), int(self.height * 0.7) + 90])
self.screen.blit(imprint2, [int(self.width * 0.55), int(self.height * 0.7) + 120])
# scale gradient
self.screen.blit(scalegradient, [int(self.width * 0.55), int(self.height * 0.4)])
# min label
pygame.gfxdraw.hline(self.screen, int(self.width * 0.55), int(self.width * 0.55)+15, int(self.height * 0.4)+minlabel_yoffset, [200, 200, 200])
self.screen.blit(minlabel, [int(self.width * 0.55) + 20, int(self.height * 0.4) + minlabel_yoffset - 7])
# median label
pygame.gfxdraw.hline(self.screen, int(self.width * 0.55), int(self.width * 0.55)+15, int(self.height * 0.4)+medianlabel_yoffset, [200, 200, 200])
self.screen.blit(medianlabel, [int(self.width * 0.55) + 20, int(self.height * 0.4) + medianlabel_yoffset - 7])
# 25 percentile line
#pygame.gfxdraw.hline(self.screen, int(self.width * 0.55), int(self.width * 0.55)+15, int(self.height * 0.4)+perc25label_yoffset, [200, 200, 200])
# 75 percentile line
#pygame.gfxdraw.hline(self.screen, int(self.width * 0.55), int(self.width * 0.55)+15, int(self.height * 0.4)+perc75label_yoffset, [200, 200, 200])
# 95 percentile line
pygame.gfxdraw.hline(self.screen, int(self.width * 0.55), int(self.width * 0.55)+15, int(self.height * 0.4)+perc95label_yoffset, [200, 200, 200])
self.screen.blit(perc95label, [int(self.width * 0.55) + 20, int(self.height * 0.4) + perc95label_yoffset - 7])
# draw values
for station_id in data.stations.keys():
val = None
if station_id in values:
val = values[station_id]
elif station_id in lastframe_values:
val = lastframe_values[station_id]
if val is not None:
color = data.value_color(val)
#print color
[xcoord, ycoord] = data.scale_position(data.stations[station_id]['x'], data.stations[station_id]['y'])
x = int(xcoord + self.width/7)
y = int(ycoord + self.height/10)
pygame.gfxdraw.filled_circle(self.screen, x, y, dotradius, color)
pygame.gfxdraw.aacircle(self.screen, x, y, dotradius, color)
lastframe_values[station_id] = val
# debugging with a random stations 032510411
#debugstations = ['032510411', '095751521']
#for debugstation in debugstations:
# [xcoord, ycoord] = data.scale_position(data.stations[debugstation]['x'], data.stations[debugstation]['y'])
# x = int(xcoord + self.width/7)
# y = int(ycoord + self.height/10)
# pygame.gfxdraw.hline(self.screen, x+dotradius, x+10, y, [200, 200, 200])
# stationtext = ("%.3f" % values[debugstation]) + " µSv/h"
# stationlabel = myriad_light_tiny.render(stationtext.decode('utf-8').encode('latin'), 1, [200, 200, 200])
# self.screen.blit(stationlabel, [x + 15, y-7])
# save buffer
pygame.image.save(self.screen, 'pngs/png_' + ('%05d' % date_cursor) + '.png')
# end drawing
#print date_cursor, datestring, "median:", median, "perc25:", perc25, "perc75:", perc75, "perc95:", perc95
if len(dates) > (date_cursor + 1):
date_cursor += 1
#else:
# sys.exit() # game pause
clock.tick(15)
pygame.display.flip()
if __name__ == "__main__":
MainWindow = MyGame(1280, 720)
MainWindow.MainLoop()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.