def geom_as_text(geometry):
"""
Define function call for converting PostGIS geometry object to well-known text representation.
:param geometry: PostGIS geometry object (string)
:return: SQL function (sqlalchemy.sql.functions.Function)
"""
return func.ST_AsText(geometry)
def test_ST_DumpPoints(self):
lake_id = self._create_one_lake()
lake = session.query(Lake).get(lake_id)
dump = lake.geom.ST_DumpPoints()
q = session.query(dump.path.label('path'),
dump.geom.label('geom')).all()
assert len(q) == 2
p1 = q[0]
assert isinstance(p1.path, list)
assert p1.path == [1]
assert isinstance(p1.geom, WKBElement)
p1 = session.execute(func.ST_AsText(p1.geom)).scalar()
assert p1 == 'POINT(0 0)'
p2 = q[1]
assert isinstance(p2.path, list)
assert p2.path == [2]
assert isinstance(p2.geom, WKBElement)
p2 = session.execute(func.ST_AsText(p2.geom)).scalar()
assert p2 == 'POINT(1 1)'
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)