1

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?

4
  • The query seems to be OK. Could you give an example of (polygon, point) pair that are returned by this query, for which SELECT ST_CONTAINS(polygon, point) (I assume run also in BigQuery?) returns False?
    – Michael
    Feb 17 at 22:41
  • 1
    Also, my psychic powers say to check if polygons have been inverted here, see stackoverflow.com/questions/63608818/…
    – Michael
    Feb 18 at 3:37
  • Your psychic powers are almost definitely correct, because thanks to your input I identified a specific gn.geography that can't be visualized in bigquerygeoviz.appspot.com, but others can. Here's a pair from the bad polygon: POINT(0.16597 52.121115) POLYGON((-0.164994414667985 51.4469063156513, -0.162934478144547 51.4487250442277, -0.154008086542985 51.4512390497389, -0.151690657954118 51.4529506338567, -0.147570784907243 51.4530576057335, -0.148686583857438 51.4473877508539, -0.153407271723649 51.4432151437921, -0.160059150080583 51.4444188195731, -0.164994414667985 51.4469063156513)) Feb 19 at 6:40
  • And the ST_AREA() of the bad polygon is indeed huge. But I can't figure out what's wrong with it (just eyeballing the coordinates), and I generated/loaded it exactly like a polygon right next to it which is fine. Hmmm... Feb 19 at 6:48
2

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;
1
  • Beautiful. Thank you Michael! Feb 19 at 10:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.