def synop(date,
last_date=None,
dwd_station_id=None,
wmo_station_id=None,
source_id=None):
if not last_date:
last_date = date + datetime.timedelta(days=1)
sources_rows = sources(dwd_station_id=dwd_station_id,
wmo_station_id=wmo_station_id,
source_id=source_id,
observation_types=['synop'])['sources']
source_ids = [row['id'] for row in sources_rows]
sql = """
SELECT *
FROM synop
WHERE
timestamp BETWEEN %(date)s AND %(last_date)s AND
source_id IN %(source_ids_tuple)s
ORDER BY timestamp
"""
params = {
'date': date,
'last_date': last_date,
'source_ids_tuple': tuple(source_ids),
}
return {
'weather': _make_dicts(fetch(sql, params)),
'sources': _make_dicts(sources_rows),
}
def poll(self):
self.logger.info("Polling for updated files")
parsed_files = {
row['url']: row
for row in fetch('SELECT * FROM parsed_files')
}
for url in self.urls:
for file_info in self.poll_url(url):
if not self.matches_known_fingerprint(parsed_files, file_info):
yield file_info
def poll(self):
self.logger.info("Polling for updated files")
parsed_files = {
row['url']: (row['last_modified'], row['file_size'])
for row in fetch('SELECT * FROM parsed_files')
}
for url in self.urls:
for file_info in self.poll_url(url):
fingerprint = (file_info['last_modified'],
file_info['file_size'])
if parsed_files.get(file_info['url']) != fingerprint:
yield file_info
def load_location(self, wmo_station_id):
rows = fetch(
"""
SELECT lat, lon, height, station_name
FROM sources
WHERE wmo_station_id = %s
ORDER BY observation_type DESC, id DESC
LIMIT 1
""",
(wmo_station_id,),
)
if not rows:
raise ValueError(
f'Unable to find location for WMO station {wmo_station_id}')
return rows[0]
def _current_weather(source_ids, not_null=None):
params = {
'source_ids': source_ids,
'source_ids_tuple': tuple(source_ids),
}
where = "source_id IN %(source_ids_tuple)s"
if not_null:
where += ''.join(f" AND {element} IS NOT NULL" for element in not_null)
sql = f"""
SELECT *
FROM current_weather
WHERE {where}
ORDER BY array_position(%(source_ids)s, source_id)
LIMIT 1
"""
rows = _make_dicts(fetch(sql, params))
if not rows:
return {}
return rows[0]
def _weather(date, last_date, source_id, not_null=None):
params = {
'date': date,
'last_date': last_date,
'source_id': source_id,
}
where = "timestamp BETWEEN %(date)s AND %(last_date)s"
order_by = "timestamp"
if isinstance(source_id, list):
where += " AND source_id IN %(source_id_tuple)s"
order_by += ", array_position(%(source_id)s, source_id)"
params['source_id_tuple'] = tuple(source_id)
else:
where += " AND source_id = %(source_id)s"
if not_null:
where += ''.join(f" AND {element} IS NOT NULL" for element in not_null)
sql = f"""
SELECT DISTINCT ON (timestamp) *
FROM weather
WHERE {where}
ORDER BY {order_by}
"""
return _make_dicts(fetch(sql, params))
def sources(lat=None,
lon=None,
dwd_station_id=None,
wmo_station_id=None,
source_id=None,
observation_types=None,
max_dist=50000,
ignore_type=False,
date=None,
last_date=None):
select = "*"
order_by = "observation_type"
params = {
'lat': lat,
'lon': lon,
'max_dist': max_dist,
'dwd_station_id': dwd_station_id,
'wmo_station_id': wmo_station_id,
'source_id': source_id,
'observation_types': tuple(observation_types or ()),
'date': date,
'last_date': last_date,
}
if source_id is not None:
if isinstance(source_id, list):
where = "id IN %(source_id_tuple)s"
order_by = ("array_position(%(source_id)s, id), observation_type")
params['source_id_tuple'] = tuple(source_id)
else:
where = "id = %(source_id)s"
elif dwd_station_id is not None:
if isinstance(dwd_station_id, list):
where = "dwd_station_id IN %(dwd_station_id_tuple)s"
order_by = (
"array_position(%(dwd_station_id)s, dwd_station_id::text), "
"observation_type")
params['dwd_station_id_tuple'] = tuple(dwd_station_id)
else:
where = "dwd_station_id = %(dwd_station_id)s"
elif wmo_station_id is not None:
if isinstance(wmo_station_id, list):
where = "wmo_station_id IN %(wmo_station_id_tuple)s"
order_by = (
"array_position(%(wmo_station_id)s, wmo_station_id::text), "
"observation_type")
params['wmo_station_id_tuple'] = tuple(wmo_station_id)
else:
where = "wmo_station_id = %(wmo_station_id)s"
elif (lat is not None and lon is not None):
distance = """
earth_distance(
ll_to_earth(%(lat)s, %(lon)s),
ll_to_earth(lat, lon)
)
"""
select += f", round({distance}) AS distance"
where = f"""
earth_box(
ll_to_earth(%(lat)s, %(lon)s),
%(max_dist)s
) @> ll_to_earth(lat, lon) AND
{distance} < %(max_dist)s
"""
if ignore_type:
order_by = "distance"
else:
order_by += ", distance"
else:
raise ValueError(
"Please supply lat/lon or dwd_station_id or wmo_station_id or "
"source_id")
if observation_types:
where += " AND observation_type IN %(observation_types)s"
if date is not None:
where += " AND last_record >= %(date)s"
if last_date is not None:
where += " AND first_record <= %(last_date)s"
sql = f"""
SELECT {select}
FROM sources
WHERE {where}
ORDER BY {order_by}
"""
rows = fetch(sql, params)
if not rows:
raise LookupError("No sources match your criteria")
return {'sources': _make_dicts(rows)}
