CODEWITHSUNDEEP
pythondjangoaggregate-functionsgeodjangodjango-queryset
yellowcap
yellowcap

Reputation: 4021

Aggregate area of geoqueryset by group in Django

I am trying to calculate aggregate areas for a model with a geodjango geometry field, grouped by an additional property. I did not find a function for that in the geoqueryset api reference, and the regular Sum aggregate function does not apply to this case.

So I tried using the "extra" argument on querysets as follows:

class MyModel(models.Model):
    name = models.CharField(max_length = 100)
    geom = models.MultiPolygonField()
    objects = models.GeoManager()

MyModel.objects.all()\
    .extra(select={'area': 'SUM(ST_Area(geom))'})\
    .values('area', 'name')

This does not work and the following programming error is returned.

column "app_mymodel.name_id" must appear in the GROUP BY clause \
or be used in an aggregate function

I tried several combinations of extra, values and annotate with these fields but was not able to get a working aggregate. If I leave away the grouping argument names the total area is returned successfully.

Any ideas on how can I get the area of a geometry field grouped by an additional property?

Upvotes: 1

Views: 726

Answers (1)

pztrick
pztrick

Reputation: 3831

I was able to hack something together using .values_list, .distinct, .area, and reduce.

The values_list(...).distinct() incantation is used to get a list of unique name attribute values.

Next, you loop through the unique values, filter the model queryset using the unique value, and then sum the filtered QuerySet using reduce. I had to use a loop (i.e. reduce) to sum, because area doesn't exist as an column in the database for use with the more conventional .aggregate(Sum(...)) method.

from django.contrib.gis.measure import Area    
from data.models import MyGeoModel

distincts = MyGeoModel.objects.order_by('name').distinct('name').values_list('name', flat=True)

for value in distincts:
    print reduce(lambda a,x: a+x.calculated_area.sq_ft, MyGeoModel.objects.filter(name=value).area(model_att='calculated_area'), Area(0))

Upvotes: 0

Related Questions