<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
</head>
<body>
<span dir="ltr" style="margin-top:0; margin-bottom:0;">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.</span>
<br>
<div class="fairemail_quote">
<div dir="ltr">
<p>08.11.2024 16:15:32 Vera Green <vera.green.ca@gmail.com>:</p>
</div>
<blockquote style="margin:0;border-left:3px solid #ccc; padding-left:10px;">
<p dir="ltr">We use command line GDAL for all our Easter processes, if your data is large I recommend you look into that option.</p>
<br>
<div class="gmail_quote">
<div dir="ltr" class="gmail_attr">
On Fri, Nov 8, 2024, 6:12 AM David Haynes <<a href="mailto:haynesd2@gmail.com">haynesd2@gmail.com</a>> wrote:
<br>
</div>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div dir="ltr">
I'll try and help you with A & B
<div>
<br>
</div>
<div>
a) Is it more efficient to convert the raster to vector data and calculate on the those than to calculate directly on the raster?
</div>
<div>
>> 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.
<br>
<br>
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.
</div>
<div>
<br>
</div>
<div>
>> 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
</div>
<div>
<br>
</div>
<div>
Way 1
</div>
<div>
1) Use ST_Union and make a big tile,
</div>
<div>
2) Do the spatial operation,
</div>
<div>
3) Break it up using ST_Tile()
<br>
The downside is you might run out of memory doing this. Also consider ST_MemUnion()
</div>
<div>
<br>
</div>
<div>
SELECT ST_Tile(ST_Slope(ST_Union(r1.ras)), 350,350)
</div>
<div>
FROM raster_table
</div>
<div>
<br>
</div>
<div>
Way 2)
</div>
<div>
A second option is to basically create an aggregate, which is likely faster.
</div>
<div>
1) Union the tiles based on ST_Touch - make mega_tiles
</div>
<div>
2) Do the spatial operation on the mega_tiles
</div>
<div>
3) Clip the mega_tiles by the old tiles bounding box
</div>
<div>
<br>
</div>
<div>
WITH rtest as
<br>
(
<br>
SELECT r1.ras, ST_Union (r1.ras) as megatile
<br>
FROM raster_table r1
<br>
LEFT JOIN raster_table r2
<br>
ON ST_Touches (r1.geom, r2.geom)
<br>
GROUP BY r1.ras
<br>
)
<br>
SELECT ST_CLIP(ST_SLOPE(megatile), ST_Envelop(r1.rast) ) as ras
<br>
FROM rtest
</div>
<div>
<br>
</div>
<div>
Maybe someone wants to make an aggregate for the raster functions?
</div>
</div>
<br>
<div class="gmail_quote">
<div dir="ltr" class="gmail_attr">
On Thu, Nov 7, 2024 at 3:31 PM <<a href="mailto:thiemo@gelassene-pferde.biz" target="_blank" rel="noreferrer">thiemo@gelassene-pferde.biz</a>> wrote:
<br>
</div>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;">
Hi
<br>
<br>
In my project
<br><a href="https://sourceforge.net/p/treintaytres/code/HEAD/tree/trunk/code_files/data_storage/" rel="noreferrer noreferrer" target="_blank">https://sourceforge.net/p/treintaytres/code/HEAD/tree/trunk/code_files/data_storage/</a> I have the
<br>
table
<br>
<br>
TABLE_SCHEMA TABLE_NAME DATA_TYPE TYPE_NAME COLUMN_NAME
<br>
treintaytres topo_files␟t 1111 uuid id
<br>
treintaytres topo_files␟t 93 timestamptz entry_pit
<br>
treintaytres topo_files␟t 1111 uuid source_id
<br>
treintaytres topo_files␟t 12 text file_name
<br>
treintaytres topo_files␟t 1111 raster tile
<br>
treintaytres topo_files␟t 93 timestamptz file_creation_pit
<br>
treintaytres topo_files␟t 12 text file_hash
<br>
<br>
TILE contains topographical height raster data from OpenTopography.
<br>
They are from different regions, let's say, some tiles cover
<br>
Switzerland, some cover New Zealand. I want to create slope and other
<br>
data from the height data and I have some questions I hope you can
<br>
answer or point me to answers.
<br>
<br>
a) Is it more efficient to convert the raster to vector data and
<br>
calculate on the those than to calculate directly on the raster?
<br>
<br>
b) To my understanding, if I calculate the slope on a raster tile, the
<br>
slope,… of the borders will have accuracy problems. My I idea, was to
<br>
"stitch" a tile with its direct neighbours, calculate on the composed
<br>
tile, and either save a cropped calculated composed tile to its
<br>
original dimension or save the calculated composed tile as is,
<br>
probably the latter.
<br>
Can I compose as follows?
<br>
with RASTER_NEIGHBORS as ( select R1.TILE as CURRENT_TILE
<br>
,R2.TILE as NEIGHBOR_TILE
<br>
,<a href="http://R1.ID" rel="noreferrer noreferrer" target="_blank">R1.ID</a> as CURRENT_ID
<br>
from TOPO_FILES␟T R1
<br>
left outer join TOPO_FILES␟T R2
<br>
on ST_Touches(R1.TILE
<br>
,R2.TILE)
<br>
or ST_Intersects(R1.TILE
<br>
,R2.TILE)
<br>
where TRUE
<br>
--and <a href="http://R1.ID" rel="noreferrer noreferrer" target="_blank">R1.ID</a> =
<br>
'6b8ca53a-bb5f-4c2b-a9c9-94b6a706e9b0'
<br>
and TRUE)
<br>
,NION as (select CURRENT_TILE as TILE
<br>
,CURRENT_ID
<br>
from RASTER_NEIGHBORS
<br>
union
<br>
select NEIGHBOR_TILE as TILE
<br>
,CURRENT_ID
<br>
from RASTER_NEIGHBORS)
<br>
select ST_Union(TILE) as COMPOSED_TILE
<br>
,CURRENT_ID
<br>
from NION
<br>
group by CURRENT_ID;
<br>
<br>
c) Finally, I want to select all the areas where slope, TRI,… conform
<br>
certain criteria and have a minium surface size. Do I do it this
<br>
better on vector data and do I need to do this on data composed of all
<br>
the contiguous areas?
<br>
<br>
I would be grateful for any nudge into the right direction. Maybe URLs
<br>
with samples.
<br>
<br>
Kind regards
<br>
<br>
Thiemo
<br>
<br>
</blockquote>
</div>
</blockquote>
</div>
</blockquote>
</div>
</body>
</html>