[postgis-users] Problem with raster2psql out-db

[Regina Obe]

Sadly I think pyramids are always ignored.

The way to create the equivalent in postgis raster, is to use the overview load, but overview tables always end up being in the database.

Which is the -l option in raster2pgsql as detailed here - https://postgis.net/docs/using_raster_dataman.html#RT_Raster_Loader 
But I forget how that works with -R if it is smart enough to use the pyramids or not to build the overview.  I suspect not, so it might result in an in-db raster table.

So your call would look something like:
raster2pgsql -R -l 2,3,4 //geoserver/t1.tif | psql

For tables you've already loaded, you can use this function to do the same as -l does

https://postgis.net/docs/RT_CreateOverview.html


It would be a good feature to have though to be able to use the built in pyramids.
I'm not sure how difficult it would be, but definitely worth a ticket I think in our ticket tracker https://postgis.net/support/

I'll send you a mantra code so you can create an osgeo account and put it in as a request if you want to.

Thanks,
Regina




  
----
From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Alex German
Sent: Thursday, November 10, 2022 2:43 AM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: Re: [postgis-users] Problem with raster2psql out-db

Thank you, Regina Obe!
Your advice really helped. Now the raster is loaded into the database . 
But there was another problem. 
The Cloud Optimized GeoTIFF raster that was created by gdal_translate. 
The raster has pyramids. However, pyramids are not used when uploading to the database. 
What could be the problem?

Alex German

чт, 10 нояб. 2022 г. в 08:30, Regina Obe <lr at pcorp.us>:
The  out_db constraint is not filled in unless you include the –C option to include constraints.
So I suspect your raster loaded is really out of database.
 
An easy way to check is run a query like this described on this page: https://postgis.net/docs/manual-3.3/RT_ST_BandMetaData.html
 
 
SELECT bmd.out_db, bmd.path 
FROM t1 AS r, ST_BandMetaData(r.rast) AS bmd LIMIT 1;
 
 
You can add an out_db constraint with this function -  https://postgis.net/docs/manual-3.3/RT_AddRasterConstraints.html
 
Like so:
 
SELECT AddRasterConstraints('t1', 'rast', 'out_db' );
 
Hope that helps,
Regina
 
From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Alex German
Sent: Wednesday, November 9, 2022 12:51 AM
To: postgis-users at lists.osgeo.org
Subject: [postgis-users] Problem with raster2psql out-db
 
Hi, everybody.
I need help.
I have a problem when using the raster2psql utility.
I use the -R key, but the raster is loaded into the raster table, and not as an external file. raster_columns in the out_db field is null
raster2pgsql -R //geoserver/t1.tif | psql
What am I doing wrong?
-- 
Alex German
 
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users



-- 
Алексей Герман
8-905-86-00-416