def way_points(bbox=None):
"""
Generate evenly-spaced points along all ways in the ROI
NOTE: output is in the projected coord sys defined by cfg.PRJ_SRID
Arguments
bbox: 5-element list/tuple, containing bounding box [x_min, x_max,
y_min, y_max, srid], the srid is an integer spatial reference ID
(EPSG code) for the float limits. Set None to return all ways in
the database
Returns:
ways: dict, keys are way IDs, values are N x 2 numpy arrays containing
the x, y position of sequential points along the way
way_pts: dict, keys are way IDs, values are N x 2 numpy arrays
containing the x, y position of evenly-spaced sequential points
interpolated along the way. The first row is always the start
point, and the last is always the endpoint. Spacing for the last
point for each way is generally less than the desired spacing,
beware! this will cause an overshoot of up to spacing for the last
point
"""
logger.info(
f'Computing way points, bbox={bbox}, spacing={cfg.OSM_WAYPT_SPACING}')
with common.connect_db(cfg.OSM_DB) as conn, conn.cursor() as cur:
# query returning geometry of all ways
where = ''
if bbox:
where = f'WHERE ST_Intersects(ways.the_geom, ST_MakeEnvelope(' \
f'{bbox[0]}, {bbox[1]}, {bbox[2]}, {bbox[3]}, {bbox[4]}))'
geom = f'ST_AsBinary(ST_Transform(the_geom, {cfg.PRJ_SRID}))'
cur.execute(f'SELECT gid, {geom} FROM ways {where};')
# read results and resample each way geometry
# note: column osm_id is non-unique, do not use this as a key below
recs = cur.fetchall()
way_pts = {}
ways = {}
for rec in recs:
# unpack record
way_id = rec[0]
line = shapely.wkb.loads(rec[1].tobytes())
ways[way_id] = np.vstack(line.xy).T
# resample line
dists = range(0, round(line.length) + 1, cfg.OSM_WAYPT_SPACING)
line_pts = [line.interpolate(d).xy for d in dists]
way_pts[way_id] = np.hstack(line_pts).T
logger.info(f'Completed way points for {len(ways)} ways')
return way_pts
def nearest_id(lon, lat):
"""
Return record ID for nearest vertex in the OSM database
Arguments:
lon, lat: floats, longitude and latitude (WGS84) of the query point
Returns: int, record ID for nearest point
"""
with common.connect_db(cfg.OSM_DB) as conn, conn.cursor() as cur:
cur.execute(
"SELECT id FROM ways_vertices_pgr ORDER BY the_geom <-> ST_SetSRID(ST_Point(%s, %s), 4326) LIMIT 1;",
(lon, lat))
return cur.fetchone()[0]
def create_db(clobber=False):
"""
Create a new database and initialize for osm dataset
Arguments:
clobber: set True to delete and re-initialize an existing database
Return: Nothing
"""
# TODO: add index, if necessary
common.new_db(cfg.OSM_DB, clobber)
with common.connect_db(cfg.OSM_DB) as conn, conn.cursor() as cur:
# add extensions
cur.execute('CREATE EXTENSION postgis;')
cur.execute('CREATE EXTENSION pgrouting;')
def update_cost_db(wpts):
"""
Update insolation cost columns for all way elements in OSM database
Arguments:
wpts: dict, output from way_points(), contains way gid as keys, and
evenly spaced points along way as values
Returns: Nothing, sets values in cost_insolation_HHMM columns of OSM DB
"""
with common.connect_db(cfg.OSM_DB) as conn:
# loop over all calculated times
for meta in common.shade_meta():
with conn.cursor() as cur:
# compute the cost at this time
logger.info(
f'Updating insolation cost for {meta["hour"]:02d}:{meta["minute"]:02d}'
)
sun_cost, shade_cost = way_insolation(meta["hour"],
meta["minute"], wpts)
# prepare columns
cur.execute(
f'ALTER TABLE ways ADD COLUMN IF NOT EXISTS {meta["sun_cost"]} float8;'
)
cur.execute(
f'ALTER TABLE ways ADD COLUMN IF NOT EXISTS {meta["shade_cost"]} float8;'
)
# run batch of sql updates
sql = f'UPDATE ways SET {meta["sun_cost"]} = %(cost)s WHERE gid = %(gid)s'
params = [{
'gid': x[0],
'cost': x[1]
} for x in sun_cost.items()]
psycopg2.extras.execute_batch(cur, sql, params, page_size=1000)
sql = f'UPDATE ways SET {meta["shade_cost"]} = %(cost)s WHERE gid = %(gid)s'
params = [{
'gid': x[0],
'cost': x[1]
} for x in shade_cost.items()]
psycopg2.extras.execute_batch(cur, sql, params, page_size=1000)
def create_db(clobber=False):
"""
Create a new database and initialize for lidar point data
Arguments:
clobber: set True to delete and re-initialize an existing database
Return: Nothing
"""
common.new_db(cfg.LIDAR_DB, clobber)
with common.connect_db(cfg.LIDAR_DB) as conn, conn.cursor() as cur:
cur.execute('CREATE EXTENSION postgis;')
cur.execute('CREATE EXTENSION pointcloud;')
cur.execute('CREATE EXTENSION pointcloud_postgis;')
cur.execute(
f'CREATE TABLE {cfg.LIDAR_TABLE} (id SERIAL PRIMARY KEY, pa PCPATCH(1));'
)
cur.execute(
f'CREATE INDEX ON {cfg.LIDAR_TABLE} USING GIST(PC_EnvelopeGeometry(pa));'
)
logger.info(
f'Created new database: {cfg.LIDAR_DB} @ {cfg.PSQL_HOST}:{cfg.PSQL_PORT}'
)
"""
Figure out a way to fix the surface gridding routines - they are terribly slow and broken now
"""
from parasol import common, cfg, lidar
import shapely
import uuid
import json
import numpy as np
xmin = 327480.17
xmax = 328500.17
ymin = 4689458.81
ymax = 4690478.81
with common.connect_db(cfg.LIDAR_DB) as conn, conn.cursor() as cur:
cur.execute(
f'SELECT PC_AsText(PC_Union(pa)) FROM lidar WHERE PC_Intersects(pa, ST_MakeEnvelope({xmin}, {ymin}, {xmax}, {ymax}, {cfg.PRJ_SRID}))'
)
data = np.array(
json.loads(cur.fetchone()[0])['pts']
) # ordered as ReturnNumber,NumberOfReturns,Classification,X,Y,Z
data = np.roll(
data,
3) # ordered as X,Y,Z,ReturnNumber,NumberOfReturns,Classification
data2 = lidar.retrieve(xmin, xmax, ymin, ymax)
def _route(lon0, lat0, lon1, lat1, time=None, beta=None):
"""
Shared utility to retrieve route from pgrouting server
Arguments:
lat0, lon0 = floats, start point latitude, longitude
lat1, lon1 = floats, end point latitude, longitude
time:
beta:
Returns: meters, sun, geojson
meters: total length of route in meters
sun: total solar cost (normalized units)
geojson: optimal route as geoJSON
"""
# parse time
if time is None:
time = datetime.now()
if not isinstance(time, datetime):
raise TypeError('Argument "time" must be a datetime object')
# get cost columns corresponding to current date/time
delta = timedelta(hours=9999) # arbitrarily large
# TODO: use common.shade_meta()
for fhours in np.arange(cfg.SHADE_START_HOUR, cfg.SHADE_STOP_HOUR,
cfg.SHADE_INTERVAL_HOUR):
this_hour = math.floor(fhours)
this_minute = math.floor((fhours - this_hour) * 60)
this_time = datetime.now().replace(hour=this_hour,
minute=this_minute,
second=0,
microsecond=0)
this_delta = abs(time - this_time)
if this_delta <= delta:
sun_cost = f'{cfg.OSM_SUN_COST_PREFIX}{this_time.hour:02d}{this_time.minute:02d}'
shade_cost = f'{cfg.OSM_SHADE_COST_PREFIX}{this_time.hour:02d}{this_time.minute:02d}'
delta = this_delta
# sql expression for cost (shortest or optimal)
if beta is None:
# shortest
cost_expr = 'length_m'
elif beta >= 0 and beta <= 1:
# optimal
cost_expr = f'{1 - beta} * {sun_cost} + {beta} * {shade_cost}'
else:
# invalid
raise ValueError('"beta" must be either in the range [0, 1] or None')
# find start/end vertices
start_id = nearest_id(lon0, lat0)
end_id = nearest_id(lon1, lat1)
# compute djikstra path, return total length, total sun cost, and route
with common.connect_db(cfg.OSM_DB) as conn, conn.cursor() as cur:
inner_sql = f'SELECT gid AS id, source, target, {cost_expr} AS cost, the_geom FROM ways'
cur.execute(
f"SELECT SUM(length_m), SUM({sun_cost}), ST_AsGeoJSON(ST_Union(the_geom)) "
f"FROM pgr_dijkstra('{inner_sql}', {start_id}, {end_id}, directed := false) "
f"LEFT JOIN ways ON (edge = gid);")
return cur.fetchone()