# Empty and full geographies in BigQuery

--

This is a topic over which we’ve spent a lot of time discussing internally, to ensure we have consistent semantics, but that rarely matters in practice. Nevertheless we got questions about it, so it is good time to cover it here.

Let’s start with empty geogrpahy, i.e. geography which contains no points. For BQGIS all empty geographies are the same, so all four columns in

`SELECT ST_GeogFromText('POINT EMPTY'), ST_GeogFromText('POLYGON EMPTY'), ST_GeogFromText('MULTILINESTRING EMPTY'), ST_GeogFromText('GEOMETRYCOLLECTION(MULTILINESTRING EMPTY)')`

are equivalent and printed as `GEOMETRYCOLLECTION EMPTY`.

Full geography is geography that represents the whole globe. How can you construct one? One way is to parse GeoJSON representing whole (planar) map:

`SELECT ST_GeogFromGeojson('{"type": "Polygon", "coordinates": [[[180, -90], [180, 90], [-180, 90], [-180, -90], [180, -90]]]}');`

Can you represent one in WKT? What if we try `POLYGON((180 -90, 180 90, -180 90, -180 -90, 180 -90))`? Remember that BQGIS treats WKT as spherical geography with geodesic edges. This leads to two issues here.

First, we get an error `Invalid polygon loop: Vertices 0 and 1 are antipodal`. That’s because the poles (180 -90) and (180 90) are antipodal, and geodesic edge between them is not defined, as all meridian lines have the same length, and none of them is any better than another. Let’s work around it by adding intermediate vertices:

`POLYGON((180 -90, 180 0, 180 90, -180 90, -180 0, -180 -90, 180 -90))`

Now the result of parsing this might be surprising first:

`LINESTRING(-180 -90, 180 0, -180 90)`

What happened? The points (180 90) and (-180, 90) represent exactly the same point on sphere, so geodesic line between them has zero length, and the whole polygon collapsed to a single line over antimeridian.

So this does not work. What can we do to describe it in WKT?

Well, BQGIS support parsing Microsoft’s WKT extention `FULLGLOBE` and you can use

`select st_geogfromtext('FULLGLOBE')`

But can we do something using more standard WKT (of course WKT was designed for flat maps, not sphere, so standard is relative here). We can represent full globe as union of smaller polygons, e.g. north and south hemispheres. We cannot use `multipolygon` shape, as polygons in a multipolygon are only allowed to intersect at finite number of points. But we can use `geometrycollection`:

`GEOMETRYCOLLECTION(POLYGON((0 0, 120 0, -120 0, 0 0)), POLYGON((0 0, -120 0, 120 0, 0 0)))`

Following left-hand-interior rule, the first polygon represents northern hemisphere and the second represents southern hemisphere. Together they represent full globe. Let’s check:

`SELECT ST_Equals( ST_GeogFromText('FULLGLOBE'), ST_GeogFromText('GEOMETRYCOLLECTION(POLYGON((0 0, 120 0, -120 0, 0 0)), POLYGON((0 0, -120 0, 120 0, 0 0)))'))true`

This very `geometrycollection `is also how BQGIS prints full globe:

`SELECT ST_GeogFromText('FULLGLOBE')GEOMETRYCOLLECTION(POLYGON((0 0, 120 0, -120 0, 0 0)), POLYGON((0 0, -120 0, 120 0, 0 0)))`

--

--

Hi, I'm TL of BigQuery Geospatial project. Posting small recipes and various notes for BQ Geospatial users.