0

I have a raster table in PostGIS. For each row, I want to create a polygon that represents the part of the raster that is filled with data (not NODATA). I found out that ST_Polygon(rast) does the job. However, it works very very slowly.

Is there another solution for this problem that works faster?

2
  • did you compare with gdal_rasterize ?
    – radouxju
    Mar 25, 2021 at 14:36
  • I need it to be part of another query. So I cannot use gdal package I think.
    – milad
    Mar 25, 2021 at 15:03

2 Answers 2

1

you could try with ST_DumpAsPolygons (which uses gdal internally). To avoid making unnecessary polygons, make sure the you set a single value for each pixel that should end up in a polygon (e.g. with ST_Reclass).

1

Would add this as a comment but don't have enough reputation. @radouxju's answer led me in the following direction:

select row_number() over () as rid, st_union((a.dump).geom) as geom
from (
    select st_dumpaspolygons(st_reclass(rast, 1, '(-10000000-10000000):1', '1BB', null)) as dump --assumes NODATA value has been assigned to raster
    from test_schema.raster
) as a
where (a.dump).val = 1;

I'm including my SQL since I struggled with a few things, e.g. ST_DumpAsPolygons returning a composite data type. I don't know if this is the fastest way, but it's fairly quick with my rasters. All credit to @radouxju.

Your Answer

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

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