Azure Cosmos DB Geospatial Calculation ST_WITHIN and ST_DISTANCE

Question

Hi there,

We are using Azure Cosmos DB to store our telemetry data. We have implemented a (geo zone/ geo-fence) feature where we have to check if a point is within a Polygon. To do this, we are using ST_WITHIN(gps_point(GeoJSON), user_created_polygon(GeoJSON)).

Without any geo zone filtering the map looks like this,

When we put a Rectangle type geo zone, It works perfectly,

But when we put a custom Polygon shape the filter doesn't work anymore.

We couldn't resolve, Why this issue is happening. Our Cosmos DB is set to Geography as a geoSpatial configuration.
We have a similar issue for Circle shape as well, for which we use ST_DISTANCE

We are using Azure Maps to plot our data.

It would be really helpful If the community could give us some lead on where we should configure or change to make this work.

Thanks

Answer 1

@Tahmid Eshayat

Here is the response from our PG team.

Points within a Polygon must be specified in counter-clockwise order. A Polygon specified in clockwise order represents the inverse of the region within it. Learn more here.

They noticed that in the first query you sent over, the Polygon coordinates defined are clockwise order. Is this intended?

Keep in mind that this is a valid Polygon (ST_ISVALID will return true). Defining a Polygon in clockwise order defines the area that is the inverse of the region within the polygon which might account for their unexplainable query results.

This is also covered in the geospatial section of our query & indexing lab in this document.

Please let us know if you have any other question regarding this.

Thanks
Navtej S

Answer 2

If the polygon is drawn by the user, it is possible that it isn't a valid polygon or that it represents the opposite area of what is being rendered. Azure Maps uses GeoJSON and renders it based on that specification, which is fairly relaxed, where as the geography objects in databases typically require polygons to have a specific ring orientation (counter-clockwise order), the same first and last coordinates, and no overlapping edges. Try using the ST_ISVALIDd or ST_ISVALIDDETAILED functions to verify if this is the issue: https://learn.microsoft.com/en-us/azure/cosmos-db/sql-query-geospatial-query