run haversine formula on MariaDB server using annotate() in django orm

Question

Hii I trying to run the Haversine formula on the MariaDB base on my model
the model is

class MeteorologicalSite(models.Model):
    lat = models.DecimalField("Latitude", max_digits=17, decimal_places=15)
    lon = models.DecimalField("Longitude", max_digits=17, decimal_places=15)  
class Site(models.Model):
    lat = models.DecimalField("Latitude", max_digits=17, decimal_places=15)
    lon = models.DecimalField("Longitude", max_digits=17, decimal_places=15)

and this is the Haversine function

def Haversine_formula(self, site):
    from django.db.models.functions import Cos, Sin, ASin, Sqrt, Radians
    lat1 = Radians(site.lat)
    lon1 = Radians(site.lon)
    lat2 = Radians(F("lat"))
    lon2 = Radians(F("lon"))
    r = 6372.8
    sql_haversine_formula = 2 * r * ASin( 
        Sqrt(

            Sin((lat1-lat2)/2)**2+
            Cos(lat1)*
            Cos(lat2)*
            Sin((lon1 - lon2)/2)**2
            )
        )
    MeteorologicalSite.objects.filter(radiation=True)\
        .annotate(mycolumn=sql_haversine_formula)

and it doesn't run it return <django.db.models.query.QuerySet object at 0xffff57b99ca0>
I tried to use lat and lon for 1 and 2 as Decimal directly and it still doesn't work
so I understand that my problem is in the way I use annotate or in me sql_haversine_formula
Does anyone have an idea why this does not work?

sorry for my English

Try this orm query: MeteorologicalSite.objects.annotate(mycolumn=sql_haversine_formula) — , Sep 07 '21 at 10:11
Try with values: MeteorologicalSite.objects.annotate(mycolumn=sql_haversine_formula).values('mycolumn') — , Sep 07 '21 at 10:38
I print the function and it print `raise FieldError( django.core.exceptions.FieldError: Expression contains mixed types: FloatField, IntegerField. You must set output_field.` — Saarsa, Sep 07 '21 at 11:06
so I tired to print `MeteorologicalSite.objects.filter(radiation=True).annotate(mycolumn=models.Value(sql_haversine_formula, output_field=models.DecimalField()))` and it print `django.core.exceptions.ValidationError: ['“Value(12745.6) * ASin(Sqrt(Sin(Radians(Value(1)) - Radians(Value(1)) / Value(2)) ^ Value(2) + Cos(Radians(Value(1))) * Cos(Radians(Value(1))) * Sin(Value(0.0)) ^ Value(2)))” value must be a decimal number.']` — Saarsa, Sep 07 '21 at 11:08
import field as: from django.db.models import FloatField and then try this query: MeteorologicalSite.objects.filter(radiation=True).annotate(mycolumn=models.Value(sql_haversine_formula, output_field=FloatField())) — , Sep 07 '21 at 11:24

score 0 · Answer 1 · answered Sep 08 '21 at 01:01

MariaDB support ST_Distance_Sphere (which is the Haversine formula). (take note of supported versions, its fairly new as of time of this post)

Using Func you can wrap this up for Django:

from django.db.models import Func

class Distance_Sphere(Func):
    function = 'ST_DISTANCE_SPHERE'

With this you should be able to use it in queries.

Credit: derived from this answer

run haversine formula on MariaDB server using annotate() in django orm

1 Answers1