3

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...

  • How many points? – Evan Carroll Aug 21 '17 at 15:36
  • Hundreds of thousands. At some point - might be millions. – NikS Aug 22 '17 at 6:38
2

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.

  • Thank you. It was exactly what I didn't manage - to wrap result of SELECT in ARRAY. – NikS Aug 23 '17 at 12:28
  • 1
    BTW, in my particular case ST_Collect doesn't affect a query runtime. – NikS Aug 23 '17 at 12:32
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.