GIS QuerySet API 参考

空间查找

这个部分中的空间查找适用于 GeometryFieldRasterField

For an introduction, see the spatial lookups introduction. For an overview of what lookups are compatible with a particular spatial backend, refer to the spatial lookup compatibility table.

栅格查找方式

参考下面的文档中,所有示例都是针对几何字段和输入的,但是查找方式可以在两侧都以相同的方式用于栅格。每当某个查找方式不支持栅格输入时,输入将自动转换为几何,必要时使用 ST_Polygon 函数。还请参阅 栅格查找引言

查找方式使用的数据库运算符可以分为三类:

  • 本地栅格支持 N:该运算符在查找的两侧都可以本地接受栅格,并且可以混合使用栅格输入和几何输入。

  • 双向栅格支持 B:该运算符仅在查找的两侧都接收栅格输入时支持栅格。对于混合查找,栅格数据会自动转换为几何数据。

  • 几何转换支持 C:该查找方式不支持原生栅格,所有栅格数据都会自动转换为几何数据。

以下示例展示了不同类型栅格支持的查找方式的 SQL 等效方式。相同的模式适用于所有空间查找方式。

案例

查找

SQL 等效方式

N, B

rast__contains=rst

ST_Contains(rast, rst)

N, B

rast__1__contains=(rst, 2)

ST_Contains(rast, 1, rst, 2)

B, C

rast__contains=geom

ST_Contains(ST_Polygon(rast), geom)

B, C

rast__1__contains=geom

ST_Contains(ST_Polygon(rast, 1), geom)

B, C

poly__contains=rst

ST_Contains(poly, ST_Polygon(rst))

B, C

poly__contains=(rst, 1)

ST_Contains(poly, ST_Polygon(rst, 1))

C

rast__crosses=rst

ST_Crosses(ST_Polygon(rast), ST_Polygon(rst))

C

rast__1__crosses=(rst, 2)

ST_Crosses(ST_Polygon(rast, 1), ST_Polygon(rst, 2))

C

rast__crosses=geom

ST_Crosses(ST_Polygon(rast), geom)

C

poly__crosses=rst

ST_Crosses(poly, ST_Polygon(rst))

仅支持在 PostGIS 后端(在本节中称为 PGRaster)中使用栅格进行空间查找。

bbcontains

可用性: PostGIS, MariaDB, MySQL, SpatiaLite, PGRaster (Native)

测试几何或栅格字段的边界框是否完全包含查找几何的边界框。

举例:

Zipcode.objects.filter(poly__bbcontains=geom)

后端

SQL 等效方式

PostGIS

poly ~ geom

MariaDB

MBRContains(poly, geom)

MySQL

MBRContains(poly, geom)

SpatiaLite

MbrContains(poly, geom)

bboverlaps

可用性: PostGIS, MariaDB, MySQL, SpatiaLite, PGRaster (Native)

测试几何字段的边界框是否与查找几何的边界框重叠。

举例:

Zipcode.objects.filter(poly__bboverlaps=geom)

后端

SQL 等效方式

PostGIS

poly && geom

MariaDB

MBROverlaps(poly, geom)

MySQL

MBROverlaps(poly, geom)

SpatiaLite

MbrOverlaps(poly, geom)

contained

可用性: PostGIS, MariaDB, MySQL, SpatiaLite, PGRaster (Native)

测试几何字段的边界框是否完全包含在查找几何的边界框内。

举例:

Zipcode.objects.filter(poly__contained=geom)

后端

SQL 等效方式

PostGIS

poly @ geom

MariaDB

MBRWithin(poly, geom)

MySQL

MBRWithin(poly, geom)

SpatiaLite

MbrWithin(poly, geom)

contains

可用性: PostGIS, Oracle, MariaDB, MySQL, SpatiaLite, PGRaster (Bilateral)

测试几何字段是否在空间上包含查找几何。

举例:

Zipcode.objects.filter(poly__contains=geom)

后端

SQL 等效方式

PostGIS

ST_Contains(poly, geom)

Oracle

SDO_CONTAINS(poly, geom)

MariaDB

ST_Contains(poly, geom)

MySQL

ST_Contains(poly, geom)

SpatiaLite

Contains(poly, geom)

contains_properly

可用性: PostGIS, PGRaster (Bilateral)

如果查找几何与几何字段的内部相交但不与边界(或外部)相交,则返回 true。

举例:

Zipcode.objects.filter(poly__contains_properly=geom)

后端

SQL 等效方式

PostGIS

ST_ContainsProperly(poly, geom)

coveredby

Availability: PostGIS, Oracle, MariaDB, MySQL, PGRaster (Bilateral), SpatiaLite

测试几何字段中是否没有点位于查找几何的外部。 [3]

举例:

Zipcode.objects.filter(poly__coveredby=geom)

后端

SQL 等效方式

PostGIS

ST_CoveredBy(poly, geom)

Oracle

SDO_COVEREDBY(poly, geom)

MariaDB

MBRCoveredBy(poly, geom)

MySQL

MBRCoveredBy(poly, geom)

SpatiaLite

CoveredBy(poly, geom)

Changed in Django 5.2:

MySQL support was added.

Changed in Django 6.0:

MariaDB 12.0.1+ support was added.

covers

Availability: PostGIS, Oracle, MySQL, PGRaster (Bilateral), SpatiaLite

测试查找几何中是否没有点位于几何字段的外部。 [3]

举例:

Zipcode.objects.filter(poly__covers=geom)

后端

SQL 等效方式

PostGIS

ST_Covers(poly, geom)

Oracle

SDO_COVERS(poly, geom)

MySQL

MBRCovers(poly, geom)

SpatiaLite

Covers(poly, geom)

Changed in Django 5.2:

MySQL support was added.

crosses

可用性: PostGIS, MariaDB, MySQL, SpatiaLite, PGRaster (Conversion)

测试几何字段是否在空间上与查找几何交叉。

举例:

Zipcode.objects.filter(poly__crosses=geom)

后端

SQL 等效方式

PostGIS

ST_Crosses(poly, geom)

MariaDB

ST_Crosses(poly, geom)

MySQL

ST_Crosses(poly, geom)

SpatiaLite

Crosses(poly, geom)

disjoint

可用性: PostGIS, Oracle, MariaDB, MySQL, SpatiaLite, PGRaster (Bilateral)

测试几何字段是否在空间上与查找几何不相交。

举例:

Zipcode.objects.filter(poly__disjoint=geom)

后端

SQL 等效方式

PostGIS

ST_Disjoint(poly, geom)

Oracle

SDO_GEOM.RELATE(poly, 'DISJOINT', geom, 0.05)

MariaDB

ST_Disjoint(poly, geom)

MySQL

ST_Disjoint(poly, geom)

SpatiaLite

Disjoint(poly, geom)

equals

可用性: PostGIS, Oracle, MariaDB, MySQL, SpatiaLite, PGRaster (Conversion)

测试几何字段是否在空间上等于查找几何。

举例:

Zipcode.objects.filter(poly__equals=geom)

后端

SQL 等效方式

PostGIS

ST_Equals(poly, geom)

Oracle

SDO_EQUAL(poly, geom)

MariaDB

ST_Equals(poly, geom)

MySQL

ST_Equals(poly, geom)

SpatiaLite

Equals(poly, geom)

exact, same_as

可用性: PostGIS, Oracle, MariaDB, MySQL, SpatiaLite, PGRaster (Bilateral)

测试几何字段是否在 Oracle、MySQL 和 SpatiaLite 上空间上等于查找几何,在 PostGIS 上它测试边界框的相等性。

举例:

Zipcode.objects.filter(poly=geom)

后端

SQL 等效方式

PostGIS

poly ~= geom

Oracle

SDO_EQUAL(poly, geom)

MariaDB

ST_Equals(poly, geom)

MySQL

ST_Equals(poly, geom)

SpatiaLite

Equals(poly, geom)

intersects

可用性: PostGIS, Oracle, MariaDB, MySQL, SpatiaLite, PGRaster (Bilateral)

测试几何字段是否在空间上与查找几何相交。

举例:

Zipcode.objects.filter(poly__intersects=geom)

后端

SQL 等效方式

PostGIS

ST_Intersects(poly, geom)

Oracle

SDO_OVERLAPBDYINTERSECT(poly, geom)

MariaDB

ST_Intersects(poly, geom)

MySQL

ST_Intersects(poly, geom)

SpatiaLite

Intersects(poly, geom)

isempty

可用性: PostGIS

测试几何是否为空。

举例:

Zipcode.objects.filter(poly__isempty=True)

isvalid

Availability: MariaDB, MySQL, PostGIS, Oracle, SpatiaLite

测试几何是否有效。

举例:

Zipcode.objects.filter(poly__isvalid=True)

后端

SQL 等效方式

MariaDB, MySQL, PostGIS, SpatiaLite

ST_IsValid(poly)

Oracle

SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(poly, 0.05) = 'TRUE'

Changed in Django 6.0:

MariaDB 12.0.1+ support was added.

geom_type

New in Django 6.0.

Availability: PostGIS, Oracle 23c+, MariaDB, MySQL, SpatiaLite

Returns the geometry type of the geometry field.

举例:

Zipcode.objects.filter(poly__geom_type="POLYGON")

后端

SQL 等效方式

PostGIS

GeometryType(geom)

MariaDB

ST_GeometryType(geom)

MySQL

ST_GeometryType(geom)

Oracle

SDO_GEOMETRY.GET_GTYPE(geom)

SpatiaLite

GeometryType(geom)

overlaps

可用性: PostGIS, Oracle, MariaDB, MySQL, SpatiaLite, PGRaster (Bilateral)

测试几何字段是否在空间上与查找几何重叠。

后端

SQL 等效方式

PostGIS

ST_Overlaps(poly, geom)

Oracle

SDO_OVERLAPS(poly, geom)

MariaDB

ST_Overlaps(poly, geom)

MySQL

ST_Overlaps(poly, geom)

SpatiaLite

Overlaps(poly, geom)

relate

可用性: PostGIS, MariaDB, Oracle, SpatiaLite, PGRaster (Conversion)

Tests if the geometry field is spatially related to the lookup geometry by the values given in the given pattern. This lookup requires a tuple parameter, (geom, pattern); the form of pattern will depend on the spatial backend:

MariaDB、PostGIS 和 SpatiaLite

On these spatial backends the intersection pattern is a string comprising nine characters, which define intersections between the interior, boundary, and exterior of the geometry field and the lookup geometry. The intersection pattern matrix may only use the following characters: 1, 2, T, F, or *. This lookup type allows users to "fine tune" a specific geometric relationship consistent with the DE-9IM model. [1]

几何示例:

# A tuple lookup parameter is used to specify the geometry and
# the intersection pattern (the pattern here is for 'contains').
Zipcode.objects.filter(poly__relate=(geom, "T*T***FF*"))

PostGIS 和 MariaDB 的 SQL 等效方式:

SELECT ... WHERE ST_Relate(poly, geom, 'T*T***FF*')

SpatiaLite 的 SQL 等效方式:

SELECT ... WHERE Relate(poly, geom, 'T*T***FF*')

栅格示例:

Zipcode.objects.filter(poly__relate=(rast, 1, "T*T***FF*"))
Zipcode.objects.filter(rast__2__relate=(rast, 1, "T*T***FF*"))

PostGIS 的 SQL 等效方式:

SELECT ... WHERE ST_Relate(poly, ST_Polygon(rast, 1), 'T*T***FF*')
SELECT ... WHERE ST_Relate(ST_Polygon(rast, 2), ST_Polygon(rast, 1), 'T*T***FF*')

Oracle

Here the relation pattern is comprised of at least one of the nine relation strings: TOUCH, OVERLAPBDYDISJOINT, OVERLAPBDYINTERSECT, EQUAL, INSIDE, COVEREDBY, CONTAINS, COVERS, ON, and ANYINTERACT. Multiple strings may be combined with the logical Boolean operator OR, for example, 'inside+touch'. [2] The relation strings are case-insensitive.

举例:

Zipcode.objects.filter(poly__relate=(geom, "anyinteract"))

Oracle 的 SQL 等效方式:

SELECT ... WHERE SDO_RELATE(poly, geom, 'anyinteract')

touches

可用性: PostGIS, Oracle, MariaDB, MySQL, SpatiaLite

测试几何字段是否在空间上与查找几何接触。

举例:

Zipcode.objects.filter(poly__touches=geom)

后端

SQL 等效方式

PostGIS

ST_Touches(poly, geom)

MariaDB

ST_Touches(poly, geom)

MySQL

ST_Touches(poly, geom)

Oracle

SDO_TOUCH(poly, geom)

SpatiaLite

Touches(poly, geom)

within

可用性: PostGIS, Oracle, MariaDB, MySQL, SpatiaLite, PGRaster (Bilateral)

测试几何字段是否在空间上位于查找几何内部。

举例:

Zipcode.objects.filter(poly__within=geom)

后端

SQL 等效方式

PostGIS

ST_Within(poly, geom)

MariaDB

ST_Within(poly, geom)

MySQL

ST_Within(poly, geom)

Oracle

SDO_INSIDE(poly, geom)

SpatiaLite

Within(poly, geom)

left

可用性: PostGIS, PGRaster (Conversion)

测试几何字段的边界框是否严格位于查找几何的边界框左侧。

举例:

Zipcode.objects.filter(poly__left=geom)

PostGIS 等效方式:

SELECT ... WHERE poly << geom

right

可用性: PostGIS, PGRaster (Conversion)

测试几何字段的边界框是否严格位于查找几何的边界框右侧。

举例:

Zipcode.objects.filter(poly__right=geom)

PostGIS 等效方式:

SELECT ... WHERE poly >> geom

overlaps_left

可用性: PostGIS, PGRaster (Bilateral)

测试几何字段的边界框是否与查找几何的边界框重叠或位于其左侧。

举例:

Zipcode.objects.filter(poly__overlaps_left=geom)

PostGIS 等效方式:

SELECT ... WHERE poly &< geom

overlaps_right

可用性: PostGIS, PGRaster (Bilateral)

测试几何字段的边界框是否与查找几何的边界框重叠或位于其右侧。

举例:

Zipcode.objects.filter(poly__overlaps_right=geom)

PostGIS 等效方式:

SELECT ... WHERE poly &> geom

overlaps_above

可用性: PostGIS, PGRaster (Conversion)

测试几何字段的边界框是否与查找几何的边界框重叠或位于其上方。

举例:

Zipcode.objects.filter(poly__overlaps_above=geom)

PostGIS 等效方式:

SELECT ... WHERE poly |&> geom

overlaps_below

可用性: PostGIS, PGRaster (Conversion)

测试几何字段的边界框是否与查找几何的边界框重叠或位于其下方。

举例:

Zipcode.objects.filter(poly__overlaps_below=geom)

PostGIS 等效方式:

SELECT ... WHERE poly &<| geom

strictly_above

可用性: PostGIS, PGRaster (Conversion)

测试几何字段的边界框是否严格位于查找几何的边界框上方。

举例:

Zipcode.objects.filter(poly__strictly_above=geom)

PostGIS 等效方式:

SELECT ... WHERE poly |>> geom

strictly_below

可用性: PostGIS, PGRaster (Conversion)

测试几何字段的边界框是否严格位于查找几何的边界框下方。

举例:

Zipcode.objects.filter(poly__strictly_below=geom)

PostGIS 等效方式:

SELECT ... WHERE poly <<| geom

距离查找

可用性: PostGIS, Oracle, MariaDB, MySQL, SpatiaLite, PGRaster (Native)

有关执行距离查询的概述,请参见 距离查询介绍

距离查找具有以下形式:

<field>__<distance lookup>=(<geometry/raster>, <distance value>[, "spheroid"])
<field>__<distance lookup>=(<raster>, <band_index>, <distance value>[, "spheroid"])
<field>__<band_index>__<distance lookup>=(<raster>, <band_index>, <distance value>[, "spheroid"])

传递给距离查找的值是一个元组;前两个值是必需的,分别是要计算距离的几何对象和距离值(可以是字段单位的数字、一个 Distance 对象或一个 查询表达式)。要传递一个波段索引给查找,可以使用一个包含三个元素的元组,其中第二个条目是波段索引。

在除了 dwithin 之外的每个距离查找中,都可以包含一个可选元素 'spheroid',以便在具有大地测量坐标系统的字段上使用更准确的大地测量距离计算函数。

在 PostgreSQL 上,'spheroid' 选项使用 ST_DistanceSpheroid 而不是 ST_DistanceSphere。对于投影坐标系统,将使用更简单的 ST_Distance 函数。对于基于椭球体的查找,栅格将被转换为几何对象。

distance_gt

返回距离几何字段到查找几何的距离大于给定距离值的模型。

举例:

Zipcode.objects.filter(poly__distance_gt=(geom, D(m=5)))

后端

SQL 等效方式

PostGIS

ST_Distance/ST_Distance_Sphere(poly, geom) > 5

MariaDB

ST_Distance(poly, geom) > 5

MySQL

ST_Distance(poly, geom) > 5

Oracle

SDO_GEOM.SDO_DISTANCE(poly, geom, 0.05) > 5

SpatiaLite

Distance(poly, geom) > 5

distance_gte

返回距离几何字段到查找几何的距离大于或等于给定距离值的模型。

举例:

Zipcode.objects.filter(poly__distance_gte=(geom, D(m=5)))

后端

SQL 等效方式

PostGIS

ST_Distance/ST_Distance_Sphere(poly, geom) >= 5

MariaDB

ST_Distance(poly, geom) >= 5

MySQL

ST_Distance(poly, geom) >= 5

Oracle

SDO_GEOM.SDO_DISTANCE(poly, geom, 0.05) >= 5

SpatiaLite

Distance(poly, geom) >= 5

distance_lt

返回距离几何字段到查找几何的距离小于给定距离值的模型。

举例:

Zipcode.objects.filter(poly__distance_lt=(geom, D(m=5)))

后端

SQL 等效方式

PostGIS

ST_Distance/ST_Distance_Sphere(poly, geom) < 5

MariaDB

ST_Distance(poly, geom) < 5

MySQL

ST_Distance(poly, geom) < 5

Oracle

SDO_GEOM.SDO_DISTANCE(poly, geom, 0.05) < 5

SpatiaLite

Distance(poly, geom) < 5

distance_lte

返回距离几何字段到查找几何的距离小于或等于给定距离值的模型。

举例:

Zipcode.objects.filter(poly__distance_lte=(geom, D(m=5)))

后端

SQL 等效方式

PostGIS

ST_Distance/ST_Distance_Sphere(poly, geom) <= 5

MariaDB

ST_Distance(poly, geom) <= 5

MySQL

ST_Distance(poly, geom) <= 5

Oracle

SDO_GEOM.SDO_DISTANCE(poly, geom, 0.05) <= 5

SpatiaLite

Distance(poly, geom) <= 5

dwithin

返回距离几何字段到查找几何的距离在给定距离内的模型。请注意,如果目标几何对象位于投影坐标系统中,您只能提供 Distance 对象。对于地理几何对象,您应该使用几何字段的单位(例如,对于 WGS84 使用度)。

举例:

Zipcode.objects.filter(poly__dwithin=(geom, D(m=5)))

后端

SQL 等效方式

PostGIS

ST_DWithin(poly, geom, 5)

Oracle

SDO_WITHIN_DISTANCE(poly, geom, 5)

SpatiaLite

PtDistWithin(poly, geom, 5)

聚合函数

Django 提供了一些与地理信息系统相关的聚合函数。有关如何使用这些聚合函数的详细信息,请参阅 有关聚合的主题指南

关键字参数

描述

tolerance

This keyword is for Oracle only. It is for the tolerance value used by the SDOAGGRTYPE procedure; the Oracle documentation has more details.

例如:

>>> from django.contrib.gis.db.models import Extent, Union
>>> WorldBorder.objects.aggregate(Extent("mpoly"), Union("mpoly"))

Collect

class Collect(geo_field, filter=None)[source]

Availability: PostGIS, MariaDB, MySQL, SpatiaLite

从几何列返回一个 GEOMETRYCOLLECTIONMULTI 几何对象。这类似于简化版本的 Union 聚合,但它可以比执行联合操作快几个数量级,因为它将几何对象汇总到一个集合或多重对象中,而不关心边界的消解。

Changed in Django 6.0:

MariaDB 12.0.1+ support was added.

Extent

class Extent(geo_field, filter=None)[source]

可用性: PostGIS, Oracle, SpatiaLite

返回 QuerySet 中所有 geo_field 的范围,以一个包括左下坐标和右上坐标的 4-元组表示。

例如:

>>> qs = City.objects.filter(name__in=("Houston", "Dallas")).aggregate(Extent("poly"))
>>> print(qs["poly__extent"])
(-96.8016128540039, 29.7633724212646, -95.3631439208984, 32.782058715820)

Extent3D

class Extent3D(geo_field, filter=None)[source]

可用性: PostGIS

返回 QuerySet 中所有 geo_field 的 3D 范围,以一个包括左下坐标和右上坐标的 6-元组表示(每个坐标包含 x、y 和 z 坐标)。

例如:

>>> qs = City.objects.filter(name__in=("Houston", "Dallas")).aggregate(Extent3D("poly"))
>>> print(qs["poly__extent3d"])
(-96.8016128540039, 29.7633724212646, 0, -95.3631439208984, 32.782058715820, 0)

MakeLine

class MakeLine(geo_field, filter=None)[source]

可用性: PostGIS, SpatiaLite

QuerySet 中的点字段几何构造一个 LineString。目前,对查询集进行排序不会产生影响。

例如:

>>> qs = City.objects.filter(name__in=("Houston", "Dallas")).aggregate(MakeLine("poly"))
>>> print(qs["poly__makeline"])
LINESTRING (-95.3631510000000020 29.7633739999999989, -96.8016109999999941 32.7820570000000018)

Union

class Union(geo_field, filter=None)[source]

可用性: PostGIS, Oracle, SpatiaLite

这个方法返回一个包含查询集中每个几何对象联合的 GEOSGeometry 对象。请注意,使用 Union 是处理器密集型的操作,在大型查询集上可能需要较长时间。

Note

如果使用这个方法的计算时间太昂贵,可以考虑使用 Collect 代替。

例如:

>>> u = Zipcode.objects.aggregate(Union(poly))  # This may take a long time.
>>> u = Zipcode.objects.filter(poly__within=bbox).aggregate(
...     Union(poly)
... )  # A more sensible approach.

脚注