Clusters need Model info

[rclark]

Here's a SQL problem: This query creates our clustered points:

SELECT 
  kmeans, 
  count(*),
  st_asgeojson(st_centroid(st_collect(geom))) AS centroid
FROM ( 
  SELECT 
    kmeans(array[ST_X(geom), ST_Y(geom)], 30) over (), 
    geom
  FROM boreholetemperature
  WHERE boreholetemperature.geom && st_makeenvelope(-130,30,-120,40, 4326)
) AS ksub
GROUP BY kmeans;

There are two problems:

1. This only clusters from the boreholetemperature table, and we want to cluster from features across multiple feature types / tables.
2. Once we can do that, we need our clustered features (the result of this query) to be aware of how many sub-features each cluster contains from any given table / model.

I think that we can overcome the first issue by doing this:

SELECT 
  kmeans, 
  count(*),
  st_asgeojson(st_centroid(st_collect(geom))) AS centroid
FROM ( 
  SELECT 
    kmeans(array[ST_X(geom), ST_Y(geom)], 30) over (), 
    geom
  FROM (
    SELECT
      geom,
      content_model
    FROM boreholetemperature
    UNION
    SELECT
      geom,
      content_model
    FROM welllog
    UNION
    SELECT
      geom,
      content_model
    FROM physicalsample
  ) as models
  WHERE models.geom && st_makeenvelope(-130,30,-120,40, 4326)
) AS ksub
GROUP BY kmeans;

I'm pretty stumped by the second problem though. Any SQL-wizards / puzzle-solvers in the room? @smrazgs @asonnenschein @jalisdairi