Annotate GIS Intersection in Nested Subquery

Question

I am trying to execute a complex Django query involving a nested subquery. Starting with the Stick model, I want to annotate the ownership_pct from the StickOwnership model (which is straightforward given the FK relationship). Next, I also want to annotate the ownership_pct from the BoxOwnership model such that the associated Box has the maximum overlap with the Stick.

For reference, here are the models:

Stick:
lateral_line_string = models.LineStringField()

Box:
polygon = models.MultiPolygonField()

BoxOwnership:
box = auto_prefetch.ForeignKey("Box")
owner = auto_prefetch.ForeignKey("Owner")
ownership_pct = DecimalField

StickOwnership:
stick= auto_prefetch.ForeignKey("Stick")
owner = auto_prefetch.ForeignKey("Owner")
ownership_pct = DecimalField

Here is what I have written so far:

from django.db.models import F, OuterRef, Subquery
from django.contrib.gis.db.models.functions import Intersection, Length

sticks = Sticks.objects.all()  # starting queryset is arbitrary
owner_in_question = Owner.objects.first()  # owner is arbitrary

stick_ownership_subquery = StickOwnership.objects.filter(stick=OuterRef('pk'),owner=owner_in_question).only('ownership_pct')

box_subquery = box.objects.filter(polygon__intersects=OuterRef(OuterRef('lateral_line_string'))).annotate(length=Length(Intersection(OuterRef(OuterRef('lateral_line_string')), F('polygon')))).order_by('-length').only('pk')

box_ownership_subquery = BoxOwnership.objects.filter(box=Subquery(box_subquery.first().pk), owner=owner_in_question).only('ownership_pct')

sticks = sticks.annotate(stick_ownership=Subquery(stick_ownership_subquery.values('ownership_pct'))).annotate(box_ownership=Subquery(box_ownership_subquery.values('ownership_pct')))

The box_subquery is throwing this error:

Traceback (most recent call last):
  File "venv/lib/python3.10/site-packages/IPython/core/interactiveshell.py", line 3251, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-8-0b4d9b715187>", line 1, in <module>
    box_subquery = Box.objects.filter(polygon__intersects=OuterRef(OuterRef('lateral_line_string'))).annotate(length=Length(Intersection(OuterRef(OuterRef('lateral_line_string')), F('polygon'))))
  File "venv/lib/python3.10/site-packages/django/db/models/query.py", line 1225, in annotate
    return self._annotate(args, kwargs, select=True)
  File "venv/lib/python3.10/site-packages/django/db/models/query.py", line 1273, in _annotate
    clone.query.add_annotation(
  File "venv/lib/python3.10/site-packages/django/db/models/sql/query.py", line 1074, in add_annotation
    annotation = annotation.resolve_expression(
  File "venv/lib/python3.10/site-packages/django/contrib/gis/db/models/functions.py", line 71, in resolve_expression
    res = super().resolve_expression(*args, **kwargs)
  File "venv/lib/python3.10/site-packages/django/db/models/expressions.py", line 762, in resolve_expression
    c.source_expressions[pos] = arg.resolve_expression(
  File "venv/lib/python3.10/site-packages/django/contrib/gis/db/models/functions.py", line 74, in resolve_expression
    source_fields = res.get_source_fields()
  File "venv/lib/python3.10/site-packages/django/db/models/expressions.py", line 390, in get_source_fields
    return [e._output_field_or_none for e in self.get_source_expressions()]
  File "venv/lib/python3.10/site-packages/django/db/models/expressions.py", line 390, in <listcomp>
    return [e._output_field_or_none for e in self.get_source_expressions()]
AttributeError: 'OuterRef' object has no attribute '_output_field_or_none'

From troubleshooting, I found that the issue lies in calling Intersection and using OuterRef for one of the geometries. When I substitute a dummy geometry, the code works fine. Is there a consideration around using OuterRef to reference a geometry field?

Annotate GIS Intersection in Nested Subquery

0 Answers0