CODEWITHSUNDEEP
pythonmysqldjangoormgeospatial
Ambroise
Ambroise

Reputation: 1659

Ordering queryset by distance relative to a given position

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 !

Upvotes: 6

Views: 1187

Answers (2)

f43d65
f43d65

Reputation: 304

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.

Upvotes: 4

Lyudmil Nenov
Lyudmil Nenov

Reputation: 257

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.

Upvotes: 0

Related Questions