New options for BigQuery GIS geospatial data ingestion

Planar WKT

If you worked with BigQuery GIS, you know it treats WKT as describing an object on spherical Earth, with geodesic edges, matching semantics of internal GEOGRAPHY type. And if you had planar data, loading it using WKT format sometimes is OK, but could lead to problems if the geometries are spatially large. For reliable translation you had to ingest planar data usingGeoJson format, which BigQuery converts from planar model to spherical one.

ST_GeogFromText(wkt, planar => TRUE)

Make Valid

BigQuery does not have ST_MakeValid function, because we’ve decided to only support valid geometry in internal representation. This avoids many problems and unexpected query results after accidentally loading bad data and then trying to use invalid values. If you have an invalid value, BigQuery prefers to tell you about it upfront.

ST_GeogFromText(wkt, make_valid => TRUE)
ST_GeogFromGeoJson(geojson, make_valid => TRUE)
ST_GeogFromText(wkt, make_valid => TRUE, planar => TRUE)
SELECT
geojson AS original_geojson,
ST_AsGeoJson(ST_GeogFromGeoJson(geojson, make_valid => TRUE))
AS make_valid_geojson
FROM my_dataset.my_table
WHERE geojson is NOT NULL AND
SAFE.ST_GeogFromGeoJson(geojson) IS NULL

Let’s use it!

Let’s repeat our last exercise of uploading San Francisco zoning dataset. Again, we download it as CSV file, upload to BigQuery with automatic schema detection (I used a table named tmp.sfzoning). This gives us a table with column the_geom of type STRING. We could not ingest it last time, as a few geometries are not valid there. But now it is easy.

SELECT 
rand() color, -- to randomly color polygons in GeoViz
the_geom original,
st_geogfromtext(the_geom, planar => TRUE, make_valid => TRUE) geom
FROM `tmp.sfzoning`
where safe.st_geogfromtext(the_geom, planar => TRUE) is null
original image
the spike that makes the polygon invalid
converted geography
CREATE TABLE my_dataset.sfzoning AS
SELECT
* EXCEPT(the_geom),
st_geogfromtext(the_geom, planar => TRUE, make_valid => TRUE) geom
FROM `tmp.sfzoning`

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Michael Entin

Michael Entin

151 Followers

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