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 is_available(self,
ignore_cart=None,
ignore_orderpos=None,
ignore_voucher_id=None,
sales_channel='web',
ignore_distancing=False,
distance_ignore_cart_id=None):
from .orders import Order
if self.blocked and sales_channel not in self.event.settings.seating_allow_blocked_seats_for_channel:
return False
opqs = self.orderposition_set.filter(
order__status__in=[Order.STATUS_PENDING, Order.STATUS_PAID],
canceled=False)
cpqs = self.cartposition_set.filter(expires__gte=now())
vqs = self.vouchers.filter(
Q(Q(valid_until__isnull=True) | Q(valid_until__gte=now()))
& Q(redeemed__lt=F('max_usages')))
if ignore_cart and ignore_cart is not True:
cpqs = cpqs.exclude(pk=ignore_cart.pk)
if ignore_orderpos:
opqs = opqs.exclude(pk=ignore_orderpos.pk)
if ignore_voucher_id:
vqs = vqs.exclude(pk=ignore_voucher_id)
if opqs.exists() or (ignore_cart is not True
and cpqs.exists()) or vqs.exists():
return False
if self.event.settings.seating_minimal_distance > 0 and not ignore_distancing:
ev = (self.subevent or self.event)
qs_annotated = Seat.annotated(
ev.seats,
self.event_id,
self.subevent,
ignore_voucher_id=ignore_voucher_id,
minimal_distance=0,
ignore_order_id=ignore_orderpos.order_id
if ignore_orderpos else None,
ignore_cart_id=(
distance_ignore_cart_id
or (ignore_cart.cart_id if ignore_cart else None)))
qs_closeby_taken = qs_annotated.annotate(distance=(
Power(F('x') - Value(self.x),
Value(2),
output_field=models.FloatField()) +
Power(F('y') - Value(self.y),
Value(2),
output_field=models.FloatField())
)).exclude(pk=self.pk).filter(
Q(has_order=True) | Q(has_cart=True) | Q(has_voucher=True),
distance__lt=self.event.settings.seating_minimal_distance**2)
if self.event.settings.seating_distance_within_row:
qs_closeby_taken = qs_closeby_taken.filter(
row_name=self.row_name)
if qs_closeby_taken.exists():
return False
return True
def test_null(self):
IntegerModel.objects.create(big=100)
obj = IntegerModel.objects.annotate(
null_power_small=Power('small', 'normal'),
null_power_normal=Power('normal', 'big'),
).first()
self.assertIsNone(obj.null_power_small)
self.assertIsNone(obj.null_power_normal)
def annotated(cls, qs, event_id, subevent, ignore_voucher_id=None, minimal_distance=0,
ignore_order_id=None, ignore_cart_id=None, distance_only_within_row=False):
from . import CartPosition, Order, OrderPosition, Voucher
vqs = Voucher.objects.filter(
event_id=event_id,
subevent=subevent,
seat_id=OuterRef('pk'),
redeemed__lt=F('max_usages'),
).filter(
Q(valid_until__isnull=True) | Q(valid_until__gte=now())
)
if ignore_voucher_id:
vqs = vqs.exclude(pk=ignore_voucher_id)
opqs = OrderPosition.objects.filter(
order__event_id=event_id,
subevent=subevent,
seat_id=OuterRef('pk'),
order__status__in=[Order.STATUS_PENDING, Order.STATUS_PAID]
)
if ignore_order_id:
opqs = opqs.exclude(order_id=ignore_order_id)
cqs = CartPosition.objects.filter(
event_id=event_id,
subevent=subevent,
seat_id=OuterRef('pk'),
expires__gte=now()
)
if ignore_cart_id:
cqs = cqs.exclude(cart_id=ignore_cart_id)
qs_annotated = qs.annotate(
has_order=Exists(
opqs
),
has_cart=Exists(
cqs
),
has_voucher=Exists(
vqs
)
)
if minimal_distance > 0:
# TODO: Is there a more performant implementation on PostgreSQL using
# https://www.postgresql.org/docs/8.2/functions-geometry.html ?
sq_closeby = qs_annotated.annotate(
distance=(
Power(F('x') - OuterRef('x'), Value(2), output_field=models.FloatField()) +
Power(F('y') - OuterRef('y'), Value(2), output_field=models.FloatField())
)
).filter(
Q(has_order=True) | Q(has_cart=True) | Q(has_voucher=True),
distance__lt=minimal_distance ** 2
)
if distance_only_within_row:
sq_closeby = sq_closeby.filter(row_name=OuterRef('row_name'))
qs_annotated = qs_annotated.annotate(has_closeby_taken=Exists(sq_closeby))
return qs_annotated
def test_null(self):
IntegerModel.objects.create(big=100)
obj = IntegerModel.objects.annotate(
null_power_small=Power("small", "normal"),
null_power_normal=Power("normal", "big"),
null_power_big=Power("big", "normal"),
).first()
self.assertIsNone(obj.null_power_small)
self.assertIsNone(obj.null_power_normal)
self.assertIsNone(obj.null_power_big)
def _front_page(paging_size=settings.PAGING_SIZE, page=0, add_filter={}, add_q=[], as_of=None, days_back=5000):
# TODO: weighting https://medium.com/hacking-and-gonzo/how-hacker-news-ranking-algorithm-works-1d9b0cf2c08d
# (P-1) / (T+2)^G
if as_of is None:
now = timezone.now()
else:
now = as_of
if connection.vendor == 'postgresql':
now_value = Value(now, output_field=fields.DateTimeField())
submission_age_float = ExpressionWrapper(
(now_value - F('created_at')), output_field=fields.DurationField())
submission_age_hours = ExpressionWrapper(
Extract(F('tf'), 'epoch') / 60 / 60 + 2.1, output_field=fields.FloatField())
real_p = ExpressionWrapper(
F('points') - 1, output_field=fields.FloatField())
formula = ExpressionWrapper(
F('p') / (Power(F('tfh'), F('g')) + 0.001), output_field=fields.FloatField())
return Story.objects.select_related('user')\
.filter(duplicate_of__isnull=True)\
.filter(points__gte=1) \
.filter(created_at__gte=now - datetime.timedelta(days=days_back)) \
.filter(created_at__lte=now) \
.filter(**add_filter) \
.annotate(tf=submission_age_float) \
.annotate(tfh=submission_age_hours) \
.annotate(p=real_p) \
.annotate(g=Value(1.8, output_field=fields.FloatField())) \
.annotate(formula=formula) \
.order_by('-formula')[(page*paging_size):(page+1)*(paging_size)]
elif connection.vendor == 'sqlite':
now_value = Value(now, output_field=fields.DateTimeField())
submission_age_float = ExpressionWrapper(
(now_value - F('created_at')), output_field=fields.FloatField())
submission_age_hours = ExpressionWrapper(
F('tf') / 60 / 60 / 1000000 + 2.1, output_field=fields.FloatField())
real_p = ExpressionWrapper(
F('points') - 1, output_field=fields.FloatField())
formula = ExpressionWrapper(
F('p') / (Power(F('tfh'), F('g')) + 0.001), output_field=fields.FloatField())
return Story.objects.select_related('user')\
.filter(duplicate_of__isnull=True)\
.filter(points__gte=1) \
.filter(created_at__gte=now - datetime.timedelta(days=days_back)) \
.filter(created_at__lte=now) \
.filter(**add_filter) \
.annotate(tf=submission_age_float) \
.annotate(tfh=submission_age_hours) \
.annotate(p=real_p) \
.annotate(g=Value(1.8, output_field=fields.FloatField())) \
.annotate(formula=formula) \
.order_by('-formula')[(page*paging_size):(page+1)*(paging_size)]
else:
raise NotImplementedError(
"No frontpage magic for database engine %s implemented" % (connection.vendor))
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=-1, normal=20, big=3)
obj = IntegerModel.objects.annotate(
small_power=Power('small', 'normal'),
normal_power=Power('normal', 'big'),
big_power=Power('big', 'small'),
).first()
self.assertIsInstance(obj.small_power, float)
self.assertIsInstance(obj.normal_power, float)
self.assertIsInstance(obj.big_power, float)
self.assertAlmostEqual(obj.small_power, obj.small**obj.normal)
self.assertAlmostEqual(obj.normal_power, obj.normal**obj.big)
self.assertAlmostEqual(obj.big_power, obj.big**obj.small)
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 get_queryset(self):
active = Count("optionwheel", filter=Q(optionwheel__is_active=True))
completed = Count("optionwheel",
filter=Q(optionwheel__is_active=False))
profit = Sum(F("optionwheel__total_profit") *
F("optionwheel__quantity"),
filter=Q(optionwheel__is_active=False),
output_field=fields.DecimalField())
collateral = Sum(F("optionwheel__collatoral") *
F("optionwheel__quantity"),
filter=Q(optionwheel__is_active=False),
output_field=fields.DecimalField())
total_wheels = Sum(F("optionwheel__quantity"),
filter=Q(optionwheel__is_active=False))
total_days_active_weighted_by_collateral = Sum(
F("optionwheel__total_days_active") * F("optionwheel__quantity") *
F("optionwheel__collatoral"),
filter=Q(optionwheel__is_active=False))
average_days = Cast(total_days_active_weighted_by_collateral,
fields.FloatField()) / Cast(
collateral, fields.FloatField())
annualized_rate_of_return = Power(
1 + profit / collateral,
BUSINESS_DAYS_IN_YEAR / Coalesce(average_days, 252))
users = User.objects.annotate(
active=active,
completed=completed,
profit=Round(100 * Coalesce(profit, 0)),
collateral=Round(100 * Coalesce(collateral, 0)),
return_percentage=Coalesce(profit / collateral, 0),
total_wheels=Coalesce(total_wheels, 0),
average_days=Coalesce(average_days, 0),
annualized_rate_of_return=Coalesce(annualized_rate_of_return, 0),
)
return users
def get_queryset(self):
"""
Optionally restricts the returned purchases to a given user,
by filtering against a `username` query parameter in the URL.
"""
# Add upvote count.
queryset = Submission.objects.annotate(upvote_count=Count("upvote"))
user_pk = self.request.user.pk if self.request.user.is_authenticated else None
if self.request.user.is_authenticated:
queryset = queryset.annotate(upvoted=Exists(
Upvote.objects.filter(
user=self.request.user.pk,
submission=OuterRef("pk"),
)))
else:
queryset = queryset.annotate(upvoted=Value(False, BooleanField()))
# Filter by username if required.
username = self.request.query_params.get("username", None)
if username is not None:
queryset = queryset.filter(user__github_id=username)
ordering = self.request.query_params.get("ordering", None)
if ordering == "hot":
# Hotness algorithm: https://medium.com/hacking-and-gonzo/how-hacker-news-ranking-algorithm-works-1d9b0cf2c08d
gravity = 1.8
class Age(Func):
template = "(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM %(expressions)s))/3600"
queryset = queryset.annotate(hotness=ExpressionWrapper(
F("upvote_count") /
Power(Age("created_at", output_field=FloatField()), gravity),
output_field=FloatField(),
))
queryset = queryset.order_by("-hotness")
elif ordering == "top":
queryset = queryset.order_by("-upvote_count")
elif ordering == "new":
queryset = queryset.order_by("-created_at")
return queryset
def disagreements_for_user(user_preferences, top=20,
threshold_mse_normed=0.12):
"""Get top disagreements between the model and the representative."""
qs = ExpertRating.objects.all()
qs = qs.filter(user=user_preferences)
qs = qs.filter(video_1__videorating__user=user_preferences,
video_2__videorating__user=user_preferences)
# adding video scores
annotations = {}
for vid in [1, 2]:
for f in VIDEO_FIELDS:
annotations[f'video_{vid}_score_{f}'] = F(f'video_{vid}__videorating__{f}')
qs = qs.annotate(**annotations)
# computing model pairwise rating
annotations = {}
for f in VIDEO_FIELDS:
# 1.0 / (1 + Math.exp(modelV1[feature] - modelV2[feature]))
one = Value(1., output_field=FloatField())
def exp(x):
return Func(x, function='EXP')
delta = F(f'video_1_score_{f}') - F(f'video_2_score_{f}')
delta = one + exp(delta)
delta = one / delta
annotations[f'model_{f}_01'] = delta
qs = qs.annotate(**annotations)
# scaling up/down
annotations = {}
for f in VIDEO_FIELDS:
annotations[f'model_{f}'] = Value(MAX_VALUE, output_field=FloatField())\
* F(f'model_{f}_01')
annotations[f'{f}_01'] = F(f) / Value(MAX_VALUE, output_field=FloatField())
qs = qs.annotate(**annotations)
# computing disagreement and thresholding...
annotations = {}
for f in VIDEO_FIELDS:
annotations[f'mse_{f}_01'] = Power(F(f'model_{f}_01') - F(f'{f}_01'),
Value(2.0, output_field=FloatField()))
qs = qs.annotate(**annotations)
# sorting over all fields and returning top 20
qs_fs = []
tot_count = 0
for f in VIDEO_FIELDS:
qs_f = qs.filter(**{f'mse_{f}_01__gte': threshold_mse_normed})
tot_count += qs_f.count()
qs_f = qs_f.order_by(f'-mse_{f}_01')
qs_f = qs_f[:top]
qs_f = qs_f.annotate(mse_01=F(f'mse_{f}_01'),
feature=Value(f, output_field=CharField()),
model_score=F(f'model_{f}'),
rating_score=F(f))
qs_fs.append(list(qs_f))
qs_f_all = [x for qs in qs_fs for x in qs]
qs_f_all = sorted(qs_f_all, key=lambda x: -x.mse_01)
# return qs_f_all
qs_f_all = qs_f_all[:top]
for item in qs_f_all:
item.video_1__video_id = item.video_1.video_id
item.video_2__video_id = item.video_2.video_id
return {'count': tot_count,
'results': qs_f_all}
def test_decimal(self):
DecimalModel.objects.create(n1=Decimal('1.0'), n2=Decimal('-0.6'))
obj = DecimalModel.objects.annotate(n_power=Power('n1', 'n2')).first()
self.assertIsInstance(obj.n_power, Decimal)
self.assertAlmostEqual(obj.n_power, Decimal(obj.n1**obj.n2))
def is_available(self,
ignore_cart=None,
ignore_orderpos=None,
ignore_voucher_id=None,
sales_channel='web',
ignore_distancing=False,
distance_ignore_cart_id=None):
from .orders import Order
if self.blocked and sales_channel not in self.event.settings.seating_allow_blocked_seats_for_channel:
return False
opqs = self.orderposition_set.filter(
order__status__in=[Order.STATUS_PENDING, Order.STATUS_PAID],
canceled=False)
cpqs = self.cartposition_set.filter(expires__gte=now())
vqs = self.vouchers.filter(
Q(Q(valid_until__isnull=True) | Q(valid_until__gte=now()))
& Q(redeemed__lt=F('max_usages')))
if ignore_cart and ignore_cart is not True:
cpqs = cpqs.exclude(pk=ignore_cart.pk)
if ignore_orderpos:
opqs = opqs.exclude(pk=ignore_orderpos.pk)
if ignore_voucher_id:
vqs = vqs.exclude(pk=ignore_voucher_id)
if opqs.exists() or (ignore_cart is not True
and cpqs.exists()) or vqs.exists():
return False
if self.event.settings.seating_minimal_distance > 0 and not ignore_distancing:
ev = (self.subevent or self.event)
qs_annotated = Seat.annotated(
ev.seats,
self.event_id,
self.subevent,
ignore_voucher_id=ignore_voucher_id,
minimal_distance=0,
ignore_order_id=ignore_orderpos.order_id
if ignore_orderpos else None,
ignore_cart_id=(distance_ignore_cart_id
or (ignore_cart.cart_id if ignore_cart
and ignore_cart is not True else None)))
q = Q(has_order=True) | Q(has_voucher=True)
if ignore_cart is not True:
q |= Q(has_cart=True)
# The following looks like it makes no sense. Why wouldn't we just use ``Value(self.x)``, we already now
# the value? The reason is that x and y are floating point values generated from our JSON files. As it turns
# out, PostgreSQL MIGHT store floating point values with a different precision based on the underlying system
# architecture. So if we generate e.g. 670.247128887222289 from the JSON file and store it to the database,
# PostgreSQL will store it as 670.247128887222289 internally. However if we query it again, we only get
# 670.247128887222 back. But if we do calculations with a field in PostgreSQL itself, it uses the full
# precision for the calculation.
# We don't actually care about the results with this precision, but we care that the results from this
# function are exactly the same as from event.free_seats(), so we do this subquery trick to deal with
# PostgreSQL's internal values in both cases.
# In the long run, we probably just want to round the numbers on insert...
# See also https://www.postgresql.org/docs/11/runtime-config-client.html#GUC-EXTRA-FLOAT-DIGITS
self_x = Subquery(Seat.objects.filter(pk=self.pk).values('x'))
self_y = Subquery(Seat.objects.filter(pk=self.pk).values('y'))
qs_closeby_taken = qs_annotated.annotate(distance=(Power(
F('x') - self_x, Value(2), output_field=models.FloatField()
) + Power(
F('y') - self_y, Value(2), output_field=models.FloatField()
))).exclude(pk=self.pk).filter(
q,
distance__lt=self.event.settings.seating_minimal_distance**2)
if self.event.settings.seating_distance_within_row:
qs_closeby_taken = qs_closeby_taken.filter(
row_name=self.row_name)
if qs_closeby_taken.exists():
return False
return True
def test_float(self):
FloatModel.objects.create(f1=2.3, f2=1.1)
obj = FloatModel.objects.annotate(f_power=Power('f1', 'f2')).first()
self.assertIsInstance(obj.f_power, float)
self.assertAlmostEqual(obj.f_power, obj.f1**obj.f2)
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 test_decimal(self):
DecimalModel.objects.create(n1=Decimal("1.0"), n2=Decimal("-0.6"))
obj = DecimalModel.objects.annotate(n_power=Power("n1", "n2")).first()
self.assertIsInstance(obj.n_power, Decimal)
self.assertAlmostEqual(obj.n_power, Decimal(obj.n1**obj.n2))
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)
