How to do arthmetic on Django 'F' types?

Question

I have an Event model, that, for the sake of simplicity only has a lat and lon FloatField().

From pythons native "geopy.distance" library, you can acquire the distance bewteen two coordinates like so:

from geopy.distance import distance
home = (33.28473, -117.20384)
event = (33.829384, -117.38932)
distance = distance(home, event).miles  #returns a float

In my application context I need to order all of the events by location. I attempted standard django annotation syntax:

Event.objects.all().annotate(distance=distance( (F('lat'),F('lon')), home ).miles).order_by('distance')

But I get the following error from the geopy library:

TypeError: float() argument must be a string or a number, not 'F'

Strangely enough, this works:

>>> def foo(x):
        return x * 2
>>> Event.objects.all().annotate(cutomField=foo(F('field')))

It seems that you can do arthmetic with F types but geopy does not seem to know this.

Is there anyway to turn an F type into a float so that geopy can process the field?

Which DB are you using? Django has decent support for PostgreSQL + PostGIS + GeoDjango — Iain Shelvington, Jun 22 '20 at 00:47
I would strongly suggest moving to PostgreSQL if you can. QuerySet annotation and ordering takes place at the DB level, you need a DB that supports geospatial functions/queries — Iain Shelvington, Jun 22 '20 at 00:53
@IainShelvington I agree but this is not a geospatial function. The principal of bringing a custom function into SQL and annotating/creating a float field. Is this possible? — Rage, Jun 22 '20 at 00:55
It **is** a geospatial function, it's returning the distance between two points on the earth. You need to define a custom database function that calls `st_distance_sphere` — Iain Shelvington, Jun 22 '20 at 01:07
@IainShelvington I've adjusted my question, please take a look — Rage, Jun 22 '20 at 01:14
You cannot pass an arbitrary function to a query, you can only call functions that the database supports. The example in your question using the `foo` function is equivalent to `annotate(cutomField=F('field') * 2)` which is supported since it is basic arithmetic. You are not passing/calling the function at the query level — Iain Shelvington, Jun 22 '20 at 01:20
@IainShelvington calculating distance between two coordinates is not considered basic arithmetic? — Rage, Jun 22 '20 at 01:27
no, it's not. Apparently Django now supports a MySQL geospatial backend https://docs.djangoproject.com/en/3.0/ref/contrib/gis/db-api/#geodjango-database-api — Iain Shelvington, Jun 22 '20 at 01:37
@IainShelvington That seems too intensive for my needs. I solved my problem by using the Cartesian formula for distance between two points (basic arithmetic). I just need a relative distance ordering and given that my range of coordinates are in the US and not anywhere near the poles, it suffices. — Rage, Jun 22 '20 at 02:36

score 1 · Accepted Answer · answered Jun 22 '20 at 14:43

Django supports negation, addition, subtraction, multiplication, division, modulo arithmetic, and the power operator on query expressions, using Python constants, variables, and even other expressions.

An F() object represents the value of a model field or annotated column. It makes it possible to refer to model field values and perform database operations using them without actually having to pull them out of the database into Python memory.

Instead, Django uses the F() object to generate an SQL expression that describes the required operation at the database level.

Above details are from django official documentation link

for eg 1.

def foo(x):
    return x*2

qs = MyModel.objects.filter().annotate(res=foo(F('colN')))
print(qs.query)

output:

SELECT col1, col2, ("model_table"."colN" * 2) AS "res" FROM "model_table"

eg 2(considering another function).

def foo_power(x):
    return x**2 ## POWER of 2

qs = MyModel.objects.filter().annotate(res=foo_power(F('colN')))
print(qs.query)

Output: SELECT col1, col2, (POWER("music_movie"."title",2)) AS "res" FROM "model_table"

Which shows calculation happens on database-side:

for geopy.distance case:

For custom set-calculation we need to write own Func, with the help of django built-in database functions. can be followed https://docs.djangoproject.com/en/3.0/ref/models/expressions/#func-expressions

Is there anyway to turn an F type into a float so that geopy can process the field?

No, you can not type cast F() i.e. <class 'django.db.models.expressions.F'> to float.

Because:

def foo_power(x):
    print(type(x), x)
    return x**2 ## POWER of 2

qs = MyModel.objects.filter().annotate(res=foo_power(F('colN')))

Output: <class 'django.db.models.expressions.F'> F(title)

How to do arthmetic on Django 'F' types?

1 Answers1

Linked