CODEWITHSUNDEEP
sqlgoogle-bigqueryuser-defined-functions
Kyle Pennell
Kyle Pennell

Reputation: 6117

How to create a geometry for each feature in a stringified geometry collection from a BigQuery JavaScript UDF?

I'm new to UDF functions and I have created a BigQuery UDF that takes a polygon geometry and creates points with it. I'm trying to draw dot density maps (converting polygon + population number to points). I've adapted the code from this blog post. Because bigQuery doesn't have a way to log variables, I've been testing things out in this codepen.

I'm at a point where the function seems to work right. The output is a geometry collection of points. It says in the bigquery docs that st_geogfromgeojson can accept a geometry collection.

My UDF returns a stringified geometry collection.

But I cannot figure out why st_geogfromgeojson doesn't work. I can't tell if I'm simply not unnesting something or what.

CREATE TEMP FUNCTION myFunc(feature string, ethnicity_column FLOAT64, year INT64)
  RETURNS string
  LANGUAGE js
  OPTIONS (
    library=["https://storage.googleapis.com.../d3.js","https://storage.googleapis.com/.../turf.min.js","https://storage.googleapis.com/.../wellknown.js"]
  )
  AS
    """
          
        if (feature === undefined || feature === null) return;
       
        
        var feature_parsed = wellknown.parse(feature)
       
        const bounds = turf.bbox(feature_parsed);
        
        const populationData = Math.round(ethnicity_column / 10);
        if (!populationData) return;
       
         const x_min = bounds[0];
         const y_min = bounds[1];
         const x_max = bounds[2];
         const y_max = bounds[3];
  
        let hits = 0;
        let count = 0;
        const limit = populationData * 10; // limit test to 10x the population.
        let points = [];
        while (hits < populationData - 1 && count < limit) {
          const lat = y_min + Math.random() * (y_max - y_min);
          const lng = x_min + Math.random() * (x_max - x_min);
          const randomPoint = turf.point([lng, lat]);
          if (turf.booleanPointInPolygon(randomPoint, feature_parsed)) {
            points.push(randomPoint);
            hits++;
          }
          count++;
        }
        return JSON.stringify((turf.geometryCollection(points)));
        
       // return JSON.stringify(points)

    """;


SELECT ST_GEOGFROMGEOJSON(JSON_EXTRACT((myFunc(st_astext(geom), white_pop, 2018)),'$')) FROM `myteam.kyle_data.blockgroups_with_acs`

But I keep hitting random errors like I'm not using the function right enter image description here

I'm open to all suggestions. I return a string for simplicity but perhaps I need to use a STRUCT. Perhaps I should cut out turf from creating the points? I must be missing something here.

Upvotes: 0

Views: 269

Answers (2)

Kyle Pennell
Kyle Pennell

Reputation: 6117

Two things that fixed it:

  • Returning array<String> instead of string
  • CROSS JOIN UNNEST in the select
CREATE TEMP FUNCTION myFunc(feature string, ethnicity_column FLOAT64)
  RETURNS array<String>
  LANGUAGE js
  OPTIONS (
    library=["https://storage.googleapis.com/../d3.js","https://storage.googleapis.com/.../turf.min.js","https://storage.googleapis.com/.../wellknown.js"]
  )
  AS
    """
        geopath = d3.geoPath()
        if (feature === undefined || feature === null) return;
        var feature_parsed = wellknown.parse(feature)
        const bounds = turf.bbox(feature_parsed);
        const populationData = Math.round(ethnicity_column / 10);
        if (!populationData) return;
         const x_min = bounds[0];
         const y_min = bounds[1];
         const x_max = bounds[2];
         const y_max = bounds[3];
        let hits = 0;
        let count = 0;
        const limit = populationData; // limit test to 10x the population.
        let points = [];
        while (hits < populationData - 1 && count < limit) {
          const lat = y_min + Math.random() * (y_max - y_min);
          const lng = x_min + Math.random() * (x_max - x_min);
          const randomPoint = turf.point([lng, lat]);
          if (turf.booleanPointInPolygon(randomPoint, feature_parsed)) {
            points.push('POINT ('+lng+' '+lat+')');
            hits++;
          }
          count++;
        }
        return points;
    """;
SELECT st_geogfromtext(points) as the_geom,  'white' as ethnicity  from (SELECT (myFunc(st_astext(geom), white_pop)) as points FROM `tableonmybq`) CROSS JOIN UNNEST(points) as points

Upvotes: 0

Michael Entin
Michael Entin

Reputation: 7744

GeoJson has two different collection types:

  • one is GeometryCollection - which is a geometry that describes a collection of geometries, e.g. union of points and polygons would be a GeometryCollection.
  • another is FeatureCollection - collection of features, objects with various properties, including geometry (which could be a geometry collection or any other geometry) and other user-defined properties.

turf.geometryCollection seems to return a Feature: https://www.npmjs.com/package/turf-geometrycollection (this is why it also accepts properties parameter).

ST_GEOGFROMGEOJSON constructs a geometry, it does support GeometryCollection, but does not support FeatureCollection or singular Feature returned by turf.

What you can do is extract a geometry from this feature, and pass it to ST_GEOGFROMGEOJSON. I think just using JSON selector $.geometry instead of $ should be enough.

Upvotes: 1

Related Questions