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)