Annotate closest OSM vertex

Question

I'm trying to get the closest OSM vertex for each row of MyModel

class MyModel(models.Model):
    location = GeometryField()

I currently use RawSQL for this

def annotate_closest_vertex(queryset):
    queryset = queryset.annotate(
            closest_vertex=RawSQL(
                """
            SELECT id
            FROM planet_osm_roads_vertices_pgr
            ORDER BY the_geom <-> "mymodel"."location" LIMIT 1
            """,
                (),
            )
        )

And I would like to use the ORM

I tried to create a unmanaged Model for OSM vertices

class OSMVertex(models.Model):
    the_geom = GeometryField()

    class Meta:
        managed = False
        db_table = "planet_osm_roads_vertices_pgr"

And a distance function

class Distance(Func):
    arity = 2

    def as_sql(
        self,
        compiler,
        connection,
        function=None,
        template=None,
        arg_joiner=None,
        **extra_context,
    ):
        connection.ops.check_expression_support(self)
        sql_parts = []
        params = []
        for arg in self.source_expressions:
            arg_sql, arg_params = compiler.compile(arg)
            sql_parts.append(arg_sql)
            params.extend(arg_params)
        return f"{sql_parts[0]} <-> {sql_parts[1]}", params

Then using a simple Subquery

def annotate_closest_vertex(queryset):
    queryset = queryset.annotate(
        closest_vertex=Subquery(
            OSMVertex.objects.order_by(
                Distance("the_geom", OuterRef("location"))
            ).values_list("pk")[:1]
        )
    )

However I get the error

/usr/local/lib/python3.8/site-packages/django/db/models/query.py:1324: in _fetch_all
    self._result_cache = list(self._iterable_class(self))
/usr/local/lib/python3.8/site-packages/django/db/models/query.py:51: in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
/usr/local/lib/python3.8/site-packages/django/db/models/sql/compiler.py:1162: in execute_sql
    sql, params = self.as_sql()
/usr/local/lib/python3.8/site-packages/django/db/models/sql/compiler.py:513: in as_sql
    extra_select, order_by, group_by = self.pre_sql_setup()
/usr/local/lib/python3.8/site-packages/django/db/models/sql/compiler.py:55: in pre_sql_setup
    self.setup_query()
/usr/local/lib/python3.8/site-packages/django/db/models/sql/compiler.py:46: in setup_query
    self.select, self.klass_info, self.annotation_col_map = self.get_select()
/usr/local/lib/python3.8/site-packages/django/db/models/sql/compiler.py:262: in get_select
    sql, params = self.compile(col)
/usr/local/lib/python3.8/site-packages/django/db/models/sql/compiler.py:445: in compile
    sql, params = node.as_sql(self, self.connection)
/usr/local/lib/python3.8/site-packages/django/db/models/expressions.py:1126: in as_sql
    subquery_sql, sql_params = query.as_sql(compiler, connection)
/usr/local/lib/python3.8/site-packages/django/db/models/sql/query.py:1103: in as_sql
    sql, params = self.get_compiler(connection=connection).as_sql()
/usr/local/lib/python3.8/site-packages/django/db/models/sql/compiler.py:513: in as_sql
    extra_select, order_by, group_by = self.pre_sql_setup()
/usr/local/lib/python3.8/site-packages/django/db/models/sql/compiler.py:56: in pre_sql_setup
    order_by = self.get_order_by()
/usr/local/lib/python3.8/site-packages/django/db/models/sql/compiler.py:400: in get_order_by
    sql, params = self.compile(resolved)
/usr/local/lib/python3.8/site-packages/django/db/models/sql/compiler.py:445: in compile
    sql, params = node.as_sql(self, self.connection)
/usr/local/lib/python3.8/site-packages/django/db/models/expressions.py:1218: in as_sql
    expression_sql, params = compiler.compile(self.expression)
/usr/local/lib/python3.8/site-packages/django/db/models/sql/compiler.py:445: in compile
    sql, params = node.as_sql(self, self.connection)
/usr/local/lib/python3.8/site-packages/django/db/models/expressions.py:933: in as_sql
    return compiler.compile(self.expression)
/usr/local/lib/python3.8/site-packages/django/db/models/sql/compiler.py:445: in compile
    sql, params = node.as_sql(self, self.connection)
compiler.compile call in the Distance function defined above: in as_sql
    arg_sql, arg_params = compiler.compile(arg)
/usr/local/lib/python3.8/site-packages/django/db/models/sql/compiler.py:445: in compile
    sql, params = node.as_sql(self, self.connection)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

self = ResolvedOuterRef(location), args = (<django.db.models.sql.compiler.SQLCompiler object at 0x7fb9f2642f70>, <django.contrib.gis.db.backends.postgis.base.DatabaseWrapper object at 0x7fbbb472b5e0>), kwargs = {}

    def as_sql(self, *args, **kwargs):
>       raise ValueError(
            'This queryset contains a reference to an outer query and may '
            'only be used in a subquery.'
        )
E       ValueError: This queryset contains a reference to an outer query and may only be used in a subquery.

/usr/local/lib/python3.8/site-packages/django/db/models/expressions.py:603: ValueError

I feel like the resolution of the OuterRef fails because it is located in the order_by clause.

Did someone already solved a similar issue ? I found a lot of issues related to OuterRef online but none helped me.

Answer 1

The workaround will be to annotate the OuterRef first and then use it in the order by, so try this:

def annotate_closest_vertex(queryset):
    queryset = queryset.annotate(
        closest_vertex=Subquery(
            OSMVertex.objects.annotate(outer_location=OuterRef('location'))
            .order_by(Distance("the_geom", "outer_location"))
            .values_list("pk")[:1]
        )
    )

In general, you can not use OuterRef in the order by, but you may use it in the annotation, and then use that annotated field name in the order by.