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

Database

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)

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