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

Deep dive into React JS — Part One -

Simple form validation in React.

Javascript Foundation: Javascript RunTime

SEO in React — Two simple step implementation

We Are All Disabled!

Annoyed looking mother holding a laptop while daughter is doing soap bubbles in her direction

Authentication using public-key cryptography with NodeJS — Part 2

Typescript React Micro-frontend Proof Of Concept Using Webpack Module Federation

Why VDOM needed

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

Leveraging Geospatial Framework in Big Data Analytics

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