CODEWITHSUNDEEP
djangodjango-orm
wkrueger
wkrueger

Reputation: 1363

Django ORM: how do I apply a function over an aggregate result?

I want to do

SELECT [field1], ST_Area(ST_Union(geometry), True) FROM table [group by field1]

Or, written in another words, how do I apply a function over an aggregate result? ST_Union is an aggregate. [field1] is just free notation to say I'd like to run both queries with or without this group by.

Also, ST_Area with 2 arguments seem not to be available on django gis helpers, so it must probably be written using Func.

Also, I want to be able to also aggregate by everything (not provide a groupBy) but django seems to add a group by id if I don't provide any .values() to the queryset.

This seems very confusing. I can't get my head around annotates and aggregates. Thank you!

Upvotes: 1

Views: 214

Answers (1)

wkrueger
wkrueger

Reputation: 1363

Apparently I can normally chain aggregates, like

from django.contrib.gis.db.models import Union, GeometryField
from django.contrib.gis.db.models.functions import Transform, Area

qs = qs.annotate(area_total=Area(Transform(Union("geometry"), 98056)))

The issue I was encountering was that I was attemping to use Func() expressions. In order to chain another function in the 1st parameter of Func, it must be wrapped with ExpressionWrapper or something else.

    qs = qs.annotate(
        area_total=Func(
            ExpressionWrapper(Union("geometry"), output_field=GeometryField()),
            True,
            function="ST_Area",
            output_field=FloatField(),
        )
    )

Upvotes: 2

Related Questions