azure - 宇宙数据库 : Why geography and geometry gives two different results with ST_WITHIN

Question

我们最近在应用程序中遇到了一个问题。我们在前端使用 leafletJs，在 map 的一部分边界内有标记。这些标记应该根据这些 map 的边界进行过滤，但显然有些标记消失了。

我们对此进行了调查，直到建立了数据库。当我们使用这个网站时http://geojson.io/为了可视化这些边界内的标记，我们可以看到边界内的标记。 geojson.io

但是当我们使用 ST_WITHIN 查询在 cosmos 数据库中给出这些坐标时，它会返回 false ST_WITHIN returns false

查询:

SELECT ST_WITHIN({'type': 'Point', 'coordinates': [
            7.753310203552246,
            45.12137985229492
        ]},
    {'type': 'Polygon', 'coordinates': [[
    [30.805664062500004, 59.40036514079251],
    [-12.260742187500002, 59.40036514079251],
    [-12.260742187500002, 43.35713822211053],
    [30.805664062500004, 43.35713822211053],
    [30.805664062500004, 59.40036514079251]
]]})

但是，当我们在 SQL 数据库中执行相同操作时，它会返回 true:ST_WITHIN returns true查询:

SELECT geometry::STGeomFromText('POINT(7.753310203552246 45.12137985229492)', 4326).STWithin(geometry::STGeomFromText('POLYGON((
30.805664062500004 59.40036514079251,
-12.260742187500002 59.40036514079251,
-12.260742187500002 43.35713822211053,
30.805664062500004 43.35713822211053,
30.805664062500004 59.40036514079251))', 4326))

然后我们发现我们可以在“比例和设置”选项卡下更改地理空间配置。将其更改为“几何”时，在应用边界框和地理空间索引后效果良好。

但是我们不清楚为什么我们需要在地理数据(经度、纬度)上使用几何。既然我们使用的是世界地图，为什么这不适用于地理配置呢？

另外，文档指定我们需要使用边界框。我们目前已经配置了一个，但是这样可以吗？ Bounding Boxes .

配置:

{
    "indexingMode": "consistent",
    "automatic": true,
    "includedPaths": [
        {
            "path": "/*"
        }
    ],
    "excludedPaths": [
        {
            "path": "/\"_etag\"/?"
        }
    ],
    "spatialIndexes": [
        {
            "path": "/*",
            "types": [
                "Point",
                "Polygon",
                "MultiPolygon",
                "LineString"
            ],
            "boundingBox": {
                "xmin": -180,
                "ymin": -90,
                "xmax": 180,
                "ymax": 90
            }
        }
    ]
}

但是，如果不这样做，我们会收到错误:

Failed to update container deviceLocations: 
{
  "code": 400,
  "body": {
    "code": "BadRequest",
    "message": "Message: {\"Errors\":[\"Required parameter 'boundingBox' for 'Geometry' collection is missing in spatial path '\\/*'\"]}\r\nActivityId: db41053e-d64a-4f58-ab5a-7b8ea90906a2, Request URI: /apps/18de512a-1eb7-4a07-a798-09120b362e04/services/9445748a-d086-4453-8c41-0adcfa7ddb0c/partitions/50e43654-77e3-424a-959f-fbac363f19cc/replicas/132355285759634497p, RequestStats: \r\nRequestStartTime: 2020-06-04T08:48:17.7891867Z, RequestEndTime: 2020-06-04T08:48:17.7992135Z, Number of regions attempted:1\r\nResponseTime: 2020-06-04T08:48:17.7992135Z, StoreResult: StorePhysicalAddress: rntbd://10.0.0.127:11300/apps/18de512a-1eb7-4a07-a798-09120b362e04/services/9445748a-d086-4453-8c41-0adcfa7ddb0c/partitions/50e43654-77e3-424a-959f-fbac363f19cc/replicas/132355285759634497p, LSN: 40, GlobalCommittedLsn: 40, PartitionKeyRangeId: , IsValid: True, StatusCode: 400, SubStatusCode: 0, RequestCharge: 1.24, ItemLSN: -1, SessionToken: -1#40, UsingLocalLSN: False, TransportException: null, ResourceType: Collection, OperationType: Replace\r\n, SDK: Microsoft.Azure.Documents.Common/2.11.0"
  },
  "headers": {
    "access-control-allow-credentials": "true",
    "access-control-allow-origin": "https://cosmos.azure.com",
    "content-location": "https://ac-cdb-t-app-10001211-atp.documents.azure.com/dbs/statisticsDB/colls/deviceLocations",
    "content-type": "application/json",
    "lsn": "40",
    "strict-transport-security": "max-age=31536000",
    "x-ms-activity-id": "db41053e-d64a-4f58-ab5a-7b8ea90906a2",
    "x-ms-cosmos-llsn": "40",
    "x-ms-cosmos-quorum-acked-llsn": "40",
    "x-ms-current-replica-set-size": "4",
    "x-ms-current-write-quorum": "3",
    "x-ms-gatewayversion": "version=2.11.0",
    "x-ms-global-committed-lsn": "40",
    "x-ms-last-state-change-utc": "Wed, 03 Jun 2020 20:51:02.831 GMT",
    "x-ms-number-of-read-regions": "0",
    "x-ms-quorum-acked-lsn": "40",
    "x-ms-request-charge": "1.24",
    "x-ms-schemaversion": "1.9",
    "x-ms-serviceversion": "version=2.11.0.0",
    "x-ms-session-token": "0:-1#40",
    "x-ms-transport-request-id": "96650",
    "x-ms-xp-role": "1",
    "x-ms-throttle-retry-count": 0,
    "x-ms-throttle-retry-wait-time-ms": 0
  },
  "activityId": "db41053e-d64a-4f58-ab5a-7b8ea90906a2"
}

Answer 1

坐标返回 false 的原因是因为在地理中绘制在圆形地球上，而几何图形是平面。

如果您获取坐标并将其插入 Great Circle Mapper你可以看到它就在外面。

我会仔细检查您是否处于 SQL Server 中的地理模式。您应该得到相同的结果。

希望这有帮助。