Opened 6 years ago

Closed 6 years ago

Last modified 6 years ago

#2143 closed enhancement (fixed)

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

Reported by: Bborie Park Owned by: Bborie Park
Priority: medium Milestone: PostGIS 2.1.0
Component: raster Version: trunk
Keywords: history Cc: mateusz@…

Description

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).

Change History (12)

comment:1 Changed 6 years ago by 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.

comment:2 Changed 6 years ago by 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)))

comment:3 Changed 6 years ago by Bborie Park

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.

comment:4 in reply to:  1 Changed 6 years ago by Bborie Park

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.

comment:5 Changed 6 years ago by 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.

comment:6 Changed 6 years ago by Bborie Park

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.

comment:7 Changed 6 years ago by pracine

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

comment:8 Changed 6 years ago by Bborie Park

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.

comment:9 Changed 6 years ago by 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.

comment:10 Changed 6 years ago by Bborie Park

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.

comment:11 Changed 6 years ago by Bborie Park

Keywords: history added
Resolution: fixed
Status: newclosed

Added in r10831

comment:12 Changed 6 years ago by mloskot

Cc: mateusz@… added
Note: See TracTickets for help on using tickets.