PostGIS raster - how to populate pixel values by set of points?

Question

I have the table "events" with column "point" that contains geometries (points). And I need to fill a raster (already created in table "rasters") using this set of points.
For simplicity BandPixelType is 1BB, default pixel value is 0, and I want it to be 1, if pixel contains at least one point from my table. Raster and geometries have the same SRID (4326). So, I need something like:
UPDATE rasters SET rast = ST_SetValue(rast, 1, ST_SetSRID(ST_Point(lon, lat), 4326), 1)
but executed with a dataset:
SELECT point FROM events WHERE ...condition...

Answer 1

I think what you want is ST_SetValues which takes an array of geomval

raster ST_SetValues(raster rast, integer nband, geomval[] geomvalset, boolean keepnodata=FALSE);

For Variant 5, an array of geomval is used to determine the specific pixels to be set. If all the geometries in the array are of type POINT or MULTIPOINT, the function uses a shortcut where the longitude and latitude of each point is used to set a pixel directly. Otherwise, the geometries are converted to rasters and then iterated through in one pass. See example Variant 5.

Emphasis added by me. see the docs for an example. Should look like this...

UPDATE rasters
SET rast = ST_SetValues(
  rast,
  1,    -- nband
  (
    SELECT ARRAY(
      SELECT (point,1)::geomval
      FROM events
      WHERE ...condition
    )
  )
);

This may also work..

UPDATE rasters
SET rast = ST_SetValues(
  rast,
  1,    -- nband
  (
    SELECT ARRAY(
      SELECT (ST_Collect(point),1)::geomval
      FROM events
      WHERE ...condition
    )
  )
);

Not sure which will be faster.

Answer 2

A more elegant way is to compose the array by using array_agg function:

UPDATE rasters
SET rast = ST_SetValues(
  rast,
  1,    -- nband
  array_agg((e.point, e.value)::geomval)
 )
 FROM events e
 WHERE ...condition;

This way you can also define the values based on events records and even JOIN rasters table and events table by some criteria.

Answer 3

Thank you @evan-carroll for your answer, it helped me quite a bit.

I just want to post this here in case somebody has the same problem as me.

If you want to create a new raster, using an existing raster as reference, and populate it with geo-referenced data from a table, you can proceed as such:

create view new_data_raster as
select
--- set values
ST_SetValues(
    --- add a band
    ST_AddBand(
        --- create empty raster with existing raster as reference
        ST_MakeEmptyRaster(r.rast),
        --- set default values to 99
        1, '8BUI', 99, 99
    ),
    --- select band 1
    1,
    (
        SELECT ARRAY(
            --- fill values where needed
            SELECT (d.geom, d.value)::geomval
            FROM data d
            where ... --- optional
        )
    )
) as rast

from reference_raster r