Cosmos DB: Why geography and geometry gives two different results with ST_WITHIN

Question

We recently came across a problem in our application. We are using leafletJs in the front-end were we have markers within the bounds of a part of the map. Those markers should be filtered based on the bounds of those map, but apparently some markers were gone.

We investigated it up until our database. When we use this site http://geojson.io/ to visualize our markers withing those bounds, we can see the marker inside the boundaries. geojson.io

But when we give those coordinates in, in the cosmos db with the ST_WITHIN query, it returns false ST_WITHIN returns false

query:

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]
]]})

However, when we do the same in a SQL database, it returns true: ST_WITHIN returns true query:

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))

We then found that we could change our geospatial configuration under the scale & settings tab. When changing this to Geometry, it works well after applying the bounding box and geospatial indexes.

However it is unclear to us, why we need to use geometry on geographical data (long, lat). Since we are using a world map, why doesn't this work for the geographic configuration.

Plus, the documentation specifies that we need use bounding boxes. We have currently configured one, but is this ok? Bounding Boxes.

Config:

{
    "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
            }
        }
    ]
}

However we get an error when not doing so:

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

The reason the coordinates returns as false is because in geography plots on a round earth and geometry is a flat plane.

If you take your coordinates and plug it into Great Circle Mapper you can see it is just outside.

I would double check you are in geography mode in SQL Server. You should get the same results.

Hope this helps.