Django order by Min Distance to a list of points

user_2985601 0
Viewed 453

I want to get a queryset of all Jobs that are within a given distance to at least one of the many provided locations, order them by the minimum distance, and do not show duplicate jobs. 

from django.db import models
from cities.models import City    

class Job(models.Model):
    title = models.CharField(max_length=255)
    cities = models.ManyToManyField(City)

If there is only one point I could do this as:

from django.contrib.gis.db.models.functions import Distance
from django.contrib.gis.geos import Point
point = Point(x, y, srid=4326)  
Job.objects.filter(cities__location__dwithin=(point, dist)) \
           .annotate(distance=Distance("cities__location", point) \
           .order_by('distance')

but when I have many points I build out a Q expression for the filter but am unsure of a clean way to annotate the Min distance of the Job to all points

query = Q()
for point in points:
    query |= Q(cities__location__dwithin=(point, dist))
Job.objects.filter(query).annotate(distance=Min(...)).order_by('distance')

FYI using postgres 12.1 with PostGIS extension

postgis django-queryset postgresql django python
1 Answers
query = Q()
distances = []
for point in points:
    query |= Q(cities__location__dwithin=(point, dist))
    distances.append(Distance("cities__location", point))

# LEAST requires 2 or more expressions, MIN works for single expression
if len(distances) == 1:
    MIN_FUNC = Min
else:
    MIN_FUNC = Least

Job.objects.filter(query).annotate(distance=MIN_FUNC(*distances)).order_by('distance')
  • MIN is an aggregation function that takes a single expression, such as a column name, and reduces multiple inputs to a single output value
  • LEAST is a conditional expression that functions by selecting the smallest value from a list of any number of expressions

https://docs.djangoproject.com/en/3.0/ref/models/querysets/#min https://docs.djangoproject.com/en/3.0/ref/models/database-functions/#least

user_29856010
Related