Composing raster tiles?

[Thiemo Kellner]

Thanks for sharing your experience. Is that saying that PostGIS has considerable overhead? Sorry, if that is naiv. I might be suffering from the hammer nail deficiency. My background is very database heavy, so too much might look like a database to me.

08.11.2024 16:15:32 Vera Green <vera.green.ca at gmail.com>:

> We use command line GDAL for all our Easter processes, if your data is large I recommend you look into that option.
> 
> 
> On Fri, Nov 8, 2024, 6:12 AM David Haynes <haynesd2 at gmail.com> wrote:
>> I'll try and help you with A & B
>> 
>> a) Is it more efficient to convert the raster to vector data and calculate on the those than to calculate directly on the raster?
>>>> I don't think it would be faster to convert to vector because that would be dumping the raster into polygons. I think for specifically calculating slope, you are better off staying in raster. 
>> 
>> b) To my understanding, if I calculate the slope on a raster tile, the slope,… of the borders will have accuracy problems. My I idea, was to "stitch" a tile with its direct neighbours, calculate on the composed  tile, and either save a cropped calculated composed tile to its  original dimension or save the calculated composed tile as is,  probably the latter.
>> 
>>>> Overall correct, some small adjustments. For any raster operation, the tiles are operated on independently, so you need to "stitch" them together. I've provided a couple of ways in pseduo code
>> 
>> Way 1
>> 1) Use ST_Union and make a big tile, 
>> 2) Do the spatial operation,
>> 3) Break it up using ST_Tile()
>> The downside is you might run out of memory doing this. Also consider ST_MemUnion()
>> 
>> SELECT ST_Tile(ST_Slope(ST_Union(r1.ras)), 350,350)
>> FROM raster_table
>> 
>> Way 2)
>> A second option is to basically create an aggregate, which is likely faster. 
>> 1) Union the tiles based on ST_Touch  - make mega_tiles
>> 2) Do the spatial operation on the mega_tiles
>> 3) Clip the mega_tiles by the old tiles bounding box
>> 
>> WITH rtest as
>> (
>> SELECT r1.ras, ST_Union (r1.ras) as megatile
>> FROM raster_table r1
>> LEFT JOIN raster_table r2
>> ON ST_Touches (r1.geom, r2.geom)
>> GROUP BY r1.ras
>> )
>> SELECT ST_CLIP(ST_SLOPE(megatile), ST_Envelop(r1.rast) ) as ras
>> FROM rtest
>> 
>> Maybe someone wants to make an aggregate for the raster functions?
>> 
>> On Thu, Nov 7, 2024 at 3:31 PM <thiemo at gelassene-pferde.biz> wrote:
>>> Hi
>>> 
>>> In my project 
>>> https://sourceforge.net/p/treintaytres/code/HEAD/tree/trunk/code_files/data_storage/ I have the 
>>> table
>>> 
>>> TABLE_SCHEMA    TABLE_NAME      DATA_TYPE       TYPE_NAME       COLUMN_NAME
>>> treintaytres    topo_files␟t    1111            uuid            id
>>> treintaytres    topo_files␟t    93              timestamptz     entry_pit
>>> treintaytres    topo_files␟t    1111            uuid            source_id
>>> treintaytres    topo_files␟t    12              text            file_name
>>> treintaytres    topo_files␟t    1111            raster          tile
>>> treintaytres    topo_files␟t    93              timestamptz     file_creation_pit
>>> treintaytres    topo_files␟t    12              text            file_hash
>>> 
>>> TILE contains topographical height raster data from OpenTopography. 
>>> They are from different regions, let's say, some tiles cover 
>>> Switzerland, some cover New Zealand. I want to create slope and other 
>>> data from the height data and I have some questions I hope you can 
>>> answer or point me to answers.
>>> 
>>> a) Is it more efficient to convert the raster to vector data and 
>>> calculate on the those than to calculate directly on the raster?
>>> 
>>> b) To my understanding, if I calculate the slope on a raster tile, the 
>>> slope,… of the borders will have accuracy problems. My I idea, was to 
>>> "stitch" a tile with its direct neighbours, calculate on the composed 
>>> tile, and either save a cropped calculated composed tile to its 
>>> original dimension or save the calculated composed tile as is, 
>>> probably the latter.
>>> Can I compose as follows?
>>> with RASTER_NEIGHBORS as (         select R1.TILE   as CURRENT_TILE
>>>                                           ,R2.TILE   as NEIGHBOR_TILE
>>>                                           ,R1.ID[http://R1.ID]     as CURRENT_ID
>>>                                       from TOPO_FILES␟T R1
>>>                            left outer join TOPO_FILES␟T R2
>>>                                         on ST_Touches(R1.TILE
>>>                                                      ,R2.TILE)
>>>                                         or ST_Intersects(R1.TILE
>>>                                                         ,R2.TILE)
>>>                                      where TRUE
>>>                                        --and R1.ID[http://R1.ID] = 
>>> '6b8ca53a-bb5f-4c2b-a9c9-94b6a706e9b0'
>>>                                        and TRUE)
>>>     ,NION as (select CURRENT_TILE as TILE
>>>                     ,CURRENT_ID
>>>                 from RASTER_NEIGHBORS
>>>               union
>>>               select NEIGHBOR_TILE as TILE
>>>                     ,CURRENT_ID
>>>                 from RASTER_NEIGHBORS)
>>>    select ST_Union(TILE) as COMPOSED_TILE
>>>          ,CURRENT_ID
>>>      from NION
>>> group by CURRENT_ID;
>>> 
>>> c) Finally, I want to select all the areas where slope, TRI,… conform 
>>> certain criteria and have a minium surface size. Do I do it this 
>>> better on vector data and do I need to do this on data composed of all 
>>> the contiguous areas?
>>> 
>>> I would be grateful for any nudge into the right direction. Maybe URLs 
>>> with samples.
>>> 
>>> Kind regards
>>> 
>>> Thiemo
>>> 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20241108/6e5bc5f5/attachment.htm>