def get_requestees_in_geo(lat, lon):
return User.query.filter(
func.ST_DWithin(User.last_geo_point_seen,
'POINT({} {})'.format(str(lat), str(lon)),
live_config.tow_radius,
False)).filter_by(active=True).filter_by(
role=User.Role.tower.value).all()
def query_segments(self, shape_id):
self._current_shape_id = shape_id
a = self.stop_class
b = self.traffic_class
rez = self.session.query(
self.stop_class, self.traffic_class,
func.ST_AsText(
func.ST_ClosestPoint(func.ST_Points(a.geom),
func.ST_StartPoint(b.geom))),
func.ST_AsText(
func.ST_ClosestPoint(func.ST_Points(a.geom),
func.ST_EndPoint(b.geom))),
func.ST_Distance(func.ST_StartPoint(b.geom), a.geom),
func.ST_Distance(func.ST_EndPoint(b.geom), a.geom),
func.ST_Distance(a.geom, b.geom)).filter(
and_(a.shape_id == shape_id,
func.ST_DWithin(a.geom, b.geom, 0.001))).order_by(a.id)
"""
, func.ST_ClosestPoint(func.ST_Points(a.geom), func.ST_StartPoint(b.geom))
, func.ST_ClosestPoint(func.ST_Points(a.geom), func.ST_EndPoint(b.geom))
, func.ST_ClosestPoint(func.ST_Points(b.geom), func.ST_StartPoint(a.geom))
, func.ST_ClosestPoint(func.ST_Points(b.geom), func.ST_EndPoint(a.geom))
, func.ST_Contains(func.ST_Buffer(a.geom, 0.00001), b.geom)
, func.ST_Contains(func.ST_Buffer(a.geom, 0.0001), b.geom)
, func.ST_Contains(func.ST_Buffer(a.geom, 0.001), b.geom)
"""
qsegs = self.group_results(rez)
return qsegs
def get_db_venues_around(db: Session, point: Point, radius: int,
limit: int) -> List[Venue]:
venues = db.query(models.Venue) \
.filter(
func.ST_DWithin(
models.Venue.coordinates,
'SRID=4326;POINT({y} {x})'.format(x=point.lat, y=point.lon),
radius)
) \
.limit(limit) \
.all()
return [venue.to_dataclass() for venue in venues]
def get_possible_requestees(self):
# Need to add time_seen to this
possible_requestees = User.query.filter(
func.ST_DWithin(User.last_geo_point_seen, self.last_geo_point_seen,
live_config.tow_radius,
False)).filter_by(active=True).filter_by(
role=self.Role.tower.value).all()
# Grab everybody but self
requestees = [r for r in possible_requestees if r.id != self.id]
logger.debug("Found {} requestees".format(len(requestees)))
# TODO: Find a way to send alerts to admins for each tow request fired
# User.send_admin_text("{} sending tow requests to: {}".format(self, requestees))
return requestees
def get_bag_object_by_address():
app.config['SQLALCHEMY_DATABASE_URI'] = app.config['DB_ALCHEMY_URI_LOC']
x_coord = request.args.get('x', None)
y_coord = request.args.get('y', None)
postcode = request.args.get('postcode', None)
straat = request.args.get('straat', None)
huisnummer = request.args.get('huisnummer', None)
query = Object.query.order_by(Object.orienteren_check,
Object.risicovol_type)
if postcode:
query = query.filter(Object.adres_postcode == postcode)
if straat:
query = query.filter(Object.adres_straatnaam == straat)
if huisnummer:
query = query.filter(Object.adres_huisnummer == huisnummer)
if x_coord and y_coord:
point = WKTElement('POINT({0} {1})'.format(x_coord, y_coord),
srid=28992)
query = query.filter(func.ST_DWithin(Object.geometry_center_geo, point, 200)) \
.order_by(func.ST_Distance(Object.geometry_center_geo, point)) \
try:
db_objects = query.limit(20) \
.all()
except SQLAlchemyError as exc:
return sqlalchemy_error(exc)
if db_objects is None:
return internal_server_error()
elif len(db_objects) == 1:
return pretty_print(db_objects[0].serialize())
return jsonify({'objecten': Serializer.serialize_list(db_objects)})
def filter_within_radius(self, lat, lng, radius):
"""
Filter user within radius from a center (lat, lng) coordinate
"""
# Define center point
point = 'POINT(%f %f)' % (lng, lat)
wkb_element = WKTElement(point, srid=4326)
# Define expression to calculate distance
# from center point to users location
if db.engine.name == 'sqlite':
distance = func \
.distance(User.location, wkb_element, 1) \
.label('distance')
else:
distance = User.location \
.distance_centroid(wkb_element) \
.cast(db.Float) \
.label('distance')
# Define lat, lng query set
lat = func.ST_Y(User.location).label('lat')
lng = func.ST_X(User.location).label('lng')
# Filter user within radius from center point
if db.engine.name == 'sqlite':
qs = User.query.filter(
func.PtDistWithin(User.location, wkb_element, radius))
else:
qs = User.query.filter(
func.ST_DWithin(User.location, wkb_element, radius))
# Append Query-time SQL expressions distance as mapped attributes
# https://docs.sqlalchemy.org/en/latest/orm/mapped_sql_expr.html
qs = qs.options(with_expression(User.distance, distance),
with_expression(User.lat, lat),
with_expression(User.lng, lng))
return qs