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 start_geojson():
pools = Carpool.query
if request.args.get('ignore_prior') != 'false':
pools = pools.filter(Carpool.leave_time >= datetime.datetime.utcnow())
near_lat = request.args.get('near.lat')
near_lon = request.args.get('near.lon')
near_radius = request.args.get('near.radius') or None
if near_lat and near_lon:
try:
near_lat = float(near_lat)
near_lon = float(near_lon)
except ValueError:
abort(400, "Invalid lat/lon format")
try:
near_radius = int(near_radius)
except ValueError:
abort(400, "Invalid radius format")
center = from_shape(Point(near_lon, near_lat), srid=4326)
if near_radius:
# We're going to say that radius is in meters.
# The conversion factor here is based on a 40deg latitude
# (roughly around Virginia)
radius_degrees = near_radius / 111034.61
pools.filter(
func.ST_Distance(Carpool.from_point, center) <= radius_degrees
)
pools = pools.order_by(func.ST_Distance(Carpool.from_point, center))
features = []
for pool in pools:
if pool.from_point is None:
continue
features.append({
'type': 'Feature',
'geometry': mapping(to_shape(pool.from_point)),
'id': url_for('carpool.details', uuid=pool.uuid, _external=True),
'properties': {
'from_place': escape(pool.from_place),
'to_place': escape(pool.destination.name),
'seats_available': pool.seats_available,
'leave_time': pool.leave_time.isoformat(),
'return_time': pool.return_time.isoformat(),
'driver_gender': escape(pool.driver.gender),
},
})
feature_collection = {
'type': 'FeatureCollection',
'features': features
}
return jsonify(feature_collection)
def find_distance_to_nearby_prop(geom, cat_id, buffer_size=2000):
sql = sqlalchemy.select([
func.ST_Distance(func.ST_Transform(geom, 3857),
func.ST_Transform(table.model.c.geom, 3857))
]).where(
and_(
func.ST_Distance(func.ST_Transform(geom, 3857),
func.ST_Transform(table.model.c.geom, 3857)) <
buffer_size, table.model.c.category_id == cat_id))
result = connection.execute(sql).fetchall()
return result
def get_closest_stations(location):
"""Given a location (home, work, or the user location), return the top 5
closest stations to that point"""
query = db.session.query(Station).order_by(
func.ST_Distance(Station.point, location)).limit(5)
return query.all()
def get_closest_node(lon, lat, session):
c_node = session.query(Ways).order_by(
Ways.the_geom.ST_Distance(
func.ST_SetSRID(func.ST_Point(lon, lat), 4326))).limit(1).all()
dist_source = session.query(
func.ST_Distance(
func.ST_SetSRID(func.ST_Point(c_node[0].x1, c_node[0].y1), 4326),
func.ST_SetSRID(func.ST_Point(lon, lat), 4326))).all()
dist_target = session.query(
func.ST_Distance(
func.ST_SetSRID(func.ST_Point(c_node[0].x2, c_node[0].y2), 4326),
func.ST_SetSRID(func.ST_Point(lon, lat), 4326))).all()
# distances return only one value
if dist_source[0][0] < dist_target[0][0]:
return c_node[0].source
else:
return c_node[0].target
def find_distance(self,geom_1,geom_2):
sql = sqlalchemy.select(
[func.ST_Distance(
func.ST_Transform(geom_1,3857),
func.ST_Transform(geom_2,3857)
)
]
)
result = connection.execute(sql).fetchone()
return result[0]
def start_geojson():
pools = Carpool.query
if request.args.get('ignore_prior') != 'false':
pools = pools.filter(Carpool.leave_time >= datetime.datetime.utcnow())
min_leave_date = request.args.get('min_leave_date')
if min_leave_date:
try:
min_leave_date = datetime.datetime.strptime(
min_leave_date, '%m/%d/%Y')
pools = pools.filter(
func.date(Carpool.leave_time) == min_leave_date)
except ValueError:
abort(400, "Invalid date format for min_leave_date")
near_lat = request.args.get('near.lat')
near_lon = request.args.get('near.lon')
if near_lat and near_lon:
try:
near_lat = float(near_lat)
near_lon = float(near_lon)
except ValueError:
abort(400, "Invalid lat/lon format")
center = from_shape(Point(near_lon, near_lat), srid=4326)
pools = pools.order_by(func.ST_Distance(Carpool.from_point, center))
features = []
for pool in pools:
if pool.from_point is None:
continue
features.append({
'type': 'Feature',
'geometry': mapping(to_shape(pool.from_point)),
'id': url_for('carpool.details', carpool_id=pool.id),
'properties': {
'from_place': pool.from_place,
'to_place': pool.to_place,
'seats_available': pool.seats_available,
'leave_time': pool.leave_time.isoformat(),
'return_time': pool.return_time.isoformat(),
'driver_gender': pool.driver.gender,
},
})
feature_collection = {'type': 'FeatureCollection', 'features': features}
return jsonify(feature_collection)
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 start_geojson():
pools = Carpool.query
if request.args.get('ignore_prior') != 'false':
pools = pools.filter(Carpool.leave_time >= datetime.datetime.utcnow())
near_lat = request.args.get('near.lat')
near_lon = request.args.get('near.lon')
near_radius = request.args.get('near.radius') or None
if near_lat and near_lon:
try:
near_lat = float(near_lat)
near_lon = float(near_lon)
except ValueError:
abort(400, "Invalid lat/lon format")
try:
near_radius = int(near_radius)
except ValueError:
abort(400, "Invalid radius format")
center = from_shape(Point(near_lon, near_lat), srid=4326)
if near_radius:
# We're going to say that radius is in meters.
# The conversion factor here is based on a 40deg latitude
# (roughly around Virginia)
radius_degrees = near_radius / 111034.61
pools.filter(
func.ST_Distance(Carpool.from_point, center) <= radius_degrees)
pools = pools.order_by(func.ST_Distance(Carpool.from_point, center))
riders = db.session.query(RideRequest.carpool_id,
func.count(RideRequest.id).label('pax')).\
filter(RideRequest.status == 'approved').\
group_by(RideRequest.carpool_id).\
subquery('riders')
pools = pools.filter(Carpool.from_point.isnot(None)).\
outerjoin(riders, Carpool.id == riders.c.carpool_id).\
filter(riders.c.pax.is_(None) | (riders.c.pax < Carpool.max_riders))
features = []
dt_format = current_app.config.get('DATE_FORMAT')
for pool in pools:
features.append({
'type':
'Feature',
'geometry':
mapping(to_shape(pool.from_point)),
'id':
url_for('carpool.details', uuid=pool.uuid, _external=True),
'properties': {
'from_place': escape(pool.from_place),
'to_place': escape(pool.destination.name),
'seats_available': pool.seats_available,
'leave_time': pool.leave_time.isoformat(),
'return_time': pool.return_time.isoformat(),
'leave_time_human': pool.leave_time.strftime(dt_format),
'return_time_human': pool.return_time.strftime(dt_format),
'driver_gender': escape(pool.driver.gender),
},
})
feature_collection = {'type': 'FeatureCollection', 'features': features}
return jsonify(feature_collection)