BigQuery: Cluster non-convex geo points into MECE shapes/polygons

19 views Asked by At

I would like to create shape (polygon, cluster) from a table of geography points (ST_GEOPOINT(lon,lat), let say in a column 'location') of a different and known categories (let say in a column 'category').Example of desired points to shape transformation

Points do not always form convex polygon/shape (as on the example), but still I need to get the shapes as MECE as possible (i.e. not intersecting/overlapping and spanning free space between shapes - perhaps not perfectly, but let say to 90%).

I tried to use ST_CONVEXHUL, but it gives completely overlapping shapes.

select
  category,
  ST_CONVEXHUL(ST_UNION_AGG(location))
from
  `project.dataset.table`
group by 
  category

Also from ST_CLUSTERDBSCAN I cannot get any meaningful results (or I just cannot use it properly).

Is there any way to do it in Google BigQuery?

0

There are 0 answers