You have to align all the rasters created with ST_AsRaster() with additional parameters (scalex, scaley, gridx, gridy) so that you can then ST_Union() them together into a single raster before ST_AsPNG() it.

In the example below I have set both pixel sizes (scalex and scaley) to 0.05 and both pixel alignment (gridx and gridy) to 0 when calling ST_AsRaster(). This correspond to variant 4 of ST_AsRaster() in the doc.

WITH bbox AS (
  SELECT ST_SetSRID(ST_GeomFromText('POLYGON((18.48287 -33.95910,18.49523 
  -33.95910,18.49523 -33.96829,18.48287 -33.96829,18.48287 -33.95910))'), 4326) 
  AS bounding_box
), clipped AS (
  SELECT ST_Union(ST_Intersection(wkb_geometry, bbox.bounding_box)) AS wkb_geometry, 
         attribute
  FROM data_table, bbox 
  WHERE ST_Intersects(wkb_geometry, bbox.bounding_box) 
  GROUP BY attribute
), rasters AS (
  SELECT ST_AsRaster(clipped.wkb_geometry, 0.05, 0.05, 0, 0, 
                     ARRAY['8BUI', '8BUI', '8BUI'], 
                     (CASE WHEN attribute = 1 THEN ARRAY [66, 135, 245] 
                           WHEN attribute = 2 THEN ARRAY[48, 59, 99]
                           --...
                           ELSE ARRAY[147,150,150] END),
                     ARRAY[0,0,0]) AS rast
  FROM clipped
)
SELECT ST_AsPNG(ST_Union(rasters.rast)) as tile
FROM rasters;

You have to align all the rasters created with ST_AsRaster() with additional parameters (scalex, scaley, gridx, gridy) so that you can then ST_Union() them together into a single raster before ST_AsPNG() it.

In the example below I have set both pixel sizes (scalex and scaley) to 0.05 and both pixel alignment (gridx and gridy) to 0 when calling ST_AsRaster(). This correspond to variant 4 of ST_AsRaster() in the doc.

WITH bbox AS (
  SELECT ST_SetSRID(ST_GeomFromText('POLYGON((18.48287 -33.95910,18.49523 
  -33.95910,18.49523 -33.96829,18.48287 -33.96829,18.48287 -33.95910))'), 4326) 
  AS bounding_box
), clipped AS (
  SELECT ST_Union(ST_Intersection(wkb_geometry, bbox.bounding_box)) AS wkb_geometry, 
         attribute
  FROM data_table, bbox 
  WHERE ST_Intersects(wkb_geometry, bbox.bounding_box) 
  GROUP BY attribute
), rasters AS (
  SELECT ST_AsRaster(clipped.wkb_geometry, 0.05, -0.05, 0, 0, 
                     ARRAY['8BUI', '8BUI', '8BUI'], 
                     (CASE WHEN attribute = 1 THEN ARRAY [66, 135, 245] 
                           WHEN attribute = 2 THEN ARRAY[48, 59, 99]
                           --...
                           ELSE ARRAY[147,150,150] END),
                     ARRAY[0,0,0]) AS rast
  FROM clipped
)
SELECT ST_AsPNG(ST_Union(rasters.rast)) as tile
FROM rasters;

You have to align all the rasters created with ST_AsRaster() with additional parameters (scalex, scaley, gridx, gridy) so that you can then ST_Union() them together into a single raster before ST_AsPNG() it.

You have to align all the rasters created with ST_AsRaster() with additional parameters (scalex, scaley, gridx, gridy) so that you can then ST_Union() them together into a single raster before ST_AsPNG() it.

In the example below I have set both pixel sizes (scalex and scaley) to 0.05 and both pixel alignment (gridx and gridy) to 0 when calling ST_AsRaster(). This correspond to variant 4 of ST_AsRaster() in the doc.

WITH bbox AS (
  SELECT ST_SetSRID(ST_GeomFromText('POLYGON((18.48287 -33.95910,18.49523 
  -33.95910,18.49523 -33.96829,18.48287 -33.96829,18.48287 -33.95910))'), 4326) 
  AS bounding_box
), clipped AS (
  SELECT ST_Union(ST_Intersection(wkb_geometry, bbox.bounding_box)) AS wkb_geometry, 
         attribute
  FROM data_table, bbox 
  WHERE ST_Intersects(wkb_geometry, bbox.bounding_box) 
  GROUP BY attribute
), rasters AS (
  SELECT ST_AsRaster(clipped.wkb_geometry, 0.05, 0.05, 0, 0, 
                     ARRAY['8BUI', '8BUI', '8BUI'], 
                     (CASE WHEN attribute = 1 THEN ARRAY [66, 135, 245] 
                           WHEN attribute = 2 THEN ARRAY[48, 59, 99]
                           --...
                           ELSE ARRAY[147,150,150] END),
                     ARRAY[0,0,0]) AS rast
  FROM clipped
)
SELECT ST_AsPNG(ST_Union(rasters.rast)) as tile
FROM rasters;