[postgis-users] ST_Tile XY indices

[Senshi]

Greetings,


I use ST_Tile and ST_CreateOverview to generate tiles from a large source
raster to be displayed as a TileXYZ layer in Openlayers (think WMS). For
that, it is is necessary to know the column and row index of each tile, so
the browser can request the correct tile by index.

What is the best way to get these indices, as neither ST_Tile nor
ST_CreateOverview seem to generate them by default.

My current workaround is to use this query for each table:


ALTER TABLE o_22_i3_tiles ADD COLUMN rowid integer, ADD COLUMN colid
integer;
UPDATE o_22_i3_tiles_schiller SET
rowid = sub.rowid,
    colid = sub.colid
FROM (
    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
) sub
WHERE o_22_i3_tiles_schiller.ctid = sub.ctid


Which is not overly efficient. Is there a smarter way to do this?


best regards
Tassilo
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170822/841dd2a5/attachment.html>