2

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.

1

As I feared (and hoped :-) ), the solution was simpler than expected:

UPDATE i3_tiles SET 
    rowid = sub.rowid,
    colid = sub.colid 
FROM (
    SELECT
        ctid,
        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
WHERE i3_tiles.ctid = sub.ctid

Postgis ST_Tile does not generate unique OIDs. In order to at least somewhat improve performance of this query, I simply use the CTIDs (google them, interesting pgsql gimmick).

EDIT: This is actually a bad solution, because in most projections, there's a very tiny difference in the X/Y coordinates (think of 13th decimal). dense_rank is proper, and will notice that difference (ST_RasterToWorldCoordX() returns double precision)) and will almost always lead to incorrect indices. Be absolutely sure how your tiles are located, and then you can work around by rounding them a bit, if you have a good local projection. This will not work in all cases, however. I'm working on small regions inside an UTM zone, so this is "good enough" for me right now:

SELECT
    ctid,
    dense_rank() over (order by round(cast(ST_RasterToWorldCoordX(rast, 1, 1) as numeric), 5))-1 as colid,
    dense_rank() over (order by round(cast(ST_RasterToWorldCoordY(rast, 1, 1) as numeric), 5))-1 as rowid
FROM o_22_i3_tiles_schiller

But I am still looking for better solutions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.