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)