def get_city(self, lat, lng): """ Get nearby city for this lat and long :param lat: Latitude :param lng: Longitude :return: City object """ point = func.ST_MakePoint(lng, lat) city = self.session.query( Cities.id, Cities.name, Cities.state_id, State.name.label('state_name'), State.short_name.label('state_short_name'), State.slug.label('state_slug'), Cities.slug, Cities.lat, Cities.lng, # Cities.geom, func.ST_Distance_Sphere(point, Cities.__table__.c.geom).label( 'distance')).join(State).order_by( func.ST_Distance_Sphere( point, Cities.__table__.c.geom)).limit(1).first() return city
def _get_end_point_of_first_segment(self, segment, point): query1 = self.session.query(func.ST_StartPoint(segment.geom), func.ST_Distance_Sphere(func.ST_StartPoint(segment.geom), point)) query2 = self.session.query(func.ST_EndPoint(segment.geom), func.ST_Distance_Sphere(func.ST_EndPoint(segment.geom), point)) point1, distance1 = query1.first() point2, distance2 = query2.first() if distance1 < distance2: return point2 else: return point1
def test_load(self): # Load a place identified by a GeoJSON Polygon. metadata = '{"parent_id": null, "name": "77977", "id": "77977", "type": "postal_code", "aliases": [{"name": "The 977", "language": "eng"}]}' geography = '{"type": "Polygon", "coordinates": [[[-96.840066, 28.683039], [-96.830637, 28.690131], [-96.835048, 28.693599], [-96.833515, 28.694926], [-96.82657, 28.699584], [-96.822495, 28.695826], [-96.821248, 28.696391], [-96.814249, 28.700983], [-96.772337, 28.722765], [-96.768804, 28.725363], [-96.768564, 28.725046], [-96.767246, 28.723276], [-96.765295, 28.722084], [-96.764568, 28.720456], [-96.76254, 28.718483], [-96.763087, 28.717521], [-96.761814, 28.716488], [-96.761088, 28.713623], [-96.762231, 28.712798], [-96.75967, 28.709812], [-96.781093, 28.677548], [-96.784803, 28.675363], [-96.793788, 28.669546], [-96.791527, 28.667603], [-96.808567, 28.678507], [-96.81505, 28.682946], [-96.820191, 28.684517], [-96.827178, 28.679867], [-96.828626, 28.681719], [-96.831309, 28.680451], [-96.83565, 28.677724], [-96.840066, 28.683039]]]}' texas_zip, is_new = self.loader.load(metadata, geography) eq_(True, is_new) eq_("77977", texas_zip.external_id) eq_("77977", texas_zip.external_name) eq_(None, texas_zip.parent) eq_("postal_code", texas_zip.type) [alias] = texas_zip.aliases eq_("The 977", alias.name) eq_("eng", alias.language) # Load another place identified by a GeoJSON Point. metadata = '{"parent_id": null, "name": "New York", "type": "state", "abbreviated_name": "NY", "id": "NY", "full_name": "New York", "aliases": [{"name": "New York State", "language": "eng"}]}' geography = '{"type": "Point", "coordinates": [-75, 43]}' new_york, is_new = self.loader.load(metadata, geography) eq_("NY", new_york.abbreviated_name) eq_("New York", new_york.external_name) eq_(True, is_new) # We can measure the distance in kilometers between New York # and Texas. distance_func = func.ST_Distance_Sphere(new_york.geometry, texas_zip.geometry) distance_qu = self._db.query().add_columns(distance_func) [[distance]] = distance_qu.all() eq_(2510, int(distance / 1000)) [alias] = new_york.aliases eq_("New York State", alias.name) eq_("eng", alias.language) # If we load the same place again, but with a different geography, # the Place object is updated. geography = '{"type": "Point", "coordinates": [-74, 44]}' new_york_2, is_new = self.loader.load(metadata, geography) eq_(False, is_new) eq_(new_york, new_york_2) # This changes the distance between the two points. distance_func = func.ST_Distance_Sphere(new_york_2.geometry, texas_zip.geometry) distance_qu = self._db.query().add_columns(distance_func) [[distance]] = distance_qu.all() eq_(2637, int(distance / 1000))
def _split_start_line(self, point, segment): # Find segment's point nearest to point nearest_point = self.session.query(func.st_closestpoint(segment.geom, point)).first() # Locate nearest point on segment. Function returns the position on segment as rate located_point = self.session.query(func.st_line_locate_point(segment.geom, nearest_point)).first() # Split segment. First half starts from 0 and ends at located_point first_half = self.session.query(func.st_asewkb(func.st_Line_Substring(segment.geom, 0, located_point)), func.ST_Distance_Sphere(func.st_Line_Substring(segment.geom, 0, located_point), nearest_point), func.ST_Length(cast(func.st_Line_Substring(segment.geom, 0, located_point), Geography))).first() # Split segment. Second half starts from located_point and ends at 1 second_half = self.session.query(func.st_asewkb(func.st_Line_Substring(segment.geom, located_point, 1)), func.ST_Distance_Sphere(func.st_Line_Substring(segment.geom, located_point, 1), nearest_point), func.ST_Length(cast(func.st_Line_Substring(segment.geom, located_point, 1), Geography))).first() # Create temporary segments (do not add these segments to session), with their own labels first_segment_labels = [] second_segment_labels = [] for label in segment.labels: first_segment_labels.append(model.Label(label=label.label, label_rate=label.label_rate)) second_segment_labels.append(model.Label(label=label.label, label_rate=label.label_rate)) first_segment = model.Segment(name=segment.name, geom=WKBElement(first_half[0]), length=first_half[2], labels=first_segment_labels) second_segment = model.Segment(name=segment.name, geom=WKBElement(second_half[0]), length=second_half[2], labels=second_segment_labels) # If located point is the same as start point, return only second half if located_point[0] == 0.0: return [second_segment] # If located point is the same as end point, return only first half if located_point[0] == 1.0: return [first_segment] # Else, return two splitted segments return [first_segment, second_segment]
def get_path2(): """Dadas una latitud y longitud, un rango de búsqueda y 2 instantes de tiempo, obtiene los teléfonos que se conectaron a antenas en esa ubicación con ese rango y entre los instantes de tiempo fijados. El valor de slider, es el tiempo mínimo de estancia de un teléfono para que aparezca en el filtro. :param lat: coordenada de latitud :param lon: coordenada de longitud :param radio: radio de búsqueda alrededor de (lat,lon) :param date_init: fecha a partir de la que se filtra :param date_end: fecha hasta la que se filtra :param slider: tiempo de estancia mínimo que debe haber estado un teléfono para que aparezca :return: tels: Contiene todos los datos de antenas y teléfonos del filtro. """ lat = request.form['lat'] lon = request.form['lon'] radio = request.form['range'] date_init = request.form['date_init'] date_end = request.form['date_end'] slider = int(request.form['slider']) # En caso de que no se definan las fechas if date_init == '': date_init = datetime.min if date_end == '': date_end = datetime.max # Punto en coordenadas para aplicar el filtro pt = WKTElement('POINT({0} {1})'.format(lon, lat)) # Consulta para encontrar llamadas en la ubicación en el periodo fijado (sin filtros de tiempo de estancia) stmt1 = Antenna.query.join(Antenna.telephones). \ add_columns(Telephone.date_init, Telephone.duration, Telephone.tel_o, Telephone.tel_d). \ filter(date_init <= Telephone.date_init, Telephone.date_init <= date_end). \ filter(func.ST_Distance_Sphere(Antenna.point, pt) < radio + Antenna.range). \ order_by(Telephone.date_init) # alias definidos para la consulta telephone_alias = aliased(Telephone) antenna_alias = aliased(Antenna) # alias para la distancia máxima entre las 2 llamadas de un mismo teléfono dif = (func.max(telephone_alias.date_init) - func.min(telephone_alias.date_init)).label("dif") # Consulta para encontrar todos los teléfonos (por ubicacion) que cumplen estancia superior a X minutos # en una ubicación en el periodo fijado stmt2 = db.session.query(telephone_alias.tel_o, antenna_alias.lon, antenna_alias.lat). \ join(telephone_alias, antenna_alias.telephones). \ filter(date_init <= telephone_alias.date_init, telephone_alias.date_init <= date_end). \ group_by(telephone_alias.tel_o, antenna_alias.lon, antenna_alias.lat). \ having(dif >= timedelta(minutes=slider)) # coger teléfonos de stmt1 y coger los que estén también en la consulta stmt2 tels = stmt1.filter(tuple_(Telephone.tel_o, Antenna.lon, Antenna.lat).in_(stmt2)).all() return render_template('base.html', tels=tels, lat=lat, lon=lon, radio=radio, date_init=date_init, date_end=date_end, slider=slider)
def get_maps(): """return markers to map""" data = {} lst = [] location = request.args.get("searchRequest") ip_address = request.environ['REMOTE_ADDR'] g_loc = geocoder.ip(ip_address) if location == "": g_loc = geocoder.ip(ip_address) else: g_loc = geocoder.google(location) latlng = g_loc.latlng import pdb; pdb.set_trace() print "latlng: " + str(latlng) point = "POINT({lng} {lat})".format(lat=latlng[0],lng=latlng[1]) query = db.session.query(BathroomData).order_by(func.ST_Distance_Sphere( \ point, BathroomData.lnglat) < 10000).filter(func.ST_Distance_Sphere( point, BathroomData.lnglat) < 10000).limit(5).all() for rec in query: qry_comments = db.session.query(Comment).filter(Comment.bathroom_id==rec.bathroom_id).all() data = {"bathroom_id": rec.bathroom_id, "lat": rec.latitude, "lng": rec.longitude, "name": rec.name, "address": rec.street, "city": rec.city, "state": rec.state, "directions": rec.directions, "unisex": rec.unisex, "accessible": rec.accessible, "changing_table": rec.changing_table, "comments": [comment.comment for comment in qry_comments]} lst.append(data) results = {"data": lst} return jsonify(results)
def _find_nearest_lines(self): query = self.session.query(model.Segment, func.ST_Distance_Sphere(model.Segment.geom, self.start_point_text_geom).label('distance'), func.ST_Length(cast(model.Segment.geom, Geography)) ).order_by('distance') segments = [] for row in query: # row format [0]model.Segment, [1]distance (meters), [2]length (meters) if row[1] > self.start_threshold: break else: lines = self._split_start_line(self.start_point_text_geom, row[0]) segments += lines return segments
def collection_query(self): query = self.db.query(self.model) qs = qsparser.parse(self.request.query_string) all_locs = False if 'all' in qs: all_locs = getboolean(qs['all']) if not all_locs: query = query.filter_by(user_id=self.request.user.id) if 'peer' in qs: peer = qs['peer'] lat = peer['latitude'] long = peer['longitude'] radius = peer['radius'] geo = "POINT({} {})".format(long, lat) gp_query = self.db.query(GeoPosition.id) f = func.ST_Distance_Sphere(GeoPosition.geo, geo) gp_query = gp_query.filter(f < radius) query = query.filter(UserLocation.id.in_(gp_query)) return query
def get_all_in_route_radius(cls, route, radius): # convert nautical miles to meters m_radius = radius * 1852 return cls.query.filter( func.ST_Distance_Sphere(cls.geo, route.path) <= m_radius).all()
def get_cities_within_radius(self, radius): """Return all cities within a given radius (in meters) of this city.""" return City.query.filter( func.ST_Distance_Sphere(City.geo, self.geo) < radius).all()
def show_prediction(): """Displays prediction (information for now)""" ################# Getting coordinates from form submission ################# print "coord: {}, add: {}, add(nav): {}, fav: {}, loc: {}".format( request.args.get('my-coordinates'), request.args.get('my-address'), request.args.get('my-address-nav'), request.args.get('my-favorites'), request.args.get('my-location')) if request.args.get( 'my-coordinates') != 'value-hidden' and request.args.get( 'my-coordinates') is not None: print 'coordinates' user_lat = request.args.get('lat') user_lon = request.args.get('lon') elif request.args.get('my-address') != 'value-hidden' and request.args.get( 'my-address') is not None: print 'address' address = request.args.get('address') print address coordinates = get_coordinates_from_address(address) user_lat = coordinates['lat'] user_lon = coordinates['lng'] elif request.args.get('my-nav-address') is not None: print 'address (nav)' address = request.args.get('address') print address coordinates = get_coordinates_from_address(address) user_lat = coordinates['lat'] user_lon = coordinates['lng'] elif request.args.get( 'my-favorites') != 'value-hidden' and request.args.get( 'my-favorites') is not None: print 'favorites' favlocation = request.args.get('favoritelocation') #has to match user ID AND location title fav_location = UserFavorite.query.filter( UserFavorite.favorite_title == favlocation, UserFavorite.user_id == session['current_user']).one() user_lat = fav_location.favorite_lat user_lon = fav_location.favorite_lng elif request.args.get( 'my-location') != 'value-hidden' and request.args.get( 'my-location') is not None: print 'my-location' user_lat = float(request.args.get('usrlat')) user_lon = float(request.args.get('usrlng')) # print user_lat # print type(user_lat) # print user_lon # print type(user_lon) else: #TODO: delete this print "something didn't work" flash("didn't work", 'error') return redirect('/location') ############################################################################ #Turning user coordinates into a point for geography user_point = 'POINT({} {})'.format(user_lon, user_lat) #Time of today or tomorrow's sunset sunset_dict = today_or_tomorrow_sunset(user_lat, user_lon) sunset_datetime_obj = sunset_dict['time'] #(for display purposes) day = sunset_dict['day'] sunset_str = sunset_dict['sunset_str'] print sunset_str local_tz = sunset_dict['local_tz'] local_time = sunset_dict['local_time'] local_sunset_time = sunset_dict['local_sunset_time'] print "Local time and tz: " print local_time print local_tz print "Local Sunset Time: {}".format(local_sunset_time) #for display purposes only current_utc = datetime.datetime.utcnow() print current_utc current_time_str = current_utc.strftime('%Y-%m-%d %H:%M:%S UTC') print current_time_str #******************* FINDING CLOSEST AIRPORT FORECAST ******************** # #forecast containing weather information AND icao code (new and imporoved) #adding try and except for no airport error try: if 'distance-search' in request.args: distance_filter = request.args.get('distance-search') distance_filter = float(distance_filter) * 1000 print "distance {}".format(distance_filter) print type(distance_filter) forecasts = find_nearest_airport_forecast(user_point, distance_filter) else: forecasts = find_nearest_airport_forecast(user_point) except: flash("I'm sorry! There are no available forecasts in this area =[ ", 'error') return redirect('/location') closest_forecast_json = forecasts[0] icao_code = closest_forecast_json['icao'] #Querying for the airport with the code from the forecast closest_airport_obj = Airport.query.filter( Airport.icao_code == icao_code).one() #Making a specifcally formated cloud dictionary to use #in return rating (which returns a dictionary with rating and description) cat_cloud_dict = make_cloud_dict(closest_forecast_json) rate_desc_dict = return_rating(cat_cloud_dict) description = rate_desc_dict['description'] #DISTANCE FROM CLOSEST AIRPORT TO USER(m): distance_to_closest = db.session.query( func.ST_Distance_Sphere(func.ST_GeomFromText(user_point, 4326), closest_airport_obj.location)).one()[0] #from m to km distance_to_closest = distance_to_closest / 1000 distance_to_closest = str(distance_to_closest)[:6] distance_to_closest = float(distance_to_closest) #**************************** RECOMENDATION *******************************# #setting the highest rating equal to the closest forecast #this way if we get a tie, it won't be considered higher cat_cloud_dict_closest = make_cloud_dict(closest_forecast_json) rate_desc_dict = return_rating(cat_cloud_dict_closest) highest_rating = rate_desc_dict['value'] #Pre-setting the recommendation to be the closest forecast recomendation = closest_forecast_json['icao'] #add all forecast ratings to dictionary so that we can rank them all_forecast_ratings = [] for forecast in forecasts: #Getting a rating for each forecast cat_cloud_dict = make_cloud_dict(forecast) rate_desc_dict = return_rating(cat_cloud_dict) rating = rate_desc_dict['value'] airport_forecast_obj = Airport.query.filter( Airport.icao_code == forecast['icao']).one() distance_to_airport = db.session.query( func.ST_Distance_Sphere(func.ST_GeomFromText(user_point, 4326), airport_forecast_obj.location)).one()[0] #m to km: distance_to_airport = distance_to_airport / 1000 distance_to_airport = str(distance_to_airport)[:6] distance_to_airport = float(distance_to_airport) rate_desc_dict['airport_obj'] = airport_forecast_obj rate_desc_dict['distance_from_user_km'] = distance_to_airport rate_desc_dict['icao'] = forecast['icao'] all_forecast_ratings.append(rate_desc_dict) #Recommended airport is the one with the highest rating if rating > highest_rating: highest_rating = rating recomendation = forecast['icao'] rec_forecast = forecast rec_desc = rate_desc_dict['description'] print "{} has a higher rating".format(recomendation) else: print "{} did not have a better forecast".format(forecast['icao']) #If there are no higher rated sunsets if recomendation == closest_forecast_json['icao']: rec_message = """You've got the highest rated sunset in your area! \n We recommend you stay right where you are! """ rec_forecast = "same" rec_lat = None rec_lng = None recomendation_obj = None distance_to_rec = None rec_desc = None else: recomendation_obj = Airport.query.filter( Airport.icao_code == recomendation).one() rec_lat = recomendation_obj.lattitude rec_lng = recomendation_obj.longitude rec_message = """{} ({}) has a higher rated sunset! \n We recommend you go there for the best sunset experience.""".format( recomendation_obj.airport_name, recomendation) #DISTANCE FROM RECOMMENDED AIRPORT TO USER(m): distance_to_rec = db.session.query( func.ST_Distance_Sphere(func.ST_GeomFromText(user_point, 4326), recomendation_obj.location)).one()[0] #m to km distance_to_rec = distance_to_rec / 1000 distance_to_rec = str(distance_to_rec)[:6] distance_to_rec = float(distance_to_rec) # RANKING THE RECOMMENDATIONS #Making sorted recomendations: #make the value (rating number) int in order to sort them [int(rating['value']) for rating in all_forecast_ratings] sorted_forecast_ratings = sorted(all_forecast_ratings, key=lambda rating: rating['value'], reverse=True) #giving rank--tied items get the same rank. rank = 1 i = 0 for rating_dict in sorted_forecast_ratings: if i != 0: #if there isn't a tie, we increase the rank if rating_dict['value'] != sorted_forecast_ratings[i - 1]['value']: rank += 1 rating_dict['rank'] = rank i += 1 return render_template('prediction.html', icao_code=icao_code, airport_obj=closest_airport_obj, sunset_time=sunset_datetime_obj, forecast=closest_forecast_json, current_utc=current_utc, description=description, userLat=user_lat, userLon=user_lon, mapsapiurl=maps_src_url, rec_forecast=rec_forecast, rec_message=rec_message, placesmapurl=places_map_url, rec_lat=rec_lat, rec_lng=rec_lng, day=day, recomendation_obj=recomendation_obj, distance_to_closest=distance_to_closest, distance_to_rec=distance_to_rec, sunset_str=sunset_str, current_time_str=current_time_str, local_time=local_time, local_tz=local_tz, local_sunset_time=local_sunset_time, rec_desc=rec_desc, sorted_forecast_ratings=sorted_forecast_ratings)
def _apply_query_filters( query: sqlalchemy.orm.query.Query, study_ids: List[int], overall_statuses: Optional[List[EnumOverallStatus]] = None, cities: Optional[List[str]] = None, states: Optional[List[str]] = None, countries: Optional[List[str]] = None, facility_canonical_ids: Optional[List[int]] = None, current_location_longitude: Optional[float] = None, current_location_latitude: Optional[float] = None, distance_max_km: Optional[int] = None, intervention_types: Optional[List[EnumIntervention]] = None, phases: Optional[List[EnumPhase]] = None, study_types: Optional[List[EnumStudy]] = None, gender: Optional[EnumGender] = None, year_beg: Optional[int] = None, year_end: Optional[int] = None, age_beg: Optional[int] = None, age_end: Optional[int] = None, ) -> sqlalchemy.orm.query.Query: # Limit studies to those with one of the defined IDs. if study_ids: query = query.filter(ModelStudy.study_id.in_(study_ids)) # Apply an overall-status filter if any are defined. if overall_statuses: _members = [ EnumOverallStatus.get_member(value=str(_status)) for _status in overall_statuses ] query = query.filter(ModelStudy.overall_status.in_(_members)) query = query.join(ModelStudy.facilities_canonical) query = add_canonical_facility_fix_filter(query=query) # Join to the study facility locations and apply filters if any such # filters are defined. if cities or states or countries or facility_canonical_ids: if cities: query = query.filter( ModelFacilityCanonical.locality.in_(cities), ) if states: query = query.filter( ModelFacilityCanonical.administrative_area_level_1.in_( states, )) if countries: query = query.filter( ModelFacilityCanonical.country.in_(countries), ) if facility_canonical_ids: query = query.filter( ModelFacilityCanonical.facility_canonical_id.in_( facility_canonical_ids, ), ) if (current_location_longitude and current_location_latitude and distance_max_km): # Convert distance to meters. distance_max_m = distance_max_km * 1000 # Define the function to calculate the distance between the given # coordinates and study facilities. func_distance = sqlalchemy_func.ST_Distance_Sphere( sqlalchemy_func.ST_GeomFromText( "POINT({} {})".format(current_location_longitude, current_location_latitude), ), ModelFacilityCanonical.coordinates, ) # If a maximum age is defined then only include studies without a # facility within the distance from the defined coordinates. query = query.filter(func_distance <= distance_max_m) # Join to the study interventions and apply filters if any such filters # are defined. if intervention_types: _members = [ EnumIntervention.get_member(value=str(_status)) for _status in intervention_types ] query = query.join(ModelStudy.interventions) query = query.filter( ModelIntervention.intervention_type.in_(_members)) # Apply an phase filter if any are defined. if phases: _members = [ EnumPhase.get_member(value=str(_status)) for _status in phases ] query = query.filter(ModelStudy.phase.in_(_members)) # Apply an study-type filter if any are defined. if study_types: _members = [ EnumStudy.get_member(value=str(_status)) for _status in study_types ] query = query.filter(ModelStudy.study_type.in_(_members)) # Join on the `eligibility` relationship if any of the fiters that # require it are defined. if gender or age_beg or age_end: query = query.join(ModelStudy.eligibility) # Apply a gender filter if defined. if gender: if gender == EnumGender.ALL: query = query.filter( ModelEligibility.gender == EnumGender.ALL.value, ) elif gender in [EnumGender.FEMALE, EnumGender.MALE]: _value = EnumGender.get_member(value=str(gender)) query = query.filter( sqlalchemy.or_( ModelEligibility.gender == EnumGender.ALL.value, ModelEligibility.gender == _value, )) # Filter studies the year of their start-date. if year_beg: query = query.filter( ModelStudy.start_date >= datetime.date(year_beg, 1, 1)) if year_end: query = query.filter( ModelStudy.start_date <= datetime.date(year_end, 12, 31)) # Filter studies by eligibility age. if age_beg or age_end: query = TypeStudies._apply_age_filter( query=query, age_beg=age_beg, age_end=age_end, ) return query
def get_parking_slots_within_radius(self, radius): """Return all cities within a given radius (in meters) of this parking_slot.""" return ParkingSlot.query.filter(func.ST_Distance_Sphere(ParkingSlot.geo, self.geo) < radius).all()
def test_load_ndjson(self): # Create a preexisting Place with an alias. old_us, is_new = get_one_or_create(self._db, Place, parent=None, external_name="United States", external_id="US", type="nation", geometry='SRID=4326;POINT(-75 43)') eq_(None, old_us.abbreviated_name) old_alias = get_one_or_create(self._db, PlaceAlias, name="USA", language="eng", place=old_us) old_us_geography = old_us.geometry # Load a small NDJSON "file" containing information about # three places. test_ndjson = """{"parent_id": null, "name": "United States", "aliases": [{"name" : "The Good Old U. S. of A.", "language": "eng"}], "type": "nation", "abbreviated_name": "US", "id": "US"} {"type": "Point", "coordinates": [-159.459551, 54.948652]} {"parent_id": "US", "name": "Alabama", "aliases": [], "type": "state", "abbreviated_name": "AL", "id": "01"} {"type": "Point", "coordinates": [-88.053375, 30.506987]} {"parent_id": "01", "name": "Montgomery", "aliases": [], "type": "city", "abbreviated_name": null, "id": "0151000"} {"type": "Point", "coordinates": [-86.034128, 32.302979]}""" input = StringIO(test_ndjson) [(us, ignore), (alabama, ignore), (montgomery, ignore)] = list(self.loader.load_ndjson(input)) # All three places were loaded as Place objects and their # relationships to each other were maintained. assert isinstance(us, Place) assert isinstance(alabama, Place) assert isinstance(montgomery, Place) eq_(None, us.parent) eq_(us, alabama.parent) eq_(alabama, montgomery.parent) # The place that existed before we ran the loader is still the # same database object, but it has had additional information # associated with it. eq_(us, old_us) eq_("US", us.abbreviated_name) # And its geography has been updated. assert old_us_geography != us.geometry # Its preexisting alias has been preserved, and a new alias added. [new_alias, old_alias] = sorted(us.aliases, key=lambda x: x.name) eq_("USA", old_alias.name) eq_("The Good Old U. S. of A.", new_alias.name) eq_("eng", new_alias.language) # We can measure the distance in kilometers between the point # chosen to represent 'Montgomery' and the point chosen to # represent 'Alabama'. distance_func = func.ST_Distance_Sphere(montgomery.geometry, alabama.geometry) [[distance]] = self._db.query().add_columns(distance_func).all() print distance eq_(276, int(distance / 1000))
def get_all_in_radius(cls, longitude, latitude, radius): # convert nautical miles to meters m_radius = radius * 1852 target_point = 'POINT({} {})'.format(longitude, latitude) return cls.query.filter( func.ST_Distance_Sphere(cls.geo, target_point) <= m_radius).all()
def upload_photo(): """Uploads photo""" #getting information about the photo from the form: location = request.form.get('location') date = request.form.get('date') description = request.form.get('description') title = request.form.get('title') rating = request.form.get('rating') image = request.files['img'] path_name = "usrid={}-{}-{}".format(session['current_user'], date, image.filename) image.save(os.path.join(app.config['UPLOAD_FOLDER'], path_name)) ###### COME UP WITH BETTER FILE NAME ####### image_path = app.config['UPLOAD_FOLDER'] + path_name #making date into object so we can add to DB: date_obj = datetime.datetime.strptime(date, '%Y-%m-%d') #Turning the location into a point for DB: coordinates = get_coordinates_from_address(location) photo_lat = coordinates['lat'] photo_lng = coordinates['lng'] photo_pt = 'POINT({} {})'.format(photo_lng, photo_lat) #finding nearest available airport so we can store that in db: nearest_airport_forecast = find_nearest_airport_forecast(photo_pt)[0] nearest_icao_code = nearest_airport_forecast['icao'] #Getting airport object for airport ID (to add to db) nearest_airport = Airport.query.filter( Airport.icao_code == nearest_icao_code).one() airport_id = nearest_airport.airport_id #using ST_Distance_Sphere gives units in meters instead of other weird something. #Distance is in METERS distance = db.session.query( func.ST_Distance_Sphere(func.ST_GeomFromText(photo_pt, 4326), nearest_airport.location)).one()[0] print photo_lat, photo_lng print nearest_airport.lattitude, nearest_airport.longitude print '**************' print distance print '**************' print type(distance) new_photo = Photo(user_id=session['current_user'], airport_id=airport_id, photo_title=title, photo_lat=photo_lat, photo_lng=photo_lng, photo_location=photo_pt, datetime=date_obj, filepath=image_path, airport_dist=distance, sunset_rating=rating, description=description) db.session.add(new_photo) db.session.commit() return redirect('/mypage')
def apply_space_filter(query, longitude, latitude, distance): return query.join(Space).filter( func.ST_Distance_Sphere( func.ST_PointFromText('POINT(' + '%.8f' % longitude + ' ' + '%.8f' % latitude + ')', 4326), Space.position) < distance)
def get_cities_within_radius(self, radius): return City.query.filter(func.ST_Distance_Sphere(City.geo, self.geo) < radius).all()