Hi Andrea,
I did it using docker and it works as expected.
Here is the copy and paste of what I did:
docker run --name postgis16 -p 5555:5432 -e
POSTGRES_PASSWORD=thePassword -d postgis/postgis:16-master
docker cp
/home/jgr/geoserver/data_dir/temp/smiguel/populacao_20221001.tiff
437022e5ebd0:/var/lib/postgresql/data
docker exec -it 437022e5ebd0 bash
gdalinfo /var/lib/postgresql/data/populacao_20221001.tiff
root@437022e5ebd0:/# psql -U postgres
psql (16.1 (Debian 16.1-1.pgdg110+1))
Type "help" for help.
postgres=# create database test;
CREATE DATABASE
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# create extension postgis;
CREATE EXTENSION
test=# create extension postgis_raster;
CREATE EXTENSION
test=# \q
root@437022e5ebd0:/# psql -U postgres test
psql (16.1 (Debian 16.1-1.pgdg110+1))
Type "help" for help.
test=# ALTER DATABASE test SET postgis.enable_outdb_rasters = true;
ALTER DATABASE
test=# ALTER DATABASE test SET postgis.gdal_enabled_drivers TO 'ENABLE_ALL';
ALTER DATABASE
test=# \q
root@437022e5ebd0:/# raster2pgsql -I -C -M -F -t auto -R
/var/lib/postgresql/data/populacao_20221001.tiff public.nome_tabella |
psql -U postgres -d test
Processing 1/1: /var/lib/postgresql/data/populacao_20221001.tiff
INFO: Using computed tile size: 285x153
BEGIN
CREATE TABLE
INSERT 0 1
INSERT 0 1
...
INSERT 0 1
INSERT 0 1
CREATE INDEX
ANALYZE
NOTICE: Adding SRID constraint
NOTICE: Adding scale-X constraint
NOTICE: Adding scale-Y constraint
NOTICE: Adding blocksize-X constraint
NOTICE: Adding blocksize-Y constraint
NOTICE: Adding alignment constraint
NOTICE: Adding number of bands constraint
NOTICE: Adding pixel type constraint
NOTICE: Adding nodata value constraint
NOTICE: Adding out-of-database constraint
NOTICE: Adding maximum extent constraint
addrasterconstraints
----------------------
t
(1 row)
COMMIT
VACUUM
Test from host:
jgr@dragon:~$ psql -h localhost -p 5555 -U postgres postgres
Password for user postgres:
psql (16.1 (Ubuntu 16.1-1.pgdg22.04+1))
Type "help" for help.
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# select * from nome_tabella limit 1;
rid | rast | filename
-----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------
1 |
01000004004AD286B5629E4D404AD286B5629E4DC0EFC13327B5B7204142CF66B5962E504100000000000000000000000000000000971300001D0199008400002F7661722F6C69622F706F737467726573716C2F646174612F706F70756C6163616F5F32303232313030312E74696666008400012F7661722F6C69622F706F737467726573716C2F646174612F706F70756C6163616F5F32303232313030312E74696666008400022F7661722F6C69622F706F737467726573716C2F646174612F706F70756C6163616F5F32303232313030312E74696666008400032F7661722F6C69622F706F737467726573716C2F646174612F706F70756C6163616F5F32303232313030312E7469666600
| populacao_20221001.tiff
(1 row)
test=# SELECT ST_Value(rast, 1, 1) FROM nome_tabella;
st_value
----------
255
255
255
255
255
255
255
255
255
255
255
...
I hope it helps!
Saluti,
Jorge
On 12/12/23 08:51, andy via postgis-users wrote:
Hi all,
I had missed some messages, sorry.
But I still can't do a query on a raster.
I'll rewrite everything I've done.
I start with docker:
docker pull postgis/postgis:16-master
docker run --name postgis16 \
-p 5432:5432 \
-v /home/user/folder:/directory_docker \
-e POSTGRES_PASSWORD=thePassword \
-d postgis/postgis:16-master
Then I run
docker exec -it postgis16 bash
If I run "ls /directory_docker/" I get "ou_s6_01_01_stack.tif"
If I run "SELECT * FROM pg_ls_dir('/directory_docker');" I get
"ou_s6_01_01_stack.tif"
Then I load this tif in postgres
raster2pgsql -I -C -M -F -t auto -R
/directory_docker/ou_s6_01_01_stack.tif public.nome_tabella | psql -U
postgres -d postgres
If I run
select * from nome_tabella limit 1;
I get something like
rid,rast,filename
1,01000....,ou_s6_01_01_stack.tif
Something that is strange for me is that there is no path, only the
filename.
At the end I run "SELECT ST_Value(rast, 1, 1) FROM nome_tabella;" and
I get
ERROR: rt_band_load_offline_data: Cannot open offline raster:
/directory_docker/ou_s6_01_01_stack.tif
Then I copy my tif in /var/lib/postgresql/data and I import it again
using raster2pgsql.
I have again
ERROR: rt_band_load_offline_data: Cannot open offline raster:
/var/lib/postgresql/data/ou_s6_01_01_stack.tif
I'm sorry I'm boring you, it should be an immediate thing, I can't
understand what the problem might be.
Thank you (below my tif metadata),
Andrea
Tif Metadata, gdalinfo /directory_docker/ou_s6_01_01_stack.tif
Driver: GTiff/GeoTIFF
Files: /directory_docker/ou_s6_01_01_stack.tif
Size is 2500, 2280
Coordinate System is:
GEOGCRS["WGS 84",
ENSEMBLE["World Geodetic System 1984 ensemble",
MEMBER["World Geodetic System 1984 (Transit)"],
MEMBER["World Geodetic System 1984 (G730)"],
MEMBER["World Geodetic System 1984 (G873)"],
MEMBER["World Geodetic System 1984 (G1150)"],
MEMBER["World Geodetic System 1984 (G1674)"],
MEMBER["World Geodetic System 1984 (G1762)"],
MEMBER["World Geodetic System 1984 (G2139)"],
ELLIPSOID["WGS 84",6378137,298.257223563,
LENGTHUNIT["metre",1]],
ENSEMBLEACCURACY[2.0]],
PRIMEM["Greenwich",0,
ANGLEUNIT["degree",0.0174532925199433]],
CS[ellipsoidal,2],
AXIS["geodetic latitude (Lat)",north,
ORDER[1],
ANGLEUNIT["degree",0.0174532925199433]],
AXIS["geodetic longitude (Lon)",east,
ORDER[2],
ANGLEUNIT["degree",0.0174532925199433]],
USAGE[
SCOPE["Horizontal component of 3D system."],
AREA["World."],
BBOX[-90,-180,90,180]],
ID["EPSG",4326]]
Data axis to CRS axis mapping: 2,1
Origin = (6.502501000000001,47.702059000000006)
Pixel Size = (0.005002000000000,-0.005002000000000)
Metadata:
AREA_OR_POINT=Area
TIFFTAG_SOFTWARE=ERDAS IMAGINE
Image Structure Metadata:
COMPRESSION=PACKBITS
INTERLEAVE=PIXEL
Corner Coordinates:
Upper Left ( 6.5025010, 47.7020590) ( 6d30' 9.00"E, 47d42' 7.41"N)
Lower Left ( 6.5025010, 36.2974990) ( 6d30' 9.00"E, 36d17'51.00"N)
Upper Right ( 19.0075010, 47.7020590) ( 19d 0'27.00"E, 47d42' 7.41"N)
Lower Right ( 19.0075010, 36.2974990) ( 19d 0'27.00"E, 36d17'51.00"N)
Center ( 12.7550010, 41.9997790) ( 12d45'18.00"E, 41d59'59.20"N)
Band 1 Block=512x512 Type=Float32, ColorInterp=Red
Band 2 Block=512x512 Type=Float32, ColorInterp=Green
Band 3 Block=512x512 Type=Float32, ColorInterp=Blue
Band 4 Block=512x512 Type=Float32, ColorInterp=Undefined
--
___________________
Andrea Borruso
website: https://medium.com/tantotanto
38° 7' 48" N, 13° 21' 9" E, EPSG:4326
___________________
"cercare e saper riconoscere chi e cosa,
in mezzo all’inferno, non è inferno,
e farlo durare, e dargli spazio"
Italo Calvino
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
