def _serialize(self, value, attr, obj): if value is None: return value else: if attr == 'geo': return { 'lng': db.session.scalar(func.ST_X(value)), 'lat': db.session.scalar(func.ST_Y(value)), 'longitude': db.session.scalar(func.ST_X(value)), 'latitude': db.session.scalar(func.ST_Y(value)) } else: return None
def test_create_extent(self): package = factories.Dataset() geojson = json.loads(self.geojson_examples["point"]) shape = asShape(geojson) package_extent = PackageExtent( package_id=package["id"], the_geom=WKTElement(shape.wkt, self.db_srid), ) package_extent.save() assert (package_extent.package_id == package["id"]) if legacy_geoalchemy: assert (Session.scalar( package_extent.the_geom.x) == geojson["coordinates"][0]) assert (Session.scalar( package_extent.the_geom.y) == geojson["coordinates"][1]) assert (Session.scalar( package_extent.the_geom.srid) == self.db_srid) else: from sqlalchemy import func assert (Session.query(func.ST_X(package_extent.the_geom)).first() [0] == geojson["coordinates"][0]) assert (Session.query(func.ST_Y(package_extent.the_geom)).first() [0] == geojson["coordinates"][1]) assert (package_extent.the_geom.srid == self.db_srid)
def get_oql(self): lat, lon = session.query(func.ST_Y(self.location), func.ST_X(self.location)).one() union = [] for tag in self.tags: osm_filter = 'around:1000,{:f},{:f}'.format(lat, lon) union += oql_from_tag(tag, False, osm_filter) return union
def search_route(): query = request.args.get('q') per_page = request.args.get('per_page', 10, type=int) page = request.args.get('page', 0, type=int) if query is None: return json.dumps({'results':[]}) result = {} with session_scope() as session: results = session.query( City.name, City.country, func.ST_Y(cast(City.location, Geometry())), func.ST_X(cast(City.location, Geometry())), City.id ) \ .filter(unaccent(City.name).ilike(unaccent('%' + query + '%'))) \ .limit(per_page + 1) \ .offset(page * per_page) \ .all() more = len(results) == per_page + 1 results = results[:per_page] result = json.dumps({ 'results': [{'id': c[4], 'text': '{}, {}'.format(c[0], c[1]), 'coords': (c[2], c[3])} for i,c in enumerate(results)], 'more': more}) return result
class City(object): az_columns = (AdminZone.id, AdminZone.name, AdminZone.code_department, func.ST_X(func.ST_Centroid(AdminZone.geometry)), func.ST_Y(func.ST_Centroid(AdminZone.geometry))) @staticmethod def format_city_res(result): return { 'id': result[0], 'name': result[1], 'code_department': result[2], 'lng': result[3], 'lat': result[4] } def __init__(self, request): self.request = request def get(self): id = self.request.matchdict['id'] return { 'results': self.format_city_res( DBSession.query(*self.az_columns).filter( AdminZone.id == id).first()) } def collection_get(self): ids = self.request.params['ids'].split(',') return { 'results': [ self.format_city_res(res) for res in DBSession.query( *self.az_columns).filter(AdminZone.id.in_(ids)).all() ] }
def test_spatial_extra_base(self, app): user = factories.User() env = {"REMOTE_USER": user["name"].encode("ascii")} dataset = factories.Dataset(user=user) offset = url_for("dataset.edit", id=dataset["id"]) res = app.get(offset, extra_environ=env) data = { "name": dataset['name'], "extras__0__key": u"spatial", "extras__0__value": self.geojson_examples["point"] } res = app.post(offset, environ_overrides=env, data=data) assert "Error" not in res, res package_extent = (Session.query(PackageExtent).filter( PackageExtent.package_id == dataset["id"]).first()) geojson = json.loads(self.geojson_examples["point"]) assert package_extent.package_id == dataset["id"] from sqlalchemy import func assert (Session.query(func.ST_X( package_extent.the_geom)).first()[0] == geojson["coordinates"][0]) assert (Session.query(func.ST_Y( package_extent.the_geom)).first()[0] == geojson["coordinates"][1]) assert package_extent.the_geom.srid == self.db_srid
def alchemy_expression(self): dt = self.product grid_spec = self.product.grid_spec doc = _jsonb_doc_expression(dt.metadata_type) projection_offset = _projection_doc_offset(dt.metadata_type) # Calculate tile refs geo_ref_points_offset = projection_offset + ['geo_ref_points'] center_point = func.ST_Centroid( func.ST_Collect( _gis_point(doc, geo_ref_points_offset + ['ll']), _gis_point(doc, geo_ref_points_offset + ['ur']), )) # todo: look at grid_spec crs. Use it for defaults, conversion. size_x, size_y = (grid_spec.tile_size or (1000.0, 1000.0)) origin_x, origin_y = grid_spec.origin return func.concat( func.floor( (func.ST_X(center_point) - origin_x) / size_x).cast(String), '_', func.floor( (func.ST_Y(center_point) - origin_y) / size_y).cast(String), )
def test_create_extent(self): package = factories.Dataset() geojson = json.loads(self.geojson_examples['point']) shape = asShape(geojson) package_extent = PackageExtent(package_id=package['id'], the_geom=WKTElement( shape.wkt, self.db_srid)) package_extent.save() assert_equals(package_extent.package_id, package['id']) if legacy_geoalchemy: assert_equals(Session.scalar(package_extent.the_geom.x), geojson['coordinates'][0]) assert_equals(Session.scalar(package_extent.the_geom.y), geojson['coordinates'][1]) assert_equals(Session.scalar(package_extent.the_geom.srid), self.db_srid) else: from sqlalchemy import func assert_equals( Session.query(func.ST_X(package_extent.the_geom)).first()[0], geojson['coordinates'][0]) assert_equals( Session.query(func.ST_Y(package_extent.the_geom)).first()[0], geojson['coordinates'][1]) assert_equals(package_extent.the_geom.srid, self.db_srid)
def on_get(self, req, resp): resp.content_type = falcon.MEDIA_JSON session = Session() resp.body = json.dumps([{ 'name': clinic.name, 'location': str(session.query(func.ST_X(clinic.location).label('x')).first().x) + ' ' + str(session.query(func.ST_Y(clinic.location).label('y')).first().y), 'opening': str(clinic.opening), 'closure': str(clinic.closure), 'doctors': [{ 'name': doctor.second_name + ' ' + doctor.first_name + '. ' + doctor.fathers_name + '.', 'specialities': [ spec.name for spec in session.query(Speciality).join(Speciality.doctors).filter(Doctor.id == doctor.id).all() ], 'cabinet': doctor.cabinet, 'phone': doctor.phone } for doctor in session.query(Doctor).join(Doctor.clinics).filter(Clinic.id == clinic.id).all() ] } for clinic in session.query(Clinic).all() ]) session.close() resp.status = falcon.HTTP_200
def query_results( self, instance_id: str, data_id: Optional[str] = None, join_metapoint: Optional[bool] = False, join_hexgrid: Optional[bool] = True, start_time: Optional[str] = None, upto_time: Optional[str] = None, ) -> Any: """Get the predictions from a model given an instance and data id. Args: instance_id: The id of the trained model instance. data_id: The id of the dataset the model predicted on. join_metapoint: If true, join the result table with the metapoint table on the point_id column. The returned query will also have 'lat', 'lon' and 'source'. join_hexgrid: TODO start_time: Get all results on and after this time. ISO formatted. upto_time: Get all results upto but not including this timestamp. ISO formatted. """ base_query = [AirQualityResultTable] # select the source (laqn, hexgrid, etc.), lat and lon if join_metapoint: base_query += [ MetaPoint.source, func.ST_X(MetaPoint.location).label("lon"), func.ST_Y(MetaPoint.location).label("lat"), ] # select the hexgrid columns if join_hexgrid: base_query += [HexGrid.geom] # open connection and start the query with self.dbcnxn.open_session() as session: readings = session.query(*base_query).filter( AirQualityResultTable.instance_id == instance_id) # join on metapoint if join_metapoint: readings = readings.join( MetaPoint, AirQualityResultTable.point_id == MetaPoint.id) # join on hexgrid if join_hexgrid: readings = readings.join( HexGrid, AirQualityResultTable.point_id == HexGrid.point_id) # filter by data id if data_id: readings = readings.filter( AirQualityResultTable.data_id == data_id) # filter by time if start_time: readings = readings.filter( AirQualityResultTable.measurement_start_utc >= start_time) if upto_time: readings = readings.filter( AirQualityResultTable.measurement_start_utc < upto_time) return readings
def get_labels(session, score, detection_filters, vehicle_filters, model, threshold): """Retrieves all possible detection-annotation pairings that satify the VOC criterion.""" overlap_score = overlap(Detection, Vehicle) # pylint: disable-msg=E1101 dist_x = (func.ST_X(func.ST_Transform(Detection.lla, 102718)) - func.ST_X(func.ST_Transform(Vehicle.lla, 102718))) \ * 0.3048 dist_y = (func.ST_Y(func.ST_Transform(Detection.lla, 102718)) - func.ST_Y(func.ST_Transform(Vehicle.lla, 102718))) \ * 0.3048 dist = func.sqrt(dist_x * dist_x + dist_y * dist_y) height_diff = func.abs( func.ST_Z(Detection.lla) - func.ST_Z(Vehicle.lla)) labels = session.query( overlap_score.label('overlap'), Vehicle.id.label('vid'), Detection.id.label('did'), dist.label('dist'), height_diff.label('height_diff'), score.label('score')) \ .select_from(Detection) \ .join(Photo) \ .join(Vehicle) \ .join(Model) \ .filter(Model.filename == model) \ .filter(Photo.test == True) \ .filter(overlap_score > 0.5) \ .filter(score > threshold) # pylint: enable-msg=E1101 for query_filter in detection_filters: labels = labels.filter(query_filter) for query_filter in vehicle_filters: labels = labels.filter(query_filter) labels = labels.order_by(desc(overlap_score)).all() return labels
def _deserialize(self, value, attr, data): if value is None: return value else: if isinstance(value, Geometry): return { 'longitude': db.session.scalar(func.ST_X(value)), 'latitude': db.session.scalar(func.ST_Y(value)) } else: return None
def generate_grids(config, area=None): bounding_box = WKTElement(config['BOUNDING_BOX'], srid=4326) grid_obj = config['GRID_OBJ'] resolution = config['RESOLUTION'] epsg = config['EPSG'] try: grids = session.query(func.ST_Dump( func.makegrid_2d(bounding_box, resolution, resolution)).geom.label('geom') # self-defined function in Psql ).subquery() # using the boundary to crop the area # if config['AREA'] == 'los_angeles': # grids = session.query(grids.c.geom) \ # .filter(func.ST_Intersects(LosAngelesCountyBoundary.wkb_geometry, grids.c.geom)).subquery() results = session.query( func.row_number().over().label('gid'), func.ST_Centroid(grids.c.geom).label('centroid'), func.ST_X(func.ST_Centroid(grids.c.geom)).label('lon'), func.ST_Y(func.ST_Centroid(grids.c.geom)).label('lat'), grids.c.geom, func.ST_X(func.ST_Transform(func.ST_Centroid(grids.c.geom), epsg)).label('lon_proj'), func.ST_Y(func.ST_Transform(func.ST_Centroid(grids.c.geom), epsg)).label('lat_proj')).all() obj_results = [] for res in results: obj_results.append(grid_obj(gid=res[0], centroid=res[1], lon=res[2], lat=res[3], geom=res[4], lon_proj=res[5], lat_proj=res[6])) # session.add_all(obj_results) # session.commit() return except Exception as e: print(e) exit(-1)
def get_service_provider(wh): active_sp = s.query( ServiceProviderSchedule.id_prestador_servicio, func.ST_X(ServiceProvider.ultima_ubicacion), func.ST_Y(ServiceProvider.ultima_ubicacion) ).join(ServiceProvider).filter( and_(ServiceProviderSchedule.id_almacen == wh, ServiceProviderSchedule.inicio < datetime.datetime.now(), ServiceProviderSchedule.fin > datetime.datetime.now())).all() selected_sp = None return False, selected_sp
def _serialize(self, value, attr, obj): if value is None: return value else: if isinstance(value, WKBElement): return json.dumps({ "longitude": db.session.scalar(func.ST_X(value)), "latitude": db.session.scalar(func.ST_Y(value)) }) else: return None
def segmentized_line_with_geographic_points(cls, trip_id): ''' Returns (lat, lon) of geographic coordinate of points every 50m along route. Mainly intended for use with testing.''' s = select([ func.ST_DumpPoints(func.ST_Transform(func.ST_Segmentize(Trip.geom_path, 50), settings.TARGET_DATUM))\ .label('dp') ]).where(Trip.trip_id == trip_id) inner_select = s.correlate(None).alias() s = select([ func.ST_Y(inner_select.columns.dp.geom), func.ST_X(inner_select.columns.dp.geom), ]) return list(engine.execute(s))
def test_spatial_extra_base(self, app): user = factories.User() env = {"REMOTE_USER": user["name"].encode("ascii")} dataset = factories.Dataset(user=user) if tk.check_ckan_version(min_version="2.9"): offset = url_for("dataset.edit", id=dataset["id"]) else: offset = url_for(controller="package", action="edit", id=dataset["id"]) res = app.get(offset, extra_environ=env) if tk.check_ckan_version(min_version="2.9"): data = { "name": dataset['name'], "extras__0__key": u"spatial", "extras__0__value": self.geojson_examples["point"] } res = app.post(offset, environ_overrides=env, data=data) else: form = res.forms[1] form['extras__0__key'] = u'spatial' form['extras__0__value'] = self.geojson_examples['point'] res = helpers.submit_and_follow(app, form, env, 'save') assert "Error" not in res, res package_extent = ( Session.query(PackageExtent) .filter(PackageExtent.package_id == dataset["id"]) .first() ) geojson = json.loads(self.geojson_examples["point"]) assert package_extent.package_id == dataset["id"] from sqlalchemy import func assert ( Session.query(func.ST_X(package_extent.the_geom)).first()[0] == geojson["coordinates"][0] ) assert ( Session.query(func.ST_Y(package_extent.the_geom)).first()[0] == geojson["coordinates"][1] ) assert package_extent.the_geom.srid == self.db_srid
def elevation_query(session, detection, elevation_raster, geoidheight_raster): """Computes the elevation of the detection above the terrain.""" # pylint: disable-msg=E1101 car_lla = Detection.lla query = session.query( func.ST_Y(car_lla), func.ST_X(car_lla), func.ST_Z(car_lla)) \ .filter(Detection.id == detection.id) # pylint: enable-msg=E1101 lat, lon, alt = query.one() elevation = index_raster(elevation_raster, lat, lon) geoidheight = index_raster( geoidheight_raster, lat, lon if lon > 0 else lon + 360) return alt - elevation - geoidheight
def convert_projected_point_to_geographic_coordinates(cls, point): '''Returns a tuple of (latitude, longitude) coordinates Args: cls (SpatialQueries): Class object point (geometry): PostGIS Geometry type of a projected point Returns: tuple of (latitude, longitude) coordinates ''' s = select([ func.ST_Transform(func.ST_WKBToSQL(point), settings.TARGET_DATUM).label('p') ]) inner_select = s.correlate(None).alias() s = select([ func.ST_Y(inner_select.columns.p), func.ST_X(inner_select.columns.p), ]) return list(engine.execute(s))[0]
def test_spatial_extra(self): ''' ''' app = self._get_test_app() user = factories.User() env = {u'REMOTE_USER': user[u'name'].encode(u'ascii')} dataset = factories.Dataset(user=user) offset = toolkit.url_for(controller=u'package', action=u'edit', id=dataset[u'id']) res = app.get(offset, extra_environ=env) form = res.forms[1] form[u'extras__0__key'] = u'spatial' form[u'extras__0__value'] = self.geojson_examples[u'point'] res = helpers.submit_and_follow(app, form, env, u'save') assert u'Error' not in res, res package_extent = Session.query(PackageExtent) \ .filter(PackageExtent.package_id == dataset[u'id']).first() geojson = json.loads(self.geojson_examples[u'point']) assert_equals(package_extent.package_id, dataset[u'id']) if legacy_geoalchemy: assert_equals(Session.scalar(package_extent.the_geom.x), geojson[u'coordinates'][0]) assert_equals(Session.scalar(package_extent.the_geom.y), geojson[u'coordinates'][1]) assert_equals(Session.scalar(package_extent.the_geom.srid), self.db_srid) else: from sqlalchemy import func assert_equals( Session.query(func.ST_X(package_extent.the_geom)).first()[0], geojson[u'coordinates'][0]) assert_equals( Session.query(func.ST_Y(package_extent.the_geom)).first()[0], geojson[u'coordinates'][1]) assert_equals(package_extent.the_geom.srid, self.db_srid)
def get_horizon_endpoints(session, photo): """Computes the endpoints of the horizon in the photo.""" if photo.id in __HORIZON_CACHE__: return __HORIZON_CACHE__[photo.id] lon, lat, alt = session.query( func.ST_X(Photo.lla), func.ST_Y(Photo.lla), func.ST_Z(Photo.lla)) \ .filter_by(id=photo.id) \ .one() point = numpy.array([[lat, lon, alt]]) enu = pygeo.LLAToENU(point).reshape((3, 3)) R = numpy.array([ [photo.r11, photo.r12, photo.r13], [photo.r21, photo.r22, photo.r23], [photo.r31, photo.r32, photo.r33], ]) K = numpy.array([ [photo.focal, 0, photo.width / 2], [0, photo.focal, photo.height / 2], [0, 0, 1], ]) P = K.dot(R.dot(enu)) h = numpy.cross(P[:, 0], P[:, 1]) m = -h[0] / h[1] b = -h[2] / h[1] endpoints = numpy.array([ [0, (m * photo.width + b) / photo.height], [1, b / photo.height], ]) __HORIZON_CACHE__[photo.id] = endpoints return __HORIZON_CACHE__[photo.id]
def lonlat( self, ) -> typing.Union[typing.Tuple[None, None], typing.Tuple[float, float]]: """ Return a tuple of (lon, lat) or return None and log exception if no coordinates can be retrieved. Note: Should only be called if self._longitude_cache and self._latitude_cache are not set """ lonlat_value = getattr(self, self.lonlat_field) try: lon, lat = db.session.query(func.ST_X(lonlat_value), func.ST_Y(lonlat_value)).first() except DataError: logger.exception( f"Failed to get lon, lat for {inspect(self).class_} with id {inspect(self).identity[0]}" ) lon, lat = None, None finally: db.session.close() return lon, lat
def pdf_cover(request): try: division_code = request.matchdict.get("divisioncode") except: raise HTTPBadRequest(detail="incorrect value for parameter " '"divisioncode"') division = get_division(request, division_code) hazard_types = get_hazard_types(request, division_code) hazards_sorted = sorted(hazard_types, key=lambda a: a["hazardlevel"].order) hazard_categories = [] for h in hazards_sorted: if h["hazardlevel"].mnemonic == _hazardlevel_nodata.mnemonic: continue hazard_categories.append( get_info_for_hazard_type(request, h["hazardtype"].mnemonic, division)) lon, lat = (request.dbsession.query( func.ST_X(ST_Centroid(AdministrativeDivision.geom)), func.ST_Y(ST_Centroid(AdministrativeDivision.geom)), ).filter(AdministrativeDivision.code == division_code).first()) context = { "hazards": hazard_types, "hazards_sorted": sorted(hazard_types, key=lambda a: a["hazardlevel"].order), "parents": get_parents(division), "division": division, "division_lonlat": (lon, lat), "hazard_categories": hazard_categories, "date": datetime.datetime.now(), } return context
def stations2_filtered_pl(start, end): last_10_minutes = datetime.utcnow() - timedelta(minutes=10) query = (db.session.query( Receiver.name.label("s"), label("lt", func.round(func.ST_Y(Receiver.location_wkt) * 10000) / 10000), label("lg", func.round(func.ST_X(Receiver.location_wkt) * 10000) / 10000), case([(Receiver.lastseen > last_10_minutes, "U")], else_="D").label("u"), Receiver.lastseen.label("ut"), label("v", Receiver.version + "." + Receiver.platform), ).order_by(Receiver.lastseen).filter( db.or_(db.and_(start < Receiver.firstseen, end > Receiver.firstseen), db.and_(start < Receiver.lastseen, end > Receiver.lastseen)))) res = db.session.execute(query) stations = json.dumps({"stations": [dict(r) for r in res]}, default=alchemyencoder) return stations
loc_model = api.model( "Location", { "latitude": fields.Float(description="latitude", required=True), "longitude": fields.Float(description="longitude", required=True), }, ) point_model = api.model( "Point", { "latitude": fields.Float(attribute=lambda x: db.session.scalar(func.ST_X(x))), "longitude": fields.Float(attribute=lambda x: db.session.scalar(func.ST_Y(x))), }, ) base_publication_model = api.model( 'Base Publication', { "id": fields.Integer(readonly=True, description="The unique identifier of the publication"), "user_id": fields.Integer(description="Id of owner user"), "title": fields.String(required=True, description="The title of the publication."), "description":
def get_address_by_id(item_id): location_item = s.query( UserLocation, func.ST_X(UserLocation.gps), func.ST_Y(UserLocation.gps), func.ST_AsText(UserLocation.gps)).filter( UserLocation.user_address_id == item_id).first() return location_item
def coords(self): return session.query(func.ST_Y(self.location), func.ST_X(self.location)).one()
def get_lat_lon(self): return session.query(func.ST_Y(self.location), func.ST_X(self.location)).one()
def dec(self): return func.ST_Y(self.radec)
def centerline_query(session, detection): """Finds the centerline orientation that most closely agrees with detection-intersected roadbeds.""" # pylint: disable-msg=E1101 car_polygon = Detection.geom car_polygon102718 = func.ST_Transform(car_polygon, 102718) car_filter = func.ST_Intersects( Roadbed.geom, car_polygon102718 ) query = session.query( Roadbed.gid) \ .filter(Detection.id == detection.id) \ .filter(car_filter) road_gids = query.all() if len(road_gids) == 0: return lat, lon, alt = session.query( func.ST_Y(Detection.lla), func.ST_X(Detection.lla), func.ST_Z(Detection.lla)) \ .filter(Detection.id == detection.id) \ .one() lla = numpy.array([[lat, lon, alt]]) enu = pygeo.LLAToENU(lla).reshape((3, 3)) roadbeds4326 = func.ST_Transform(Roadbed.geom, 4326) centerlines4326 = PlanetOsmLine.way centerline_filter = func.ST_Intersects(roadbeds4326, centerlines4326) centerline_frac = func.ST_Line_Locate_Point( centerlines4326, Detection.lla) centerline_start_frac = func.least(1, centerline_frac + 0.01) centerline_end_frac = func.greatest(0, centerline_frac - 0.01) centerline_start = func.ST_Line_Interpolate_Point(centerlines4326, centerline_start_frac) centerline_end = func.ST_Line_Interpolate_Point(centerlines4326, centerline_end_frac) segments = session.query( func.ST_Y(centerline_start).label('lats'), func.ST_X(centerline_start).label('lons'), func.ST_Y(centerline_end).label('late'), func.ST_X(centerline_end).label('lone'), PlanetOsmLine.oneway) \ .filter(Detection.id == detection.id) \ .filter(centerline_filter) \ .filter(Roadbed.gid.in_(road_gids)) \ .filter(PlanetOsmLine.osm_id >= 0) \ .filter(PlanetOsmLine.railway.__eq__(None)) # pylint: enable-msg=E1101 for segment in segments: segment_start = pygeo.LLAToECEF(numpy.array( [[segment.lats, segment.lons, alt]], dtype=numpy.float64 )) segment_end = pygeo.LLAToECEF(numpy.array( [[segment.late, segment.lone, alt]], dtype=numpy.float64 )) segment_dir = (segment_end - segment_start) segment_dir /= numpy.linalg.norm(segment_dir) segment_rot = enu.T.dot(segment_dir.T) segment_angle = math.atan2(segment_rot[1], segment_rot[0]) yield segment_angle, segment.oneway