Parameterized queries - can GeoJson object values be used? ...

[bdcoder2]

Consider the following:

Given the following document structure:

// Document using Microsoft.Azure.Cosmos.Spatial.Point ...

   public class test_doc
   {

      public String id { get; set; }

      public Microsoft.Azure.Cosmos.Spatial.Point geopoint { get; set; }

      // More properties
      public String desc { get; set; }

   }

Build a query without parameters ...

bounds_str = "{'type':'Polygon','coordinates':[[[-113.247049,53.657421],[-113.733431,53.657421],[-113.733431,53.393183],[-113.247049,53.393183],[-113.247049,53.657421]]]}";

sql_query_text = "SELECT * FROM testcontainer t WHERE ST_WITHIN( t.geopoint, " + bounds_str + ")";

query_def = new Microsoft.Azure.Cosmos.QueryDefinition( sql_query_text );

When the above query is executed, the query works as expected:

Query Total time: 00:00:00.0067482
Total Request Units consumed: 6.73
Documents found: 2

Now build the SAME query using a parameter ...

sql_query_text = "SELECT * FROM testcontainer t WHERE ST_WITHIN( t.geopoint, @bounds )";

query_def = new Microsoft.Azure.Cosmos.QueryDefinition( sql_query_text );

query_def.WithParameter( "@bounds", bounds_str );

When the above query is executed, we get a different (incorrect) result ...

Query Total time: 00:00:00.0054688
Total Request Units consumed: 3.1
Documents found: 0

Parameterized queries do not seem to work with GeoJson values / objects ?

• Is this true? If so, it should be mentioned in the documentation.
• If not true, please provide an example of a GeoJson object as a parameter.

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: d4787bd5-5e70-c491-0772-5158de8b8624
• Version Independent ID: cb34b3f3-4dcb-0c98-ace5-1da73b2259bc
• Content: Parameterized queries in Azure Cosmos DB
• Content Source: articles/cosmos-db/sql/sql-query-parameterized-queries.md
• Service: cosmos-db
• Sub-service: cosmosdb-sql
• GitHub Login: @timsander1
• Microsoft Alias: tisande

[SaurabhSharma-MSFT]

@bdcoder2 Thanks for your feedback! We will investigate and update as appropriate.

[SaurabhSharma-MSFT]

@timsander1 I am seeing the same behavior in my locals. Can you please provide your inputs if this is supported.

[bdcoder2]

26-Oct-2021 - Additional information:

Tried the following experiment using the CosmosDB Emulator:

Execute the following query in ComosDB Emulator:

    SELECT ST_ISVALID({'type':'Polygon','coordinates':[[[-113.247049,53.657421],[-113.733431,53.657421], 
    [-113.733431,53.393183],[-113.247049,53.393183],[-113.247049,53.657421]]]}) AS b 

The emulator gives the following result, which shows the polygon (geo-JSON string) is valid:

[
    {
        "b": true
    }
]

Next, the following code (C# console application) was executed using a polygon (same JSON string as above) as a parameter:

    bounds_str = "{'type':'Polygon','coordinates':[[[-113.247049,53.657421],[-113.733431,53.657421],[-113.733431,53.393183],[-113.247049,53.393183],[-113.247049,53.657421]]]}";

    QueryDefinition param_sql_query_1 = new QueryDefinition( "SELECT ST_ISVALID(@polygon), ST_ISVALIDDETAILED(@polygon)" )
                         .WithParameter( "@polygon", bounds_str );

    FeedResponse<dynamic> result_1 = await m_rentals_container.GetItemQueryIterator<dynamic>( param_sql_query_1 ).ReadNextAsync();

    Console.WriteLine( $"Result: {result_1.First()}" );

The output is:

Result: {
  "$1": false,
  "$2": {
    "valid": false,
    "reason": "Unable to process the geometry."
  }
}

It appears that using a geo-JSON string in a parameterized query will fail because the runtime is evaluating the polygon as "invalid" and is "unable to process the geometry".

However, if a polygon OBJECT is passed as a parameter, the query will evaluate the polygon as valid.

Lesson learned: Use geospatial OBJECTS as parameters rather than geo-JSON strings as it appears that geo-JSON string parameters are not supported?- verification from Microsoft would be appreciated.

[timsander1]

There are no restrictions about GeoJSON when it comes to parameters. If you're hitting an issue with spatial, this is a bug in the product not the documentation.

Can you file a support ticket and we will try to repro? If you don't have a support plan, you could also open an SDK issue in the Cosmos DB SDK that you were using. Here's the .NET SDK repo - https://github.com/Azure/azure-cosmos-dotnet-v3

[timsander1]

please-close