Spatial join points and polygons in BigQuery?

Question

I have two tables; one has points (e.g., specific addresses with one geography POINT per row) another has areas (e.g., neighbourhoods with one geography POLYGON per row).

I'd like to join them, to have the neighbourhood associated with the address. Following this question I've tried the following:

SELECT
  pp.full_address,
  gn.name,
  ST_CONTAINS(gn.geography, pp.geopoint) as is_contained,
  pp.geopoint,
  gn.geography
FROM
  addresses pp,
  neighbourhoods gn
WHERE
  ST_CONTAINS(gn.geography, pp.geopoint)

But it doesn't seem to work. I get points that clearly aren't contained in the polygon. Note the is_contained column in the result is always True, but if I copy & paste a polygon/point pair into a standalone query like SELECT ST_CONTAINS(polygon, point) it returns False.

What am I missing?

Answer 1

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:

1. 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.
2. 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.

https://cloud.google.com/bigquery/docs/gis-data#polygon_orientation

Run something like this to "fix" (invert) huge polygons:

UPDATE neighbourhoods 
SET polygon = ST_GeogFromText(ST_AsText(polygon)) 
WHERE ST_Area(polygon) > 1e14;