[postgis-users] out db: not able to query the raster
Jorge Gustavo Rocha
jgr at di.uminho.pt
Tue Dec 12 02:38:28 PST 2023
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 at 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 at 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 at 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 at 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 at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20231212/6993b5c1/attachment.htm>
More information about the postgis-users
mailing list