ST_CONTAINS() giving FALSE even if Point lies within polygon. google-bigquery - Stack Overflow

The Bigquery ST_Contains() and ST_WItHIN() function in the below query is returning FALSE, however the point lies within the polygon:

WITH bounds AS (
SELECT ST_GeogFromText('POLYGON((100.1953125 18.972632184382203, 100.1953125 72.49968929857847, -166.9921875 72.49968929857847, -166.9921875 18.972632184382203, 100.1953125 18.972632184382203))') AS polygon
)
SELECT
ST_Contains (bounds.polygon, ST_GeogPoint(-95.712891, 37.09024)) AS does_bounds_contains, -- Example point in the US
ST_WItHIN(bounds.polygon, ST_GeogPoint(-95.712891, 37.09024)) AS is_within_bounds -- Example point in the US
FROM
bounds;

google-bigquery

I have checked plotting the polygon on third party website and clearly see that the mentioned point exists in the polygon. However, bigquery ST_Contains() function returns FALSE.

Can you please help me understand what is wrong here?

The Bigquery ST_Contains() and ST_WItHIN() function in the below query is returning FALSE, however the point lies within the polygon:

WITH bounds AS (
SELECT ST_GeogFromText('POLYGON((100.1953125 18.972632184382203, 100.1953125 72.49968929857847, -166.9921875 72.49968929857847, -166.9921875 18.972632184382203, 100.1953125 18.972632184382203))') AS polygon
)
SELECT
ST_Contains (bounds.polygon, ST_GeogPoint(-95.712891, 37.09024)) AS does_bounds_contains, -- Example point in the US
ST_WItHIN(bounds.polygon, ST_GeogPoint(-95.712891, 37.09024)) AS is_within_bounds -- Example point in the US
FROM
bounds;

google-bigquery

I have checked plotting the polygon on third party website and clearly see that the mentioned point exists in the polygon. However, bigquery ST_Contains() function returns FALSE.

Can you please help me understand what is wrong here?

• google-bigquery

Share Improve this question Follow asked 15 hours ago Pradeep BobalPradeep Bobal 1

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Use below instead.

WITH bounds AS (
SELECT ST_GeogFromText('''POLYGON((
  100.1953125 18.972632184382203, 
  100.1953125 72.49968929857847, 
  -166.9921875 72.49968929857847, 
  -166.9921875 18.972632184382203, 
  100.1953125 18.972632184382203))
''', oriented => true) AS polygon
)
SELECT
ST_Contains(polygon, ST_GeogPoint(-95.712891, 37.09024)) AS does_bounds_contains, -- Example point in the US
ST_Within(ST_GeogPoint(-95.712891, 37.09024), polygon) AS is_within_bounds -- Example point in the US
FROM
bounds;   

with output

Note

1. use of oriented => true
   oriented: A named argument with a BOOL literal. If the value is TRUE, any polygons in the input are assumed to be oriented as follows: when traveling along the boundary of the polygon in the order of the input vertices, the interior of the polygon is on the left. This allows WKT to represent polygons larger than a hemisphere. See also ST_MAKEPOLYGONORIENTED, which is similar to ST_GEOGFROMTEXT with oriented=TRUE. If the value is FALSE or omitted, this function returns the polygon with the smaller area.

and

2. switched point and polygon in ST_Within
   Given two geographies a and b, ST_WITHIN(a, b) returns the same result as ST_CONTAINS(b, a). Note the opposite order of arguments.