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:[email protected]] On Behalf Of Alex German Sent: Thursday, November 10, 2022 2:43 AM To: PostGIS Users Discussion <[email protected]> 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 <[email protected]>: 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:[email protected]] On Behalf Of Alex German Sent: Wednesday, November 9, 2022 12:51 AM To: [email protected] 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 [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users -- Алексей Герман 8-905-86-00-416 _______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
