Spatial Indexing not working with ST_DISTANCE queries and '<'

Question

Spatial indexing does not seem to be working on a collection which contains a document with GeoJson coordinates. I've tried using the default indexing policy which inherently provides spatial indexing on all fields.

I've tried creating a new Cosmos Db account, database, and collection from scratch without any success of getting the spatial indexing to work with ST_DISTANCE query.

I've setup a simple collection with the following indexing policy:

{
    "indexingMode": "consistent",
    "automatic": true,
    "includedPaths": [
        {
            "path": "/\"location\"/?",
            "indexes": [
                {
                    "kind": "Spatial",
                    "dataType": "Point"
                },
                {
                    "kind": "Range",
                    "dataType": "Number",
                    "precision": -1
                },
                {
                    "kind": "Range",
                    "dataType": "String",
                    "precision": -1
                }
            ]
        }
    ],
    "excludedPaths": [
        {
            "path": "/*",
        },
        {
            "path": "/\"_etag\"/?"
        }
    ]
}

The document that I've inserted into the collection:

{
    "id": "document1",
    "type": "Type1",
    "location": {
        "type": "Point",
        "coordinates": [
            -50,
            50
        ]
    },
    "name": "TestObject"
}

The query that should return the single document in the collection:

SELECT * FROM f WHERE f.type = "Type1" and ST_DISTANCE(f.location, {'type': 'Point', 'coordinates':[-50,50]}) < 200000

Is not returning any results. If I explicitly query without using the spatial index like so:

SELECT * FROM f WHERE f.type = "Type1" and ST_DISTANCE({'type': 'Point', 'coordinates':[f.location.coordinates[0],f.location.coordinates[1]]}, {'type': 'Point', 'coordinates':[-50,50]}) < 200000

It returns the document as it should, but doesn't take advantage of the indexing which I will need because I will be storing a lot of coordinates.

This seems to be the same issue referenced here. If I add a second document far away and change the '<' to '>' in the first query it works!

I should mention this is only occurring on Azure. When I use the Azure Cosmos Db Emulator it works perfectly! What is going on here?! Any tips or suggestions are much appreciated.

UPDATE: I found out the reason that the query works on the Emulator and not Azure - the database on the emulator doesn't have provisioned (shared) throughput among its collections, while I made the database in Azure with provisioned throughput to keep costs down (i.e. 4 collections sharing 400 RU/s). I created a non provisioned throughput database in Azure and the query works with spatial indexing!! I will log this issue with Microsoft to see if there is a reason why this is the case?

Mike Ubezzi Mike Ubezzi · Accepted Answer · 2019-05-30T04:40:29

Thanks for following up with additional details with regards to a fixed collection being the solution but, I did want to get some additional information.

The Cosmos DB Emulator now supports containers:

By default, you can create up to 25 fixed size containers (only supported using Azure Cosmos DB SDKs), or 5 unlimited containers using the Azure Cosmos Emulator. By modifying the PartitionCount value, you can create up to 250 fixed size containers or 50 unlimited containers, or any combination of the two that does not exceed 250 fixed size containers (where one unlimited container = 5 fixed size containers). However it's not recommended to set up the emulator to run with more than 200 fixed size containers. Because of the overhead that it adds to the disk IO operations, which result in unpredictable timeouts when using the endpoint APIs.

So, I want to see which version of the Emulator you were using. Current version is azure-cosmosdb-emulator-2.2.2.

Spatial Indexing not working with ST_DISTANCE queries and '<'

1 Answers