Ok, since the polygon seems to be inverted, here is the explanation:
Each polygon on a sphere has complimentary one that occupies everything else on the planet. They share the same boundary, and are described by exactly same vertices. Say, oceans and continents are described by exactly the same coastal line. How do geospatial systems distinguish between them? There are two kind of systems:
- some systems only support polygons smaller than hemisphere, and choose whatever polygon is smaller. So polygon in the example above would mean continents, and there is no way to describe oceans in such systems.
- other systems support both, and use orientation rule to distinguish between the two. BigQuery is one of them, and uses following rule: when walking along boundary, the left side is considered interior. Oceans and continents would be described by same vertices, but in the opposite order.
When walking along the polygon boundary above, the left side is whole globe except small city block - it was inverted. There is nothing technically wrong with it - it is just that BigQuery thinks it describes different thing than what it was supposed to describe.
Note that some BigQuery functions default to simpler semantics from rule (1), e.g.
ST_GeogFromText(wkt) does it, unless you pass second parameter
ST_GeogFromText(wkt, oriented => TRUE). So if you copy-paste the polygon definition in
ST_GeogFromText(wkt) and run the same
ST_CONTAINS you might get different result, but then you are really dealing with a different polygon.
However when loading data, BigQuery always assumes oriented polygons, so you can export geospatial data and load it back with full fidelity.
Run something like this to "fix" (invert) huge polygons:
SET polygon = ST_GeogFromText(ST_AsText(polygon))
WHERE ST_Area(polygon) > 1e14;