def chunk(pickup_file): session = db_connection.SESSIONMAKER() with open(pickup_file, 'r') as pickup_file: pickups = csv.DictReader(pickup_file) count = 0 ditched = 0 start = datetime.datetime.now() for pickup in pickups: pickup_loc = 'SRID=4326;POINT({} {})'.format( pickup['Lon'], pickup['Lat']) pickup_geo = func.ST_GeogFromText(pickup_loc) nearest = session.query(db_model.Intersection.id, func.ST_Distance(pickup_geo, db_model.Intersection.location)).\ filter(func.ST_DWithin(db_model.Intersection.location, pickup_geo, 200)).\ order_by(func.ST_Distance(pickup_geo, db_model.Intersection.location).asc()).first() if nearest is None: ditched += 1 continue nearest = nearest[0] pickup_time = datetime.datetime.strptime(pickup['Date/Time'], "%m/%d/%Y %H:%M:%S") session.add( db_model.Pickup(time=pickup_time, base=pickup['Base'], location=pickup_geo, intersection=nearest, week_chunk=week_chunk(pickup_time), month_chunk=month_chunk(pickup_time))) if count % 10000 == 0: stop = datetime.datetime.now() print((stop - start).total_seconds(), count, ditched) start = datetime.datetime.now() count += 1 session.commit() session.close()
def query(self, source, target, core, column, pkey): # Get all POIs of fclass `on` pois = select( [source.c[self.source_id], source.c.WKT], source.c[self.source_column] == self.source_filter, ).cte("pois") # Compute the distance from `column` to each POI within given distance distance = func.ST_Distance( core.ST_GeoFromText(target.c[column]), core.ST_GeoFromText(pois.c.WKT), ) pairs = (select( [target, distance.label(self.feature_name)], distance < self.within, ).select_from(pois).cte("pairs")) # Partition results to get the smallest distance (nearest POI) query = select([ pairs, func.row_number().over( partition_by=pairs.c[pkey], order_by=pairs.c[self.feature_name].asc(), ).label("row_number"), ]).select_from(pairs) query = select( [col for col in query.columns if col.key != "row_number"], query.c["row_number"] == 1, ) return query
def get_closest_node(longitude, latitude): """ Get the closest 10 nodes to the given longitude and latitude. This function converts geometry points to srid METER_UNIT_SRID(26986) point using ST_Transform and then calculate their distance in meters using ST_Distance. This function may not return an array of exact length 10 if there is less than 10 points in the database. :param longitude: the longitude of the origin point :param latitude: the latitude of the origin point :return: JSON of an array containing the 10 closest nodes (if there are more than 10 nodes). The array is sorted in ascending distance order. node object keys: node_id(int), geometry(geom{type(str), coordinates(list[int])}) """ origin_point = _get_srid_point(longitude, latitude) nodes_ascend_dist_order_query_result = Node.query \ .with_entities(Node.node_id, Node.geom.ST_AsGeoJSON()) \ .order_by(func.ST_Distance(func.ST_Transform(Node.geom, METER_UNIT_SRID), _transform_to_meter_srid(origin_point)).asc()) ten_closest_nodes = [] node_count = 0 for node_data in nodes_ascend_dist_order_query_result: if node_count >= 10: break ten_closest_nodes.append(parse_node_response(node_data)) node_count += 1 return jsonify(ten_closest_nodes)
def query(self, source, target, core, column): # Get all POIs of fclass `on` pois = select( [source.c[self.source_id], source.c.WKT], source.c[self.source_column] == self.source_filter, ).cte("pois") # Compute the distance from `column` to each POI within given distance distance = func.ST_Distance( core.ST_GeoFromText(target.c[column]), core.ST_GeoFromText(pois.c.WKT), ) pairs = (select( [target, pois.c[self.source_id], distance.label("distance")], distance < self.within, ).select_from(pois).cte("pairs")) # Partition results to get the smallest distance (nearest POI) keep_columns = [ cols for cols in pairs.columns if cols.key not in ["distance", self.source_id] ] query = (select([ *keep_columns, func.count(distinct(pairs.c[self.source_id])).label( self.feature_name), ]).select_from(pairs).group_by(*keep_columns)) return query
def get_poi_along_path(cls, lines, radius=5, filters=None): if filters is None: filters = {} """ SELECT park.* FROM park WHERE ST_DWithin(line.geom, park.geom, radius) ORDER BY ST_Line_Locate_Point(line.geom, park.geom), ST_Distance(line.geom, park.geom); :param lines: an array of locations :param radius: the search radius :param filters: filters for searching :return: list of all POI's found """ linestring = RoutePaths(paths=lines) q = db.session.query(Park).join(Ride).filter(func.ST_DWithin(linestring.locations, Park.location, radius)) \ .order_by( desc(func.ST_Line_Locate_Point(linestring.locations, Park.location)), desc(func.ST_Distance(linestring.locations, Park.location))) final = _combine_filters(q, filters) return final.all()
def nearby(): try: lat = float(request.args.get('lat')) long = float(request.args.get('long')) except (TypeError, ValueError): logging.info('Missing latitude and longitude params') return 'Missing latitude and longitude params', 400 dist = int(request.args.get('distance') or 350) current_pos = 'POINT(%f %f)' % (long, lat) places_raw = db.session \ .query( Place, func.ST_Distance(current_pos, Place.position).label('distance'), func.count(Place.guides) ).filter(func.ST_DWithin( Place.position, current_pos, dist) ).outerjoin(Place.guides) \ .group_by(Place.id) \ .order_by('distance') \ .all() places = [] for p_raw in places_raw: p = to_object(p_raw[0], excluded=['position']) p['numGuides'] = p_raw[2] p['distance'] = p_raw[1] places.append(p) logging.info('Returning %d results' % len(places)) return jsonify(places)
def add(cls, s, centre, radius_km, title, delete=False): ''' if delete is True, nearby similar entries are deleted before insertion. if delete is False, and there is already a nearby similar entry, it is returned. otherwise, insertion is attempted without deletion. centre is (lon, lat) ''' lon, lat = centre srid = utm_srid(lat, lon) centre = text(point(lon, lat)) radius = radius_km * 1000 if delete is not None: query = s.query(SectorGroup). \ filter(func.ST_Distance(SectorGroup.centre, centre) < radius, SectorGroup.srid == srid) n = query.count() if delete: if n: log.warning(f'Deleting {n} previous sector groups') query.delete(synchronize_session=False) else: if n: sector_group = query.first() log.info(f'Using previously defined sector group "{sector_group.title}"') return sector_group return add(s, SectorGroup(srid=srid, centre=centre, radius=radius, title=title))
def nearby(meters, lat, lng, start_time): loc = WKTElement("POINT(%0.8f %0.8f)" % (lng, lat)) meals = Meal.query.filter(func.ST_Distance(loc, Meal.geo) <= meters) \ .filter(Meal.pickup_time >= start_time) \ .filter(Meal.pickup_time <= closing_datetime()) \ .order_by(Meal.pickup_time) return meals.limit(20).all()
def test_distance_query(geospatial_db_session): distances = geospatial_db_session\ .query(Place, func.ST_Distance(REF_POINT, Place.position))\ .all() assert len(distances) == 3 duomo_dist = next(p for p in distances if p[0].name == 'Piazza Duomo') assert 190 < duomo_dist[1] < 200
def get(self, args, **_kwargs): dist = func.ST_Distance(Shop.position, from_shape(Point(args['lng'], args['lat']), srid=4326), True). \ label('dist') query = db.session.query(Shop, Shop.position, dist).options( lazyload('prices'), lazyload('tags')).filter(dist < 1000 * args['dist']).order_by( dist.asc()).limit(args['count']) shops = query.all() return ShopDistSchema().dump(shops, many=True).data
def _within_distance(compiler, geom1, geom2, distance, *args): """ST_DWithin in early versions of PostGIS 1.3 does not work when distance = 0. So we are directly using the (correct) internal definition. Note that the definition changed in version 1.3.4, see also: http://postgis.refractions.net/docs/ST_DWithin.html """ return and_(func.ST_Expand(geom2, distance).op('&&')(geom1), func.ST_Expand(geom1, distance).op('&&')(geom2), func.ST_Distance(geom1, geom2) <= distance)
def by_location(cls, location, distance_threshold=0.025): location = location.to_wkt_element() distance = func.ST_Distance(cls.location_wkt, location) airport = db.session.query(cls, distance.label('distance')) \ .order_by(distance).first() if airport is not None and (distance_threshold is None or airport.distance < distance_threshold): return airport.Airport else: return None
def get(self, cityId): args = neighborParser.parse_args() origin = City.query.filter(City.id == cityId).first() if not origin: abort(404, message='City {} does not exist'.format(cityId)) neighbors = City.query.filter(City.id != origin.id).order_by( func.ST_Distance(City.location, origin.location)) if args['sameCountry']: neighbors = neighbors.filter( City.countryCode == origin.countryCode) neighbors = neighbors.limit(args['limit']).offset(args['offset']) return [asJson(n) for n in neighbors.all()]
def test_nearby_with_distances_query(geospatial_db_session): nearby_places = geospatial_db_session\ .query(Place, func.ST_Distance(REF_POINT, Place.position).label('distance'))\ .filter(func.ST_DWithin( Place.position, REF_POINT, 300) ).order_by('distance')\ .all() assert len(nearby_places) == 2 assert nearby_places[1][0].name == 'Galleria Vittorio Emanuele' assert nearby_places[0][0].name == 'Piazza Duomo'
def _run_activity_journal(self, s, ajournal, power_model): if ajournal.route_edt: for sector_group in s.query(SectorGroup). \ filter(func.ST_Distance(SectorGroup.centre, ajournal.centre) < SectorGroup.radius). \ all(): count = 0 for sjournal in find_and_add_sector_journals(s, sector_group, ajournal): loader = self._get_loader(s, add_serial=False) add_sector_statistics(s, sjournal, loader, power_model=power_model) loader.load() count += 1 if count: log.info(f'Found {count} sectors for activity on {ajournal.start}')
def by_location(cls, location, distance_threshold=0.025, date=datetime.utcnow()): location = location.to_wkt_element() distance = func.ST_Distance(cls.location_wkt, location) airport = db.session.query(cls, distance.label('distance')) \ .filter(or_(cls.valid_until == None, cls.valid_until > date)) \ .order_by(distance).first() if airport is not None and (distance_threshold is None or airport.distance < distance_threshold): return airport.Airport else: return None
def get_galaxies(): try: args = request.get_json() except: return ("Whoaaaa that JSON is a little wonky") if args is None: args = request.args if args is None: return ("Invalid Arguments.") if "api_token" in args: apitoken = args['api_token'] user = db.session.query( models.users).filter(models.users.api_token == apitoken).first() if user is None: return jsonify("invalid api_token") else: return jsonify("api_token is required") filter = [] filter1 = [] filter1.append(models.glade_2p3.pgc_number != -1) filter1.append(models.glade_2p3.distance > 0) filter1.append(models.glade_2p3.distance < 100) trim = db.session.query(models.glade_2p3).filter(*filter1) orderby = [] if 'ra' in args and 'dec' in args: ra = args.get('ra') dec = args.get('dec') if function.isFloat(ra) and function.isFloat(dec): geom = "SRID=4326;POINT(" + str(ra) + " " + str(dec) + ")" orderby.append(func.ST_Distance(models.glade_2p3.position, geom)) if 'name' in args: name = args.get('name') ors = [] ors.append(models.glade_2p3._2mass_name.contains(name.strip())) ors.append(models.glade_2p3.gwgc_name.contains(name.strip())) ors.append(models.glade_2p3.hyperleda_name.contains(name.strip())) ors.append(models.glade_2p3.sdssdr12_name.contains(name.strip())) filter.append(fsq.sqlalchemy.or_(*ors)) galaxies = trim.filter(*filter).order_by(*orderby).limit(15).all() galaxies = [x.json for x in galaxies] return jsonify(galaxies)
def get(self): parser = reqparse.RequestParser() parser.add_argument('lng', type=float, required=True, help='longitude is required') parser.add_argument('lat', type=float, required=True, help='latitude is required') parser.add_argument('query', required=True, help='query is required') args = parser.parse_args() point = WKTElement('POINT({0} {1})'.format(args['lng'], args['lat']), srid=4326) looking_for = '%{0}%'.format(args['query']) stations = db.session.query(Station).filter( or_(Station.name.ilike(looking_for), Station.address.ilike(looking_for)) ).order_by(func.ST_Distance(Station.position, point)).all() payload = {'q': args['query'], 'lat': args['lat'], 'lon': args['lng']} r = requests.get('http://photon.komoot.de/api/', params=payload) poiList = [] for st in stations: poiList.append(Poi(st.name, st.address, st.position)) responsejson = r.json() for feature in responsejson['features']: print feature address = '' if 'street' in feature['properties']: address = feature['properties']['street'] if 'city' in feature['properties']: address = address + ' - %s' % feature['properties']['city'] if 'state' in feature['properties']: address = address + ' - %s' % feature['properties']['state'] if 'country' in feature['properties']: address = address + ' - %s' % feature['properties']['country'] lon = feature['geometry']['coordinates'][0] lat = feature['geometry']['coordinates'][1] pos = WKTElement('POINT({0} {1})'.format(lon, lat), srid=4326) poiList.append(Poi(feature['properties']['name'], address, pos)) return poiList
def roadbed_query(session, detection): """Find roadbeds that intersect the detection's footprint.""" car_lla = detection.lonlat # pylint: disable-msg=E1101 roadbeds4326 = func.ST_Transform(Roadbed.geom, 4326) car_roadbed_dist = func.ST_Distance(roadbeds4326, car_lla) query = session.query( car_roadbed_dist, Roadbed.gid) \ .filter(func.ST_Intersects(car_lla, roadbeds4326)) \ .order_by(car_roadbed_dist.asc()) # pylint: enable-msg=E1101 roadbed = query.first() return roadbed
def closest2Fesom(dbcon, fesominfo, geoWKT=None, samplePoints=[]): """returns vertices of a FESOM grid that have the smallest distance to sample points""" #retrieve/reflect the table tbl=dbcon.getTable(fesominfo["mesh"]["vertTable"],'fesom') qry=select([tbl.c.topo, tbl.c.nodeid,literal_column('geom::geometry').label('geom')]) if geoWKT: qry=qry.where(func.ST_within(literal_column('geom::geometry'), func.ST_GeomFromText(geoWKT,4326))) if len(samplePoints)!=0: pp=[] for p in samplePoints: # print(p) qry1=qry.order_by(func.ST_Distance(literal_column('geom::geometry'), func.ST_GeomFromText(p.wkt,4326))) qry1=qry1.limit(1) pp.append(dbcon.dbeng.execute(qry1).first()._row) return pp
def _search_with_position(current_pos, search_term): places_raw = db.session \ .query( Place, func.ST_Distance(current_pos, Place.position).label('distance'), func.count(Place.guides) ).filter(or_( Place.name.ilike('%{}%'.format(search_term)), Place.tags.like('%{}%'.format(search_term)) )) \ .outerjoin(Place.guides) \ .group_by(Place.id) \ .order_by('distance') \ .all() places = [] for p_raw in places_raw: p = to_object(p_raw[0], excluded=['position']) p['numGuides'] = p_raw[2] p['distance'] = p_raw[1] places.append(p) logging.info('Returning %d results' % len(places)) return jsonify(places)
def compute_listing_score(conn, listing, city, category): def compute_score(selection_result): score = 0 for row in selection_result: doublestar = int(row.stars*2) if doublestar in score_dict: score += score_dict[doublestar] return score if listing.longitude == None or listing.latitude == None: return 0 selection = select([yelp_table.c.stars])\ .select_from(yelp_table.join(yelp2category_table, yelp_table.c.id == yelp2category_table.c.yelp_id)\ .join(categories_table, yelp2category_table.c.category_id == categories_table.c.id)\ .join(yelp_geo_table, yelp_geo_table.c.yelp_id == yelp_table.c.id))\ .where(yelp_table.c.city == city)\ .where(categories_table.c.name == category)\ .where(func.ST_Distance(f'POINT({listing.longitude} {listing.latitude})', yelp_geo_table.c.location) <= distance_threshold)\ .where(yelp_table.c.stars > 3) result = conn.execute(selection) return compute_score(result)
def search_nearest_partner(): try: longitude = float(request.args.get('lng')) latitude = float(request.args.get('lat')) except ValueError: message = { 'message': f'Bad request: lng and lat are ' 'required and should be float type' } return make_response(message, 400, HEADERS) else: partner = (Partner.query.filter( func.ST_Intersects( Partner.coverage_area, f'POINT({longitude} {latitude})')).order_by( func.ST_Distance( Partner.address, f'POINT({longitude} {latitude})')).first()) if partner: return make_response(schema.dump(partner), 200, HEADERS) return make_response('', 204, HEADERS)
def distance(self, location): loc1 = cast(self.location_wkt, Geography) loc2 = func.ST_GeographyFromText(location.to_wkt()) return db.session.scalar(func.ST_Distance(loc1, loc2))
def on_get(self, req, resp): resp.content_type = falcon.MEDIA_JSON session = Session() matchedSymptoms = json.loads(req.get_header('symptoms')) location = 'POINT('+req.get_header("latitude")+' '+req.get_header("longitude")+')' clinicsForSympts = [] for symptId in matchedSymptoms: clinicsForSympts.append( set( session.query(Clinic.id) .join(Clinic.doctors) .join(Doctor.specialities) .join(Speciality.symptoms) .filter(Symptom.id == symptId) .distinct(Clinic.id) .all() ) ) matchedClinics = set(clinicsForSympts[0]).intersection(*clinicsForSympts) closestClinic = session.query(Clinic.id) \ .filter(Clinic.id.in_(matchedClinics)) \ .order_by(asc(func.ST_Distance(Clinic.location, func.ST_GeographyFromText(location)))) \ .first() if closestClinic is None: resp.status = falcon.HTTP_404 resp.body = json.dumps({}) return clinicOutput = session.query(Clinic).filter(Clinic.id == closestClinic.id).first() matchedSpecs = session.query(Speciality.id) \ .join(Speciality.symptoms) \ .filter(Symptom.id.in_(matchedSymptoms)) \ .all() doctorsOutput = session.query(Doctor).join(Doctor.clinics).join(Doctor.specialities) \ .filter(Clinic.id == closestClinic.id).filter(Speciality.id.in_(matchedSpecs)).distinct(Doctor.id).all() specsOutput = session.query(Speciality.name).join(Clinic.doctors).join(Doctor.specialities)\ .filter(Clinic.id == closestClinic.id).filter(Speciality.id.in_(matchedSpecs)) losationStringX = session.query(func.ST_X(clinicOutput.location).label('x')).first() losationStringY = session.query(func.ST_Y(clinicOutput.location).label('y')).first() respp = { 'name': clinicOutput.name, 'location': str(losationStringX.x) + ' ' + str(losationStringY.y), 'opening': str(clinicOutput.opening), 'closure': str(clinicOutput.closure), 'doctors': [{ 'fname': doctor.first_name, 'sname': doctor.second_name, 'pname': doctor.fathers_name, 'specialities': [ spec.name for spec in specsOutput.filter(Doctor.id == doctor.id).all() ], 'cabinet': doctor.cabinet, 'phone': doctor.phone } for doctor in doctorsOutput ] } resp.body = json.dumps(respp) resp.status = falcon.HTTP_200
def apply(self, query): point = f"SRID=4326;POINT({self.values['lat']} {self.values['lon']})" return query.order_by( asc(func.ST_Distance(models.School.location, point)))
def get(self, args): shop_ids = args['shops'] product_ids = args['products'] tags = args['tags'] with_geo = args['geoDist'] is not None and args[ 'geoLng'] is not None and args['geoLat'] is not None no_geo = args['geoDist'] is None and args['geoLng'] is None and args[ 'geoLat'] is None if not (with_geo or no_geo): return custom_error( 'geo', ['Invalid geo parameters combination']), ErrorCode.BAD_REQUEST with_date = (args['dateFrom'] is not None and args['dateTo'] is not None) and \ (args['dateFrom'] <= args['dateTo']) no_date = args['dateFrom'] is None and args['dateTo'] is None if not (with_date or no_date): return custom_error( 'date', ['Invalid date parameters combination']), ErrorCode.BAD_REQUEST sorts = [x.split('|') for x in args['sort']] for sort in sorts: if (sort[0] == 'geoDist' and no_geo) or (sort[0] == 'date' and no_date): return custom_error( 'sort', ['Invalid sort parameter']), ErrorCode.BAD_REQUEST # extra validation, some combinations are illegal dist_col = None if with_geo: dist = func.ST_Distance(Shop.position, from_shape(Point(args['geoLng'], args['geoLat']), srid=4326), True).\ label('dist') query = db.session.query(Price, dist) else: query = Price.query query = query.join(Price.product, Price.shop) today = date.today() date_from = args['dateFrom'] if args['dateFrom'] else today date_to = args['dateTo'] if args['dateTo'] else today query = query.filter(Price.date.between(date_from, date_to)) if shop_ids: query = query.filter(Shop.id.in_(shop_ids)) if product_ids: query = query.filter(Product.id.in_(product_ids)) if tags: query = query.filter( or_( Product.tags.any( func.lower(ProductTag.name).in_(map(str.lower, tags))), Shop.tags.any( func.lower(ShopTag.name).in_(map(str.lower, tags))))) if with_geo: subq = query.subquery() dist_col = subq.c.dist query = db.session.query(Price, dist_col).select_entity_from(subq).filter( dist_col < 1000 * args['geoDist']) # nested query to avoid recalculating distance expr # WHERE and HAVING clauses can't refer to column names (dist) def to_sort_operator(field, order): sort_field = { 'geoDist': dist_col, 'price': Price.price, 'date': Price.date }[field] sort_order = {'ASC': asc, 'DESC': desc}[order] return sort_order(sort_field) query = query.order_by( *[to_sort_operator(field, order) for field, order in sorts]) start = args['start'] count = args['count'] total = query.count() query = query.offset(start).limit(count) prices_page = query.all() prices = PricesResource.PriceSchema(many=True).dump(prices_page).data return { 'start': start, 'count': count, 'total': total, 'prices': prices }
def sort_by_distance(self, lat, lng) -> None: self.query = self.query.order_by( asc( func.ST_Distance( Item.geom, WKTElement('POINT({} {})'.format(lng, lat), srid=4326))))