New options for BigQuery GIS geospatial data ingestion

Planar WKT

ST_GeogFromText(wkt, planar => TRUE)

Make Valid

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!

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`

--

--

--

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

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

AMAP’s Transformation from Navigation Positioning to High-Precision Positioning

Reshape Analytics Partners with Alteryx to Enhance Business Outcomes for its Customers

Don’t Get Stuck With Eye Candy Metrics When Reporting From Cloud Data

Mind Blowing Application of Analytics in the Telecom Industry

How To Make Hypothesis In Research Paper

json.dump vs json.dumps, json.load vs json.loads

Learning Data Science through competitions

Using Tensorboard

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

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

More from Medium

Overcoming large-scale geospatial analysis using Google BigQuery

Extra geography simplification in BigQuery

Plotting Bar Charts in BigQuery Using a SQL User Defined Function (UDF)

Load Data from CSV File to Bigquery using Python bigquery client Library.