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')
