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)