Composing raster tiles?
Regina Obe
lr at pcorp.us
Fri Nov 8 22:11:53 PST 2024
The main overhead I see with postgis raster are the following
1. Limitation of raster sizes of about 1GB as I recall
2. Detoasting of data which is almost always needed https://www.postgresql.org/docs/current/storage-toast.html
3. Mem copying of data, probably more so than using say GDAL directly
4. Probably easier to do parallel operations on command line (though admittedly I haven’t tried)
Admittedly my experience with using GDAL and python directly is very low compared to doing everything in the database so I really don’t know the benefits of doing it all in GDAL.
I still prefer to use the database hammer over having to learn yet another tool. My sense is also that the amount of SQL/plpgsql code you have to write would be much less than trying to pull your stuff out of the DB and stuff it in python/GDAL to do the same thing. This is all to say if the performance is good enough for you, I say go for that hammer even if you think you are just dealing with a nail.
From: Thiemo Kellner <thiemo at gelassene-pferde.biz>
Sent: Friday, November 8, 2024 12:16 PM
To: PostGIS Users <postgis-users at lists.osgeo.org>
Subject: Re: Composing raster tiles?
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 <mailto: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 <mailto: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 <mailto: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/20241109/e65192d4/attachment.htm>
More information about the postgis-users
mailing list