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