Ordering queryset by distance relative to a given position

Question

I have a model containing latitude and longitude information as FloatFields.

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

I would like to order this model by an increasing distance from a given position(lat, lon) but can't seem to do so using F() expressions (using haversine library, I haven't succeeded in transforming them into float numbers)

Trader.objects.all().annotate(distance=haversine((lat, lon), (float(F('latitude')), float(F('longitude'))))).order_by('distance')

This query throws the following TypeError :

float() argument must be a string or a number

I have also tried using ExpressionWrapper with a FloatField() as output_field argument, without success.

How could I achieve that ?

Thank you in advance for any help !

What (type of data) does your function `F()` return? I suspect it is returning some sort of sequence (list, tuple,...). — Alan Hoover, Jul 29 '15 at 15:26
Does this answer your question? [Django sort by distance](https://stackoverflow.com/questions/19703975/django-sort-by-distance) — Francisco, Apr 01 '20 at 16:36

f43d65 · Accepted Answer · 2015-07-30T10:16:58.133

4

Func is new in Django 1.8.

from django.db.models import Func, F

class Sin(Func):
    function = 'SIN'

class Cos(Func):
    function = 'COS'

class Acos(Func):
    function = 'ACOS'

class Radians(Func):
    function = 'RADIANS'

radlat = Radians(latitude) # given latitude
radlong = Radians(longitude) # given longitude
radflat = Radians(F('latitude'))
radflong = Radians(F('longitude'))

Expression = 3959.0 * Acos(Cos(radlat) * Cos(radflat) *
                           Cos(radflong - radlong) +
                           Sin(radlat) * Sin(radflat))

Trader.objects.annotate(distance=Expression).order_by('distance')

Based on this post.

edited Jul 30 '15 at 10:16

answered Jul 30 '15 at 05:42

f43d65

314
2
7

After a lot of searching, your answer was the most helpful. To whom it may concern, I wrapped this code in an object manager class. It is available in this gist: https://gist.github.com/rchrd2/5e0b014640a459a14ef038975d2a3683 – rchrd2 Jan 02 '17 at 22:33

score 0 · Answer 2 · answered Jul 29 '15 at 17:49

Please note that https://docs.djangoproject.com/en/1.8/ref/models/expressions/#f-expressions "generates SQL" and float("generate sql") does not make sense. "generate sql" == use the value of the field in the generated sql. You would have to change the haversine function with something that knows how to translate itself to sql that does heavy calculations and is friends with django...

You would definitely have to get creative to solve this elegantly. Raw sql seems to be fastest way to get results if you can write the sql to do that calculation. Think about using geo extentions like postgis for example. You can think about precalculation if you have a small and fixed set of input (lat, lon) combinations or "precompute estimations" to be able to limit query result count and calculate and sort in python.

Ordering queryset by distance relative to a given position

2 Answers2