Re: Advice on loading OpenTopography data into a PostGIS database apreciated

[email protected] escribió:

> 2024-11-01 00:28:33 - root - DEBUG -  
> source_id:4f68d890-a08c-4c06-8aa5-741ad36b6abe
> Traceback (most recent call last):
>   File  
> "/home/thiemo/external_projects/svn/33/trunk/code_files/data_storage/load_OpenTopography_data.py", line 737, in  
> <module>
>     main()
>   File  
> "/home/thiemo/external_projects/svn/33/trunk/code_files/data_storage/load_OpenTopography_data.py", line 714, in  
> main
>     process_files(
>   File  
> "/home/thiemo/external_projects/svn/33/trunk/code_files/data_storage/load_OpenTopography_data.py", line 442, in  
> process_files
>     cur.execute(statement, params)
> psycopg2.errors.InternalError_: RASTER_fromGDALRaster: Could not  
> open bytea with GDAL. Check that the bytea is of a GDAL supported  
> format

I presume there is a privilege problem I do not understand. My user  
treintaytres owns the database in question (treintaytres).

treintaytres=> \l+ treinta*
                                                                        
List of databases
      Name     |    Owner     | Encoding | Locale Provider | Collate |  
    Ctype    | Locale | ICU Rules | Access privileges | Size  |  
Tablespace | Description
--------------+--------------+----------+-----------------+---------+-------------+--------+-----------+-------------------+-------+------------+-------------
  treintaytres | treintaytres | UTF8     | libc            | C       |  
de_DE.UTF-8 |        |           |                   | 16 MB |  
pg_default |
(1 row)

treintaytres=> \du
                                List of roles
   Role name   |                         Attributes
--------------+------------------------------------------------------------
  logger       |
  postgres     | Superuser, Create role, Create DB, Replication, Bypass RLS
  treintaytres |
  utils        |
  utils_l      |


The PostGIS extensions are installed - maybe I am missing out on one.

treintaytres=> \dx postgis*
                                   List of installed extensions
       Name      | Version | Schema |                        Description
----------------+---------+--------+------------------------------------------------------------
  postgis        | 3.5.0   | public | PostGIS geometry and geography  
spatial types and functions
  postgis_raster | 3.5.0   | public | PostGIS raster types and functions
(2 rows)


treintaytres seems to be able to enable all drivers, but not load them.

treintaytres=> set postgis.gdal_enabled_drivers = 'ENABLE_ALL';
SET
treintaytres=> WITH foo AS (
     SELECT  
ST_AsPNG(ST_AddBand(ST_AddBand(ST_AddBand(ST_MakeEmptyRaster(2, 2, 0,  
0, 0.1, -0.1, 0, 0, 4326), 1, '8BUI', 1, 0), 2, '8BUI', 2, 0), 3,  
'8BUI', 3, 0)) AS png
),
bar AS (
     SELECT 1 AS rid, ST_FromGDALRaster(png) AS rast FROM foo
     UNION ALL
     SELECT 2 AS rid, ST_FromGDALRaster(png, 3310) AS rast FROM foo
)
SELECT
     rid,
     ST_Metadata(rast) AS metadata,
     ST_SummaryStats(rast, 1) AS stats1,
     ST_SummaryStats(rast, 2) AS stats2,
     ST_SummaryStats(rast, 3) AS stats3
FROM bar
ORDER BY rid;
WARNING:  permission denied to set parameter "postgis.gdal_enabled_drivers"
ERROR:  rt_raster_to_gdal: Could not load the output GDAL driver
CONTEXT:  PL/pgSQL function st_aspng(raster,text[]) line 31 at RETURN


I am not sure whether this is of importance, but I created the  
database treintaytres from a template database that had the extensions  
installed already.

Kind regards

Thiemo