subject:"Re\: \[postgis\-users\] out db\: not able to query the raster"

Re: [postgis-users] out db: not able to query the raster

2023-12-12 Thread andy via postgis-users

Hi to all,
using

ALTER DATABASE test SET postgis.enable_outdb_rasters = true;

and

ALTER DATABASE test SET postgis.gdal_enabled_drivers TO 'ENABLE_ALL';

IT WORKS

Thank you again Jorge!

I should have read the documentation better. Sorry.
I don't know if it is useful to return an error message, when trying to
read a tif out, and these settings are not set.

Best regards

-- 
___

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

Re: [postgis-users] out db: not able to query the raster

2023-12-12 Thread andy via postgis-users

Hi Jorge,
thank you very much.

I hope it help


It helps if the way to make it works properly is to run:

ALTER DATABASE test SET postgis.enable_outdb_rasters = true;

and

ALTER DATABASE test SET postgis.gdal_enabled_drivers TO 'ENABLE_ALL';

These seem to me to be the only two differences.
I will let you know





-- 
___

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

Re: [postgis-users] out db: not able to query the raster

2023-12-12 Thread Jorge Gustavo Rocha via postgis-users


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 :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  -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 | 
0104004AD286B5629E4D404AD286B5629E4DC0EFC13327B5B7204142CF66B5962E504197131D019900842F7661722F6C69622F706F737467726573716C2F646174612F706F70756C6163616F5F32303232313030312E7469008400012F7661722F6C69622F706F737467726573716C2F646174612F706F70756C6163616F5F32303232313030312E7469008400022F7661722F6C69622F706F737467726573716C2F646174612F706F70756C6163616F5F32303232313030312E7469008400032F7661722F6C69622F706F737467726573716C2F646174612F706F70756C6163616F5F32303232313030312E746900 
| 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

Re: [postgis-users] out db: not able to query the raster

2023-12-12 Thread andy via postgis-users

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.5025011,47.7020596)
Pixel Size = (0.0050020,-0.0050020)
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

Re: [postgis-users] out db: not able to query the raster

2023-12-06 Thread andy via postgis-users

I should correct myself: I meant to say that if I point directly to the
image,  postgis doesn't see it.

On Thu, 7 Dec 2023 at 08:30, andy  wrote:

> Good morning,
> I still couldn't read the tif. As written before postgis can read the
> folder, and it sees the image inside.
> But if I point directly to the folder, it doesn't see it.
>
> What am I doing wrong?
>
> Sorry to write again, but I am stuck.
>
> Thank you
>
> --
> ___
>
> 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
>


-- 
___

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

Re: [postgis-users] out db: not able to query the raster

2023-12-06 Thread andy via postgis-users

Good morning,
I still couldn't read the tif. As written before postgis can read the
folder, and it sees the image inside.
But if I point directly to the folder, it doesn't see it.

What am I doing wrong?

Sorry to write again, but I am stuck.

Thank you

-- 
___

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

Re: [postgis-users] out db: not able to query the raster

2023-12-06 Thread Regina Obe via postgis-users

Yah the data directory is less likely to have screwed up permissions that the 
postgres account can’t access.  So I would go with Jorge’s idea.

 

That said I would think that having permission with r r r  should do the trick 
and the fact you can get a directory listing suggests the postgres process can 
at least read the list of files.

 

The only thing I can think of is maybe something is locking those files in some 
write mode preventing any other process from reading.  I’m assuming you are 
done with loading so shouldn’t be that.

 

To rule out postgis raster is at fault, maybe try restarting the service and do 
that pg_stat_file check again immediately after restarting.

 

Regarding your question about “Why is it offline? It's there.”

 

It’s offline because it’s not in your database. Out-db / offline are equivalent 
terminology in postgis raster.  We should probably change that terminology to 
out-db cause offline means not reachable in other contexts and just confusing.  
I think calling it out-db is a lot clearer.

 

 

From: postgis-users  On Behalf Of Jorge 
Gustavo Rocha via postgis-users
Sent: Wednesday, December 6, 2023 5:25 PM
To: postgis-users@lists.osgeo.org
Cc: Jorge Gustavo Rocha 
Subject: Re: [postgis-users] out db: not able to query the raster

 

Hi Andrea,

I always put the raster somewhere below Postgresql data_directory.

psql
show data_directory;

I hope it helps.

Saluti,

Jorge

On 04/12/23 22:23, andy via postgis-users wrote:

Hi, 

if I run "SELECT (ST_BandMetaData(rast)).* FROM rasters_outdb LIMIT 1;", in the 
field path I have "/directory_docker/ou_s6_01_01_stack.tif"

 

If I run "ls -l /directory_docker/ou_s6_01_01_stack.tif" I have

-rwxr--r-- 1 1000 1000 2100227 Dec  4 08:32 
/directory_docker/ou_s6_01_01_stack.tif

 

But when I run "SELECT ST_Value(rast, 1, 1) FROM rasters_outdb;" I get 

ERROR:  rt_band_load_offline_data: Cannot open offline raster: 
/directory_docker/ou_s6_01_01_stack.tif

 

Why is it offline? It's there.

I'm using postgis/postgis:16-master

 

I have created the sql file to load it in postgres, running

 

raster2pgsql -I -C -M -F -t auto -R /directory_docker/ou_s6_01_01_stack.tif 
public.rasters_outdb

 

How to read the pixel values using a PG SQL query?

 

Thank you


 

-- 

___

 

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

Re: [postgis-users] out db: not able to query the raster

2023-12-06 Thread Jorge Gustavo Rocha via postgis-users


Hi Andrea,

I always put the raster somewhere below Postgresql data_directory.

psql
show data_directory;

I hope it helps.

Saluti,

Jorge

On 04/12/23 22:23, andy via postgis-users wrote:

Hi,
if I run "SELECT (ST_BandMetaData(rast)).* FROM rasters_outdb LIMIT 
1;", in the field path I have "/directory_docker/ou_s6_01_01_stack.tif"


If I run "ls -l /directory_docker/ou_s6_01_01_stack.tif" I have

-rwxr--r-- 1 1000 1000 2100227 Dec  4 08:32 
/directory_docker/ou_s6_01_01_stack.tif


But when I run "SELECT ST_Value(rast, 1, 1) FROM rasters_outdb;" I get

ERROR:  rt_band_load_offline_data: Cannot open offline raster: 
/directory_docker/ou_s6_01_01_stack.tif


Why is it offline? It's there.
I'm using postgis/postgis:16-master

I have created the sql file to load it in postgres, running

raster2pgsql -I -C -M -F -t auto -R 
/directory_docker/ou_s6_01_01_stack.tif public.rasters_outdb


How to read the pixel values using a PG SQL query?

Thank you

--
___

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

Re: [postgis-users] out db: not able to query the raster

2023-12-04 Thread andy via postgis-users

Hi Regina,
I'm running all from docker.
"/directory_docker" is pointing to a folder on my host machine.

If I run

SELECT * FROM pg_ls_dir('/directory_docker');

I get my files

   pg_ls_dir
---
 ou_s6_01_01_stack.sql
 ou_s6_01_01_stack.tif
(2 rows)

But, if I run

SELECT * FROM pg_stat_file('/directory_docker/ou_s6_01_01_stack.tif ');

I have the same error I do not know how to solve

ERROR: could not stat file "/directory_docker/ou_s6_01_01_stack.tif ": No
such file or directory

Thank you


-- 
___

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

Re: [postgis-users] out db: not able to query the raster

2023-12-04 Thread Regina Obe via postgis-users

Are you running postgres inside the docker container or are you using your own?

 

I wasn’t clear if you are just using the raster2pgsql from docker or you are 
using the postgresql as well.

 

It’s possible inside the container it doesn’t know the path as such.

 

Try this query in your postgresql, to see if your postgresql can read that path

 

SELECT *

FROM pg_ls_dir('/directory_docker');

 

And can read the file

 

SELECT *

FROM pg_stat_file('/directory_docker/ou_s6_01_01_stack.tif ');

 

 

From: postgis-users  On Behalf Of andy 
via postgis-users
Sent: Monday, December 4, 2023 5:24 PM
To: PostGIS Users Discussion 
Cc: andy 
Subject: [postgis-users] out db: not able to query the raster

 

Hi,

if I run "SELECT (ST_BandMetaData(rast)).* FROM rasters_outdb LIMIT 1;", in the 
field path I have "/directory_docker/ou_s6_01_01_stack.tif"

 

If I run "ls -l /directory_docker/ou_s6_01_01_stack.tif" I have

-rwxr--r-- 1 1000 1000 2100227 Dec  4 08:32 
/directory_docker/ou_s6_01_01_stack.tif

 

But when I run "SELECT ST_Value(rast, 1, 1) FROM rasters_outdb;" I get 

ERROR:  rt_band_load_offline_data: Cannot open offline raster: 
/directory_docker/ou_s6_01_01_stack.tif

 

Why is it offline? It's there.

I'm using postgis/postgis:16-master

 

I have created the sql file to load it in postgres, running

 

raster2pgsql -I -C -M -F -t auto -R /directory_docker/ou_s6_01_01_stack.tif 
public.rasters_outdb

 

How to read the pixel values using a PG SQL query?

 

Thank you


 

-- 

___

 

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

Re: [postgis-users] out db: not able to query the raster

Re: [postgis-users] out db: not able to query the raster

Re: [postgis-users] out db: not able to query the raster

Re: [postgis-users] out db: not able to query the raster

Re: [postgis-users] out db: not able to query the raster

Re: [postgis-users] out db: not able to query the raster

Re: [postgis-users] out db: not able to query the raster

Re: [postgis-users] out db: not able to query the raster

Re: [postgis-users] out db: not able to query the raster

Re: [postgis-users] out db: not able to query the raster

10 matches

Site Navigation

Mail list logo

Footer information