Persistent UDFs + BQ GIS = ♥

CREATE FUNCTION demo.STx_MakeMulti(geojson STRING) RETURNS STRING 
LANGUAGE js AS '''
var obj = JSON.parse(geojson);
if (obj.type == "Polygon") {
obj.type = "MultiPolygon";
obj.coordinates = [obj.coordinates];
}
return JSON.stringify(obj);
''';
SELECT 
demo.STx_MakeMulti(ST_AsGeoJson(geo)),
ST_Equals(
ST_GeogFromGeoJson(demo.STx_MakeMulti(ST_AsGeoJson(geo))),
geo)
FROM UNNEST(
[ST_GeogFromText('polygon((1 1, 1 2, 2 2, 1 1))'),
ST_GeogFromText('multipolygon(((1 1, 1 2, 2 2, 1 1)),((10 1, 10 2, 11 2, 10 1)))')]) geo;
CREATE OR REPLACE FUNCTION demo.STx_DumpCollection(geojson STRING) RETURNS ARRAY<STRING>
LANGUAGE js AS '''
var obj = JSON.parse(geojson);
if (obj.type == "GeometryCollection") {
return obj.geometries.map(g => JSON.stringify(g));
}
return [geojson];
''';
SELECT id, geo 
FROM sample_table d,
UNNEST(demo.STx_DumpCollection(ST_AsGeoJson(d.geom))) geo
CREATE OR REPLACE FUNCTION demo.STx_DumpPoints(geog geography) 
RETURNS ARRAY<Geography> AS ((
select array (
select ST_GeogPoint(cast(s[offset(0)] as float64),
cast(s[offset(1)] as float64))
from (
select split(p, " ") s
from unnest((
select REGEXP_EXTRACT_ALL(ST_Astext(geog),
r"[-\.0-9e]+ [-\.0-9e]+") pair)) p
))
));
with data as (
select * from unnest(
[st_geogfromtext('multipoint((-1.1 2.2), (3.3 -4.4))'),
st_geogfromtext('linestring(5.5e-5 6, 7 8, 9 10)')]) g
)
select g, p from data, unnest(demo.STx_DumpPoints(g)) p

--

--

--

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

Aspect-Oriented Programming in JavaScript — Cooding Dessign

You don’t know JavaScript until you can beat this game

Angular Routing — Step by Step for Dummies

Partial Application with React

Faster and lesser coding using Javascript Array Functions

Know These JavaScript Basics Before Learning React

Template Inheritance in Angular

How to create a Stellar wallet?

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

Cloud Data Fusion: Customizing Compute Profiles at Runtime

Timeseries databases demystified