[postgis-users] Help with ST_CLIP

[Simon SPDBA Greener]

Regina,

> How big is the area you are trying to do?

It is a 1.3G GeoTIFF 
(https://www.deepreef.org/bathymetry/65-3dgbr-bathy.html).

> Couple of thoughts of what to try
> 1) ST_Clip is a bit finicky in that if a pixel is only partially covered it is not considered to be on. So what I usually do is just buffer my clipping geometry  a tincy bit like 1 meter like below to grab any pixels only partially overlapped.
OK
> 2) Also you should only be returning the part that intersects.  You are missing a WHERE clause unless you have just one raser tile, but still good to have a WHERE anyway as a precaution.  You'll know immediately if you get no rows they don't even intersect.
> You can speed up the intersect check by adding a functional index geometry(geog) to your geography table
How long have functional indexes been supported?
> 3) I tend to have last arg be crop = true (you had false).  If you set to false, you end up with the same raster size as your original but filled up with nulls there were was data.  Generally not something you want.
Ahh, silly me.
> Try this:
>   create table gbr100x100clip
>   as
> SELECT ST_Clip(g.rast, 1, ST_Buffer(m.geog,1)::geometry, true) as rast
> from
>   gbr_marine_park_boundary as m
> 	INNER JOIN        gbr100x100 as g ON ST_Intersects(g.rast, m.geog::geometry);

This worked a treat. Thanks, Regina (+1)!

regards

Simon