使用 Azure Cosmos DB 查询地理空间数据Querying geospatial data with Azure Cosmos DB

适用于: SQL API

本文介绍了如何使用 SQL 和 LINQ 查询 Azure Cosmos DB 中的地理空间数据。This article will cover how to query geospatial data in Azure Cosmos DB using SQL and LINQ. 目前,只有 Azure Cosmos DB SQL API 帐户支持存储和访问地理空间数据。Currently storing and accessing geospatial data is supported by Azure Cosmos DB SQL API accounts only. Azure Cosmos DB 支持以下用于查询地理空间的开放地理空间信息联盟 (OGC) 内置函数。Azure Cosmos DB supports the following Open Geospatial Consortium (OGC) built-in functions for geospatial querying. 有关 SQL 语言中的整套内置函数的详细信息,请参阅 Azure Cosmos DB 中的查询系统函数For more information on the complete set of built-in functions in the SQL language, see Query System Functions in Azure Cosmos DB.

空间 SQL 内置函数Spatial SQL built-in functions

下面列出了可用于在 Azure Cosmos DB 中进行查询的地理空间系统函数:Here is a list of geospatial system functions useful for querying in Azure Cosmos DB:

使用情况Usage 说明Description
ST_DISTANCE (spatial_expr, spatial_expr)ST_DISTANCE (spatial_expr, spatial_expr) 返回两个 GeoJSON 点、多边形或 LineString 表达式之间的距离。Returns the distance between the two GeoJSON Point, Polygon, or LineString expressions.
ST_WITHIN (spatial_expr, spatial_expr)ST_WITHIN (spatial_expr, spatial_expr) 返回一个布尔表达式,指示第一个 GeoJSON 对象(点、多边形或 LineString)是否在第二个 GeoJSON 对象 (点、多边形或 LineString)内。Returns a Boolean expression indicating whether the first GeoJSON object (Point, Polygon, or LineString) is within the second GeoJSON object (Point, Polygon, or LineString).
ST_INTERSECTS (spatial_expr, spatial_expr)ST_INTERSECTS (spatial_expr, spatial_expr) 返回一个布尔表达式,指示两个指定的 GeoJSON 对象 (点、多边形或 LineString)是否相交。Returns a Boolean expression indicating whether the two specified GeoJSON objects (Point, Polygon, or LineString) intersect.
ST_ISVALIDST_ISVALID 返回一个布尔值,指示指定的 GeoJSON 点、多边形或 LineString 表达式是否有效。Returns a Boolean value indicating whether the specified GeoJSON Point, Polygon, or LineString expression is valid.
ST_ISVALIDDETAILEDST_ISVALIDDETAILED 返回一个 JSON 值,其中包含一个布尔值,该值指示指定的 GeoJSON 点、多边形或 LineString 表达式是否有效。Returns a JSON value containing a Boolean value if the specified GeoJSON Point, Polygon, or LineString expression is valid. 如果无效,则它将原因返回为字符串值。If invalid, it returns the reason as a string value.

空间函数可用于对空间数据执行邻近查询。Spatial functions can be used to perform proximity queries against spatial data. 例如,以下查询使用 ST_DISTANCE 内置函数返回所有家族文档,且这些文档在指定位置的 30 公里内。For example, here's a query that returns all family documents that are within 30 km of the specified location using the ST_DISTANCE built-in function.

查询Query

    SELECT f.id
    FROM Families f
    WHERE ST_DISTANCE(f.location, {"type": "Point", "coordinates":[31.9, -4.8]}) < 30000

结果Results

    [{
      "id": "WakefieldFamily"
    }]

如果在索引策略中包含空间索引,则将通过索引有效地进行“距离查询”。If you include spatial indexing in your indexing policy, then "distance queries" will be served efficiently through the index. 有关空间索引编制的详细信息,请参阅地理空间索引编制For more information on spatial indexing, see geospatial indexing. 如果不存在指定路径的空间索引,则查询将扫描容器。If you don't have a spatial index for the specified paths, the query will do a scan of the container.

ST_WITHIN 可用于检查点是否在多边形内。ST_WITHIN can be used to check if a point lies within a Polygon. 多边形通常用来表示边界,例如邮政编码、省/自治区边界或自然构成物。Commonly Polygons are used to represent boundaries like zip codes, state boundaries, or natural formations. 再次说明,如果在索引策略中包含空间索引,则将通过索引有效地进行“within”查询。Again if you include spatial indexing in your indexing policy, then "within" queries will be served efficiently through the index.

ST_WITHIN 中的多边形参数只能包含单个环形,也就是说,多边形本身不能包含洞。Polygon arguments in ST_WITHIN can contain only a single ring, that is, the Polygons must not contain holes in them.

查询Query

    SELECT *
    FROM Families f
    WHERE ST_WITHIN(f.location, {
        "type":"Polygon",
        "coordinates": [[[31.8, -5], [32, -5], [32, -4.7], [31.8, -4.7], [31.8, -5]]]
    })

结果Results

    [{
      "id": "WakefieldFamily",
    }]

备注

与 Azure Cosmos DB 查询中不匹配类型的工作方式类似,如果任一参数中指定的位置值格式不正确或无效,则会评估为“未定义” ,并且会在查询结果中跳过已评估的文档。Similar to how mismatched types work in Azure Cosmos DB query, if the location value specified in either argument is malformed or invalid, then it evaluates to undefined and the evaluated document to be skipped from the query results. 如果查询没有返回任何结果,请运行 ST_ISVALIDDETAILED 进行调试,以了解空间类型无效的原因。If your query returns no results, run ST_ISVALIDDETAILED to debug why the spatial type is invalid.

Azure Cosmos DB 还支持执行反向查询,即可在 Azure Cosmos DB 中索引多边形或线,并查询包含指定点的区域。Azure Cosmos DB also supports performing inverse queries, that is, you can index polygons or lines in Azure Cosmos DB, then query for the areas that contain a specified point. 这种模式通常在物流中用于识别如卡车进入或离开指定区域的时间等情况。This pattern is commonly used in logistics to identify, for example, when a truck enters or leaves a designated area.

查询Query

    SELECT *
    FROM Areas a
    WHERE ST_WITHIN({"type": "Point", "coordinates":[31.9, -4.8]}, a.location)

结果Results

    [{
      "id": "MyDesignatedLocation",
      "location": {
        "type":"Polygon",
        "coordinates": [[[31.8, -5], [32, -5], [32, -4.7], [31.8, -4.7], [31.8, -5]]]
      }
    }]

ST_ISVALIDST_ISVALIDDETAILED 可用来检查空间对象是否有效。ST_ISVALID and ST_ISVALIDDETAILED can be used to check if a spatial object is valid. 例如,下列查询检查纬度值 (-132.8) 超出范围的点的有效性。For example, the following query checks the validity of a point with an out of range latitude value (-132.8). ST_ISVALID 仅返回一个布尔值,ST_ISVALIDDETAILED 则返回布尔值和字符串,字符串中包含认为它无效的原因。ST_ISVALID returns just a Boolean value, and ST_ISVALIDDETAILED returns the Boolean and a string containing the reason why it is considered invalid.

查询Query

    SELECT ST_ISVALID({ "type": "Point", "coordinates": [31.9, -132.8] })

结果Results

    [{
      "$1": false
    }]

这些函数也可以用来验证多边形。These functions can also be used to validate Polygons. 例如,在这里我们使用 ST_ISVALIDDETAILED 来验证未闭合的多边形。For example, here we use ST_ISVALIDDETAILED to validate a Polygon that is not closed.

查询Query

    SELECT ST_ISVALIDDETAILED({ "type": "Polygon", "coordinates": [[ 
        [ 31.8, -5 ], [ 31.8, -4.7 ], [ 32, -4.7 ], [ 32, -5 ] 
        ]]})

结果Results

    [{
       "$1": { 
            "valid": false, 
            "reason": "The Polygon input is not valid because the start and end points of the ring number 1 are not the same. Each ring of a Polygon must have the same start and end points." 
          }
    }]

.NET SDK 中的 LINQ 查询LINQ querying in the .NET SDK

SQL .NET SDK 还提供存根方法 Distance()Within(),供用户在 LINQ 表达式中使用。The SQL .NET SDK also providers stub methods Distance() and Within() for use within LINQ expressions. SQL LINQ 提供程序会将这些方法调用转换为等效的 SQL 内置函数调用(分别为 ST_DISTANCE 和 ST_WITHIN)。The SQL LINQ provider translates this method calls to the equivalent SQL built-in function calls (ST_DISTANCE and ST_WITHIN respectively).

以下是 LINQ 查询的示例,该查询使用 LINQ 在 Azure Cosmos 容器中查找其 location 值在指定点的 30 公里半径内的所有文档。Here's an example of a LINQ query that finds all documents in the Azure Cosmos container whose location value is within a radius of 30 km of the specified point using LINQ.

LINQ 距离查询LINQ query for Distance

    foreach (UserProfile user in container.GetItemLinqQueryable<UserProfile>(allowSynchronousQueryExecution: true)
        .Where(u => u.ProfileType == "Public" && u.Location.Distance(new Point(32.33, -4.66)) < 30000))
    {
        Console.WriteLine("\t" + user);
    }

同样地,以下查询查找所有文档,这些文档的 location 均在指定的方框/多边形内。Similarly, here's a query for finding all the documents whose location is within the specified box/Polygon.

LINQ Within 查询LINQ query for Within

    Polygon rectangularArea = new Polygon(
        new[]
        {
            new LinearRing(new [] {
                new Position(31.8, -5),
                new Position(32, -5),
                new Position(32, -4.7),
                new Position(31.8, -4.7),
                new Position(31.8, -5)
            })
        });

    foreach (UserProfile user in container.GetItemLinqQueryable<UserProfile>(allowSynchronousQueryExecution: true)
         .Where(a => a.Location.Within(rectangularArea)))
    {
        Console.WriteLine("\t" + user);
    }

后续步骤Next steps

已经学会如何开始使用 Azure Cosmos DB 中的地理空间支持,下一步现在可以:Now that you have learned how to get started with geospatial support in Azure Cosmos DB, next you can: