Links
WoIds
- 650272: Frankfurt
- 636438: Bad Homburg
- 626678: Saint Raphael
- 664942: Karlsruhe
- 1591691: Kapstadt
- 1582504: Johannesburg
- 9807: Vancouver
- 676757: München
- 656958: Hamburg
Database
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, AVG(temperature) AS temperature, temperatureUnit,
AVG(windDirection) AS windDirection, AVG(windSpeed) AS windSpeed, windSpeedUnit,
AVG(pressure) AS pressure, pressureUnit, AVG(humidity) AS humidity, COUNT(DATE(timestamp)) 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, AVG(temperature) AS temperature,
temperatureUnit, AVG(windDirection) AS windDirection, AVG(windSpeed) AS windSpeed, windSpeedUnit,
AVG(pressure) AS pressure, pressureUnit, AVG(humidity) AS humidity,
COUNT(STRFTIME("%Y-%m", timestamp)) 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, AVG(temperature) AS temperature,
temperatureUnit, AVG(windDirection) AS windDirection, AVG(windSpeed) AS windSpeed, windSpeedUnit,
AVG(pressure) AS pressure, pressureUnit, AVG(humidity) AS humidity,
COUNT(STRFTIME("%Y", timestamp)) 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