Search code examples
pythondjangopostgisdjango-ormgeodjango

GeoDjango: How to perform a query of spatially close records

I have two Django models (A and B) which are not related by any foreign key, but both have a geometry field. 

class A(Model):
    position = PointField(geography=True)

class B(Model):
    position = PointField(geography=True)

I would like to relate them spatially, i.e. given a queryset of A, being able to obtain a queryset of B containing those records that are at less than a given distance to A.

I haven't found a way using pure Django's ORM to do such a thing.

Of course, I could write a property in A such as this one:

@property
def nearby(self):
    return B.objects.filter(position__dwithin=(self.position, 0.1))

But this only allows me to fetch the nearby records on each instance and not in a single query, which is far from efficient.

I have also tried to do this:

nearby = B.objects.filter(position__dwithin=(OuterRef('position'), 0.1))
query = A.objects.annotate(nearby=Subquery(nearby.values('pk')))

list(query)  # error here

However, I get this error for the last line:

ValueError: This queryset contains a reference to an outer query and may only be used in a subquery

Does anybody know a better way (more efficient) of performing such a query or maybe the reason why my code is failing?

I very much appreciate.

Solution

  • I finally managed to solve it, but I had to perform a raw SQL query in the end.

    This will return all A records with an annotation including a list of all nearby B records:

    from collections import namedtuple

from django.db import connection


with connection.cursor() as cursor:
    cursor.execute('''SELECT id, array_agg(b.id) as nearby FROM myapp_a a
                      LEFT JOIN myapp_b b ON ST_DWithin(a.position, p.position, 0.1)
                      GROUP BY a.id''')

    nt_result = namedtuple('Result', [col[0] for col in cursor.description])
    results = [nt_result(*row) for row in cursor.fetchall()]

    References: