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)