def query_area(point): # point='36.589 -1.25435' try: data = {} query1 = select([Soil], func.ST_Contains(Soil.wkb_geometry, func.ST_Transform(func.ST_GeomFromText(f'POINT({point})', 4326), 4326))) result = conn.execute(query1) for row in result: data['pH'] =str(row.phaq) data['drainage_desc'] = str(row.sdra_descr) data['soil drainage'] =str(row.drai) data['soil class'] =str(row.soil) data['relief'] =str(row.lndf_descr) data['slope'] =str(row.slop) query_2 = session.query(Aez).filter(func.ST_Contains(Aez.wkb_geometry, func.ST_Transform(func.ST_GeomFromText(f'POINT({point})', 4326), 4326))) for i in query_2: print(f'Aez zone at this point is: ' , i.aezcode) data['Aez'] = str(i.aezcode) query_3 = session.query(Rain).filter(func.ST_Contains(func.Geometry(Rain.wkb_geometry), func.Geometry(func.ST_GeographyFromText(f'POINT({point})')))) for i in query_3: print(f'Rain at this point is: ' , i.dn, 'mm') data['rainfall'] = str(i.dn) return Area(data) except Exception as e: return ErrorModel("Area Not Found")
def _get_zones(self): '''Get zones records Returns tuple (zone_map, records). The zone_map field carries a list of tuples (point_id, zone_id). The records field carries a list of records for the zones file. ''' db_zones = db.zones values = ', '.join([ '(%s, ST_Transform(ST_SetSRID(ST_Point(%s, %s), 4326), 900913))' % (loc['location_id'], loc['lng'], loc['lat']) for loc in self.locations ]) s = (select([ text('locs.id'), db_zones.c.id, db_zones.c.data ]).select_from( db_zones.join( text('(VALUES %s) AS locs (id, geom) ' % values), func.ST_Contains(db_zones.c.area, text('locs.geom')))).where( db_zones.c.models_id == self.models_id).order_by( text('locs.id'))) result = self.conn.execute(s) zone_map = [] records = [] for row in result: data = [row[0] + 1] data.extend(row[2]) records.append(data) zone_map.append([row[0], row[1]]) result.close() return zone_map, records
def pks(self): """ Return the primary keys of the Controller's geometry if exist (area or center or closest centers) :return int: pk """ # if it's a point, check t_areas.center and t_co2.geometry if isinstance(self.geo_object, Point): # check in a larger radius circle for closest center query = select([Areas.id]).where(Areas.center == self.geometry) result = areasOps.exec_func_query(query, multi=False) if result: return True, result else: closest = self.what_are_the_closest_centers_to_(self.geometry) return True, closest else: query = select([Areas.id ]).where(func.ST_Contains(self.geometry, Areas.aoi)) # print(str(query.compile())) result = areasOps.exec_func_query(query, multi=True) if not result: return False, None return True, result
def increasing_area(p, results='start', step=0): # check if square contains point # if not recursively increase the size if results != 'start' and results or step == 25: return results[0] if results else None print(p, step) query = select([Areas.center ]).where(func.ST_Contains(Areas.aoi, p)) #print(str(query.compile())) results = areasOps.exec_func_query(query, multi=True) lookup = from_wkt(p).__geo_interface__['coordinates'] stepping = lookup for r in range(100): s = step - 4 if mapping.get(str(s), None): if step % 2 == 0: stepping = (lookup[mapping.get('2')[0]] + mapping.get('2')[1], lookup[1]) else: stepping = (lookup[0], lookup[mapping.get('3')[0]] + mapping.get('3')[1]) step += 1 new_point = spatial.shape_geometry(stepping[0], stepping[1]) return increasing_area(new_point, results, step)
async def get_all_observations_in_polygon(self, polygon): """Retrieve all observations in a bounding box polygon.""" try: # TRICKY: await means the object will be a coroutine # until the event loop 'gets back' to it # and can yield results. Don't chain # the filter() and all() functions directly. # ALSO -- i'm not even sure this is helping... # so i want to find out if the query object returns # immediately and doesn't fetch from the db until # instructed to do so with .fetch() or .fetch_all() # or something result = await self.db_session.query(Observation, School) # TRICKY: Order of calls matter here. Filter after # join. result = result.join(School).filter( func.ST_Contains(polygon, Observation.observation_geom)).all() except DbConnectionError: raise DbConnectionError except Exception as e: self._logger.error(str(e)) self.db_session.rollback() return {'status': 'fail', 'message': str(e)} self._logger.debug( f"Get School Observations in polygon; retrieved {len(result)} observations" ) return {'status': 'success', 'result': result}
def _get_boundary(latitude, longitude): point = Point(float(longitude), float(latitude)) wkb_element = from_shape(point, srid=4326) boundary = Boundary.query.filter( func.ST_Contains(Boundary.polygon, wkb_element)).first() current_app.logger.info("boundary: " + boundary.name) return boundary
def query_builder(search_parameter): if search_parameter.service_category == 'AutoService': classTask = BusinessOwnerTask ownerType = AutoServiceBusinessOwner else: classTask = BusinessOwnerTaskCarWash ownerType = CarWashBusinessOwner query = db.session.query(classTask, ownerType, ServicesDefinition) \ .join(ServicesDefinition, classTask.service_definition_id == ServicesDefinition.id) \ .join(ownerType, ownerType.id == classTask.business_owner_id) \ .filter(ServicesDefinition.service_grade == search_parameter.service_grade) \ .filter( ServicesDefinition.service_category == search_parameter.service_category) # TODO search_parameter.service_category # TODO add activation to search # search_parameter.service_grade # .filter(cast(BusinessOwners.flags['activation'], String) == cast('true', String)) # return query if len(search_parameter.name) > 0: query = query.filter(User.name == search_parameter.name) if len(search_parameter.service_types) > 0: query = query.filter( ServicesDefinition.service_type_id.in_( search_parameter.service_types)) if len(search_parameter.region) > 0: polygon = CarWashBusinessOwner.polygon_maker( search_parameter.region) query = query.filter( func.ST_Contains( func.ST_GeomFromText(polygon, Keys.SRID_VALUE), BusinessOwner.geom)) return query
def get_incidents(in_polygon, crime_type): """ Get the last 15 days worth of incidents of a certain crime type that fall within the given polygon """ # calculate 15 days ago bound = datetime.now() - timedelta(days=15) # get rows a json features geojson = ( db.session.query(func.ST_AsGeoJSON(CrimeIncidentSimple.c.geom).label("json")) .filter( and_( func.ST_Contains(in_polygon, CrimeIncidentSimple.c.geom), CrimeIncidentSimple.c.dispatch_date_time >= bound, CrimeIncidentSimple.c.crime_type == crime_type, ) ) .all() ) # return geojson as feature collection return { "type": "FeatureCollection", "features": [json.loads(f.json) for f in geojson], }
def query_stats_by_lnglats(lnglats: List[LngLat], *, session=None): if not lnglats: return [] # sum age groups query = session.query( func.sum(AgeGroup.total).label("total"), func.sum(AgeGroup.age_0_to_4).label("age_0_to_4"), func.sum(AgeGroup.age_5_to_9).label("age_5_to_9"), func.sum(AgeGroup.age_10_to_14).label("age_10_to_14"), func.sum(AgeGroup.age_15_to_19).label("age_15_to_19"), func.sum(AgeGroup.age_20_to_24).label("age_20_to_24"), func.sum(AgeGroup.age_25_to_29).label("age_25_to_29"), func.sum(AgeGroup.age_30_to_34).label("age_30_to_34"), func.sum(AgeGroup.age_35_to_39).label("age_35_to_39"), func.sum(AgeGroup.age_40_to_44).label("age_40_to_44"), func.sum(AgeGroup.age_45_to_49).label("age_45_to_49"), func.sum(AgeGroup.age_50_to_54).label("age_50_to_54"), func.sum(AgeGroup.age_55_to_59).label("age_55_to_59"), func.sum(AgeGroup.age_60_to_64).label("age_60_to_64"), func.sum(AgeGroup.age_65_to_69).label("age_65_to_69"), func.sum(AgeGroup.age_70_to_74).label("age_70_to_74"), func.sum(AgeGroup.age_75_to_79).label("age_75_to_79"), func.sum(AgeGroup.age_80_to_84).label("age_80_to_84"), func.sum(AgeGroup.age_85_plus).label("age_85_plus"), ) # join AgeGroup and DisseminationArea tables.. query = query.filter(DisseminationArea.dissemination_area_id == AgeGroup.geo_code) # by input locations location_filters = [ func.ST_Contains(DisseminationArea.geometry, f"SRID=4326;POINT({lnglat.lng} {lnglat.lat})") for lnglat in lnglats ] query = query.filter(or_(*location_filters)) aggregated = query.first() return { "total": aggregated.total, "age_0_to_4": aggregated.age_0_to_4, "age_5_to_9": aggregated.age_5_to_9, "age_10_to_14": aggregated.age_10_to_14, "age_15_to_19": aggregated.age_15_to_19, "age_20_to_24": aggregated.age_20_to_24, "age_25_to_29": aggregated.age_25_to_29, "age_30_to_34": aggregated.age_30_to_34, "age_35_to_39": aggregated.age_35_to_39, "age_40_to_44": aggregated.age_40_to_44, "age_45_to_49": aggregated.age_45_to_49, "age_50_to_54": aggregated.age_50_to_54, "age_55_to_59": aggregated.age_55_to_59, "age_60_to_64": aggregated.age_60_to_64, "age_65_to_69": aggregated.age_65_to_69, "age_70_to_74": aggregated.age_70_to_74, "age_75_to_79": aggregated.age_75_to_79, "age_80_to_84": aggregated.age_80_to_84, "age_85_plus": aggregated.age_85_plus, }
async def get_observations_by_school(self, school_names, env=None, polygon=None): """ TODO: fill me out """ if not isinstance(school_names, (list, )): school_names = [school_names] school_ids = list() if not env: env = self.db_env resolver = await SchoolNameResolver(env) #import ipdb; ipdb.set_trace(context=11) for s in school_names: if isinstance(s, str): # TRICKY: resolver returns a list of tuples # each entry in the list is a school # whose canonical name had the lowest # levenshtein distance to the candidate # school name. there may be more than one resolved_schools = await resolver.resolve_school_names( school_names) school_ids = [rs[0] for rs in resolved_schools] elif isinstance(s, int): # NOTE: Leaving this for when APIs hit this API # and are smart enough to send the ID itself school_ids.append(s) else: err_msg = f"Unexpected school name (and type) provided: {s}; \ returning empty set" self._logger.error(err_msg) return {'status': 'fail', 'message': err_msg, 'result': []} try: query = await self.db_session.query(Observation) if not polygon: result = query.filter( Observation.school_id.in_(school_ids)).all() else: result = query.filter( Observation.school_id.in_(school_ids), func.ST_Contains(polygon, Observation.observation_geom)).all() except DbConnectionError: raise DbConnectionError except Exception as e: self.db_session.rollback() self._logger.error(str(e)) return {'status': 'fail', 'message': str(e), 'result': []} return {'status': 'success', 'result': result}
def get_census_geoid(self, lat, lon, year): geoid = {} if (year == "All"): for year_value in s.Socio_Econ_Data_Years.values(): census_obj_name = "CensusBlockGrps" + year_value census_obj = globals()[census_obj_name] session = Session() # given this lat lon, find the census tract that contains it. query = session.query(census_obj.geoid). \ filter(func.ST_Contains(census_obj.geom, func.ST_GeomFromText("POINT(" + str(lon) + " " + str(lat) + ")", 4269))) result = session.execute(query) for query_return_values in result: tmp_geoid = query_return_values[0] if (len(tmp_geoid) > 14): geoid[year_value] = tmp_geoid[-14:] else: geoid[year_value] = tmp_geoid session.close() else: census_obj_name = "CensusBlockGrps" + year census_obj = globals()[census_obj_name] session = Session() # given this lat lon, find the census tract that contains it. query = session.query(census_obj.geoid). \ filter(func.ST_Contains(census_obj.geom, func.ST_GeomFromText("POINT(" + str(lon) + " " + str(lat) + ")", 4269))) result = session.execute(query) for query_return_values in result: tmp_geoid = query_return_values[0] if (len(tmp_geoid) > 14): geoid[year] = tmp_geoid[-14:] else: geoid[year] = tmp_geoid session.close() return geoid
def find_ooaza(db: Session, coordinate: Coordinate): ooaza = (db.query( OoazaPolygon.prefecture_name, OoazaPolygon.city_name, OoazaPolygon.ooaza, OoazaPolygon.polygon, ).filter( func.ST_Contains(OoazaPolygon.polygon, func.ST_GeomFromText(coordinate.to_wkt()))).first()) return ooaza
def get_time_zone(longitude, latitude): time_zone = 'Unknown' tz_world = TzWorld.query.filter( func.ST_Contains(TzWorld.geom, WKTElement('POINT (%s %s)' % (longitude, latitude), srid=4326))).first() if tz_world: time_zone = tz_world.tzid return time_zone
def which_areas_contains_this_polygon(self): """ Return the list of areas contained by the controller's polygon :return generator: ResultProxy """ query = select([Areas ]).where(func.ST_Contains(self.geometry, Areas.aoi)) print(str(query.compile())) self.results_proxy = areasOps.exec_func_query(query, multi=True) return self.results_proxy
def get_community_surrounding(cls, longitude, latitude): """Get a Community with boundaries surrounding a given point specified by longitude and latitude. Returns the Community id, name and boundaries. """ return db.session.query( Community, func.ST_AsGeoJSON(Community.boundaries)).filter( func.ST_Contains( Community.boundaries, WKTElement("POINT({} {})".format(longitude, latitude), 4326))).first()
def get_stadium_groups_by_points(points: List[PointDC]): return db.session.query(StadiumGroup) \ .filter(func.ST_Contains(func.ST_MakePolygon(func.ST_GeomFromText( f"LINESTRING(" f"{points[0].lat} {points[0].long}, " f"{points[1].lat} {points[1].long}, " f"{points[2].lat} {points[2].long}, " f"{points[3].lat} {points[3].long}, " f"{points[0].lat} {points[0].long} " f")" )), func.ST_MakePoint(StadiumGroup.lat, StadiumGroup.long))).all()
def which_points_contains_this_area(self): """ Return the list of points contained by this area :return generator: ResultProxy """ query = select([Xco2 ]).where(func.ST_Contains(self.geometry, Xco2.geometry)) print(str(query.compile())) results = areasOps.exec_func_query(query, multi=True) return results
def query_dissemination_area_by_lnglats(lnglats: List[LngLat], *, session=None): if not lnglats: return [] location_filters = [ func.ST_Contains(DisseminationArea.geometry, f"SRID=4326;POINT({lnglat.lng} {lnglat.lat})") for lnglat in lnglats ] query = session.query(DisseminationArea).filter(or_(*location_filters)) return [area.to_dict() for area in query.all()]
def geoj(): try: lat = float(request.args.get('latitude')) lon = float(request.args.get('longitude')) except: return 'Please send Latitude and Longitude' Point = 'POINT('+ str(lon) + ' ' +str(lat) + ')' query = db.session.query(Boundary.name, Boundary.type, Boundary.parent ).filter(func.ST_Contains(Boundary.geom, func.ST_Transform(func.ST_GeometryFromText(Point,4326), 4326))).all() schema = BoundarySchema(many=True) output = schema.dump(query).data output = json.dumps({'result': output}, default=alchemyencoder) return (json.dumps(json.loads(output), indent=2))
def get(self): parser = reqparse.RequestParser() parser.add_argument('request_fields', action='append') #xmin parser.add_argument('sw_longitude', type=float) #ymin parser.add_argument('sw_latitude', type=float) #xmax parser.add_argument('ne_longitude', type=float) #ymax parser.add_argument('ne_latitude', type=float) parser.add_argument('keyword', type=str) parser.add_argument('current_user_id', type=int) args = parser.parse_args() pin_query = Pin.query sw_longitude = args['sw_longitude'] sw_latitude = args['sw_latitude'] ne_longitude = args['ne_longitude'] ne_latitude = args['ne_latitude'] pin_query = pin_query.filter(func.ST_Contains(func.ST_MakeEnvelope(sw_longitude, sw_latitude, ne_longitude, ne_latitude, 4326), Pin.geo)) if(args['request_fields']): request_fields = tuple(args['request_fields']) pin_schema = PinSchema(only=request_fields) else: pin_schema = PinSchema() if(args['keyword']): keyword = args['keyword'] combined_search_vector = ( Pin.search_vector | User.search_vector | func.coalesce(Tag.search_vector, u'') ) pin_query = pin_query.join(User).outerjoin(PinTag).outerjoin(Tag).filter(combined_search_vector.match(parse_search_query(keyword))) pins = pin_query.all() if(args['current_user_id']): for pin in pins: if(pin.votes): for vote in pin.votes: if(vote.user_id == args['current_user_id']): pin.vote_by_current_user = vote if(not pins): return {"message" :"Pin not found"}, HTTP_NOT_FOUND try: pin_json = pin_schema.dump(pins, many=True).data return pin_json except AttributeError as err: return {"message" : {"request_fields" : format(err)} }, HTTP_BAD_REQUEST
def is_point_in_any_area(cls, point): """ Find the area the geometry belong to, if any in the database. Just an easier accessor for AreasOps.get_aoi_that_contains_(). :return tuple: (False, None) or (True, (object_tuple,)) """ query = select([Areas]).where(func.ST_Contains(Areas.aoi, point)) #print(str(query.compile())) result = areasOps.exec_func_query(query, multi=False) if not result: return False, None return True, result
def _search_thing(lat: float, lng: float, srid: int = -1) -> List[search_pb2.SearchResponse]: """ :param lat: :param lng: :param srid: :return: """ session = session_factory() query = session.query(Thing).filter( func.ST_Contains(Thing.geom, Position(lat, lng, srid).to_wktelement)) return _query_to_responses(query, lambda rec: rec.name)
def append_query_trips(trips, is_rectangle, vertices): if is_rectangle: return trips \ .filter(func.ST_Contains( func.ST_MakeBox2D(func.ST_Point(vertices[0], vertices[1]), func.ST_POINT(vertices[2], vertices[3])), Trip.dropoff)) \ .filter(func.ST_Contains( func.ST_MakeBox2D(func.ST_Point(vertices[0], vertices[1]), func.ST_POINT(vertices[2], vertices[3])), Trip.pickup)); else: # We are building the LINESTRING() query object dynamically because we do not know how many vertices there are polygon = 'LINESTRING(' for x in range(len(vertices)): if x % 2 == 0: if x != 0: polygon += ', ' polygon += vertices[x] + ' ' else: polygon += vertices[x] polygon += ', ' + vertices[0] + ' ' + vertices[1] + ')' return trips.filter(func.ST_Contains(func.ST_MakePolygon(func.ST_GeomFromText(polygon)), Trip.pickup)) \ .filter(func.ST_Contains(func.ST_MakePolygon(func.ST_GeomFromText(polygon)), Trip.dropoff));
def get_places_in_rect(self, rect, sort_close_to=None): query = self.session.query(TestPlace) query = query.filter( func.ST_Contains( func.ST_MakeEnvelope(rect[0], rect[1], rect[2], rect[3], 4326), cast(TestPlace.coord, Geometry))) if sort_close_to is not None: query = query.order_by( func.ST_DistanceSphere( func.ST_GeomFromText('POINT({} {} 4326)'.format( sort_close_to[0], sort_close_to[1])), cast(TestPlace.coord, Geometry), )) places = query.limit(100) return [place for place in places]
async def get(self, request, index): document_index = get_table(index) qs = select([document_index]) q = request.args.get('q', None) if q: qs = qs.where(document_index.c.body == q) c = request.args.get('c', None) if c: qs = qs.where(document_index.c.content_type.in_(c.split(','))) p = request.args.get('p', None) if p: latlon = p.split(',') latlon = list(map(float, latlon)) distance = int(request.args.get('r', 25)) geom = func.ST_GeomFromEWKT('SRID=4326;POINT(%s %s)' % (latlon[1], latlon[0])) qs = qs.where( or_( and_( document_index.c.geom_point.isnot(None), func.ST_DistanceSphere(document_index.c.geom_point, geom) < distance), and_( document_index.c.geom_linestring.isnot(None), func.ST_DistanceSphere( document_index.c.geom_linestring, geom) < distance), and_(document_index.c.geom_polygon.isnot(None), func.ST_Contains(document_index.c.geom_polygon, geom)))) try: rows = await request.app.db.fetch_all(qs) except UndefinedTableError: return json({'error': 'ERROR: The index does\'t exist'}) results = [] i = 0 for x in rows: results.append({ 'content_type': x['content_type'], 'object_id': x['object_id'], 'data': x['data'] }) i += 1 return json({'results': results, 'count': i})
def get_tile_mercator(easting, northing): session = session_factory() with session.begin(subtransactions=True): r = session.query(Tile).filter( func.ST_Contains( Tile.geometry, func.ST_SetSRID(func.ST_Point(easting, northing), cs.WEB_MERCATOR_CODE))).first() if r: return r ewkt = Tile.create_tile_ewkt_mercator(easting, northing) tile = Tile() tile.geometry = ewkt CountryBounds.set_country_for_tile(tile) return tile
async def get_observations_by_conference(self, conference_ids, env=None, polygon=None): """ TODO: fill me out """ if env == None: env = self.db_env # TODO: we'll need a conference name resolver # as well, which will take an env arg if type(conference_ids) is not list: conference_ids = [conference_ids] try: # TODO: change this to an "in" query. if not polygon: query = await self.db_session.query(Observation, School, Conference) result = query.filter( School.id == Observation.school_id).filter( Conference.id == School.conference_id).filter( Conference.id.in_(conference_ids)).all() else: query = await self.db_session.query(Observation, School, Conference) result = query.filter( School.id == Observation.school_id).filter( Conference.id == School.conference_id).filter( Conference.id.in_(conference_ids)).filter( func.ST_Contains( polygon, Observation.observation_geom)).all() except DbConnectionError: raise DbConnectionError except Exception as e: self.db_session.rollback() self._logger.error(str(e)) return {'status': 'fail', 'message': str(e)} return {'status': 'success', 'result': result}
def _search_commune(lat: float, lng: float, srid: int = 4326) -> List[search_pb2.SearchResponse]: """ :param lat: :param lng: :param srid: :return: """ session = session_factory() query = session.query(OGRGeoJSON).filter( func.ST_Contains(OGRGeoJSON.wkb_geometry, Position(lat, lng, srid).to_wkbelement)) return _query_to_responses( query, lambda rec: "{} - {} - {}".format(rec.insee, rec.nom, rec.wikipedia))
def example(engine): # [START bigquery_sqlalchemy_create_table_with_geography] from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, String from sqlalchemy_bigquery import GEOGRAPHY Base = declarative_base() class Lake(Base): __tablename__ = "lakes" name = Column(String, primary_key=True) geog = Column(GEOGRAPHY) # [END bigquery_sqlalchemy_create_table_with_geography] Lake.__table__.create(engine) # [START bigquery_sqlalchemy_insert_geography] from sqlalchemy.orm import sessionmaker from sqlalchemy_bigquery import WKT Session = sessionmaker(bind=engine) session = Session() lake = Lake(name="Majeur", geog="POLYGON((0 0,1 0,1 1,0 1,0 0))") lake2 = Lake(name="Garde", geog=WKT("POLYGON((1 0,3 0,3 2,1 2,1 0))")) b = WKT("POLYGON((3 0,6 0,6 3,3 3,3 0))").wkb lake3 = Lake(name="Orta", geog=b) session.add_all((lake, lake2, lake3)) session.commit() # [END bigquery_sqlalchemy_insert_geography] # [START bigquery_sqlalchemy_query_geography_wkb] from sqlalchemy import func lakes_touching_lake2 = list( session.query(Lake).filter(func.ST_Touches(Lake.geog, lake2.geog))) # [END bigquery_sqlalchemy_query_geography_wkb] # [START bigquery_sqlalchemy_query_geography_text] lakes_containing = list( session.query(Lake).filter(func.ST_Contains(Lake.geog, "POINT(4 1)"))) # [END bigquery_sqlalchemy_query_geography_text] return lakes_touching_lake2, lakes_containing
def test_should_return_an_exisiting_area(): from src.xco2 import Areas from sqlalchemy import func from src.spatial import spatial # get a center from t_areas # build a smaller area around it # check if ST_Contains find the area in the db query = Areas.__table__.select().limit(1) one = dbProxy.alchemy.execute(query).first() square, _ = spatial.shape_aoi(one.center, size=1) contains = Areas.__table__.select().where( func.ST_Contains(Areas.aoi, square)) c_query = dbProxy.alchemy.execute(contains).fetchall() if c_query: for c in c_query: print(c.id, c.aoi, '\n') else: raise AssertionError('test_should_return_an_exisiting_area FAILED')