def test_ST_AsGeoJson_feature(self):
self._create_one_lake()
# Test feature
s2 = select([func.ST_AsGeoJSON(Lake, 'geom')])
r2 = session.execute(s2).scalar()
assert loads(r2) == {
"type": "Feature",
"geometry": {
"type": "LineString",
"coordinates": [[0, 0], [1, 1]]
},
"properties": {
"id": 1
}
}
# Test feature with subquery
ss3 = select([Lake,
bindparam('dummy_val', 10).label('dummy_attr')]).alias()
s3 = select([func.ST_AsGeoJSON(ss3, 'geom')])
r3 = session.execute(s3).scalar()
assert loads(r3) == {
"type": "Feature",
"geometry": {
"type": "LineString",
"coordinates": [[0, 0], [1, 1]]
},
"properties": {
"dummy_attr": 10,
"id": 1
}
}
def test_ST_AsGeoJson(self):
lake_id = self._create_one_lake()
lake = session.query(Lake).get(lake_id)
# Test geometry
s1 = select([func.ST_AsGeoJSON(Lake.__table__.c.geom)])
r1 = session.execute(s1).scalar()
assert loads(r1) == {
"type": "LineString",
"coordinates": [[0, 0], [1, 1]]
}
# Test geometry ORM
s1_orm = lake.geom.ST_AsGeoJSON()
r1_orm = session.execute(s1_orm).scalar()
assert loads(r1_orm) == {
"type": "LineString",
"coordinates": [[0, 0], [1, 1]]
}
# Test with function inside
s1_func = select(
[func.ST_AsGeoJSON(func.ST_MakeValid(Lake.__table__.c.geom))])
r1_func = session.execute(s1_func).scalar()
assert loads(r1_func) == {
"type": "LineString",
"coordinates": [[0, 0], [1, 1]]
}
def test_two(self):
stmt = select([func.ST_AsGeoJSON(Lake, "geom")])
self._assert_stmt(
stmt,
'SELECT ST_AsGeoJSON(lake, %(ST_AsGeoJSON_2)s) AS '
'"ST_AsGeoJSON_1" FROM gis.lake',
)
def test_one(self):
stmt = select([func.ST_AsGeoJSON(Lake.__table__.c.geom)])
self._assert_stmt(
stmt,
'SELECT ST_AsGeoJSON(gis.lake.geom) AS "ST_AsGeoJSON_1" FROM gis.lake'
)
class LocationAnswer(_AnswerMixin, Answer):
"""A GEOMETRY('POINT', 4326) answer.
Accepts input in the form
{
'lng': <longitude>,
'lat': <latitude>
}
The output is a GeoJSON.
"""
__tablename__ = 'answer_location'
main_answer = sa.Column(Geometry('POINT', 4326))
geo_json = column_property(func.ST_AsGeoJSON(main_answer))
@hybrid_property
def answer(self):
"""LocationAnswer.geo_json."""
return self.geo_json
@answer.setter
def answer(self, location: dict):
"""Set LocationAnswer.main_answer using a dict of lng and lat."""
self.main_answer = 'SRID=4326;POINT({lng} {lat})'.format(**location)
__mapper_args__ = {'polymorphic_identity': 'location'}
__table_args__ = _answer_mixin_table_args()
def test_four(self):
stmt = select([func.ST_AsGeoJSON(TestSTAsGeoJson.TblWSpacesAndDots, "geom")])
self._assert_stmt(
stmt,
'SELECT ST_AsGeoJSON("this is.an AWFUL.name", :ST_AsGeoJSON_2) '
'AS "ST_AsGeoJSON_1" FROM "another AWFUL.name for.schema".'
'"this is.an AWFUL.name"',
)
def test_nested_funcs(self):
stmt = select([func.ST_AsGeoJSON(func.ST_MakeValid(func.ST_MakePoint(1, 2)))])
self._assert_stmt(
stmt,
'SELECT '
'ST_AsGeoJSON(ST_MakeValid('
'ST_MakePoint(:ST_MakePoint_1, :ST_MakePoint_2)'
')) AS "ST_AsGeoJSON_1"',
)
def test_three(self):
sq = select([Lake, bindparam("dummy_val", 10).label("dummy_attr")]).alias()
stmt = select([func.ST_AsGeoJSON(sq, "geom")])
self._assert_stmt(
stmt,
'SELECT ST_AsGeoJSON(anon_1, %(ST_AsGeoJSON_2)s) AS "ST_AsGeoJSON_1" '
"FROM (SELECT gis.lake.id AS id, gis.lake.geom AS geom, %(dummy_val)s AS "
"dummy_attr FROM gis.lake) AS anon_1",
)
def test_unknown_func(self):
stmt = select([
func.ST_AsGeoJSON(func.ST_UnknownFunction(func.ST_MakePoint(1, 2)))
])
self._assert_stmt(
stmt,
'SELECT '
'ST_AsGeoJSON(ST_UnknownFunction('
'ST_MakePoint(:ST_MakePoint_1, :ST_MakePoint_2)'
')) AS "ST_AsGeoJSON_1"',
)
def _build_geojson_select(statement):
"""
See usages below.
"""
# this is basically a translation of the postgis ST_AsGeoJSON example into sqlalchemy/geoalchemy2
return func.json_build_object(
"type",
"FeatureCollection",
"features",
func.json_agg(func.ST_AsGeoJSON(statement.subquery(), maxdecimaldigits=5).cast(JSON)),
)
class FacilityAnswer(_AnswerMixin, Answer):
"""A facility answer (a la Revisit).
FacilityAnswer.answer is a dictionary with 4 keys:
facility_location, facility_id, facility_name, facility_sector
facility_location accepts input in the form
{
'lng': <longitude>,
'lat': <latitude>
}
and outputs a GeoJSON.
"""
__tablename__ = 'answer_facility'
main_answer = sa.Column(Geometry('POINT', 4326))
geo_json = column_property(func.ST_AsGeoJSON(main_answer))
facility_id = sa.Column(pg.TEXT)
facility_name = sa.Column(pg.TEXT)
facility_sector = sa.Column(pg.TEXT)
@hybrid_property
def answer(self) -> OrderedDict:
"""A dictionary of location, id, name, and sector."""
return OrderedDict((
('facility_location', self.geo_json),
('facility_id', self.facility_id),
('facility_name', self.facility_name),
('facility_sector', self.facility_sector),
))
@answer.setter
def answer(self, facility_info: dict):
"""Set FacilityAnswer.answer with a dict."""
self.main_answer = ('SRID=4326;POINT({lng} {lat})'.format(
**facility_info))
self.facility_id = facility_info['facility_id']
self.facility_name = facility_info['facility_name']
self.facility_sector = facility_info['facility_sector']
__mapper_args__ = {'polymorphic_identity': 'facility'}
__table_args__ = _answer_mixin_table_args() + (sa.CheckConstraint("""
(CASE WHEN (main_answer IS NULL) AND
(facility_id IS NULL) AND
(facility_name IS NULL) AND
(facility_sector IS NULL)
THEN 1 ELSE 0 END) +
(CASE WHEN (main_answer IS NOT NULL) AND
(facility_id IS NOT NULL) AND
(facility_name IS NOT NULL) AND
(facility_sector IS NOT NULL)
THEN 1 ELSE 0 END) =
1
"""), )
def test_ST_GeoJSON(self):
lake_id = self._create_one_lake()
def _test(r):
r = json.loads(r)
assert r["type"] == "LineString"
assert r["coordinates"] == [[0, 0], [1, 1]]
s = select([func.ST_AsGeoJSON(Lake.__table__.c.geom)])
r = session.execute(s).scalar()
_test(r)
lake = session.query(Lake).get(lake_id)
r = session.execute(lake.geom.ST_AsGeoJSON()).scalar()
_test(r)
r = session.query(Lake.geom.ST_AsGeoJSON()).scalar()
_test(r)
def find_by_record_id(
record_id: str,
session: session_scope = None) -> Optional[DatasetDB]:
attributes = [
DatasetDB.id, DatasetDB.provenance_id, DatasetDB.name,
DatasetDB.description, DatasetDB.json_metadata,
DatasetDB.created_at,
func.ST_AsGeoJSON(
DatasetDB.spatial_coverage).label('spatial_coverage_geojson')
]
if session is None:
with session_scope() as sess:
return sess.query(*attributes).filter(
DatasetDB.id == record_id).first()
else:
return session.query(*attributes).filter(
DatasetDB.id == record_id).first()
def common_path(request, db, where):
dd = db.metadata.tables["device_data"]
devices = db.metadata.tables["devices"]
legs = db.metadata.tables["leg_modes"]
users = db.metadata.tables["users"]
# get data for specified date, or last 12h if unspecified
date = request.args.get("date")
# passed on to simplify_geometry
maxpts = int(request.args.get("maxpts") or 0)
mindist = int(request.args.get("mindist") or 0)
# Exclude given comma-separated modes in processed path of path, stops by
# default. Blank argument removes excludes
exarg = request.args.get("exclude")
exclude = True if exarg == "" else not_(
legs.c.mode.in_((exarg or "STILL").split(",")))
if date:
start = datetime.strptime(date, '%Y-%m-%d').replace(hour=0,
minute=0,
second=0,
microsecond=0)
else:
start = datetime.now() - timedelta(hours=12)
end = start + timedelta(hours=24)
# in the export link case, we get a date range
firstday = request.args.get("firstday")
firstday = firstday and datetime.strptime(firstday, '%Y-%m-%d')
firstday = firstday or datetime.now()
lastday = request.args.get("lastday")
lastday = lastday and datetime.strptime(lastday, '%Y-%m-%d')
lastday = lastday or firstday
date_start = firstday.replace(hour=0, minute=0, second=0, microsecond=0)
date_end = lastday.replace(hour=0, minute=0, second=0, microsecond=0) \
+ timedelta(hours=24)
if request.args.get("firstday") or request.args.get("lastday"):
start, end = date_start, date_end
# find end of user legs
legsend = select([func.max(legs.c.time_end).label("time_end")], where,
devices.join(users).join(legs)).alias("legsend")
# use user legs if available
legsed = select(
[ func.ST_AsGeoJSON(dd.c.coordinate).label("geojson"),
cast(legs.c.mode, String).label("activity"),
legs.c.line_name,
legs.c.time_start.label("legstart"),
cast(legs.c.time_start, String).label("time_start"),
cast(legs.c.time_end, String).label("time_end"),
legs.c.id,
dd.c.time],
and_(
where,
legs.c.activity != None,
exclude,
dd.c.time >= start,
dd.c.time < end),
devices \
.join(users) \
.join(legs) \
.join(dd, and_(
legs.c.device_id == dd.c.device_id,
between(dd.c.time, legs.c.time_start, legs.c.time_end))))
# fall back on raw trace beyond end of user legs
unlegsed = select([
func.ST_AsGeoJSON(dd.c.coordinate).label("geojson"),
cast(dd.c.activity_1, String).label("activity"),
literal(None).label("line_name"),
literal(None).label("legstart"),
literal(None).label("time_start"),
literal(None).label("time_end"),
literal(None).label("id"), dd.c.time
],
and_(
where, dd.c.time >= start, dd.c.time < end,
or_(legsend.c.time_end.is_(None),
dd.c.time > legsend.c.time_end)),
dd.join(devices).join(legsend, literal(True)))
# Sort also by leg start time so join point repeats adjacent to correct leg
query = legsed.union_all(unlegsed).order_by(text("time, legstart"))
query = query.limit(35000) # sanity limit vs date range
points = db.engine.execute(query)
# re-split into legs, and the raw part
segments = (legpts
for (legid, legpts) in dict_groups(points, ["legstart"]))
features = []
for points in segments:
# discard the less credible location points
points = trace_discard_sidesteps(points, BAD_LOCATION_RADIUS)
# simplify the path geometry by dropping redundant points
points = simplify_geometry(points,
maxpts=maxpts,
mindist=mindist,
keep_activity=True)
features += trace_linestrings(
points, ('id', 'activity', 'line_name', 'time_start', 'time_end'))
return jsonify({'type': 'FeatureCollection', 'features': features})
def destinations(session_token):
dd = db.metadata.tables["device_data"]
devices = db.metadata.tables["devices"]
users = db.metadata.tables["users"]
legs = db.metadata.tables["legs"]
# Limit number of destinations on output, all if blank given
limit = request.args.get("limit", DESTINATIONS_LIMIT)
limit = None if limit == "" else int(limit)
# Exclude nearby and faraway destinations from point if given, or last
# device location is not given. All included if blank given in either.
lat = request.args.get("lat")
lng = request.args.get("lng")
exclude = True
if "" not in (lat, lng):
if None not in (lat, lng):
excoord = "POINT(%s %s)" % (lng, lat)
else:
excoord = db.engine.execute(select(
[func.ST_AsText(dd.c.coordinate)],
devices.c.token == session_token,
order_by=dd.c.time.desc(),
limit=1)).scalar()
if excoord is not None:
rmin, rmax = INCLUDE_DESTINATIONS_BETWEEN
exclude = and_(
not_(func.st_dwithin(legs.c.coordinate_start, excoord, rmin)),
func.st_dwithin(legs.c.coordinate_start, excoord, rmax))
start = datetime.datetime.now() - datetime.timedelta(days=30)
query = select(
[ func.ST_AsGeoJSON(legs.c.coordinate_start).label("geojson"),
legs.c.time_start,
legs.c.time_end],
and_(
devices.c.token == session_token,
legs.c.time_end >= start,
legs.c.activity == "STILL",
exclude),
devices.join(users).join(legs))
stops = list(dict(x) for x in db.engine.execute(query))
for x in stops:
x["coordinates"] = json.loads(x["geojson"])["coordinates"]
dests = sorted(
stop_clusters(stops, DEST_RADIUS_MAX * 2),
key=lambda x: x["visits_rank"])
geojson = {
"type": "FeatureCollection",
"features": [
{ "type": "Feature",
"geometry": {
"type": "Point",
"coordinates": d["coordinates"]},
"properties": d}
for d in dests[:limit]]}
for f in geojson["features"]:
del f["properties"]["coordinates"] # included in geometry
f["properties"]["visits"] = len(f["properties"]["visits"])
devices_table_id = get_device_table_id_for_session(session_token)
client_log_table_insert(devices_table_id, get_user_id_from_device_id(devices_table_id), "MOBILE-DESTINATIONS", "")
return jsonify(geojson)
