[postgis-users] ST_Tile XY indices

Senshi tassilo.m at gmail.com
Tue Aug 22 01:48:41 PDT 2017


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
UPDATE o_22_i3_tiles_schiller SET
rowid = sub.rowid,
    colid = sub.colid
        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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170822/841dd2a5/attachment.html>

More information about the postgis-users mailing list