如何用Django ORM实现为MyModel关联最近的OSM顶点？

问题背景 #

需要为MyModel的每一行数据获取最近的OSM顶点，最初通过RawSQL实现：

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

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
            """,
                (),
            )
        )

为改用Django ORM实现，创建了OSM顶点的非托管模型：

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

    class Meta:
        managed = False
        db_table = "planet_osm_roads_vertices_pgr"

并定义了自定义Distance函数以使用PostGIS的<->距离操作符：

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

尝试用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]
        )
    )

错误信息：

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

解决方案 #

方案1：简化自定义Distance函数并正确引用字段 #

调整自定义Distance函数，使用Django的Func模板机制自动处理字段编译，同时在子查询中用F()引用内部字段，OuterRef()引用外部字段：

from django.db.models import Func, F, Subquery, OuterRef

class Distance(Func):
    arity = 2
    template = "%(expressions)s"
    arg_joiner = " <-> "

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

方案2：在子查询的order_by中使用RawSQL #

如果方案1仍有问题，可以在子查询的排序逻辑中使用RawSQL直接编写距离表达式，确保外部字段被正确引用：

from django.db.models import Subquery, OuterRef, RawSQL

def annotate_closest_vertex(queryset):
    queryset = queryset.annotate(
        closest_vertex=Subquery(
            OSMVertex.objects.order_by(
                RawSQL('the_geom <-> mymodel.location', ())
            ).values_list("pk", flat=True)[:1]
        )
    )
    return queryset

方案3：使用PostGIS原生ST_Distance（索引友好性稍弱） #

如果不需要依赖<->操作符的索引优化，可以直接使用Django GIS内置的Distance函数：

from django.contrib.gis.db.models.functions import Distance
from django.db.models import Subquery, OuterRef

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

说明 #

• 方案1和方案2都利用了PostGIS的<->操作符，该操作符可以利用空间索引大幅提升排序性能，适合大数据量场景。
• 方案3使用标准的ST_Distance函数，兼容性更好但索引优化效果不如<->。

内容的提问来源于stack exchange，提问作者Alombaros