How to create multiple sizes of square box polygon within another polygon using bigquery

Question

I am trying to create sequeries of random polygon of square shapes (the red box) that sits within a larger polygon (the blue polygon), given the larger polygon's geometries. How can I achieve this in big query. I als

I tried the ST_MAKEENVELOP() but it's no found in BQ, as well as ST_XMIN and ST_XMAX

Answer 1

Consider below approach - it should be good starting point for you to get what your exact use case is!

WITH input AS (
  SELECT geo_code, city_geom, ST_BOUNDINGBOX(city_geom) box
  FROM `bigquery-public-data.utility_us.us_cities_area`
  WHERE geo_code = '51445'
), grid AS (
  SELECT geo_code, city_geom, 
    box.xmin + x * (box.xmax - box.xmin) / num_cols AS x_min,
    box.xmin + (x + 1) * (box.xmax - box.xmin) / num_cols AS x_max,
    box.ymin + y * (box.ymax - box.ymin) / num_rows AS y_min,
    box.ymin + (y + 1) * (box.ymax - box.ymin) / num_rows AS y_max
  FROM input, UNNEST([STRUCT(20 AS num_cols, 20 AS num_rows)]),
  UNNEST(GENERATE_ARRAY(0, num_cols - 1)) AS x, UNNEST(GENERATE_ARRAY(0, num_rows - 1)) AS y
)
SELECT ANY_VALUE(city_geom) city_geom, ST_UNION_AGG(smaller_box) boxes FROM (
  SELECT geo_code, city_geom, smaller_box FROM (
    SELECT geo_code, city_geom, 
      ST_GEOGFROM(FORMAT('POLYGON((%f %f, %f %f, %f %f, %f %f, %f %f))', 
        x_min, y_min, x_min, y_max, x_max, y_max, x_max, y_min, x_min, y_min)) AS smaller_box
    FROM grid
  )
  WHERE ST_WITHIN(smaller_box, city_geom)
  ORDER BY RAND()
  LIMIT 10
)
GROUP BY geo_code    

with output

that is visualized as below