[raster] Width and Height column of raster_columns view may need changing

[dustymugs]

With tiles no longer being padded, right and bottom edge tiles of a coverage may be of different width and/or height. The constraints (blocksize x and blocksize y) themselves need to be changed to constrain as a set (width AND height = X AND Y).

Constraint testing would be something like the following, just in a function:

SELECT
	dim
FROM (
SELECT ROW(4,4) AS dim
UNION ALL
SELECT ROW(2,4) AS dim
UNION ALL
SELECT ROW(4,2) AS dim
UNION ALL
SELECT ROW(2,2) AS dim
) foo
INTERSECT
SELECT ROW(2,3) AS dim

So what should be the value(s) in the width/height columns of raster_columns? The "desired" tile size (the one specified with -t in raster2psql or tilewidth/tileheight in ST_Tile()), which would be a singular value. Or should the columns be an array of the permitted widths or heights? Or is having two seperate columns no longer appropriate and there should just be one column (say "tile_size") with an array of sets (each set indicating a width/height).

[pracine]

By "Width" and "Height" I guess you mean "blocksize_x" and "blocksize_y"?

They should be the identical x and y size of the blocks, except the right-most and bottom tiles. There is no need to know about the size of the smaller tiles in the metadata.

So the check should be the same but so not take the right-most and bottom tiles in the computation.

I think the best way to test for the right most and bottom tiles is to build a dummy empty raster with the extent with a scale_x = blocksize_x and scale_y = -blocksize_y and to check if the upperleft corner of the tiles are equal to the upperleft corner of the (width - 1) and (height - 1) pixels of the dummy raster.

[pracine]

A tentative algo to apply the width constraint:

1) Compute the extent of the whole coverage (fast enough)

2) Get the width and the height of the tile intersecting the upper left corner of the extent (fast if there is an index. There should be one)

3) Rasterize the extent with the width and the height obtained in 2) (fast)

4) Set the constraint like this:

ALTER TABLE schemaname.tablename ADD CONSTRAINT enforce_width_tablename CHECK (st_width(rast) = width AND (st_upperleft(x) != st_rastertoworldcoordx(rasterizedextent, st_width(rasterrisedextent) - 1, 1)))

[dustymugs]

Your algo is flawed in step 2. There is no way to access an index. As per PostgreSQL's docs:

​http://www.postgresql.org/docs/9.0/static/sql-createtable.html

Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row.

Additionally, any access to an index requires knowing the names of the table and column you're dealing with. You won't know that in a CHECK constraint. Sure, you could hard code the names in… but that fails you the moment you need to change the table or column name.

There also is NO way to determine what value to put into the raster_columns view from that algo.

[dustymugs]

They should be the identical x and y size of the blocks, except the right-most and bottom tiles. There is no need to know about the size of the smaller tiles in the metadata.

That's what I needed a second opinion on.

[pracine]

The algorythm was for _add_raster_constraint_blocksize() in which you can do subqueries and access indexes.

I agree you can not leave "rasterizedextent" in the final constraint but you can put "ST_MakeEmptyRaster(x,x,x,x,x) instead.

[dustymugs]

After more thinking, the width and height constraints should be kept simple. Using something like the example provided is no longer just a width constraint but rather a two part constraint, a width AND whether or not the tile is part of a coverage.

ALTER TABLE schemaname.tablename ADD CONSTRAINT enforce_width_tablename CHECK (st_width(rast) = width AND (st_upperleft(x) != st_rastertoworldcoordx(rasterizedextent, st_width(rasterrisedextent) - 1, 1)))

Ensuring that a tile is part of a coverage should be a separate constraint.

[pracine]

I don't follow you. How are you going to ensure block sizes are enforced only for not right most and bottom tiles?

[dustymugs]

You don't. You're assuming the table is a coverage. How about if the user doesn't want a coverage, namely me. I just want a width check, which has nothing to do with whether or not the tile falls perfectly in a coverage's grid.

Therefore, you have to have a separate constraint. A width constraint is just that, a width constraint. Same goes with height. A coverage tile constraint ensures that the width and height are correct because the tile having the correct width and height is critical to be part of a coverage.

[pracine]

Does that mean you will have to add columns to raster_columns?

Personally I don't have problems with the fact that this constraint would be specific for coverage and would be a bit complex. The advantage is you don't have to change the structure of raster_columns. It not just a "width" constraint, it's a "coverage blocksize_x" constraint.

[dustymugs]

No, I don't expect to add any new columns to raster_columns. The "tile of coverage" constraint should be making sure that the tile is of the appropriate width AND height.

[dustymugs]

Added in r10831