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_unionMy problem is that this creates an image that looks like the following:-
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.
أكثر...
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_unionMy problem is that this creates an image that looks like the following:-
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.
أكثر...