[raster] Huge literal in enforce_max_extent_rast check constraint

[strk]

After importing a raster with this command:

raster2pgsql -C -I -s 4326 -Y -t '256x256' -l 2,4,8,16

The definition of the imported table has an "enforce_max_extent_rast" which uses a literal geometry value using 177,797 hex characters. Shouldn't it just be a 5 vertices polygon ?

The constraint reads:

"enforce_max_extent_rast" CHECK (st_coveredby(st_convexhull(rast),
'<177,797 hex chars here>'::geometry))

[Bborie Park]

It currently is not the envelope or convex hull of the set of raster's. Instead it is the footprint. I'm fine with changing to a convex hull assuming robe2 doesn't see an issue.

[strk]

What is that constraint needed for ?

[Bborie Park]

Depends on your use case. I use it ensure continous streams of meteorological data per variable is consistent over time. For static raster's, it is of less value.

[strk]

What I meant was, is it needed for the raster_columns view ? Isn't that the only reason why one would want raster2pgsql to automatically add constraints ?

[darkblueb]

quick tests.. using this image {{{wget ​http://www.naturalearthdata.com/http//www.naturalearthdata.com/download /10m/raster/HYP_HR_SR_OB_DR.zip}}} —

 POSTGIS="2.2.0dev r12639" GEOS="3.4.1-CAPI-1.8.0 r3894" PROJ="Rel. 4.8.0, 
6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.9.1" 
LIBJSON="UNKNOWN" RASTER

/usr/lib/postgresql/9.3/bin/raster2pgsql -C -I -s 4326 -Y -t '256x256' -l 2,4,8,16
 HYP_HR_SR_OB_DR.tif | psql -q ne_test0

 \d hyp_hr_sr_ob_dr
...
Check constraints:
    "enforce_height_rast" CHECK (st_height(rast) = ANY (ARRAY[256, 48]))
    "enforce_max_extent_rast" CHECK (st_coveredby(st_convexhull(rast),
 '0106000020E61000008801000001030000000100... 
(far too many bytes here) ...
32BC0D1F9CCCCCCCC28C007EA323333332BC01CC2AAAAAAAA30C007EA323333332BC05007EFEEEEEE34C007EA323333332BC0844C3333333339C0'::geometry))
    "enforce_nodata_values_rast" CHECK (_raster_constraint_nodata_values(rast)::numeric(16,10)[] = '{NULL,NULL,NULL}'::numeric(16,10)[])

— test 21

 POSTGIS="2.1.4dev r12632" GEOS="3.4.1-CAPI-1.8.0 r3894" PROJ="Rel. 4.8.0, 
6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.9.1" 
LIBJSON="UNKNOWN" RASTER

$  /usr/lib/postgresql/9.3/bin/raster2pgsql 
RELEASE: 2.1.4dev GDAL_VERSION=110 (r12632)

similar result…

[strk]

I was importing HYP_LR.tif

[Bborie Park]

Yes and no. It is consumed by raster_columns but I consider that more a side benefit. I never use the raster_columns view.

[Bborie Park]

I'm fine with simplifying the geometry computed for max extent. Its all in plpgsql anyways.

[strk]

I'd keep the standard constraints for the sole purpose of enforcing what raster_columns is advertising. If there's nothing in raster_columns that depends on the "convex hull" of the raster, I'd rather avoid having it constrained by the AddRasterConstraint function.

BTW, documentation for the function does not mention "extent" as being one of the constraints: ​http://postgis.net/docs/RT_AddRasterConstraints.html nor in the short or in the detailed description. Was it a recent introduction ?

[strk]

Robe: do you see issues with changing the enforce_max_extent_rast parameter to the envelope ? The documentation for raster_columns says: "extent This is the extent of all the raster rows in your raster set." so I'd have it computed using ST_Extent(raster) — which does not exist, see #1662. I guess ST_Extent(rast::geometry) is the closest one, is that good enough for the sake of raster_columns ?

Sorry if I insist on this ticket but whenever I have to look at a raster_columns entry I cant because the "extent" field fills up the whole screen… :/

[robe]

not off hand. Never realized it was huge and not rectangular. Go for it. Just make sure to note it as a possible breaking change.

[Bborie Park]

convex hull of convex hulls then…

[strk]

Why not just a rectangle ? Isn't that what clients look for when inspecting raster_columns ? The max extent showing all the data… To avoid a full scan.

[Bborie Park]

I guess the question is "What is the extent"? Envelope or Convex Hull. I think the nice thing about convex hull is that any new rasters added to the table is constrained to the Convex Hull.

[strk]

Can't the Convex Hull have any number of vertices ? The extent, in postgis core, has historically meant a rectangle. It's actually still returned as a BOX type, rather than a GEOMETRY. Why should it be different for rasters ?

[Bborie Park]

Ah. Valid argument. Envelope of envelopes it is then!

[robe]

I don't think it's worth changing. In raster it can't have any number of vertices, and I'm more concerned about breaking people's assumptions and forcing them to rebuild all their indexes if we do this. And even though the end seems like the same, to the planner it is not so it would never use an existing index unless we require everyone to drop thier indexes and reindex for 2.2.

I think originally a long time ago we use ST_Envelope, but the fundamental difference bewteen the ST_Envelop and ST_ConvexHull is that ST_ConvexHull handles rotated rasters and will give the right answer. So it saved a bit in those functions calls where we know there is no nodata because those type of functions would naturally use the index. If you change then they have to do both an ST_Envelop and an ST_ConvexHull to utilize index.

Other issue before which I don't think is relevant now is that ST_envelope was float4 rather than float4. I wish Pierre was here to explain. I don't recall the long discussion of why this makde ST_Envelope kinda useless.

[strk]

Robe: this ticket has nothing to do with indexes. Wrong ticket ?

[robe]

strk nah I think I just misunderstood the conversation and thought for some reason you were swapping out ST_ConvexHull(rast) for ST_Envelope(rast) in index. So disregard my grumblings.

[Bborie Park]

Fixed in -trunk as of r12740