def get(self, request):
start_time = time.time()
_lat = float(request.query_params.get('latitude', 35.1874726))
_lon = float(request.query_params.get('longitude', 126.900115))
inputcate = request.query_params.get('category', '한식')
catelist = list(inputcate.split(','))
for ind, k in enumerate(catelist):
if catelist[ind] == '음식점':
catelist.pop(ind)
catelist.extend(
['중식', '술집', '한식', '카페', '분식', '세계음식', '양식', '뷔페', '일식'])
zoom = int(request.query_params.get('zoomlevel', 14))
searchrange = (15.9 * (2**(19 - zoom)) * 2) / 1000
dlat = Radians(F('latitude') - _lat)
dlong = Radians(F('longitude') - _lon)
a = (Power(Sin(dlat / 2), 2) + Cos(Radians(_lat)) *
Cos(Radians(F('latitude'))) * Power(Sin(dlong / 2), 2))
c = 2 * ATan2(Sqrt(a), Sqrt(1 - a))
d = 6371 * c
te = Location.objects.select_related('category').annotate(
distance=d).filter(distance__lt=searchrange).filter(
category__name__in=catelist).order_by('rank')
if (te.count() > 50):
te = te[:50]
serializer = LocationSerializer(te, many=True)
return Response(serializer.data)
def test_decimal(self):
DecimalModel.objects.create(n1=Decimal('-12.9'), n2=Decimal('0.6'))
obj = DecimalModel.objects.annotate(n1_radians=Radians('n1'), n2_radians=Radians('n2')).first()
self.assertIsInstance(obj.n1_radians, Decimal)
self.assertIsInstance(obj.n2_radians, Decimal)
self.assertAlmostEqual(obj.n1_radians, Decimal(math.radians(obj.n1)))
self.assertAlmostEqual(obj.n2_radians, Decimal(math.radians(obj.n2)))
def annotate_distance(self, point, units=None):
lat_1 = point['latitude']
lon_1 = point['longitude']
lat_2 = F('latitude')
lon_2 = F('longitude')
phi_1 = Radians(lat_1)
phi_2 = Radians(lat_2)
delta_phi = Radians(lat_2 - lat_1)
delta_lambda = Radians(lon_2 - lon_1)
a = Power(Sin(delta_phi / 2), 2) +\
Cos(phi_1) * Cos(phi_2) *\
Power(Sin(delta_lambda / 2), 2)
c = 2 * ASin(Sqrt(a))
units = DEFAULT_UNITS if units is None else units
r = EARTH_RADIUS / METERS_IN[units]
d = c * r
return self.exclude(latitude=None).exclude(longitude=None).annotate(
distance=d)
def calculate_distance1(point1, point2):
"""
Giving two points using Haversine distance
Calculate the distance with the api view class that is used for suggestions endpoint.
and filter the view using Django's Database functions.
Note: Used only with view to avoid CombinedExpression errors which is generated when using with serailizer
Writing it out with Django's Database functions.
In contrast to raw SQL,
this also gives the advantage of being able to easily append/prepend other ORM filters.
"""
# Assign the vlues to these called variables to reflect the function's usage
current_lat = point1[0]
current_long = point1[1]
searched_lat = point2[0]
searched_long = point2[1]
dlat = Radians(current_lat - searched_lat)
dlong = Radians(current_long - searched_long)
a = (Power(Sin(dlat / 2), 2) + Cos(Radians(searched_lat)) *
Cos(Radians(current_lat)) * Power(Sin(dlong / 2), 2))
c = 2 * ATan2(Sqrt(a), Sqrt(1 - a))
d = 6371 * c
return d
def test_float(self):
FloatModel.objects.create(f1=-27.5, f2=0.33)
obj = FloatModel.objects.annotate(f1_radians=Radians('f1'), f2_radians=Radians('f2')).first()
self.assertIsInstance(obj.f1_radians, float)
self.assertIsInstance(obj.f2_radians, float)
self.assertAlmostEqual(obj.f1_radians, math.radians(obj.f1))
self.assertAlmostEqual(obj.f2_radians, math.radians(obj.f2))
def test_decimal(self):
DecimalModel.objects.create(n1=Decimal("-12.9"), n2=Decimal("0.6"))
obj = DecimalModel.objects.annotate(n1_radians=Radians("n1"),
n2_radians=Radians("n2")).first()
self.assertIsInstance(obj.n1_radians, Decimal)
self.assertIsInstance(obj.n2_radians, Decimal)
self.assertAlmostEqual(obj.n1_radians, Decimal(math.radians(obj.n1)))
self.assertAlmostEqual(obj.n2_radians, Decimal(math.radians(obj.n2)))
def get_queryset(self):
dlat = Radians(F('branch__location__lati') - self.request.user.lati)
dlong = Radians(F('branch__location__lngt') - self.request.user.lngt)
a = (Power(Sin(dlat / 2), 2) + Cos(Radians(self.request.user.lati)) *
Cos(Radians(F('branch__location__lati'))) *
Power(Sin(dlong / 2), 2))
c = 2 * ATan2(Sqrt(a), Sqrt(1 - a))
d = 6371 * c
queryset = Product.objects.annotate(
distance=d).order_by('distance').filter(distance__lt=50)[:50]
return queryset
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 test_integer(self):
IntegerModel.objects.create(small=-20, normal=15, big=-1)
obj = IntegerModel.objects.annotate(
small_radians=Radians('small'),
normal_radians=Radians('normal'),
big_radians=Radians('big'),
).first()
self.assertIsInstance(obj.small_radians, float)
self.assertIsInstance(obj.normal_radians, float)
self.assertIsInstance(obj.big_radians, float)
self.assertAlmostEqual(obj.small_radians, math.radians(obj.small))
self.assertAlmostEqual(obj.normal_radians, math.radians(obj.normal))
self.assertAlmostEqual(obj.big_radians, math.radians(obj.big))
def _dummyinput(self):
print('dummydata를 input합니다.')
User.objects.all().delete()
models.CourseTotal.objects.all().delete()
models.CourseDetail.objects.all().delete()
_lat = 35.1874726
_lon = 126.900115
searchrange = (15.9 * (2**(19 - 11.5)) * 2) / 1000
dlat = Radians(F('latitude') - _lat)
dlong = Radians(F('longitude') - _lon)
a = (Power(Sin(dlat / 2), 2) + Cos(Radians(_lat)) *
Cos(Radians(F('latitude'))) * Power(Sin(dlong / 2), 2))
c = 2 * ATan2(Sqrt(a), Sqrt(1 - a))
d = 6371 * c
te = models.Location.objects.select_related('category').annotate(
distance=d).filter(distance__lt=searchrange).order_by('rank')
getlist = list(te.values_list('id', flat=True))
for i in range(10):
print(f'{i+1}회 시작')
login_email = f'test{i+1}@test.com'
age = random.choice(range(20, 50))
random_plate = []
for j in range(10):
random_plate.extend([j + 1] * random.randint(1, 5))
testuser = User.objects.create_user(email=login_email,
password='******',
nickname=f'test{i+1}',
gender='남',
age=age)
for k in range(100):
taste = random.choice(random_plate)
tour = random.choice(random_plate)
activity = random.choice(random_plate)
N = random.choice(range(2, 6))
days = random.choice(range(1, 30))
input_days = datetime.date(2020, 10, days)
coursetotal = models.CourseTotal.objects.create(
user_id=testuser.id,
taste=taste,
tour=tour,
activity=activity,
traveldate=input_days)
location_sample = random.sample(getlist, N)
for ind, location_id in enumerate(location_sample):
models.CourseDetail.objects.create(
location_id=location_id,
order=ind + 1,
coursetotal_id=coursetotal.id)
print(f'{i+1}회 Done')
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 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)
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_haversine_distance_annotation(current_lat, current_long):
"""
This uses the ‘haversine’ formula to calculate the great-circle distance
between two points – that is, the shortest distance over the earth’s
surface – giving an ‘as-the-crow-flies’ distance between the points
(ignoring any hills they fly over, of course!).
Haversine formula:
a = sin²(Δφ/2) + cos φ1 ⋅ cos φ2 ⋅ sin²(Δλ/2)
c = 2 ⋅ atan2( √a, √(1−a) )
d = R ⋅ c
where φ is latitude, λ is longitude,
R is earth’s radius (mean radius = 6,371km);
note that angles need to be in radians to pass to trig functions!
"""
R = 6371.0088
dlat = Radians(F('latitude')) - Radians(current_lat)
dlong = Radians(F('longitude')) - Radians(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 = R * c
return d
def test_radians(self):
"""
Tests radians function applied to a column.
"""
q1 = Author.objects.values("num").annotate(num_radians=Radians("num"))
compiler = SQLCompiler(q1.query, self.connection, "default")
sql_query, params = compiler.query.as_sql(compiler, self.connection)
self.assertEqual(
sql_query,
"SELECT tests_author.num, ((tests_author.num) * 3.141592653589793 "
"/ 180) AS num_radians FROM tests_author",
)
self.assertEqual(params, ())
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 test_null(self):
IntegerModel.objects.create()
obj = IntegerModel.objects.annotate(
null_radians=Radians('normal')).first()
self.assertIsNone(obj.null_radians)
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)