result = task.read(f'''
SELECT
`stations`.`id` AS `id`,
`stations`.`name` AS `name`,
`stations`.`name_alt` AS `name_alt`,
`stations`.`region` AS `region`,
`stations`.`country` AS `country`,
`stations`.`national_id` AS `national_id`,
CAST(`stations`.`wmo` AS CHAR(5)) AS `wmo`,
`stations`.`icao` AS `icao`,
`stations`.`iata` AS `iata`,
`stations`.`latitude` AS `latitude`,
`stations`.`longitude` AS `longitude`,
`stations`.`altitude` AS `altitude`,
`stations`.`tz` as `timezone`,
`stations`.`history` as `history`,
MIN(`inventory_hourly`.`start`) AS "hourly_start",
MAX(`inventory_hourly`.`end`) AS "hourly_end",
MIN(`inventory_daily`.`start`) AS "daily_start",
MAX(`inventory_daily`.`end`) AS "daily_end"
FROM `stations`
LEFT JOIN (
SELECT
`station`,
`start`,
`end`
FROM `inventory`
WHERE
`mode` = "H"
) AS `inventory_hourly`
ON `stations`.`id` = `inventory_hourly`.`station`
LEFT JOIN (
SELECT
`station`,
`start`,
`end`
FROM `inventory`
WHERE
`mode` = "D"
) AS `inventory_daily`
ON `stations`.`id` = `inventory_daily`.`station`
GROUP BY `stations`.`id`
''')
exit()
else:
task.set_var(f'station_counter_{MODE}', counter + STATIONS_PER_CYCLE)
# DataFrame which holds all data
df_full = None
for station_file in stations:
try:
# Get national weather station ID
national_id = str(station_file[-13:-8]) if MODE == 'recent' else str(
station_file[-32:-27])
station = task.read(
f"SELECT `id` FROM `stations` WHERE `national_id` LIKE '{national_id}'"
).iloc[0][0]
# DataFrame which holds data for one weather station
df_station = None
# Go through all parameters
for parameter in PARAMETERS:
try:
remote_file = find_file(ftp, parameter['dir'], national_id)
if remote_file is not None:
hash = hashlib.md5(remote_file.encode('utf-8')).hexdigest()
result = task.read(
f'''
SET STATEMENT
max_statement_time=60
FOR
SELECT
`date`,
SUBSTRING_INDEX(GROUP_CONCAT(`tavg` ORDER BY `priority`), ",", 1) AS `tavg`,
SUBSTRING_INDEX(GROUP_CONCAT(`tmin` ORDER BY `priority`), ",", 1) AS `tmin`,
SUBSTRING_INDEX(GROUP_CONCAT(`tmax` ORDER BY `priority`), ",", 1) AS `tmax`,
SUBSTRING_INDEX(GROUP_CONCAT(`prcp` ORDER BY `priority`), ",", 1) AS `prcp`,
SUBSTRING_INDEX(GROUP_CONCAT(`snow` ORDER BY `priority`), ",", 1) AS `snow`,
SUBSTRING_INDEX(GROUP_CONCAT(`wdir` ORDER BY `priority`), ",", 1) AS `wdir`,
SUBSTRING_INDEX(GROUP_CONCAT(`wspd` ORDER BY `priority`), ",", 1) AS `wspd`,
SUBSTRING_INDEX(GROUP_CONCAT(`wpgt` ORDER BY `priority`), ",", 1) AS `wpgt`,
SUBSTRING_INDEX(GROUP_CONCAT(`pres` ORDER BY `priority`), ",", 1) AS `pres`,
SUBSTRING_INDEX(GROUP_CONCAT(`tsun` ORDER BY `priority`), ",", 1) AS `tsun`
FROM (
(SELECT
`date`,
`tavg`,
`tmin`,
`tmax`,
`prcp`,
`snow`,
NULL AS `wdir`,
`wspd`,
`wpgt`,
`pres`,
`tsun`,
"A" AS `priority`
FROM `daily_national`
WHERE
`station` = :station
)
UNION ALL
(SELECT
`date`,
`tavg`,
`tmin`,
`tmax`,
`prcp`,
`snow`,
`wdir`,
`wspd`,
`wpgt`,
NULL AS `pres`,
`tsun`,
"A" AS `priority`
FROM `daily_ghcn`
WHERE
`station` = :station
)
UNION ALL
(SELECT
DATE(CONVERT_TZ(`hourly_national`.`time`, "UTC", :timezone)) AS `date`,
IF(count(`hourly_national`.`temp`)<24, NULL, ROUND(AVG(`hourly_national`.`temp`),1)) AS `tavg`,
IF(count(`hourly_national`.`temp`)<24, NULL, MIN(`hourly_national`.`temp`)) AS `tmin`,
IF(count(`hourly_national`.`temp`)<24, NULL, MAX(`hourly_national`.`temp`)) AS `tmax`,
IF(count(`hourly_national`.`prcp`)<24, NULL, SUM(`hourly_national`.`prcp`)) AS `prcp`,
NULL AS `snow`,
IF(count(`hourly_national`.`wdir`)<24, NULL, ROUND(AVG(`hourly_national`.`wdir`),1)) AS `wdir`,
IF(count(`hourly_national`.`wspd`)<24, NULL, ROUND(AVG(`hourly_national`.`wspd`),1)) AS `wspd`,
NULL AS `wpgt`,
IF(count(`hourly_national`.`pres`)<24, NULL, ROUND(AVG(`hourly_national`.`pres`),1)) AS `pres`,
NULL AS `tsun`,
"B" AS `priority`
FROM `hourly_national`
WHERE
`hourly_national`.`station` = :station
GROUP BY
`station`,
`date`
)
UNION ALL
(SELECT
DATE(CONVERT_TZ(`hourly_isd`.`time`, "UTC", :timezone)) AS `date`,
IF(count(`hourly_isd`.`temp`)<24, NULL, ROUND(AVG(`hourly_isd`.`temp`),1)) AS `tavg`,
IF(count(`hourly_isd`.`temp`)<24, NULL, MIN(`hourly_isd`.`temp`)) AS `tmin`,
IF(count(`hourly_isd`.`temp`)<24, NULL, MAX(`hourly_isd`.`temp`)) AS `tmax`,
IF(count(`hourly_isd`.`prcp`)<24, NULL, SUM(`hourly_isd`.`prcp`)) AS `prcp`,
NULL AS `snow`,
IF(count(`hourly_isd`.`wdir`)<24, NULL, ROUND(AVG(`hourly_isd`.`wdir`),1)) AS `wdir`,
IF(count(`hourly_isd`.`wspd`)<24, NULL, ROUND(AVG(`hourly_isd`.`wspd`),1)) AS `wspd`,
NULL AS `wpgt`,
IF(count(`hourly_isd`.`pres`)<24, NULL, ROUND(AVG(`hourly_isd`.`pres`),1)) AS `pres`,
NULL AS `tsun`,
"B" AS `priority`
FROM `hourly_isd`
WHERE
`hourly_isd`.`station` = :station
GROUP BY
`station`,
`date`
)
UNION ALL
(SELECT
DATE(CONVERT_TZ(`hourly_synop`.`time`, "UTC", :timezone)) AS `date`,
IF(count(`hourly_synop`.`temp`)<24, NULL, ROUND(AVG(`hourly_synop`.`temp`),1)) AS `tavg`,
IF(count(`hourly_synop`.`temp`)<24, NULL, MIN(`hourly_synop`.`temp`)) AS `tmin`,
IF(count(`hourly_synop`.`temp`)<24, NULL, MAX(`hourly_synop`.`temp`)) AS `tmax`,
IF(count(`hourly_synop`.`prcp`)<24, NULL, SUM(`hourly_synop`.`prcp`)) AS `prcp`,
IF(count(`hourly_synop`.`snow`)<24, NULL, MAX(`hourly_synop`.`snow`)) AS `snow`,
IF(count(`hourly_synop`.`wdir`)<24, NULL, ROUND(AVG(`hourly_synop`.`wdir`),1)) AS `wdir`,
IF(count(`hourly_synop`.`wspd`)<24, NULL, ROUND(AVG(`hourly_synop`.`wspd`),1)) AS `wspd`,
IF(count(`hourly_synop`.`wpgt`)<24, NULL, MAX(`wpgt`)) AS `wpgt`,
IF(count(`hourly_synop`.`pres`)<24, NULL, ROUND(AVG(`hourly_synop`.`pres`),1)) AS `pres`,
NULL AS `tsun`,
"C" AS `priority`
FROM `hourly_synop`
WHERE
`hourly_synop`.`station` = :station
GROUP BY
`station`,
`date`
)
UNION ALL
(SELECT
DATE(CONVERT_TZ(`hourly_metar`.`time`, "UTC", :timezone)) AS `date`,
IF(count(`hourly_metar`.`temp`)<24, NULL, ROUND(AVG(`hourly_metar`.`temp`),1)) AS `tavg`,
IF(count(`hourly_metar`.`temp`)<24, NULL, MIN(`hourly_metar`.`temp`)) AS `tmin`,
IF(count(`hourly_metar`.`temp`)<24, NULL, MAX(`hourly_metar`.`temp`)) AS `tmax`,
NULL AS `prcp`,
NULL AS `snow`,
IF(count(`hourly_metar`.`wdir`)<24, NULL, ROUND(AVG(`hourly_metar`.`wdir`),1)) AS `wdir`,
IF(count(`hourly_metar`.`wspd`)<24, NULL, ROUND(AVG(`hourly_metar`.`wspd`),1)) AS `wspd`,
NULL AS `wpgt`,
IF(count(`hourly_metar`.`pres`)<24, NULL, ROUND(AVG(`hourly_metar`.`pres`),1)) AS `pres`,
NULL AS `tsun`,
"D" AS `priority`
FROM `hourly_metar`
WHERE
`hourly_metar`.`station` = :station
GROUP BY
`station`,
`date`
)
{f"""
UNION ALL
(SELECT
DATE(CONVERT_TZ(`hourly_model`.`time`, "UTC", :timezone)) AS `date`,
IF(count(`hourly_model`.`temp`)<24, NULL, ROUND(AVG(`hourly_model`.`temp`),1)) AS `tavg`,
IF(count(`hourly_model`.`temp`)<24, NULL, MIN(`hourly_model`.`temp`)) AS `tmin`,
IF(count(`hourly_model`.`temp`)<24, NULL, MAX(`hourly_model`.`temp`)) AS `tmax`,
IF(count(`hourly_model`.`prcp`)<24, NULL, SUM(`hourly_model`.`prcp`)) AS `prcp`,
NULL AS `snow`,
IF(count(`hourly_model`.`wdir`)<24, NULL, ROUND(AVG(`hourly_model`.`wdir`),1)) AS `wdir`,
IF(count(`hourly_model`.`wspd`)<24, NULL, ROUND(AVG(`hourly_model`.`wspd`),1)) AS `wspd`,
IF(count(`hourly_model`.`wpgt`)<24, NULL, MAX(`hourly_model`.`wpgt`)) AS `wpgt`,
IF(count(`hourly_model`.`pres`)<24, NULL, ROUND(AVG(`hourly_model`.`pres`),1)) AS `pres`,
NULL AS `tsun`,
"E" AS `priority`
FROM `hourly_model`
WHERE
`hourly_model`.`station` = :station
GROUP BY
`station`,
`date`
)
""" if SCOPE == 'full' else ''}
) AS `daily_derived`
WHERE
`tavg` IS NOT NULL
OR `tmin` IS NOT NULL
OR `tmax` IS NOT NULL
OR `prcp` IS NOT NULL
GROUP BY
`date`
ORDER BY
`date`
''', {
'station': station[0],
'timezone': station[1]
})
'PO': 27,
'SQ': 27,
'FC': 27,
'SS': 27,
'DS': 27
}
return condicodes.get(str(code), None)
except BaseException:
return None
# Get ICAO stations
stations = task.read(
"""SELECT `id`, `icao` FROM `stations` WHERE `icao` IS NOT NULL""")
stations = stations.set_index('icao')
# Get cycle
cycle = (datetime.now() - timedelta(hours=2)).strftime('%H')
# Create request for JSON file
url = f"https://tgftp.nws.noaa.gov/data/observations/metar/cycles/{cycle}Z.TXT"
req = request.Request(url)
# Get METAR strings
with request.urlopen(req) as raw:
file = raw.read().decode().splitlines()
data = []
result = task.read(
f'''
SET STATEMENT
max_statement_time=90
FOR
SELECT
DATE(MIN(`time`)) AS `date`,
DATE_FORMAT(MIN(`time`), '%H') AS `hour`,
SUBSTRING_INDEX(GROUP_CONCAT(`temp` ORDER BY `priority`), ',', 1) AS `temp`,
SUBSTRING_INDEX(GROUP_CONCAT(`dwpt` ORDER BY `priority`), ',', 1) AS `dwpt`,
SUBSTRING_INDEX(GROUP_CONCAT(`rhum` ORDER BY `priority`), ',', 1) AS `rhum`,
SUBSTRING_INDEX(GROUP_CONCAT(`prcp` ORDER BY `priority`), ',', 1) AS `prcp`,
SUBSTRING_INDEX(GROUP_CONCAT(`snow` ORDER BY `priority`), ',', 1) AS `snow`,
SUBSTRING_INDEX(GROUP_CONCAT(`wdir` ORDER BY `priority`), ',', 1) AS `wdir`,
SUBSTRING_INDEX(GROUP_CONCAT(`wspd` ORDER BY `priority`), ',', 1) AS `wspd`,
SUBSTRING_INDEX(GROUP_CONCAT(`wpgt` ORDER BY `priority`), ',', 1) AS `wpgt`,
SUBSTRING_INDEX(GROUP_CONCAT(`pres` ORDER BY `priority`), ',', 1) AS `pres`,
SUBSTRING_INDEX(GROUP_CONCAT(`tsun` ORDER BY `priority`), ',', 1) AS `tsun`,
SUBSTRING_INDEX(GROUP_CONCAT(`coco` ORDER BY `priority`), ',', 1) AS `coco`
FROM (
(SELECT
`time`,
`temp`,
ROUND((243.04*(LN(`rhum`/100)+((17.625*`temp`)/(243.04+`temp`)))/(17.625-LN(`rhum`/100)-((17.625*`temp`)/(243.04+`temp`)))),1) AS `dwpt`,
`rhum`,
`prcp`,
NULL AS `snow`,
`wdir`,
`wspd`,
NULL AS `wpgt`,
`pres`,
`tsun`,
NULL AS `coco`,
'A' AS `priority`
FROM `hourly_national`
WHERE
`station` = :station
{f'AND `time` BETWEEN "{start_year}-01-01 00:00:00" AND "{end_year}-12-31 23:59:59"' if MODE == 'recent' else ''}
)
UNION ALL
(SELECT
`time`,
`temp`,
ROUND((243.04*(LN(`rhum`/100)+((17.625*`temp`)/(243.04+`temp`)))/(17.625-LN(`rhum`/100)-((17.625*`temp`)/(243.04+`temp`)))),1) AS `dwpt`,
`rhum`,
`prcp`,
NULL AS `snow`,
`wdir`,
`wspd`,
NULL AS `wpgt`,
`pres`,
NULL AS `tsun`,
NULL AS `coco`,
'A' AS `priority`
FROM `hourly_isd`
WHERE
`station` = :station
{f'AND `time` BETWEEN "{start_year}-01-01 00:00:00" AND "{end_year}-12-31 23:59:59"' if MODE == 'recent' else ''}
)
UNION ALL
(SELECT
`time`,
`temp`,
ROUND((243.04*(LN(`rhum`/100)+((17.625*`temp`)/(243.04+`temp`)))/(17.625-LN(`rhum`/100)-((17.625*`temp`)/(243.04+`temp`)))),1) AS `dwpt`,
`rhum`,
`prcp`,
`snow`,
`wdir`,
`wspd`,
`wpgt`,
`pres`,
`tsun`,
`coco`,
'B' AS `priority`
FROM `hourly_synop`
WHERE
`station` = :station
{f'AND `time` BETWEEN "{start_year}-01-01 00:00:00" AND "{end_year}-12-31 23:59:59"' if MODE == 'recent' else ''}
)
UNION ALL
(SELECT
`time`,
`temp`,
ROUND((243.04*(LN(`rhum`/100)+((17.625*`temp`)/(243.04+`temp`)))/(17.625-LN(`rhum`/100)-((17.625*`temp`)/(243.04+`temp`)))),1) AS `dwpt`,
`rhum`,
NULL AS `prcp`,
NULL AS `snow`,
`wdir`,
`wspd`,
NULL AS `wpgt`,
`pres`,
NULL AS `tsun`,
`coco`,
'C' AS `priority`
FROM `hourly_metar`
WHERE
`station` = :station
{f'AND `time` BETWEEN "{start_year}-01-01 00:00:00" AND "{end_year}-12-31 23:59:59"' if MODE == 'recent' else ''}
)
{f"""
UNION ALL
(SELECT
`time`,
`temp`,
ROUND((243.04*(LN(`rhum`/100)+((17.625*`temp`)/(243.04+`temp`)))/(17.625-LN(`rhum`/100)-((17.625*`temp`)/(243.04+`temp`)))),1) AS `dwpt`,
`rhum`,
`prcp`,
NULL AS `snow`,
`wdir`,
`wspd`,
`wpgt`,
`pres`,
NULL AS `tsun`,
`coco`,
'D' AS `priority`
FROM `hourly_model`
WHERE
`station` = :station
{f'AND `time` BETWEEN "{start_year}-01-01 00:00:00" AND "{end_year}-12-31 23:59:59"' if MODE == 'recent' else ''}
)
""" if SCOPE == 'full' else ''}
) AS `hourly_derived`
GROUP BY
DATE_FORMAT(`time`, '%Y %m %d %H')
ORDER BY
`time`
''', {'station': station[0]})
exit()
else:
task.set_var(f'station_counter_{MODE}', counter + STATIONS_PER_CYCLE)
# DataFrame which holds all data
df_full = None
for remote_file in files:
try:
# Get national weather station ID
national_id = str(remote_file[-13:-8]) if MODE == 'recent' else str(
remote_file[-32:-27])
station_df = task.read(
f"SELECT `id`, `altitude` FROM `stations` WHERE `national_id` LIKE '{national_id}'"
)
station = station_df.iloc[0][0]
altitude = station_df.iloc[0][1]
hash = hashlib.md5(remote_file.encode('utf-8')).hexdigest()
local_file = os.path.dirname(__file__) + os.sep + hash
ftp.retrbinary("RETR " + remote_file, open(local_file, 'wb').write)
# Unzip file
zipped = ZipFile(local_file, 'r')
filelist = zipped.namelist()
raw = None
for file in filelist:
if file[:7] == 'produkt':
raw = BytesIO(zipped.open(file, 'r').read())