Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Django sort by distance

I have the following model:

class Vacancy(models.Model):
    lat = models.FloatField('Latitude', blank=True)
    lng = models.FloatField('Longitude', blank=True)

How should I make a query to sort by distance (distance is infinity)?

Working on PosgreSQL, GeoDjango if it is required.

like image 508
Rukomoynikov Avatar asked Oct 31 '13 10:10

Rukomoynikov


3 Answers

the .distance(ref_location) is removed in django >=1.9 you should use an annotation instead.

from django.contrib.gis.db.models.functions import Distance
from django.contrib.gis.measure import D
from django.contrib.gis.geos import Point

ref_location = Point(1.232433, 1.2323232, srid=4326)
yourmodel.objects.filter(location__distance_lte=(ref_location, D(m=2000)))                                                     
    .annotate(distance=Distance("location", ref_location))                                                                
    .order_by("distance")

also you should narrow down your search with the dwithin operator which uses the spatial index, distance does not use the index which slows your query down:

yourmodel.objects.filter(location__dwithin=(ref_location, 0.02))
    .filter(location__distance_lte=(ref_location, D(m=2000)))
    .annotate(distance=Distance('location', ref_location))
    .order_by('distance')

see this post for an explanation of location__dwithin=(ref_location, 0.02)

like image 160
cleder Avatar answered Oct 19 '22 19:10

cleder


Here is a solution that does not require GeoDjango.

from django.db import models
from django.db.models.expressions import RawSQL


class Location(models.Model):
    latitude = models.FloatField()
    longitude = models.FloatField()
    ...


def get_locations_nearby_coords(latitude, longitude, max_distance=None):
    """
    Return objects sorted by distance to specified coordinates
    which distance is less than max_distance given in kilometers
    """
    # Great circle distance formula
    gcd_formula = "6371 * acos(least(greatest(\
    cos(radians(%s)) * cos(radians(latitude)) \
    * cos(radians(longitude) - radians(%s)) + \
    sin(radians(%s)) * sin(radians(latitude)) \
    , -1), 1))"
    distance_raw_sql = RawSQL(
        gcd_formula,
        (latitude, longitude, latitude)
    )
    qs = Location.objects.all() \
    .annotate(distance=distance_raw_sql) \
    .order_by('distance')
    if max_distance is not None:
        qs = qs.filter(distance__lt=max_distance)
    return qs

Use as follow:

nearby_locations = get_locations_nearby_coords(48.8582, 2.2945, 5)

If you are using sqlite you need to add somewhere

import math
from django.db.backends.signals import connection_created
from django.dispatch import receiver


@receiver(connection_created)
def extend_sqlite(connection=None, **kwargs):
    if connection.vendor == "sqlite":
        # sqlite doesn't natively support math functions, so add them
        cf = connection.connection.create_function
        cf('acos', 1, math.acos)
        cf('cos', 1, math.cos)
        cf('radians', 1, math.radians)
        cf('sin', 1, math.sin)
        cf('least', 2, min)
        cf('greatest', 2, max)
like image 21
rphlo Avatar answered Oct 19 '22 21:10

rphlo


Note: Please check cleder's answer below which mentions about deprecation issue (distance -> annotation) in Django versions.

First of all, it is better to make a point field instead of making lat and lnt separated:

from django.contrib.gis.db import models

location = models.PointField(null=False, blank=False, srid=4326, verbose_name='Location')

Then, you can filter it like that:

from django.contrib.gis.geos import Point
from django.contrib.gis.measure import D

distance = 2000 
ref_location = Point(1.232433, 1.2323232)

res = YourModel.objects.filter(
    location__distance_lte=(
        ref_location,
        D(m=distance)
    )
).distance(
    ref_location
).order_by(
    'distance'
)
like image 44
cem Avatar answered Oct 19 '22 21:10

cem