How to pass results of conditional statement to another SQL statement?

Question

The following SQL performs the following workflow using the PostGIS extension in PostgreSQL:

1. Make some sample polygons for this example
2. Create 1000 random points in each polygon
3. Cluster the random points in each polygon into 4 clusters

I would like to add a conditional statement so that the area of each polygon determines how many clusters to make rather than a set amount for each polygon.

Here is a conditional statement that determines the number of clusters based on area:

--Conditional statement
SELECT poly_id,
    CASE
      WHEN ST_Area(geom) > 9 THEN 1
      ELSE 0
    END
  FROM polys;

How can I integrate this conditional statement into my SQL statement that applies the clustering? The end result should be polygons with a variable number of clusters per polygon determined by the polygon area.

---

Full SQL code with sample data

-- Make up some data
CREATE TABLE polys(poly_id, geom) AS (
        VALUES  (1, 'POLYGON((1 1, 1 5, 4 5, 4 4, 2 4, 2 2, 4 2, 4 1, 1 1))'::GEOMETRY),
                (2, 'POLYGON((6 6, 6 10, 8 10, 9 7, 8 6, 6 6))'::GEOMETRY)
    );

-- Create point clusters within each polygon
CREATE TABLE pnt_clusters AS
  SELECT  polys.poly_id,
          ST_ClusterKMeans(pts.geom, 4) OVER(PARTITION BY polys.poly_id) AS cluster_id,
          pts.geom
  FROM    polys,
          LATERAL ST_Dump(ST_GeneratePoints(polys.geom, 1000, 1)) AS pts
;

---

Edit:

When I attempt to combine the SQL statements, I get the following error:

ERROR:  syntax error at or near "AS"
LINE 4: ...ans(pts.geom, 8) OVER(PARTITION BY polys.poly_id) AS cluster...

---

-- Create point clusters within each polygon
CREATE TABLE pnt_clusters3 AS
  SELECT  polys.poly_id,
      CASE
          WHEN ST_Area(geom) >9 THEN ST_ClusterKMeans(pts.geom, 8) OVER(PARTITION BY polys.poly_id) AS cluster_id,
          pts.geom
          ELSE ST_ClusterKMeans(pts.geom, 2) OVER(PARTITION BY polys.poly_id) AS cluster_id,
          pts.geom
      END
  FROM    polys,
          LATERAL ST_Dump(ST_GeneratePoints(polys.geom, 1000, 1)) AS pts
;

Answer 1

Please see correction to your syntax error:

-- Create point clusters within each polygon
CREATE TABLE pnt_clusters3 AS
  SELECT  polys.poly_id,
      CASE
          WHEN ST_Area(polys.geom) >9 THEN ST_ClusterKMeans(pts.geom, 8) OVER(PARTITION BY polys.poly_id) 
          
          ELSE ST_ClusterKMeans(pts.geom, 2) OVER(PARTITION BY polys.poly_id) 
         
      END AS cluster_id,
        pts.geom
  FROM    polys,
          LATERAL ST_Dump(ST_GeneratePoints(polys.geom, 1000, 1)) AS pts
;