0

I stored sentinel 2 images as a tiled raster in my DB. The tile size is 244x244. So I want to extract data from those rasters. Currently I am trying to export the data by giving a point and number of pixels. everything is working fine except for one case.

So when the area of interest covers multiple tiles it will crop according to the region of interest.

select st_union(ST_Clip(rast,  ST_GeomFromText('POLYGON ((539800.0 5449880.0, 539800.0 5455000.0, 544920.0 5455000.0, 544920.0 5449880.0, 539800.0 5449880.0))',32632), true)) as rast
from sentinel_2.t32unv where rast && ST_GeomFromText('POLYGON ((539800.0 5449880.0, 539800.0 5455000.0, 544920.0 5455000.0, 544920.0 5449880.0, 539800.0 5449880.0))', 32632) and acquisition_date = '2022-09-23'

this query returns clipped-only raster data.

I have also this without ST_UNION

select ST_Clip(rast,  ST_GeomFromText('POLYGON ((539800.0 5449880.0, 539800.0 5455000.0, 544920.0 5455000.0, 544920.0 5449880.0, 539800.0 5449880.0))',32632), true) as rast
    from sentinel_2.t32unv where rast && ST_GeomFromText('POLYGON ((539800.0 5449880.0, 539800.0 5455000.0, 544920.0 5455000.0, 544920.0 5449880.0, 539800.0 5449880.0))', 32632) and acquisition_date = '2022-09-23'

and this returns 2 rows of raster (as expected)

I checked both results with QGIS and the results are perfect. But when I run this query

select 
        (ST_PixelAsPolygons(rast, 1)).x as x,
        (ST_PixelAsPolygons(rast, 1)).y as y,
        (ST_PixelAsPolygons(rast, 1)).geom as geom,
        (ST_PixelAsPolygons(rast, 1)).val as band1,
        (ST_PixelAsPolygons(rast, 2)).val as band2, 
        (ST_PixelAsPolygons(rast, 3)).val as band3, 
        (ST_PixelAsPolygons(rast, 4)).val as band4, 
        (ST_PixelAsPolygons(rast, 5)).val as band5, 
        (ST_PixelAsPolygons(rast, 6)).val as band6, 
        (ST_PixelAsPolygons(rast, 7)).val as band7,
        (ST_PixelAsPolygons(rast, 8)).val as band8,
        (ST_PixelAsPolygons(rast, 9)).val as band9, 
        (ST_PixelAsPolygons(rast, 10)).val as band10, 
        (ST_PixelAsPolygons(rast, 11)).val as band11, 
        (ST_PixelAsPolygons(rast, 12)).val as band12

from clipped_raster

I am getting two different results. The result without ST_UNION returns correct results that matches with QGIS(visual analysis), while the result from the raster that are returned by ST_UNION shows different values for the last two band11 and band12. Specfically for band11 originally I have 0-1 values, while the last query is returning values in range 0-100. For band12 i am getting NULL values, while the band has values 0-1.

What is a problem?

Result without st_union

enter image description here

result with st_union enter image description here

Improve this question
4
  • As per documentation, st_union returns the last pixel value by default. That means, your pixel value is picked from the last raster that enters the union.
    – DavidP
    Commented Dec 5, 2022 at 9:50
  • @DavidP thanks for your response. If that is the case, why do I have the correct values in the 11 bands?
    – Manap Shymyr
    Commented Dec 8, 2022 at 9:21
  • @DavidP how can I solve the problem?
    – Manap Shymyr
    Commented Dec 27, 2022 at 0:58
  • st_union should return only one raster/row. I don't know why you get more than one...
    – DavidP
    Commented Jan 2, 2023 at 10:22

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.