I want to generate tiles from a postgis raster, to be used as a tileLayer in an Openlayers map.
The tiling is fairly easy to do using ST_Tile, resulting in a new table, with each record being one tile. However, ST_Tile does not return information about the position each tile would have in the original image, which is a problem when trying to use a tiled layer.
I need each record to have an attribute indicating its row and col position of the given parent raster. I figured out a somewhat ugly workaround to determine the relative positions of each tile:
SELECT dense_rank() over (order by ST_RasterToWorldCoordX(rast, 1, 1)) as colid, dense_rank() over (order by ST_RasterToWorldCoordY(rast, 1, 1)) as rowid FROM i3_tiles
This query ranks the tiles in X/Y direction, effectively giving me the col and row as I need it. Now I'd like to update the tiles table with the information gained from this. To this end, I add two columns to the table: rowid and colid, both of integer type.
How can I use the above query to assign the correct values to each record? I tried
UPDATE i3_tiles SET rowid = sub.rowid, colid = sub.colid FROM ( SELECT dense_rank() over (order by ST_RasterToWorldCoordX(rast, 1, 1)) as colid, dense_rank() over (order by ST_RasterToWorldCoordY(rast, 1, 1)) as rowid FROM i3_tiles ) sub
But that leads to each record being assigned the colid/rowid of the first result of the query. I assume I somehow need to limit the subquery to make sure it picks the value of the correct record to assign, but I'm not sure how to work this out. I'm not an SQL wizard, sadly :( . My current guess is that this could be done with a join or the magic "with" that I don't really know how or if it applies.