# Prefetching spatially related data

**URL:** <https://forum.djangoproject.com/t/prefetching-spatially-related-data/35310>\
**Category:** Using the ORM\
**Created:** [October 1, 2024, 8:47pm UTC](https://forum.djangoproject.com/t/prefetching-spatially-related-data/35310 "2024-10-01T20:47:42Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![pdahl](https://avatars.discourse-cdn.com/v4/letter/p/ed8c4c/32.png) [@pdahl](https://forum.djangoproject.com/u/pdahl)\
**Post date:** [October 1, 2024, 8:47pm UTC](https://forum.djangoproject.com/t/prefetching-spatially-related-data/35310/1 "2024-10-01T20:47:42Z")

</div>

I have created something similar to the following in my project:

```auto
from django.contrib.gis.db import models

class Location(models.Model):
    area = models.PolygonField()

class Plant(models.Model):
   position = models.PointField()

```

I’m trying to come up with a way to describe a relation between the two models such that I can get a Plant queryset with prefetched Locations that have areas that intersect Plant object positions.  
i.e. :

```auto
plants = Plant.objects.prefetch_related('locations')

```

I know how to get the locations for one object without a relation. Beyond that I actually came up with a way to define a custom relation and manager to get the proper related records, but the private API is just a bit opaque for me to figure out exactly what is happening with the prefetched values.

Does anyone know if there is a simpler way of avoiding the N+1 select problem when it comes to spatially related data like this?

---

<div class="post-metadata">

**Author:** ![charettes](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/charettes/32/27_2.png) [@charettes](https://forum.djangoproject.com/u/charettes)\
**Post date:** [October 2, 2024, 1:46am UTC](https://forum.djangoproject.com/t/prefetching-spatially-related-data/35310/2 "2024-10-02T01:46:45Z")

</div>

Unfortunately the ORM doesn’t allow you to define relationship between models that don’t involve foreign keys. If it was the case you could possibly do something like

```python
class Location(models.Model):
    area = models.PolygonField()

class Plant(models.Model):
   position = models.PointField()
   locations = Relation(
        Location, Q(position__within=F("locations__area"))
   )

```

Knowing that it’s important to understand that prefetch related is really just a glorified `__in` lookup that performs assignments. In other words

```python
Author.objects.prefetch_related("books")

```

Is equivalent to

```python
authors = Author.objects.all()
authors_books = defaultdict(list)
for book in Book.objects.filter(author__in=authors):
    authors_books[book.author_id].append(book)
for author in authors:
    author._prefetch_related_cache["books"] = authors_books[author.id]

```

This means that in order to _emulate_ what `prefetch_related('locations')` does you need to craft a query that can return all the associated `Location` annotated with `plant_id` within their defined areas from a set of `Plant`.

Something like the following should work

```python
from django.contrib.postgres.expressions import ArraySubquery
from django.db.models import OuterRef, Exists

plant_ids = [plant.id for plant in plants]
locations = Location.objects.filter(
    Exists(
        Plant.objects.filter(
            id__in=plant_ids,
            position__within=OuterRef("area"),
        )
    )
).annotate(
    plant_ids=ArraySubquery(
        Plant.objects.filter(
            position__within=OuterRef("area")
        ).values("id")
    )
)
plant_locations = defaultdict(list)
for location in locations:
    for plant_id in location.plant_ids:
        plant_locations[plant_id].append(location)

```

Which should perform a single SQL query of the form

```sql
SELECT
   location.*,
   ARRAY(
       SELECT id
       FROM plant
       WHERE ST_Within(plant.position, location.area)
   ) plant_ids
FROM location
WHERE EXISTS (
    SELECT 1
    FROM plant
    WHERE ST_Within(plant.position, location.area)
    LIMIT 1
)

```

Which should be roughly equivalent to the more concise but hard to express through the ORM and likely more performant

```sql
SELECT
   location.*,
   array_agg(plant.id) plant_ids
FROM location
INNER JOIN plant ON (ST_Within(plant.position, location.area))
GROUP BY location.id

```

---

<div class="post-metadata">

**Author:** ![cliff688](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/cliff688/32/22816_2.png) [@cliff688](https://forum.djangoproject.com/u/cliff688)\
**Post date:** [October 2, 2024, 9:47am UTC](https://forum.djangoproject.com/t/prefetching-spatially-related-data/35310/3 "2024-10-02T09:47:40Z")

</div>

> [@charettes](#):
>
> ```auto
> for author in authors:
> author._prefetch_related_cache['authors'] = authors_books[author.id]
> 
> ```

isn’t this supposed to be:

```auto
for author in authors:
    author._prefetch_related_cache["books"] = authors_books[author.id]

```

---

<div class="post-metadata">

**Author:** ![charettes](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/charettes/32/27_2.png) [@charettes](https://forum.djangoproject.com/u/charettes)\
**Post date:** [October 2, 2024, 11:19am UTC](https://forum.djangoproject.com/t/prefetching-spatially-related-data/35310/4 "2024-10-02T11:19:58Z")

</div>

Yes, it shouldn’t affect the intent of the untested pseudo code but it’s more correct.

---

<div class="post-metadata">

**Author:** ![pdahl](https://avatars.discourse-cdn.com/v4/letter/p/ed8c4c/32.png) [@pdahl](https://forum.djangoproject.com/u/pdahl)\
**Post date:** [October 2, 2024, 4:33pm UTC](https://forum.djangoproject.com/t/prefetching-spatially-related-data/35310/5 "2024-10-02T16:33:35Z")

</div>

Thank you so much for such an in-depth reply. Just for completeness, do you know if there is any way to express that kind of join using the ORM?

```auto
INNER JOIN plant ON (ST_Within(plant.position, location.area))

```

---

<div class="post-metadata">

**Author:** ![charettes](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/charettes/32/27_2.png) [@charettes](https://forum.djangoproject.com/u/charettes)\
**Post date:** [October 2, 2024, 4:53pm UTC](https://forum.djangoproject.com/t/prefetching-spatially-related-data/35310/6 "2024-10-02T16:53:08Z")

</div>

You’ll have to [use `extra` as a last resort](https://docs.djangoproject.com/en/5.1/ref/models/querysets/#extra) and some low level expressions to achieve that I’m afraid

```sql
SELECT
   location.*,
   array_agg(plant.id) plant_ids
FROM location, plant
WHERE ST_Contains(location.area, plant.position)
GROUP BY location.id

```

which is equivalent as the above is an implicit `INNER JOIN` through a `WHERE` criteria.

I believe the following should work

```python
from django.db.models.expression import Col

plant_table = Plant._meta.db_table
plant_id_field = Plant._meta.get_field("id")
plant_location_field = Plant._meta.get_field("location")
Location.objects.extra(
    tables=[plant_table]
).annotate(
     plant_ids=ArrayAgg(Col(plant_table, plant_id_field)),
).filter(
    area__contains=Col(plant_table, plant_location_field),
)

```

---

<div class="post-metadata">

**Author:** ![pdahl](https://avatars.discourse-cdn.com/v4/letter/p/ed8c4c/32.png) [@pdahl](https://forum.djangoproject.com/u/pdahl)\
**Post date:** [October 2, 2024, 6:23pm UTC](https://forum.djangoproject.com/t/prefetching-spatially-related-data/35310/7 "2024-10-02T18:23:51Z")

</div>

Enlightening!

Thank you so much for your help.