def parse_temperatures(database: SqliteUtil, tmin_files: List[str],
tmax_files: List[str], steps: int, day: int,
src_epsg: int, prj_epsg: int):
log.info('Allocating tables for air temperatures.')
create_tables(database)
files = zip(tmax_files, tmin_files)
profile_count = 0
point_count = 0
temperatures = []
points = []
profiles = {}
n = 1
transformer = Transformer.from_crs(f'epsg:{src_epsg}',
f'epsg:{prj_epsg}',
always_xy=True,
skip_equivalent=True)
project = transformer.transform
def apply(id: int, temp: Callable):
for step in range(steps):
prop = step / steps
row = (id, step, int(86400 * prop), temp(24 * prop))
yield row
log.info('Loading temperatures from netCDF4 files.')
for tmax_file, tmin_file in files:
tmaxnc = Dataset(tmax_file, 'r')
tminnc = Dataset(tmin_file, 'r')
lons = tmaxnc.variables['lon']
lats = tmaxnc.variables['lat']
shape = tmaxnc.variables['tmax'].shape
tmaxs = tmaxnc.variables['tmax'][day]
tmins = tminnc.variables['tmin'][day]
for i in range(shape[1]):
for j in range(shape[2]):
tmax = tmaxs[i][j]
tmin = tmins[i][j]
if tmax != -9999.0:
x, y = project(lons[i][j], lats[i][j])
idx = f'{tmax}-{tmin}'
if idx not in profiles:
temp = iterpolation(tmin, tmax, 5, 15)
temperatures.extend(apply(profile_count, temp))
profiles[idx] = profile_count
profile_count += 1
profile = profiles[idx]
point = Point(point_count, x, y, profile)
points.append(point)
point_count += 1
if point_count == n:
log.info(
f'Loading air temperature reading {point_count}.')
n <<= 1
tmaxnc.close()
tminnc.close()
if point_count != n >> 1:
log.info(f'Loading air temperature reading {point_count}.')
def load():
for point in points:
x, y = point.x, point.y
yield (point.id, (x, y, x, y), point.profile)
log.info('Starting network update for air temperatures.')
log.info('Building spatial index from temperature profile locations.')
index = Index(load())
used = set()
log.info('Loading network links.')
links = load_links(database)
log.info('Applying temperature profiles to links.')
iter_links = counter(links, 'Applying profile to link %s.')
for link in iter_links:
result = index.nearest((link.x, link.y, link.x, link.y), objects=True)
profile = next(result).object
link.air_temperature = profile
used.add(profile)
def dump_links():
for link in links:
yield (link.id, link.air_temperature)
log.info('Writing updated links to database.')
database.insert_values('temp_links', dump_links(), 2)
database.connection.commit()
del links
log.info('Loading network parcels.')
parcels = load_parcels(database)
residential = profile_count
temperatures.extend(apply(profile_count, lambda x: 26.6667))
profile_count += 1
commercial = profile_count
temperatures.extend(apply(profile_count, lambda x: 26.6667))
profile_count += 1
other = profile_count
temperatures.extend(apply(profile_count, lambda x: 26.6667))
profile_count += 1
used.add(residential)
used.add(commercial)
used.add(other)
log.info('Applying temperature profiles to parcels.')
iter_parcels = counter(parcels, 'Applying profile to parcel %s.')
for parcel in iter_parcels:
if not parcel.cooling:
x, y = xy(parcel.center)
result = index.nearest((x, y, x, y), objects=True)
profile = next(result).object
parcel.air_temperature = profile
used.add(profile)
elif parcel.kind == 'residential':
parcel.air_temperature = residential
elif parcel.kind == 'commercial':
parcel.air_temperature = commercial
else:
parcel.air_temperature = other
def dump_parcels():
for parcel in parcels:
yield (parcel.apn, parcel.air_temperature)
log.info('Writing updated parcels to database.')
database.insert_values('temp_parcels', dump_parcels(), 2)
database.connection.commit()
del parcels
def dump_temperatures():
for temp in temperatures:
if temp[0] in used:
yield temp
log.info('Writing parsed air temperatures to database.')
database.insert_values('air_temperatures', dump_temperatures(), 4)
database.connection.commit()
del temperatures
log.info('Merging, dropping and renaming old tables.')
query = '''
CREATE INDEX temp_links_link
ON temp_links(link_id);
'''
database.cursor.execute(query)
query = '''
CREATE TABLE temp_links_merged
AS SELECT
links.link_id,
links.source_node,
links.terminal_node,
links.length,
links.freespeed,
links.capacity,
links.permlanes,
links.oneway,
links.modes,
temp_links.air_temperature,
links.mrt_temperature
FROM links
INNER JOIN temp_links
USING(link_id);
'''
database.cursor.execute(query)
query = '''
CREATE INDEX temp_parcels_parcel
ON temp_parcels(apn);
'''
database.cursor.execute(query)
query = '''
CREATE TABLE temp_parcels_merged
AS SELECT
parcels.apn,
parcels.maz,
parcels.type,
parcels.cooling,
temp_parcels.air_temperature,
parcels.mrt_temperature,
parcels.center,
parcels.region
FROM parcels
INNER JOIN temp_parcels
USING(apn);
'''
database.cursor.execute(query)
original = database.count_rows('links')
merged = database.count_rows('temp_links_merged')
if original != merged:
log.error('Original links and updated links tables '
'do not align; quiting to prevent data loss.')
raise RuntimeError
else:
database.drop_table('links', 'temp_links')
query = '''
ALTER TABLE temp_links_merged
RENAME TO links;
'''
database.cursor.execute(query)
original = database.count_rows('parcels')
merged = database.count_rows('temp_parcels_merged')
if original != merged:
log.error('Original parcels and updated parcels tables '
'do not align; quiting to prevent data loss.')
raise RuntimeError
else:
database.drop_table('parcels', 'temp_parcels')
query = '''
ALTER TABLE temp_parcels_merged
RENAME TO parcels;
'''
database.cursor.execute(query)
database.connection.commit()
log.info('Creating indexes on new tables.')
create_indexes(database)
log.info('Writing process metadata.')
def parse_mrt(database: SqliteUtil, path: str, src_epsg: int, prj_epsg: int,
bounds:int = 30, steps: int = 96):
log.info('Allocating tables for MRT temperature profiles.')
create_tables(database)
log.info('Loading network nodes from database.')
nodes: Dict[str,Node]
nodes = load_nodes(database)
log.info('Loading network links from database.')
links: Dict[str,Link]
links= load_links(database, nodes)
log.info(f'Searching for mrt files in {path}')
csvfiles = iter(glob(f'{path}/**/*.csv', recursive=True))
log.info('Handling initial dataset for profile construction.')
points: List[Point]
time: int
points, time = parse_points(next(csvfiles), src_epsg, prj_epsg)
log.info('Building spatial index on MRT points.')
index = Index((point.entry() for point in points))
log.info('Scanning link bounds and building profiles.')
mapping: Dict[FrozenSet[int],int] = {}
count = 0
empty = 0
iter_links = counter(links.values(), 'Scanning link %s.')
for link in iter_links:
d = link.terminal_node.x * link.source_node.y - \
link.source_node.x * link.terminal_node.y
dx = link.terminal_node.x - link.source_node.x
dy = link.terminal_node.y - link.source_node.y
l = sqrt(dy * dy + dx * dx)
nearby = index.intersection(link.bounds(bounds))
contained = []
for uuid in nearby:
point = points[uuid]
x = point.x
y = point.y
if l > 0:
dist = abs(dy * x - dx * y + d ) / l
else:
px = point.x - link.source_node.x
py = point.y - link.source_node.y
dist = sqrt(px * px + py * py)
if dist <= bounds:
contained.append(point.id)
if contained:
profile = frozenset(contained)
if profile in mapping:
link.profile = mapping[profile]
else:
mapping[profile] = count
link.profile = count
count += 1
else:
empty += 1
profiles: List[Tuple[int]]
profiles = [tuple(key) for key in mapping.keys()]
if empty:
log.warning(f'Found {empty} links without any MRT temperature profile.')
def dump_points():
idx = time // (86400 // steps)
for uuid, profile in enumerate(profiles):
mrt, pet, utci = 0, 0, 0
count = len(profile)
for ptid in profile:
point = points[ptid]
mrt += point.mrt
pet += point.pet
utci += point.utci
yield (uuid, idx, time, mrt / count, pet / count, utci / count)
def dump_links():
for link in links.values():
yield (link.id, link.profile)
log.info('Writing link updates and temperatures to dataabse.')
database.insert_values('mrt_temperatures', dump_points(), 6)
database.insert_values('temp_links', dump_links(), 2)
log.info('Merging, dropping and renaming old tables.')
query = '''
CREATE INDEX temp_links_link
ON temp_links(link_id);
'''
database.cursor.execute(query)
query = '''
CREATE TABLE temp_links_merged
AS SELECT
links.link_id,
links.source_node,
links.terminal_node,
links.length,
links.freespeed,
links.capacity,
links.permlanes,
links.oneway,
links.modes,
links.air_temperature,
temp_links.mrt_temperature
FROM links
INNER JOIN temp_links
USING(link_id);
'''
database.cursor.execute(query)
original = database.count_rows('links')
merged = database.count_rows('temp_links_merged')
if original != merged:
log.error('Original links and updated links tables '
'do not align; quiting to prevent data loss.')
raise RuntimeError
database.drop_table('links', 'temp_links')
query = '''
ALTER TABLE temp_links_merged
RENAME TO links;
'''
database.cursor.execute(query)
database.connection.commit()
del links
del nodes
del index
del mapping
del points
log.info('Handling remain temperatures with defined profile.')
def dump_temperaures(time: int, temperatures: List[Tuple[float,float,float]]):
idx = time // (86400 // steps)
for uuid, profile in enumerate(profiles):
mrt, pet, utci = 0, 0, 0
count = len(profile)
for tempid in profile:
temp = temperatures[tempid]
mrt += temp[0]
pet += temp[1]
utci += temp[2]
yield (uuid, idx, time, mrt / count, pet / count, utci / count)
for csvfile in csvfiles:
time: int
temperatures: List[Tuple[float,float,float]]
temperatures, time = parse_temperatures(csvfile)
log.info('Writing temperature data to database.')
database.insert_values('mrt_temperatures',
dump_temperaures(time, temperatures), 6)
database.connection.commit()
log.info('Creating indexes on new/updated tables.')
create_indexes(database)