def GetDistance(point_latitude, point_longitude): # Calculate distance. See https://www.thutat.com/web/en/programming-and-tech-stuff/ # web-programming/postgres-query-with-gps-distance-calculations-without-postgis/ distance = ( ACos( Sin(Radians(F("location_lat"))) * Sin(Radians(point_latitude)) + Cos(Radians(F("location_lat"))) * Cos(Radians(point_latitude)) * Cos(Radians(F("location_lon") - point_longitude)) ) * 6371 * 1000 ) return distance
def nearest_host_within_x_km(self, current_lat, current_long, x_km): """ Greatest circle distance formula """ dlat = Radians(F("latitude") - current_lat, output_field=models.DecimalField()) dlong = Radians(F("longitude") - current_long, output_field=models.DecimalField()) a = Power(Sin(dlat / 2), 2) + Cos(Radians(current_lat, output_field=models.DecimalField())) * Cos( Radians(F("latitude")) ) * Power(Sin(dlong / 2), 2) c = 2 * ATan2(Sqrt(a), Sqrt(1 - a)) d = 6371 * c return self.annotate(distance=d).order_by("distance").filter(distance__lt=x_km)
def with_frames(self): frames_asc = Frame.objects.filter(sighting=OuterRef('id')).order_by('timestamp') frames_desc = Frame.objects.filter(sighting=OuterRef('id')).order_by('-timestamp') return self.prefetch_related( Prefetch( 'frames', queryset=Frame.objects.with_flight_time(), ) ).annotate( frame_count=Count('frames'), fa=Subquery(frames_asc.values('altitude')[:1]), la=Subquery(frames_desc.values('altitude')[:1]), fz=Subquery(frames_asc.values('azimuth')[:1]), lz=Subquery(frames_desc.values('azimuth')[:1]), arc_length=Degrees( ACos( Sin(Radians(F('fa'))) * Sin(Radians(F('la'))) + Cos(Radians(F('fa'))) * Cos(Radians(F('la'))) * Cos(Radians(F('fz') - F('lz'))) ) ), )
def get_stations(self, lat, lng, filter1, filter2, filter4, filter7): queryset = Stations.objects.annotate(distance=(6371 * ACos( Cos(Radians(float(lat))) * Cos(Radians('lat')) * Cos(Radians('lng') - Radians(float(lng))) + Sin(Radians(float(lat))) * Sin(Radians('lat'))))) queryset = queryset.filter(distance__lte=10) querysetUnion = None if filter1 is not None or filter1 is True: queryset_filter_1 = queryset.filter( Q(chgerType="01") | Q(chgerType="03") | Q(chgerType="05") | Q(chgerType="06")) querysetUnion = querysetUnion.union( queryset_filter_1 ) if querysetUnion is not None else queryset_filter_1 if filter2 is not None or filter2 is True: queryset_filter_2 = queryset.filter(chgerType="02") querysetUnion = querysetUnion.union( queryset_filter_2 ) if querysetUnion is not None else queryset_filter_2 if filter4 is not None or filter4 is True: queryset_filter_4 = queryset.filter( Q(chgerType="04") | Q(chgerType="05") | Q(chgerType="06")) querysetUnion = querysetUnion.union( queryset_filter_4 ) if querysetUnion is not None else queryset_filter_4 if filter7 is not None or filter7 is True: queryset_filter_7 = queryset.filter( Q(chgerType="03") | Q(chgerType="06") | Q(chgerType="07")) querysetUnion = querysetUnion.union( queryset_filter_7 ) if querysetUnion is not None else queryset_filter_7 return queryset if querysetUnion is None else querysetUnion
def get_queryset(self): region = self.request.query_params.get("region", "") mapy = self.request.query_params.get("mapy", "") mapx = self.request.query_params.get("mapx", "") name = self.request.query_params.get("name", "") # 0. 아무것도 선택 X if region == "전체" and len(mapy) == 0 and len(mapx) == 0 and len( name) == 0: queryset = models.TourSpot.objects.all().order_by("-readcount") return queryset # 1. 지역만 elif region is not None and len(mapy) == 0 and len(mapx) == 0 and len( name) == 0: queryset = models.TourSpot.objects.all().filter( addr1__contains=region).order_by("-readcount") return queryset # 2. 장바구니(위도, 경도)만 elif region == "전체" and mapy is not None and mapx is not None and len( name) == 0: # 위도 경도 계산 -> 가까운 순 부터 print("22222222") dlat = Radians(F('mapy') - float(mapy)) dlong = Radians(F('mapx') - float(mapx)) a = (Power(Sin(dlat / 2), 2) + Cos(Radians(float(mapy))) * Cos(Radians(F('mapy'))) * Power(Sin(dlong / 2), 2)) c = 2 * ATan2(Sqrt(a), Sqrt(1 - a)) d = 6371 * c queryset = models.TourSpot.objects.annotate( distance=d).exclude(Q(mapy=mapy) & Q(mapx=mapx)).order_by('distance') return queryset # 3. 검색어만 elif region == "전체" and len(mapy) == 0 and len( mapx) == 0 and name is not None: queryset = models.TourSpot.objects.all().filter( Q(title__contains=name) | Q(addr1__contains=name)).order_by("-readcount") return queryset # 4. 지역, 장바구니(위도, 경도) 선택했을 때 elif region is not None and mapy is not None and mapx is not None and len( name) == 0: print("22222222") dlat = Radians(F('mapy') - float(mapy)) dlong = Radians(F('mapx') - float(mapx)) a = (Power(Sin(dlat / 2), 2) + Cos(Radians(float(mapy))) * Cos(Radians(F('mapy'))) * Power(Sin(dlong / 2), 2)) c = 2 * ATan2(Sqrt(a), Sqrt(1 - a)) d = 6371 * c queryset = models.TourSpot.objects.annotate(distance=d).filter( addr1__contains=region).exclude(Q(mapy=mapy) & Q( mapx=mapx)).order_by('distance') return queryset # 5. 지역, 검색어 선택했을 때 elif region is not None and len(mapy) == 0 and len( mapx) == 0 and name is not None: queryset = models.TourSpot.objects.filter( Q(addr1__contains=region), (Q(title__contains=name) | Q(addr1__contains=name))) return queryset # 6. 장바구니(위도, 경도), 검색어 선택했을 때 elif region == "전체" and mapy is not None and mapx is not None and name is not None: print("22222222") dlat = Radians(F('mapy') - float(mapy)) dlong = Radians(F('mapx') - float(mapx)) a = (Power(Sin(dlat / 2), 2) + Cos(Radians(float(mapy))) * Cos(Radians(F('mapy'))) * Power(Sin(dlong / 2), 2)) c = 2 * ATan2(Sqrt(a), Sqrt(1 - a)) d = 6371 * c queryset = models.TourSpot.objects.annotate(distance=d).filter( Q(title__contains=name) | Q(addr1__contains=name)).exclude( Q(mapy=mapy) & Q(mapx=mapx)).order_by('distance') return queryset # 7. 지역, 장바구니(위도, 경도), 검색어 선택했을 때 elif region is not None and mapy is not None and mapx is not None and name is not None: print("22222222") dlat = Radians(F('mapy') - float(mapy)) dlong = Radians(F('mapx') - float(mapx)) a = (Power(Sin(dlat / 2), 2) + Cos(Radians(float(mapy))) * Cos(Radians(F('mapy'))) * Power(Sin(dlong / 2), 2)) c = 2 * ATan2(Sqrt(a), Sqrt(1 - a)) d = 6371 * c queryset = models.TourSpot.objects.annotate(distance=d).filter( Q(addr1__contains=region), (Q(title__contains=name) | Q(addr1__contains=name) )).exclude(Q(mapy=mapy) & Q(mapx=mapx)).order_by('distance') return queryset else: queryset = models.TourSpot.objects.all().order_by("id") return queryset
def get_queryset(self): region = self.request.query_params.get("region", "") lat = self.request.query_params.get("lat", "") lon = self.request.query_params.get("lon", "") name = self.request.query_params.get("name", "") # 0. 아무것도 선택 X if region == "전체" and len(lat) == 0 and len(lon) == 0 and len( name) == 0: queryset = models.Store.objects.all().order_by("id") return queryset # 1. 지역만 elif region is not None and len(lat) == 0 and len(lon) == 0 and len( name) == 0: queryset = models.Store.objects.all().filter( address__contains=region).order_by("id") return queryset # 2. 장바구니(위도, 경도)만 elif region == "전체" and lat is not None and lon is not None and len( name) == 0: # 위도 경도 계산 -> 가까운 순 부터 print("22222222") dlat = Radians(F('latitude') - float(lat)) dlong = Radians(F('longitude') - float(lon)) a = (Power(Sin(dlat / 2), 2) + Cos(Radians(float(lat))) * Cos(Radians(F('latitude'))) * Power(Sin(dlong / 2), 2)) c = 2 * ATan2(Sqrt(a), Sqrt(1 - a)) d = 6371 * c queryset = models.Store.objects.annotate( distance=d).exclude(Q(latitude=lat) & Q(longitude=lon)).order_by('distance') return queryset # 3. 검색어만 elif region == "전체" and len(lat) == 0 and len( lon) == 0 and name is not None: queryset = models.Store.objects.all().filter( store_name__contains=name).order_by("id") return queryset # 4. 지역, 장바구니(위도, 경도) 선택했을 때 elif region is not None and lat is not None and lon is not None and len( name) == 0: print("22222222") dlat = Radians(F('latitude') - float(lat)) dlong = Radians(F('longitude') - float(lon)) a = (Power(Sin(dlat / 2), 2) + Cos(Radians(float(lat))) * Cos(Radians(F('latitude'))) * Power(Sin(dlong / 2), 2)) c = 2 * ATan2(Sqrt(a), Sqrt(1 - a)) d = 6371 * c queryset = models.Store.objects.annotate(distance=d).filter( address__contains=region).exclude( Q(latitude=lat) & Q(longitude=lon)).order_by('distance') return queryset # 5. 지역, 검색어 선택했을 때 elif region is not None and len(lat) == 0 and len( lon) == 0 and name is not None: queryset = models.Store.objects.filter(address__contains=region, store_name__contains=name) return queryset # 6. 장바구니(위도, 경도), 검색어 선택했을 때 elif region == "전체" and lat is not None and lon is not None and name is not None: dlat = Radians(F('latitude') - float(lat)) dlong = Radians(F('longitude') - float(lon)) a = (Power(Sin(dlat / 2), 2) + Cos(Radians(float(lat))) * Cos(Radians(F('latitude'))) * Power(Sin(dlong / 2), 2)) c = 2 * ATan2(Sqrt(a), Sqrt(1 - a)) d = 6371 * c queryset = models.Store.objects.annotate(distance=d).filter( store_name__contains=name).exclude( Q(latitude=lat) & Q(longitude=lon)).order_by('distance') return queryset # 7. 지역, 장바구니(위도, 경도), 검색어 선택했을 때 elif region is not None and lat is not None and lon is not None and name is not None: dlat = Radians(F('latitude') - float(lat)) dlong = Radians(F('longitude') - float(lon)) a = (Power(Sin(dlat / 2), 2) + Cos(Radians(float(lat))) * Cos(Radians(F('latitude'))) * Power(Sin(dlong / 2), 2)) c = 2 * ATan2(Sqrt(a), Sqrt(1 - a)) d = 6371 * c queryset = models.Store.objects.annotate(distance=d).filter( address__contains=region, store_name__contains=name).exclude( Q(latitude=lat) & Q(longitude=lon)).order_by('distance') return queryset else: queryset = models.Store.objects.all().order_by("id") return queryset
def browse(request): """ To browse Bookmarks objects for given params and sorting order. @return Response of bookmarks objects list. """ params = request.query_params sort_by = params.get('sort_by', None) #Default sorting order if not sort_by: sort_by = 'customer_id' #Customer customer_id = params.get('customer_id', None) q_customer = Q() if customer_id: #Query Expression to get Bookmarks according to customer id. q_customer = Q(customer_id=customer_id) #Source name source_name = params.get('source_name', None) q_source_name = Q() if source_name: #Query Expression to get Bookmarks according to source name. q_source_name = Q(source_name__icontains=source_name) #Title title = params.get('title', None) q_title = Q() if title: #Query Expression to get Bookmarks according to title. q_title = Q(title__icontains=title) #Geo Location current_lat = params.get('lat', None) current_long = params.get('long', None) radius = params.get('radius', None) q_location = Q() if all(v is not None for v in [current_long, current_lat, radius]): """ Implementation of haversine formula to get customers whose geoloactions lies within the given radius taking the request param points as center location. a = sin²(Δφ/2) + cos φ1 ⋅ cos φ2 ⋅ sin²(Δλ/2) c = 2 ⋅ atan2( √a, √(1−a) ) d = 6371 #Earth radius """ current_lat = float(current_lat) current_long = float(current_long) dlat = Radians(F('latitude') - current_lat) dlong = Radians(F('longitude') - current_long) a = (Power(Sin(dlat / 2), 2) + Cos(Radians(current_lat)) * Cos(Radians(F('latitude'))) * Power(Sin(dlong / 2), 2)) c = 2 * ATan2(Sqrt(a), Sqrt(1 - a)) d = 6371 * c customers = Customer.objects.filter()\ .annotate(distance=d)\ .filter(distance__lt=radius) #Query expression for all customers lies within the radius to filter Bookmark model. q_location = Q(customer__in=customers) #Date Range start_date = params.get('start_date', None) end_date = params.get('end_date', None) q_date_range = Q() if start_date is not None and end_date is not None: start_date = datetime.strptime(start_date, "%Y-%m-%d") end_date = datetime.strptime(end_date, "%Y-%m-%d") end_date = end_date + timedelta(days=1) #Query Expression to get Bookmarks according to customer id. q_date_range = Q(created_at__range=(start_date, end_date)) #Filter and sort Bookmark model for the given query params. bookmarks = Bookmark.objects.filter(q_customer | q_source_name | q_title | q_date_range | q_location).order_by(sort_by) #Bookmark serializer serializer = BookmarkListSerializers(bookmarks, many=True) return Response(serializer.data)
def test_null(self): IntegerModel.objects.create() obj = IntegerModel.objects.annotate(null_cos=Cos('normal')).first() self.assertIsNone(obj.null_cos)
def get(self, request, kms): # Get user coordinates profile = MentorProfile.objects.get(user=request.user) latitude = float(profile.latitude) longitude = float(profile.longitude) radius = 6378.137 # this is in kms # Filter events by distance from user location using Great Circle formula events = Event.objects.annotate(distance=( radius * (2 * ATan2(Sqrt(Sin((Radians(F('latitude')) - Radians(latitude))/2) ** 2 + Cos(Radians(latitude)) * Cos(Radians(F('latitude'))) * Sin((Radians(F('longitude')) - Radians(longitude))/2)**2), Sqrt(1 - (Sin((Radians(F('latitude')) - Radians(latitude))/2) ** 2 + Cos(Radians(latitude)) * Cos(Radians(F('latitude'))) * Sin((Radians(F('longitude')) - Radians(longitude))/2)**2)))) )).filter(distance__lte=kms).order_by('distance')[:30] for event in events: print("EVENT DISTANCE", event.distance) serializer = EventSerializer(events, many=True) return Response(serializer.data)