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.

0

I needed an image that shows if a property on an RGB raster. Your solution helped me a lot!

Pay attention that I used the Alpha of RGBA to set the transparency of where I didn't want to show... You can use this feature to give a certain opacity to the elements you want to show on your map.

I also used ST_Boundary to use only the edges of the geometry.

Here are my adjustments:

with
--Get the geometry I want to show on the raster
geom as (
    select ST_Union(geom) as geom from public.vector_900aa100_0f94_4542_b23b_b51695df9696
),
--Create an envelope some distance from the geometry
envelope as (
    select ST_Expand(st_envelope(geom), 1000) as envelope_geom, geom from geom
),
--Cut raster and color it
raster_map as (
    SELECT ST_Union(rast) as rast from public.raster_rgb_2020, envelope where ST_Boundary(envelope_geom)::BOX2d  && rast
),
--Creates raster based on geometry
raster_geom as (
     SELECT ST_AsRaster(ST_Buffer(ST_Boundary(geom),35, 'endcap=square join=round'),       --SCHEDULE GEOMETRY
                            rast,            --reference raster
                            ARRAY['8BUI','8BUI','8BUI'], --pixeltype
                            ARRAY[200,10,10,255],          --data value
                            ARRAY[0,0,0,0]               --no data value
                            ) as rast FROM  envelope, 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;

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.