如何在Django中计算Frechet距离? [英] How to calculate Frechet Distance in Django?

问题描述

这基本上是关于在Django代码中运行自定义PostGIS函数的问题.这个站点上有很多相关的答案，最接近我的情况是这个.建议使用Func()或什至GeoFunc()类，但是那里没有地理空间功能的示例.后者('GeoFunc')甚至对我抛出st_geofunc does not exist异常(Django 2.1.5)都不起作用.

This is basically a question about running custom PostGIS functions inside the Django code. There is a number of related answers on this site, most close to my case is this one. It is suggested to use Func() or even GeoFunc() classes but there is no example for geospatial functions there. The latter ('GeoFunc') didn't even work for me throwing st_geofunc does not exist exception (Django 2.1.5).

我必须完成的任务是根据它们到给定几何形状的Frechet距离过滤LineStrings. Frechet距离应该使用PostGIS提供的 ST_FrechetDistance 函数进行计算.

The task that I have to complete is to filter LineStrings based on their Frechet Distance to the given geometry. Frechet Distance is supposed to be calculated using ST_FrechetDistance function provided by PostGIS.

在另一个基于SQLAlchemy的项目中，我使用以下功能(正在运行)完成了完全相同的任务:

In another project based on SQLAlchemy I complete the exact same task with the following function (it is working):

from geoalchemy2 import Geography, Geometry
from sqlalchemy import func, cast

def get_matched_segments(wkt: str, freche_threshold: float = 0.002):
    matched_segments = db_session.query(RoadElement).filter(
        func.ST_Dwithin(
            RoadElement.geom,
            cast(wkt, Geography),
            10
        )
    ).filter(
        (func.ST_FrechetDistance(
            cast(RoadElement.geom, Geometry),
            cast(wkt, Geometry),
            0.1
        ) < freche_threshold) |
        # Frechet Distance is sensitive to geometry direction
        (func.ST_FrechetDistance(
            cast(RoadElement.geom, Geometry),
            func.ST_Reverse(cast(wkt, Geometry)),
            0.1
        ) < freche_threshold)
    )
    return matched_segments

正如我所说，上面的函数正在运行，我想在Django中重新实现它.我必须添加其他几何图形的SRS转换，因为在基于SQLite的项目中，线串最初位于EPSG:4326中，而在Django中，它们最初位于EPSG:3857中.这是我想出的:

As I said, the function above is working and I wanted to re-implement it in Django. I had to add additional SRS transformation of geometries because in SQLite-based projects LineStrings were in EPSG:4326 and in Django they are in EPSG:3857 initially. Here is what I came up with:

from django.db.models import Func, Value, Q, QuerySet, F
from django.contrib.gis.geos import GEOSGeometry


class HighwayOnlyMotor(models.Model):
    geom = LineStringField(srid=3857)

def get_matched_segments(wkt: str, freche_threshold: float = 0.002) -> QuerySet:
    linestring = GEOSGeometry(wkt, srid=4326)
    transform_ls = linestring.transform(3857, clone=True)
    linestring.reverse()
    frechet_annotation = HighwayOnlyMotor.objects.filter(
        geom__dwithin=(transform_ls, D(m=20))  
    ).annotate(
        fre_forward=Func(
            Func(F('geom'), Value(4326), function='ST_Transform'),
            Value(wkt),
            Value(0.1),
            function='ST_FrechetDistance'
        ),
        fre_backward=Func(
            Func(F('geom'), Value(4326), function='ST_Transform'),
            Value(linestring.wkt),
            Value(0.1),
            function='ST_FrechetDistance'
        )
    )
    matched_segments = frechet_annotation.filter(
        Q(fre_forward__lte=freche_threshold) |
        Q(fre_backward__lte=freche_threshold)
    )
    return matched_segments

它不起作用，因为frechet_annotation QuerySet引发异常:

It doesn't work, as the frechet_annotation QuerySet throws an exception:

django.db.utils.ProgrammingError: cannot cast type double precision to bytea
LINE 1: ...548 55.717805109,36.825235998 55.717761246)', 0.1)::bytea AS...
                                                             ^

似乎我错误地定义了"ST_FrechetDistance"计算.我该如何解决?

Seems that I incorrectly defined 'ST_FrechetDistance' calculation. How do I fix it?

更新

检出Django编写的SQL.总体上是正确的，但是尝试将FrecheDistance的结果强制转换为bytea会破坏ST_FrechetDistance(...)::bytea.当我在不使用bytea强制转换的情况下手动运行查询时，SQL可以正常工作.所以问题是如何避免强制转换为bytea?

Checked out the SQL that Django composed. It is overall correct but attempts to cast the result of FrecheDistance to bytea spoils it ST_FrechetDistance(...)::bytea. When I manually run the query without bytea cast, the SQL works. So the question is how to avoid this cast to bytea?

推荐答案

在您的SQLAlchemy示例中，您正在执行GeoDjango中未执行的操作，即将WKT字符串强制转换为Geometry .
本质上，这里发生的是您正在尝试使用PostGIS函数，但是要向其传递字符串而不是Geometry.

In your SQLAlchemy example, you are doing something that you didn't do in the GeoDjango one and that is to cast the WKT string to Geometry.
What happens here essentially is that you are trying to use a PostGIS function but instead of a Geometry, you are passing it a string.

在修复第一个问题后，我们会偶然发现的另一个问题是以下异常:

Another problem that we would stumble upon after fixing the first one would be the following exception:

django.core.exceptions.FieldError: Cannot resolve expression type, unknown output_field

，这就是为什么我们需要基于GeoFunc创建自定义数据库功能的原因.但这本身就带来了一些问题，我们将需要考虑以下因素:

and that is why we need to create a custom database function based on GeoFunc. That poses some problems of its own though and we will need to consider the following:

• 我们的数据库函数将接收2个几何作为参数.

• Our DB Function will receive 2 Geometries as arguments.

这有点令人费解，但是如果我们看一下

That is a bit convoluted, but if we look at the code of GeoFunc we will see that the class inherits a mixin called: GeoFuncMixin which has the attribute geom_param_pos = (0,) and specifies the positions of the function arguments that will be geometries. (Yeaahhh frameworks are fun :P)

因此，我们的自定义数据库功能应如下所示:

Therefore our custom DB Function should look like this:

from django.contrib.gis.db.models.functions import GeoFunc
from django.db.models.fields import FloatField

class FrechetDistance(GeoFunc):
    function='ST_FrechetDistance'
    geom_param_pos = (0, 1,)
    output_field = FloatField()

现在，我们可以在查询中使用此函数来计算 ST_FrechetDistance .
我们还需要解决将几何传递给函数的原始问题，而不仅仅是WKT字符串:

Now we can use this function in our query to calculate the ST_FrechetDistance.
We will also need to address the original issue of passing geometries to the function and not just WKT strings:

def get_matched_segments(wkt: str, freche_threshold: float = 0.002) -> QuerySet:
    forward_linestring = GEOSGeometry(wkt, srid=4326)
    backward_linestring = GEOSGeometry(wkt, srid=4326)
    backward_linestring.reverse()
    backward_linestring.srid = 4326  # On Django 2.1.5 `srid` is lost after `reverse()`
    transform_ls = linestring.transform(3857, clone=True)

    frechet_annotation = HighwayOnlyMotor.objects.filter(
        geom__dwithin=(transform_ls, D(m=20))  
    ).annotate(
        fre_forward=FrechetDistance(
            Func(F('geom'), Value(4326), function='ST_Transform'),
            Value(forward_linestring),
            Value(0.1)
        ),
        fre_backward=FrechetDistance(
            Func(F('geom'), Value(4326), function='ST_Transform'),
            Value(backward_linestring),
            Value(0.1)
        )
    )
    matched_segments = frechet_annotation.filter(
        Q(fre_forward__lte=freche_threshold) |
        Q(fre_backward__lte=freche_threshold)
    )
    return matched_segments   

这篇关于如何在Django中计算Frechet距离?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！