Is there a trick to using ST_Intersects in Snowflake with a point and a multipolygon?

Question

I can get ST_Intersects to work with a point and a polygon but not a point and a multipolygon. Is there an easy way to split the multipolygon into individual polygons and run ST_Intersects on each of those?

Answer 1

I see no problems intersecting points and multipolygons:

with a as (
select to_geography('POLYGON((0.0 0.0, 1.0 0.0, 1.0 2.0, 0.0 2.0, 0.0 0.0))') as polygon
union all select to_geography('MULTIPOLYGON(((-124.20 42.00, -120.01 41.99, -121.1 42.01, -124.20 42.0)),  ((0.0 0.0, 1.0 0.0, 1.0 2.0, 0.0 2.0, 0.0 0.0)))')
), b as (
  select to_geography('POINT(0.1 0.1)') point
  union all select to_geography('POINT(3.3 3.3)')
  union all select to_geography('POINT(-124.20 42.00)')
)


select ST_ASWKT(point), ST_ASWKT(polygon), st_intersects(polygon, point) intersects
from a, b

The results match exactly what I expected: