Wetterdatenbank

Links

• Mit Wetterdaten arbeiten: Yahoo Weather API
• Yahoo Weather RSS Feed
• Connection Manager: Retrieving a Yahoo! Weather RSS Feed
• Python XML ElementTree

WoIds

• 650272: Frankfurt
• 636438: Bad Homburg
• 626678: Saint Raphael
• 664942: Karlsruhe
• 1591691: Kapstadt
• 1582504: Johannesburg
• 9807: Vancouver
• 676757: München
• 656958: Hamburg

Database

• SQLite

Schema

CREATE TABLE IF NOT EXISTS locations
    (woeid INTEGER PRIMARY KEY ASC ON CONFLICT IGNORE NOT NULL, city VARCHAR(255) NOT NULL,
     region VARCHAR(255), country VARCHAR(255), latitude DECIMAL(8,3), longitude DECIMAL(8,3))

CREATE TABLE IF NOT EXISTS conditions
    (id INTEGER PRIMARY KEY ASC ON CONFLICT IGNORE NOT NULL, text VARCHAR(255) NOT NULL)

CREATE TABLE IF NOT EXISTS measurements
    (id INTEGER PRIMARY KEY ASC ON CONFLICT FAIL NOT NULL, timestamp DATETIME,
     locationId INTEGER NOT NULL, temperature INTEGER, temperatureUnit CHAR(1),
     conditionId INTEGER, windDirection INTEGER, windSpeed DECIMAL(7,3), windSpeedUnit CHAR(5),
     pressure DECIMAL(8,3), pressureUnit CHAR(2), humidity DECIMAL(4,1))

CREATE UNIQUE INDEX IF NOT EXISTS measurementLocationAndTimeIndex ON measurements (timestamp ASC, locationId ASC)

CREATE INDEX IF NOT EXISTS locationsCities ON locations (city ASC)

CREATE INDEX IF NOT EXISTS conditionsTexts ON conditions (text ASC)

CREATE INDEX IF NOT EXISTS measurementLocation ON measurements (locationId ASC)

Views

CREATE VIEW IF NOT EXISTS measurements_day_avg AS
    SELECT DATE(timestamp) AS date, locationId, city, ROUND(AVG(temperature), 2) AS temperature,
        temperatureUnit, ROUND(AVG(windDirection), 2) AS windDirection, ROUND(AVG(windSpeed), 2) AS windSpeed,
        windSpeedUnit, ROUND(AVG(pressure), 2) AS pressure, pressureUnit, ROUND(AVG(humidity), 2) AS humidity,
        COUNT(locationId) AS count
    FROM measurements, locations WHERE locationId = locations.woeid GROUP BY date, locationId

CREATE VIEW IF NOT EXISTS measurements_month_avg AS
    SELECT STRFTIME("%Y-%m", timestamp) AS month, locationId, city, ROUND(AVG(temperature), 2) AS temperature,
        temperatureUnit, ROUND(AVG(windDirection), 2) AS windDirection, ROUND(AVG(windSpeed), 2) AS windSpeed,
        windSpeedUnit, ROUND(AVG(pressure), 2) AS pressure, pressureUnit, ROUND(AVG(humidity), 2) AS humidity,
        COUNT(locationId) AS count
    FROM measurements, locations WHERE locationId = locations.woeid GROUP BY month, locationId

CREATE VIEW IF NOT EXISTS measurements_year_avg AS
    SELECT STRFTIME("%Y", timestamp) AS year, locationId, city, ROUND(AVG(temperature), 2) AS temperature,
        temperatureUnit, ROUND(AVG(windDirection), 2) AS windDirection, ROUND(AVG(windSpeed), 2) AS windSpeed,
        windSpeedUnit, ROUND(AVG(pressure), 2) AS pressure, pressureUnit, ROUND(AVG(humidity), 2) AS humidity,
        COUNT(locationId) AS count
    FROM measurements, locations WHERE locationId = locations.woeid GROUP BY year, locationId

Queries

SELECT * from measurements, locations WHERE locations.woeid = measurements.locationId AND city="Frankfurt" ORDER BY measurements.id

SELECT DATE(timestamp), city, COUNT(temperature), AVG(temperature), MIN(temperature), MAX(temperature) FROM measurements, locations
    WHERE locations.woeid = measurements.locationId GROUP BY locationId, DATE(timestamp) ORDER BY city, timestamp

SELECT COUNT(m1.date), AVG(m1.temperature - m2.temperature), MIN(m1.temperature - m2.temperature), MAX(m1.temperature - m2.temperature), null AS const FROM
        (SELECT DATE(timestamp) AS date, city, AVG(temperature) AS temperature FROM measurements, locations
            WHERE locations.woeid = measurements.locationId AND city = "Frankfurt" GROUP BY locationId, DATE(timestamp)) m1
    INNER JOIN
        (SELECT DATE(timestamp) AS date, city, AVG(temperature) AS temperature FROM measurements, locations
            WHERE locations.woeid = measurements.locationId AND city = "Bad Homburg vor der Hohe" GROUP BY locationId, DATE(timestamp)) m2
    ON m1.date = m2.date
    WHERE m1.temperature > m2.temperature GROUP BY const