Obtain the footprint of a large raster in PostGIS

Question

I need to get the footprint (not the extent) 10GB raster in PostGIS similar to this question (but it's a very old question without any proper answer). So far the best approach I have come up with is

WRITE TABLE myschema.mypolygon AS 
SELECT
    ST_DumpAsPolygons(ST_Reclass(rast,
    '(0-100):1',
    '16BUI', -9999))
FROM
    myschema.myraster;

This basically changes all the raster values to 1 (I know the range is well within 0-100) and then passes this raster over to ST_DumpAsPolygons which will create polygons based on raster values.

I am wondering if there is a faster and more efficient way of doing this?

Answer 1

I don't really know if there is a faster way to do this, because that's usually how I do it and it's not really the bottleneck part usually. But here is a few idea that maybe can help you:

• Try to prefilter using ST_SummaryStats to only do the reclass and dump for tiles that are not full of data (the count is the number of values different of nodata, so if you compare to the size of the tile you can know the number of nodata). For the tiles that are full, you can use ST_Envelope of your tile that is maybe a bit faster. Once you got the envelopes and the polygons dumps, you can union it to have your final result.

• If your nodata is on the outside, maybe you can use ST_MinConvexHull. If not, maybe it's at least on the outside of your tiles so you can apply that tile by tile then union the result.

• Parallelize the work on your tiles. I usually call postgres requests from a python script that launch parallel queries when postgres cannot parallelize the work itself. In this kind of cases, each parallel worker would process only tiles modulo a number (WHERE MOD(rid, nb_core) == num_worker). You insert your result polys into a table that you can later union.