Wetterdatenbank - Versionsgeschichte

192.168.111.166: /* Views */

2014-05-23T19:20:26Z

Views

← Nächstältere Version		Version vom 23. Mai 2014, 19:20 Uhr
Zeile 46:		Zeile 46:
	<pre>		<pre>
	CREATE VIEW IF NOT EXISTS measurements_day_avg AS		CREATE VIEW IF NOT EXISTS measurements_day_avg AS
	SELECT DATE(timestamp) AS date, locationId, city, AVG(temperature) AS temperature, temperatureUnit,		SELECT DATE(timestamp) AS date, locationId, city, ROUND(AVG(temperature), 2) AS temperature,
	AVG(windDirection) AS windDirection, AVG(windSpeed) AS windSpeed, windSpeedUnit,		temperatureUnit, ROUND(AVG(windDirection), 2) AS windDirection, ROUND(AVG(windSpeed), 2) AS windSpeed,
	AVG(pressure) AS pressure, pressureUnit, AVG(humidity) AS humidity, COUNT(~~DATE(timestamp)~~) AS count		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		FROM measurements, locations WHERE locationId = locations.woeid GROUP BY date, locationId

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

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

192.168.111.166: /* Views */

2014-05-23T19:08:55Z

Views

@@ Zeile 50: / Zeile 50: @@
          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
 </pre>

192.168.111.166 am 23. Mai 2014 um 18:56 Uhr

2014-05-23T18:56:42Z

← Nächstältere Version		Version vom 23. Mai 2014, 18:56 Uhr
Zeile 41:		Zeile 41:

	CREATE INDEX IF NOT EXISTS measurementLocation ON measurements (locationId ASC)		CREATE INDEX IF NOT EXISTS measurementLocation ON measurements (locationId ASC)
			</pre>

			=== Views ===
			<pre>
			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
	</pre>		</pre>

192.168.111.166: /* Queries */

2014-05-23T18:38:35Z

Queries

← Nächstältere Version		Version vom 23. Mai 2014, 18:38 Uhr
Zeile 47:		Zeile 47:
	SELECT * from measurements, locations WHERE locations.woeid = measurements.locationId AND city="Frankfurt" ORDER BY measurements.id		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) ~~FROMmeasurements~~, locations		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		WHERE locations.woeid = measurements.locationId GROUP BY locationId, DATE(timestamp) ORDER BY city, timestamp

192.168.111.166: /* Queries */

2014-05-23T18:28:45Z

Queries

← Nächstältere Version		Version vom 23. Mai 2014, 18:28 Uhr
Zeile 47:		Zeile 47:
	SELECT * from measurements, locations WHERE locations.woeid = measurements.locationId AND city="Frankfurt" ORDER BY measurements.id		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		SELECT DATE(timestamp), city, COUNT(temperature), AVG(temperature), MIN(temperature), MAX(temperature) FROMmeasurements, locations
	WHERE locations.woeid = measurements.locationId GROUP BY locationId, DATE(timestamp) ORDER BY city, timestamp		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
	</pre>		</pre>

imported>WikiSysop: /* WoIds */

2014-03-21T19:21:32Z

WoIds

← Nächstältere Version		Version vom 21. März 2014, 19:21 Uhr
Zeile 13:		Zeile 13:
	*1582504: Johannesburg		*1582504: Johannesburg
	*9807: Vancouver		*9807: Vancouver
			*676757: München
			*656958: Hamburg

	== Database ==		== Database ==

192.168.111.166 am 26. Oktober 2013 um 09:31 Uhr

2013-10-26T09:31:36Z

← Nächstältere Version		Version vom 26. Oktober 2013, 09:31 Uhr
Zeile 15:		Zeile 15:

	== Database ==		== Database ==
			*SQLite

			=== Schema ===
	<pre>		<pre>
	CREATE TABLE IF NOT EXISTS locations		CREATE TABLE IF NOT EXISTS locations
Zeile 38:		Zeile 41:
	</pre>		</pre>

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

192.168.111.166: /* Database */

2013-10-26T09:30:44Z

Database

192.168.111.166: /* Queries */

2013-10-26T09:30:16Z

Queries

← Nächstältere Version		Version vom 26. Oktober 2013, 09:30 Uhr
Zeile 35:		Zeile 35:
	<pre>		<pre>
	SELECT * from measurements, locations WHERE locations.woeid = measurements.locationId AND city="Frankfurt" ORDER BY measurements.id		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 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
	</pre>		</pre>

192.168.111.166: Die Seite wurde neu angelegt: „== Links == [http://www.phpgangsta.de/mit-wetterdaten-arbeiten-yahoo-weather-api Mit Wetterdaten arbeiten: Yahoo Weather API] [http://developer.yahoo.com/wea…“

2013-10-26T09:27:41Z

Die Seite wurde neu angelegt: „== Links == *[http://www.phpgangsta.de/mit-wetterdaten-arbeiten-yahoo-weather-api Mit Wetterdaten arbeiten: Yahoo Weather API] *[http://developer.yahoo.com/wea…“

Neue Seite

== Links ==
*[http://www.phpgangsta.de/mit-wetterdaten-arbeiten-yahoo-weather-api Mit Wetterdaten arbeiten: Yahoo Weather API]
*[http://developer.yahoo.com/weather Yahoo Weather RSS Feed]
*[http://developer.yahoo.com/yui/examples/connection/weather.html Connection Manager: Retrieving a Yahoo! Weather RSS Feed]
*[http://docs.python.org/2/library/xml.etree.elementtree.html Python XML ElementTree]

== WoIds ==
*650272: Frankfurt
*636438: Bad Homburg
*626678: Saint Raphael
*664942: Karlsruhe
*1591691: Kapstadt
*1582504: Johannesburg
*9807: Vancouver

== Database ==
<pre>
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)
</pre>

== Queries ==
<pre>
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
</pre>

← Nächstältere Version		Version vom 26. Oktober 2013, 09:30 Uhr
Zeile 19:		Zeile 19:
	(woeid INTEGER PRIMARY KEY ASC ON CONFLICT IGNORE NOT NULL, city VARCHAR(255) NOT NULL,		(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))		region VARCHAR(255), country VARCHAR(255), latitude DECIMAL(8,3), longitude DECIMAL(8,3))

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

	CREATE TABLE IF NOT EXISTS measurements		CREATE TABLE IF NOT EXISTS measurements
	(id INTEGER PRIMARY KEY ASC ON CONFLICT FAIL NOT NULL, timestamp DATETIME,		(id INTEGER PRIMARY KEY ASC ON CONFLICT FAIL NOT NULL, timestamp DATETIME,
Zeile 26:		Zeile 28:
	conditionId INTEGER, windDirection INTEGER, windSpeed DECIMAL(7,3), windSpeedUnit CHAR(5),		conditionId INTEGER, windDirection INTEGER, windSpeed DECIMAL(7,3), windSpeedUnit CHAR(5),
	pressure DECIMAL(8,3), pressureUnit CHAR(2), humidity DECIMAL(4,1))		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 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 locationsCities ON locations (city ASC)

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

	CREATE INDEX IF NOT EXISTS measurementLocation ON measurements (locationId ASC)		CREATE INDEX IF NOT EXISTS measurementLocation ON measurements (locationId ASC)
	</pre>		</pre>

Wetterdatenbank - Versionsgeschichte

192.168.111.166: /* Views */

192.168.111.166: /* Views */

192.168.111.166 am 23. Mai 2014 um 18:56 Uhr

192.168.111.166: /* Queries */

192.168.111.166: /* Queries */

imported>WikiSysop: /* WoIds */

192.168.111.166 am 26. Oktober 2013 um 09:31 Uhr

192.168.111.166: /* Database */

192.168.111.166: /* Queries */

192.168.111.166: Die Seite wurde neu angelegt: „== Links == *[http://www.phpgangsta.de/mit-wetterdaten-arbeiten-yahoo-weather-api Mit Wetterdaten arbeiten: Yahoo Weather API] *[http://developer.yahoo.com/wea…“

192.168.111.166: Die Seite wurde neu angelegt: „== Links == [http://www.phpgangsta.de/mit-wetterdaten-arbeiten-yahoo-weather-api Mit Wetterdaten arbeiten: Yahoo Weather API] [http://developer.yahoo.com/wea…“