3

I have two tables, SCHEDULE contains polygon geometries and RASTER contains raster tiles

I want to take a polygon from the SCHEDULE table and overlay it on top of a set of RASTER tiles that intersect its bounding box.

I have got this far:-

WITH schedule AS (
        -- SELECT A SCHEDULE GEOMETRY AND CREATE A buffer around it 
        SELECT ST_SetSrid(ST_Boundary(ST_Expand(wkb_geometry, 500)),27700) as buffer, wkb_geometry as wkb_geometry, streetward from schedule where schedule_id = 1532952
    ),
    --MERGE THE SCHEDULE GEOMETRY WITH THE BUFFER
    expanded_schedule AS (

        SELECT ST_Union(wkb_geometry, buffer) as wkb_geometry FROM schedule
    ),
    raster_map as (
        --UNION ALL OF THE RASTERS IN RASTER TO COVER BUFFER
        SELECT ST_Union(rast) as rast from RASTER,schedule where ST_Boundary(wkb_geometry)::BOX2d  && rast

    ),
    raster_geom AS (
        --MAKE A RASTER FROM THE GEOMETRY
         SELECT ST_SetBandNoDAtaVAlue(

                ST_ColorMap(
                    ST_AsRaster(wkb_geometry, --SCHEDULE GEOMETRY
                            rast,            --reference raster
                            ARRAY['8BUI','8BUI','8BUI'],         --pixeltype
                            ARRAY[118,118,118],          --data value
                            ARRAY[100,100,100]           --no data value
                            ), 
                    1, '118 1 1 1 1
                        100 254 254 254 254')
            ,100)
         --SELECT ST_AsRaster(ST_Buffer(wkb_geometry, 50), rast,ARRAY['8BUI'], ARRAY[118])
         as rast FROM  expanded_schedule,raster_map

    ) , 
    raster_union AS (
        SELECT rast FROM raster_geom
        UNION
        SELECT rast FROM raster_map
    )
    SELECT ST_AsPng(ST_Union(rast)) FROM raster_union

My problem is that this creates an image that looks like the following:- Output image

The SCHEDULE geometry area has worked fine, but the bounding box area has masked out the map tiles below. I do not really understand why? I suspect that the buffer geometry is "filled in" as far as postgis is concerned. What am I doing wrong, or should I follow another strategy.

1

I cracked this in the end but it is not idea.

I removed the ST_Colorband call and now the rasters merge as they should using the colorband of the RASTER raster:-

raster_geom AS (

         SELECT ST_AsRaster(ST_Buffer(wkb_geometry,5),       --SCHEDULE GEOMETRY
                            rast,            --reference raster
                            ARRAY['8BUI','8BUI','8BUI'], --pixeltype
                            ARRAY[24,24,24],             --data value
                            ARRAY[0,0,0]             --no data value
                            )--, 

         as rast FROM  schedule,raster_map

    ) 

But it means that I cannot set transparency values which is a real pain.

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.