Azure Cosmos DB Geospatial Calculation ST_WITHIN and ST_DISTANCE

Tahmid Eshayat 286 Reputation points
2020-09-06T03:39:21.47+00:00

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,

22786-image.png

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

22787-image.png

22808-image.png

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

22738-image.png

22739-image.png

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

Azure Cosmos DB
Azure Cosmos DB
An Azure NoSQL database service for app development.
1,521 questions
Azure Maps
Azure Maps
An Azure service that provides geospatial APIs to add maps, spatial analytics, and mobility solutions to apps.
653 questions
Accepted answer
  1. Navtej Singh Saini 4,221 Reputation points Microsoft Employee
    2020-09-14T22:13:02.793+00:00

    @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

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. rbrundritt 16,456 Reputation points Microsoft Employee
    2020-09-09T04:56:23.507+00:00

    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://video2.skills-academy.com/en-us/azure/cosmos-db/sql-query-geospatial-query