CODEWITHSUNDEEP
azure-cosmosdb
Kristian Damian
Kristian Damian

Reputation: 1356

Cosmos DB query return points outside the polygon using ST_Within

Im using the query explorer in cosmosdb for searching points who are inside a polygon, my polygon in this sample is this (can be saw in geojson.io)

{
   "type": "FeatureCollection",
   "features": [
   {
       "type": "Feature",
       "geometry":{
              "type":"Polygon",
              "coordinates":   
                [[[-107.44131585954042, 24.801824950217672], 
                [-107.43361255454303, 24.801824950217672], 
                [-107.43361255454303, 24.791345071112183], 
                [-107.44131585954042, 24.791345071112183],
                [-107.44131585954042, 24.801824950217672]]]
      }, 
    "properties": {
        "name": "The Polygon"
    }
  },
  {
    "type": "Feature",
    "geometry": {
        "type": "Point",
        "coordinates": [-107.437779, 24.798064]
    },
    "properties": {
        "name": "The point inside the polygon"
    }
  },
  {
    "type": "Feature",
    "geometry": {
        "type": "Point",
        "coordinates": [-107.39355, 24.792837]
    },
    "properties": {
        "name": "The point offside the polygon"
    }
  }
 ]
}

but when i search in the query explorer, cosmosdb retrieve me both points This is my query

SELECT * FROM root 
WHERE ST_Within(
    root["Punto"], {"type": "Polygon", "coordinates":   
    [[[-107.44131585954042, 24.801824950217672], 
      [-107.43361255454303, 24.801824950217672], 
      [-107.43361255454303, 24.791345071112183], 
      [-107.44131585954042, 24.791345071112183],
      [-107.44131585954042, 24.801824950217672]]]
      })

root["Punto"] is a valid GeoJSON Point, i checked it with the ST_ISVALID function, and also, if i use the function ST_DISTANCE, checking if the distance between the point and the polygon is greater than zero, the data retrieved is correct, but i don't know if this approach is correct

This is my second query using ST_DISTANCE

SELECT root.NombreUbicacion, root.Punto
from root
where ST_DISTANCE (root.Punto, {"type": "Polygon", "coordinates":   
                               [[[-107.44131585954042, 24.801824950217672], 
                               [-107.43361255454303, 24.801824950217672], 
                               [-107.43361255454303, 24.791345071112183], 
                               [-107.44131585954042, 24.791345071112183],
                               [-107.44131585954042, 24.801824950217672]]]
     }) > 0

Upvotes: 3

Views: 1134

Answers (2)

Kristian Damian
Kristian Damian

Reputation: 1356

I'm send a mail to [email protected] and they told me that i need to rearrange the polygon

The behavior that you are seeing is because the points in the polygon are in the reverse order. If you look at the documentation

"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."

So, i change the query to this, a it works like a charm

SELECT * FROM root 
WHERE ST_Within(
root["Punto"], {"type": "Polygon", "coordinates":   
[[[-107.44131585954042, 24.801824950217672],
  [-107.44131585954042, 24.791345071112183],
  [-107.43361255454303, 24.791345071112183], 
  [-107.43361255454303, 24.801824950217672], 
  [-107.44131585954042, 24.801824950217672]]]
})

Thanks to the cosmosdb team

Upvotes: 10

Fei Han
Fei Han

Reputation: 27793

Under "Spatial SQL built-in functions" section in this article, you will see:

enter image description here

Updated:

As you said, using the point {'type': 'Point', 'coordinates':[-107.437779, 24.798064]} it returns the distance.

enter image description here

But based on my test with many other valid GeoJSON Points, ST_DISTANCE (point_expr, polygon_expr) often return 0, so using ST_DISTANCE (point_expr, polygon_expr) in WHERE condition as a filter may not work as expected.

enter image description here

I can not find official documentations or blogs that explain the above issue, if possible, you can contact the team on [email protected] to get details about ST_DISTANCE (spatial_expr, spatial_expr).

Upvotes: 1

Related Questions