PostGIS Out-DB Raster Not Behaving As Expected

classic Classic list List threaded Threaded
16 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

PostGIS Out-DB Raster Not Behaving As Expected

Osahon Oduware
Hi All,

I created an out-db raster using the following syntax:

raster2pgsql -s {srid} -c -R -I -C -F -t auto {absolute_file_path} public.{table} | psql -h {host} -p {port} -d {database} -U {user}

The table was created successfully. I wanted to confirm that the actual file is being read from the location in the filesystem by performing the following steps:
1) I moved the raster file to a different location.
2) I opened QGIS and attempted to load the raster from PostGIS table.

I was surprised that QGIS could load the file. How is this possible when the actual raster data is not stored in the database table?

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: PostGIS Out-DB Raster Not Behaving As Expected

Jorge Gustavo Rocha
Hi,

According to the documentation [1], if you add the option -l (like -l
2,4,8,16 ), the overviews are created in the database, even if you use
the -R option.

But since you did not use the -l option, I don't know how you can see
the images.

Can you check the layer properties in QGIS? Are any pyramids available
or not?

Regards,

Jorge


Às 16:52 de 10-04-2017, Osahon Oduware escreveu:

> Hi All,
>
> I created an out-db raster using the following syntax:
>
> raster2pgsql -s {srid} -c -R -I -C -F -t auto {absolute_file_path}
> public.{table} | psql -h {host} -p {port} -d {database} -U {user}
>
> The table was created successfully. I wanted to confirm that the actual
> file is being read from the location in the filesystem by performing the
> following steps:
> 1) I moved the raster file to a different location.
> 2) I opened QGIS and attempted to load the raster from PostGIS table.
>
> I was surprised that QGIS could load the file. *How is this possible
> when the actual raster data is not stored in the database table?*
>
>
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>

J. Gustavo
--
Jorge Gustavo Rocha
Departamento de Informática
Universidade do Minho
4710-057 Braga
Tel: +351 253604480
Fax: +351 253604471
Móvel: +351 910333888
skype: nabocudnosor
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: PostGIS Out-DB Raster Not Behaving As Expected

Regina Obe-2
You should also check the following queries

SELECT r_table_name, r_raster_column, out_db FROM raster_columns;

Out_db should be true for all your bands for the specific table and as Jorge mentioned, you shouldn't see any over view tables (which start with o_..{table}.
I believe QGIS internally tries to use over view tables to speed things up, so it's possible it's using an overview table instead of the main outdb table if you are zoomed out enough.

For outdb, querying the meta data of the raster will tell you the path if it's an outdb.  Replace nj_ned with your table name.

SELECT t.rid, (md).isoutdb, (md).path
FROM nj_ned AS t, ST_BandMetaData(t.rast) AS md
limit 1;

Hope that helps,
Regina
http://postgis.us
http://postgis.net


-----Original Message-----
From: postgis-users [mailto:[hidden email]] On Behalf Of Jorge Gustavo Rocha
Sent: Monday, April 10, 2017 2:14 PM
To: [hidden email]
Subject: Re: [postgis-users] PostGIS Out-DB Raster Not Behaving As Expected

Hi,

According to the documentation [1], if you add the option -l (like -l
2,4,8,16 ), the overviews are created in the database, even if you use the -R option.

But since you did not use the -l option, I don't know how you can see the images.

Can you check the layer properties in QGIS? Are any pyramids available or not?

Regards,

Jorge


 s 16:52 de 10-04-2017, Osahon Oduware escreveu:

> Hi All,
>
> I created an out-db raster using the following syntax:
>
> raster2pgsql -s {srid} -c -R -I -C -F -t auto {absolute_file_path}
> public.{table} | psql -h {host} -p {port} -d {database} -U {user}
>
> The table was created successfully. I wanted to confirm that the
> actual file is being read from the location in the filesystem by
> performing the following steps:
> 1) I moved the raster file to a different location.
> 2) I opened QGIS and attempted to load the raster from PostGIS table.
>
> I was surprised that QGIS could load the file. *How is this possible
> when the actual raster data is not stored in the database table?*
>
>
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>

J. Gustavo
--
Jorge Gustavo Rocha
Departamento de Inform tica
Universidade do Minho
4710-057 Braga
Tel: +351 253604480
Fax: +351 253604471
M vel: +351 910333888
skype: nabocudnosor
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: PostGIS Out-DB Raster Not Behaving As Expected

Osahon Oduware
Hi Regina,

I have tried the queries above. For the related raster table,

SELECT r_table_name, r_raster_column, out_db FROM raster_columns; RETURNS
"raster_table","rast","[False, False, False]"

SELECT t.rid, (md).isoutdb, (md).path
FROM nj_ned AS t, ST_BandMetaData(t.rast) AS md
limit 1; RETURNS
1,False,""

I believe from the result of the 1st query that out-db is not enabled. However, the environment variable POSTGIS_ENABLE_OUTDB_RASTERS is SET to 1. I have tried to also enable it with the following, restarted the postgres Service, and re-loaded the raster, but the 1st query gives the same result:

ALTER DATABASE <database_name> SET postgis.enable_outdb_rasters TO True;


Please, could you help me further on what I am not doing right to enable the out-db rasters.




On Mon, Apr 10, 2017 at 8:31 PM, Regina Obe <[hidden email]> wrote:
You should also check the following queries

SELECT r_table_name, r_raster_column, out_db FROM raster_columns;

Out_db should be true for all your bands for the specific table and as Jorge mentioned, you shouldn't see any over view tables (which start with o_..{table}.
I believe QGIS internally tries to use over view tables to speed things up, so it's possible it's using an overview table instead of the main outdb table if you are zoomed out enough.

For outdb, querying the meta data of the raster will tell you the path if it's an outdb.  Replace nj_ned with your table name.

SELECT t.rid, (md).isoutdb, (md).path
FROM nj_ned AS t, ST_BandMetaData(t.rast) AS md
limit 1;

Hope that helps,
Regina
http://postgis.us
http://postgis.net


-----Original Message-----
From: postgis-users [mailto:[hidden email]] On Behalf Of Jorge Gustavo Rocha
Sent: Monday, April 10, 2017 2:14 PM
To: [hidden email]
Subject: Re: [postgis-users] PostGIS Out-DB Raster Not Behaving As Expected

Hi,

According to the documentation [1], if you add the option -l (like -l
2,4,8,16 ), the overviews are created in the database, even if you use the -R option.

But since you did not use the -l option, I don't know how you can see the images.

Can you check the layer properties in QGIS? Are any pyramids available or not?

Regards,

Jorge


 s 16:52 de 10-04-2017, Osahon Oduware escreveu:
> Hi All,
>
> I created an out-db raster using the following syntax:
>
> raster2pgsql -s {srid} -c -R -I -C -F -t auto {absolute_file_path}
> public.{table} | psql -h {host} -p {port} -d {database} -U {user}
>
> The table was created successfully. I wanted to confirm that the
> actual file is being read from the location in the filesystem by
> performing the following steps:
> 1) I moved the raster file to a different location.
> 2) I opened QGIS and attempted to load the raster from PostGIS table.
>
> I was surprised that QGIS could load the file. *How is this possible
> when the actual raster data is not stored in the database table?*
>
>
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>

J. Gustavo
--
Jorge Gustavo Rocha
Departamento de Inform tica
Universidade do Minho
4710-057 Braga
Tel: +351 253604480
Fax: +351 253604471
M vel: +351 910333888
skype: nabocudnosor
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: PostGIS Out-DB Raster Not Behaving As Expected

Tumasgiu Rossini
Hi,

could it be that you are trying to tile your rasters with the option -"t auto" ?

Sorry, I can't test it right now.

2017-04-11 11:07 GMT+02:00 Osahon Oduware <[hidden email]>:
Hi Regina,

I have tried the queries above. For the related raster table,

SELECT r_table_name, r_raster_column, out_db FROM raster_columns; RETURNS
"raster_table","rast","[False, False, False]"

SELECT t.rid, (md).isoutdb, (md).path
FROM nj_ned AS t, ST_BandMetaData(t.rast) AS md
limit 1; RETURNS
1,False,""

I believe from the result of the 1st query that out-db is not enabled. However, the environment variable POSTGIS_ENABLE_OUTDB_RASTERS is SET to 1. I have tried to also enable it with the following, restarted the postgres Service, and re-loaded the raster, but the 1st query gives the same result:

ALTER DATABASE <database_name> SET postgis.enable_outdb_rasters TO True;


Please, could you help me further on what I am not doing right to enable the out-db rasters.




On Mon, Apr 10, 2017 at 8:31 PM, Regina Obe <[hidden email]> wrote:
You should also check the following queries

SELECT r_table_name, r_raster_column, out_db FROM raster_columns;

Out_db should be true for all your bands for the specific table and as Jorge mentioned, you shouldn't see any over view tables (which start with o_..{table}.
I believe QGIS internally tries to use over view tables to speed things up, so it's possible it's using an overview table instead of the main outdb table if you are zoomed out enough.

For outdb, querying the meta data of the raster will tell you the path if it's an outdb.  Replace nj_ned with your table name.

SELECT t.rid, (md).isoutdb, (md).path
FROM nj_ned AS t, ST_BandMetaData(t.rast) AS md
limit 1;

Hope that helps,
Regina
http://postgis.us
http://postgis.net


-----Original Message-----
From: postgis-users [mailto:[hidden email]] On Behalf Of Jorge Gustavo Rocha
Sent: Monday, April 10, 2017 2:14 PM
To: [hidden email]
Subject: Re: [postgis-users] PostGIS Out-DB Raster Not Behaving As Expected

Hi,

According to the documentation [1], if you add the option -l (like -l
2,4,8,16 ), the overviews are created in the database, even if you use the -R option.

But since you did not use the -l option, I don't know how you can see the images.

Can you check the layer properties in QGIS? Are any pyramids available or not?

Regards,

Jorge


 s 16:52 de 10-04-2017, Osahon Oduware escreveu:
> Hi All,
>
> I created an out-db raster using the following syntax:
>
> raster2pgsql -s {srid} -c -R -I -C -F -t auto {absolute_file_path}
> public.{table} | psql -h {host} -p {port} -d {database} -U {user}
>
> The table was created successfully. I wanted to confirm that the
> actual file is being read from the location in the filesystem by
> performing the following steps:
> 1) I moved the raster file to a different location.
> 2) I opened QGIS and attempted to load the raster from PostGIS table.
>
> I was surprised that QGIS could load the file. *How is this possible
> when the actual raster data is not stored in the database table?*
>
>
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>

J. Gustavo
--
Jorge Gustavo Rocha
Departamento de Inform tica
Universidade do Minho
4710-057 Braga
Tel: <a href="tel:+351%20253%20604%20480" value="+351253604480" target="_blank">+351 253604480
Fax: <a href="tel:+351%20253%20604%20471" value="+351253604471" target="_blank">+351 253604471
M vel: <a href="tel:+351%20910%20333%20888" value="+351910333888" target="_blank">+351 910333888
skype: nabocudnosor
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: PostGIS Out-DB Raster Not Behaving As Expected

Osahon Oduware
Hi Tumasgiu,

Yes, but I have tested it without the tiling too.

On Tue, Apr 11, 2017 at 10:27 AM, Tumasgiu Rossini <[hidden email]> wrote:
Hi,

could it be that you are trying to tile your rasters with the option -"t auto" ?

Sorry, I can't test it right now.

2017-04-11 11:07 GMT+02:00 Osahon Oduware <[hidden email]>:
Hi Regina,

I have tried the queries above. For the related raster table,

SELECT r_table_name, r_raster_column, out_db FROM raster_columns; RETURNS
"raster_table","rast","[False, False, False]"

SELECT t.rid, (md).isoutdb, (md).path
FROM nj_ned AS t, ST_BandMetaData(t.rast) AS md
limit 1; RETURNS
1,False,""

I believe from the result of the 1st query that out-db is not enabled. However, the environment variable POSTGIS_ENABLE_OUTDB_RASTERS is SET to 1. I have tried to also enable it with the following, restarted the postgres Service, and re-loaded the raster, but the 1st query gives the same result:

ALTER DATABASE <database_name> SET postgis.enable_outdb_rasters TO True;


Please, could you help me further on what I am not doing right to enable the out-db rasters.




On Mon, Apr 10, 2017 at 8:31 PM, Regina Obe <[hidden email]> wrote:
You should also check the following queries

SELECT r_table_name, r_raster_column, out_db FROM raster_columns;

Out_db should be true for all your bands for the specific table and as Jorge mentioned, you shouldn't see any over view tables (which start with o_..{table}.
I believe QGIS internally tries to use over view tables to speed things up, so it's possible it's using an overview table instead of the main outdb table if you are zoomed out enough.

For outdb, querying the meta data of the raster will tell you the path if it's an outdb.  Replace nj_ned with your table name.

SELECT t.rid, (md).isoutdb, (md).path
FROM nj_ned AS t, ST_BandMetaData(t.rast) AS md
limit 1;

Hope that helps,
Regina
http://postgis.us
http://postgis.net


-----Original Message-----
From: postgis-users [mailto:[hidden email]] On Behalf Of Jorge Gustavo Rocha
Sent: Monday, April 10, 2017 2:14 PM
To: [hidden email]
Subject: Re: [postgis-users] PostGIS Out-DB Raster Not Behaving As Expected

Hi,

According to the documentation [1], if you add the option -l (like -l
2,4,8,16 ), the overviews are created in the database, even if you use the -R option.

But since you did not use the -l option, I don't know how you can see the images.

Can you check the layer properties in QGIS? Are any pyramids available or not?

Regards,

Jorge


 s 16:52 de 10-04-2017, Osahon Oduware escreveu:
> Hi All,
>
> I created an out-db raster using the following syntax:
>
> raster2pgsql -s {srid} -c -R -I -C -F -t auto {absolute_file_path}
> public.{table} | psql -h {host} -p {port} -d {database} -U {user}
>
> The table was created successfully. I wanted to confirm that the
> actual file is being read from the location in the filesystem by
> performing the following steps:
> 1) I moved the raster file to a different location.
> 2) I opened QGIS and attempted to load the raster from PostGIS table.
>
> I was surprised that QGIS could load the file. *How is this possible
> when the actual raster data is not stored in the database table?*
>
>
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>

J. Gustavo
--
Jorge Gustavo Rocha
Departamento de Inform tica
Universidade do Minho
4710-057 Braga
Tel: <a href="tel:+351%20253%20604%20480" value="+351253604480" target="_blank">+351 253604480
Fax: <a href="tel:+351%20253%20604%20471" value="+351253604471" target="_blank">+351 253604471
M vel: <a href="tel:+351%20910%20333%20888" value="+351910333888" target="_blank">+351 910333888
skype: nabocudnosor
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Fwd: PostGIS Out-DB Raster Not Behaving As Expected

Osahon Oduware
In reply to this post by Osahon Oduware
Hi Regina,

I would like to add that the SHOW command below returns "on": 

SHOW postgis.enable_outdb_rasters;
which I believe indicates that the environment variable is SET to enable out-db raster. However, the result from your 1st query shows that the raster table isn't created with out-db. Any reason for this behaviour?


---------- Forwarded message ----------
From: Osahon Oduware <[hidden email]>
Date: Tue, Apr 11, 2017 at 10:07 AM
Subject: Re: [postgis-users] PostGIS Out-DB Raster Not Behaving As Expected
To: PostGIS Users Discussion <[hidden email]>
Cc: Regina Obe <[hidden email]>, Jorge Gustavo Rocha <[hidden email]>


Hi Regina,

I have tried the queries above. For the related raster table,

SELECT r_table_name, r_raster_column, out_db FROM raster_columns; RETURNS
"raster_table","rast","[False, False, False]"

SELECT t.rid, (md).isoutdb, (md).path
FROM nj_ned AS t, ST_BandMetaData(t.rast) AS md
limit 1; RETURNS
1,False,""

I believe from the result of the 1st query that out-db is not enabled. However, the environment variable POSTGIS_ENABLE_OUTDB_RASTERS is SET to 1. I have tried to also enable it with the following, restarted the postgres Service, and re-loaded the raster, but the 1st query gives the same result:

ALTER DATABASE <database_name> SET postgis.enable_outdb_rasters TO True;


Please, could you help me further on what I am not doing right to enable the out-db rasters.




On Mon, Apr 10, 2017 at 8:31 PM, Regina Obe <[hidden email]> wrote:
You should also check the following queries

SELECT r_table_name, r_raster_column, out_db FROM raster_columns;

Out_db should be true for all your bands for the specific table and as Jorge mentioned, you shouldn't see any over view tables (which start with o_..{table}.
I believe QGIS internally tries to use over view tables to speed things up, so it's possible it's using an overview table instead of the main outdb table if you are zoomed out enough.

For outdb, querying the meta data of the raster will tell you the path if it's an outdb.  Replace nj_ned with your table name.

SELECT t.rid, (md).isoutdb, (md).path
FROM nj_ned AS t, ST_BandMetaData(t.rast) AS md
limit 1;

Hope that helps,
Regina
http://postgis.us
http://postgis.net


-----Original Message-----
From: postgis-users [mailto:[hidden email]] On Behalf Of Jorge Gustavo Rocha
Sent: Monday, April 10, 2017 2:14 PM
To: [hidden email]
Subject: Re: [postgis-users] PostGIS Out-DB Raster Not Behaving As Expected

Hi,

According to the documentation [1], if you add the option -l (like -l
2,4,8,16 ), the overviews are created in the database, even if you use the -R option.

But since you did not use the -l option, I don't know how you can see the images.

Can you check the layer properties in QGIS? Are any pyramids available or not?

Regards,

Jorge


 s 16:52 de 10-04-2017, Osahon Oduware escreveu:
> Hi All,
>
> I created an out-db raster using the following syntax:
>
> raster2pgsql -s {srid} -c -R -I -C -F -t auto {absolute_file_path}
> public.{table} | psql -h {host} -p {port} -d {database} -U {user}
>
> The table was created successfully. I wanted to confirm that the
> actual file is being read from the location in the filesystem by
> performing the following steps:
> 1) I moved the raster file to a different location.
> 2) I opened QGIS and attempted to load the raster from PostGIS table.
>
> I was surprised that QGIS could load the file. *How is this possible
> when the actual raster data is not stored in the database table?*
>
>
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>

J. Gustavo
--
Jorge Gustavo Rocha
Departamento de Inform tica
Universidade do Minho
4710-057 Braga
Tel: +351 253604480
Fax: +351 253604471
M vel: +351 910333888
skype: nabocudnosor
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users



_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fwd: PostGIS Out-DB Raster Not Behaving As Expected

Tumasgiu Rossini
Hi,

I think I found the probelm, it is the -s option which make the sql
applying a st_transfrom on the raster to reproject it into the desired CRS
before inserting it in the table, thus creating an in-db raster.

2017-04-11 12:14 GMT+02:00 Osahon Oduware <[hidden email]>:
Hi Regina,

I would like to add that the SHOW command below returns "on": 

SHOW postgis.enable_outdb_rasters;
which I believe indicates that the environment variable is SET to enable out-db raster. However, the result from your 1st query shows that the raster table isn't created with out-db. Any reason for this behaviour?


---------- Forwarded message ----------
From: Osahon Oduware <[hidden email]>
Date: Tue, Apr 11, 2017 at 10:07 AM
Subject: Re: [postgis-users] PostGIS Out-DB Raster Not Behaving As Expected
To: PostGIS Users Discussion <[hidden email]>
Cc: Regina Obe <[hidden email]>, Jorge Gustavo Rocha <[hidden email]>


Hi Regina,

I have tried the queries above. For the related raster table,

SELECT r_table_name, r_raster_column, out_db FROM raster_columns; RETURNS
"raster_table","rast","[False, False, False]"

SELECT t.rid, (md).isoutdb, (md).path
FROM nj_ned AS t, ST_BandMetaData(t.rast) AS md
limit 1; RETURNS
1,False,""

I believe from the result of the 1st query that out-db is not enabled. However, the environment variable POSTGIS_ENABLE_OUTDB_RASTERS is SET to 1. I have tried to also enable it with the following, restarted the postgres Service, and re-loaded the raster, but the 1st query gives the same result:

ALTER DATABASE <database_name> SET postgis.enable_outdb_rasters TO True;


Please, could you help me further on what I am not doing right to enable the out-db rasters.




On Mon, Apr 10, 2017 at 8:31 PM, Regina Obe <[hidden email]> wrote:
You should also check the following queries

SELECT r_table_name, r_raster_column, out_db FROM raster_columns;

Out_db should be true for all your bands for the specific table and as Jorge mentioned, you shouldn't see any over view tables (which start with o_..{table}.
I believe QGIS internally tries to use over view tables to speed things up, so it's possible it's using an overview table instead of the main outdb table if you are zoomed out enough.

For outdb, querying the meta data of the raster will tell you the path if it's an outdb.  Replace nj_ned with your table name.

SELECT t.rid, (md).isoutdb, (md).path
FROM nj_ned AS t, ST_BandMetaData(t.rast) AS md
limit 1;

Hope that helps,
Regina
http://postgis.us
http://postgis.net


-----Original Message-----
From: postgis-users [mailto:[hidden email]] On Behalf Of Jorge Gustavo Rocha
Sent: Monday, April 10, 2017 2:14 PM
To: [hidden email]
Subject: Re: [postgis-users] PostGIS Out-DB Raster Not Behaving As Expected

Hi,

According to the documentation [1], if you add the option -l (like -l
2,4,8,16 ), the overviews are created in the database, even if you use the -R option.

But since you did not use the -l option, I don't know how you can see the images.

Can you check the layer properties in QGIS? Are any pyramids available or not?

Regards,

Jorge


 s 16:52 de 10-04-2017, Osahon Oduware escreveu:
> Hi All,
>
> I created an out-db raster using the following syntax:
>
> raster2pgsql -s {srid} -c -R -I -C -F -t auto {absolute_file_path}
> public.{table} | psql -h {host} -p {port} -d {database} -U {user}
>
> The table was created successfully. I wanted to confirm that the
> actual file is being read from the location in the filesystem by
> performing the following steps:
> 1) I moved the raster file to a different location.
> 2) I opened QGIS and attempted to load the raster from PostGIS table.
>
> I was surprised that QGIS could load the file. *How is this possible
> when the actual raster data is not stored in the database table?*
>
>
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>

J. Gustavo
--
Jorge Gustavo Rocha
Departamento de Inform tica
Universidade do Minho
4710-057 Braga
Tel: <a href="tel:+351%20253%20604%20480" value="+351253604480" target="_blank">+351 253604480
Fax: <a href="tel:+351%20253%20604%20471" value="+351253604471" target="_blank">+351 253604471
M vel: <a href="tel:+351%20910%20333%20888" value="+351910333888" target="_blank">+351 910333888
skype: nabocudnosor
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users



_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fwd: PostGIS Out-DB Raster Not Behaving As Expected

Tumasgiu Rossini
Maybe a warning showing up when using -s and -R options together could be a nice thing to do ?
Or maybe implementing another option which would use st_setsrid instead of st_transform.


2017-04-11 14:25 GMT+02:00 Tumasgiu Rossini <[hidden email]>:
Hi,

I think I found the probelm, it is the -s option which make the sql
applying a st_transfrom on the raster to reproject it into the desired CRS
before inserting it in the table, thus creating an in-db raster.

2017-04-11 12:14 GMT+02:00 Osahon Oduware <[hidden email]>:
Hi Regina,

I would like to add that the SHOW command below returns "on": 

SHOW postgis.enable_outdb_rasters;
which I believe indicates that the environment variable is SET to enable out-db raster. However, the result from your 1st query shows that the raster table isn't created with out-db. Any reason for this behaviour?


---------- Forwarded message ----------
From: Osahon Oduware <[hidden email]>
Date: Tue, Apr 11, 2017 at 10:07 AM
Subject: Re: [postgis-users] PostGIS Out-DB Raster Not Behaving As Expected
To: PostGIS Users Discussion <[hidden email]>
Cc: Regina Obe <[hidden email]>, Jorge Gustavo Rocha <[hidden email]>


Hi Regina,

I have tried the queries above. For the related raster table,

SELECT r_table_name, r_raster_column, out_db FROM raster_columns; RETURNS
"raster_table","rast","[False, False, False]"

SELECT t.rid, (md).isoutdb, (md).path
FROM nj_ned AS t, ST_BandMetaData(t.rast) AS md
limit 1; RETURNS
1,False,""

I believe from the result of the 1st query that out-db is not enabled. However, the environment variable POSTGIS_ENABLE_OUTDB_RASTERS is SET to 1. I have tried to also enable it with the following, restarted the postgres Service, and re-loaded the raster, but the 1st query gives the same result:

ALTER DATABASE <database_name> SET postgis.enable_outdb_rasters TO True;


Please, could you help me further on what I am not doing right to enable the out-db rasters.




On Mon, Apr 10, 2017 at 8:31 PM, Regina Obe <[hidden email]> wrote:
You should also check the following queries

SELECT r_table_name, r_raster_column, out_db FROM raster_columns;

Out_db should be true for all your bands for the specific table and as Jorge mentioned, you shouldn't see any over view tables (which start with o_..{table}.
I believe QGIS internally tries to use over view tables to speed things up, so it's possible it's using an overview table instead of the main outdb table if you are zoomed out enough.

For outdb, querying the meta data of the raster will tell you the path if it's an outdb.  Replace nj_ned with your table name.

SELECT t.rid, (md).isoutdb, (md).path
FROM nj_ned AS t, ST_BandMetaData(t.rast) AS md
limit 1;

Hope that helps,
Regina
http://postgis.us
http://postgis.net


-----Original Message-----
From: postgis-users [mailto:[hidden email]] On Behalf Of Jorge Gustavo Rocha
Sent: Monday, April 10, 2017 2:14 PM
To: [hidden email]
Subject: Re: [postgis-users] PostGIS Out-DB Raster Not Behaving As Expected

Hi,

According to the documentation [1], if you add the option -l (like -l
2,4,8,16 ), the overviews are created in the database, even if you use the -R option.

But since you did not use the -l option, I don't know how you can see the images.

Can you check the layer properties in QGIS? Are any pyramids available or not?

Regards,

Jorge


 s 16:52 de 10-04-2017, Osahon Oduware escreveu:
> Hi All,
>
> I created an out-db raster using the following syntax:
>
> raster2pgsql -s {srid} -c -R -I -C -F -t auto {absolute_file_path}
> public.{table} | psql -h {host} -p {port} -d {database} -U {user}
>
> The table was created successfully. I wanted to confirm that the
> actual file is being read from the location in the filesystem by
> performing the following steps:
> 1) I moved the raster file to a different location.
> 2) I opened QGIS and attempted to load the raster from PostGIS table.
>
> I was surprised that QGIS could load the file. *How is this possible
> when the actual raster data is not stored in the database table?*
>
>
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>

J. Gustavo
--
Jorge Gustavo Rocha
Departamento de Inform tica
Universidade do Minho
4710-057 Braga
Tel: <a href="tel:+351%20253%20604%20480" value="+351253604480" target="_blank">+351 253604480
Fax: <a href="tel:+351%20253%20604%20471" value="+351253604471" target="_blank">+351 253604471
M vel: <a href="tel:+351%20910%20333%20888" value="+351910333888" target="_blank">+351 910333888
skype: nabocudnosor
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users



_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users



_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fwd: PostGIS Out-DB Raster Not Behaving As Expected

Osahon Oduware
Hi Tumasgiu,

Thanks a lot, that solved the problem. You made my day.

On Tue, Apr 11, 2017 at 1:31 PM, Tumasgiu Rossini <[hidden email]> wrote:
Maybe a warning showing up when using -s and -R options together could be a nice thing to do ?
Or maybe implementing another option which would use st_setsrid instead of st_transform.


2017-04-11 14:25 GMT+02:00 Tumasgiu Rossini <[hidden email]>:
Hi,

I think I found the probelm, it is the -s option which make the sql
applying a st_transfrom on the raster to reproject it into the desired CRS
before inserting it in the table, thus creating an in-db raster.

2017-04-11 12:14 GMT+02:00 Osahon Oduware <[hidden email]>:
Hi Regina,

I would like to add that the SHOW command below returns "on": 

SHOW postgis.enable_outdb_rasters;
which I believe indicates that the environment variable is SET to enable out-db raster. However, the result from your 1st query shows that the raster table isn't created with out-db. Any reason for this behaviour?


---------- Forwarded message ----------
From: Osahon Oduware <[hidden email]>
Date: Tue, Apr 11, 2017 at 10:07 AM
Subject: Re: [postgis-users] PostGIS Out-DB Raster Not Behaving As Expected
To: PostGIS Users Discussion <[hidden email]>
Cc: Regina Obe <[hidden email]>, Jorge Gustavo Rocha <[hidden email]>


Hi Regina,

I have tried the queries above. For the related raster table,

SELECT r_table_name, r_raster_column, out_db FROM raster_columns; RETURNS
"raster_table","rast","[False, False, False]"

SELECT t.rid, (md).isoutdb, (md).path
FROM nj_ned AS t, ST_BandMetaData(t.rast) AS md
limit 1; RETURNS
1,False,""

I believe from the result of the 1st query that out-db is not enabled. However, the environment variable POSTGIS_ENABLE_OUTDB_RASTERS is SET to 1. I have tried to also enable it with the following, restarted the postgres Service, and re-loaded the raster, but the 1st query gives the same result:

ALTER DATABASE <database_name> SET postgis.enable_outdb_rasters TO True;


Please, could you help me further on what I am not doing right to enable the out-db rasters.




On Mon, Apr 10, 2017 at 8:31 PM, Regina Obe <[hidden email]> wrote:
You should also check the following queries

SELECT r_table_name, r_raster_column, out_db FROM raster_columns;

Out_db should be true for all your bands for the specific table and as Jorge mentioned, you shouldn't see any over view tables (which start with o_..{table}.
I believe QGIS internally tries to use over view tables to speed things up, so it's possible it's using an overview table instead of the main outdb table if you are zoomed out enough.

For outdb, querying the meta data of the raster will tell you the path if it's an outdb.  Replace nj_ned with your table name.

SELECT t.rid, (md).isoutdb, (md).path
FROM nj_ned AS t, ST_BandMetaData(t.rast) AS md
limit 1;

Hope that helps,
Regina
http://postgis.us
http://postgis.net


-----Original Message-----
From: postgis-users [mailto:[hidden email]] On Behalf Of Jorge Gustavo Rocha
Sent: Monday, April 10, 2017 2:14 PM
To: [hidden email]
Subject: Re: [postgis-users] PostGIS Out-DB Raster Not Behaving As Expected

Hi,

According to the documentation [1], if you add the option -l (like -l
2,4,8,16 ), the overviews are created in the database, even if you use the -R option.

But since you did not use the -l option, I don't know how you can see the images.

Can you check the layer properties in QGIS? Are any pyramids available or not?

Regards,

Jorge


 s 16:52 de 10-04-2017, Osahon Oduware escreveu:
> Hi All,
>
> I created an out-db raster using the following syntax:
>
> raster2pgsql -s {srid} -c -R -I -C -F -t auto {absolute_file_path}
> public.{table} | psql -h {host} -p {port} -d {database} -U {user}
>
> The table was created successfully. I wanted to confirm that the
> actual file is being read from the location in the filesystem by
> performing the following steps:
> 1) I moved the raster file to a different location.
> 2) I opened QGIS and attempted to load the raster from PostGIS table.
>
> I was surprised that QGIS could load the file. *How is this possible
> when the actual raster data is not stored in the database table?*
>
>
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>

J. Gustavo
--
Jorge Gustavo Rocha
Departamento de Inform tica
Universidade do Minho
4710-057 Braga
Tel: <a href="tel:+351%20253%20604%20480" value="+351253604480" target="_blank">+351 253604480
Fax: <a href="tel:+351%20253%20604%20471" value="+351253604471" target="_blank">+351 253604471
M vel: <a href="tel:+351%20910%20333%20888" value="+351910333888" target="_blank">+351 910333888
skype: nabocudnosor
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users



_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users



_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fwd: PostGIS Out-DB Raster Not Behaving As Expected

Osahon Oduware
In reply to this post by Tumasgiu Rossini
Hi Tumasgiu,

I agree with you regarding the warning. How do I then set the SRID for the raster so that it is part of the metadata information?

On Tue, Apr 11, 2017 at 1:31 PM, Tumasgiu Rossini <[hidden email]> wrote:
Maybe a warning showing up when using -s and -R options together could be a nice thing to do ?
Or maybe implementing another option which would use st_setsrid instead of st_transform.


2017-04-11 14:25 GMT+02:00 Tumasgiu Rossini <[hidden email]>:
Hi,

I think I found the probelm, it is the -s option which make the sql
applying a st_transfrom on the raster to reproject it into the desired CRS
before inserting it in the table, thus creating an in-db raster.

2017-04-11 12:14 GMT+02:00 Osahon Oduware <[hidden email]>:
Hi Regina,

I would like to add that the SHOW command below returns "on": 

SHOW postgis.enable_outdb_rasters;
which I believe indicates that the environment variable is SET to enable out-db raster. However, the result from your 1st query shows that the raster table isn't created with out-db. Any reason for this behaviour?


---------- Forwarded message ----------
From: Osahon Oduware <[hidden email]>
Date: Tue, Apr 11, 2017 at 10:07 AM
Subject: Re: [postgis-users] PostGIS Out-DB Raster Not Behaving As Expected
To: PostGIS Users Discussion <[hidden email]>
Cc: Regina Obe <[hidden email]>, Jorge Gustavo Rocha <[hidden email]>


Hi Regina,

I have tried the queries above. For the related raster table,

SELECT r_table_name, r_raster_column, out_db FROM raster_columns; RETURNS
"raster_table","rast","[False, False, False]"

SELECT t.rid, (md).isoutdb, (md).path
FROM nj_ned AS t, ST_BandMetaData(t.rast) AS md
limit 1; RETURNS
1,False,""

I believe from the result of the 1st query that out-db is not enabled. However, the environment variable POSTGIS_ENABLE_OUTDB_RASTERS is SET to 1. I have tried to also enable it with the following, restarted the postgres Service, and re-loaded the raster, but the 1st query gives the same result:

ALTER DATABASE <database_name> SET postgis.enable_outdb_rasters TO True;


Please, could you help me further on what I am not doing right to enable the out-db rasters.




On Mon, Apr 10, 2017 at 8:31 PM, Regina Obe <[hidden email]> wrote:
You should also check the following queries

SELECT r_table_name, r_raster_column, out_db FROM raster_columns;

Out_db should be true for all your bands for the specific table and as Jorge mentioned, you shouldn't see any over view tables (which start with o_..{table}.
I believe QGIS internally tries to use over view tables to speed things up, so it's possible it's using an overview table instead of the main outdb table if you are zoomed out enough.

For outdb, querying the meta data of the raster will tell you the path if it's an outdb.  Replace nj_ned with your table name.

SELECT t.rid, (md).isoutdb, (md).path
FROM nj_ned AS t, ST_BandMetaData(t.rast) AS md
limit 1;

Hope that helps,
Regina
http://postgis.us
http://postgis.net


-----Original Message-----
From: postgis-users [mailto:[hidden email]] On Behalf Of Jorge Gustavo Rocha
Sent: Monday, April 10, 2017 2:14 PM
To: [hidden email]
Subject: Re: [postgis-users] PostGIS Out-DB Raster Not Behaving As Expected

Hi,

According to the documentation [1], if you add the option -l (like -l
2,4,8,16 ), the overviews are created in the database, even if you use the -R option.

But since you did not use the -l option, I don't know how you can see the images.

Can you check the layer properties in QGIS? Are any pyramids available or not?

Regards,

Jorge


 s 16:52 de 10-04-2017, Osahon Oduware escreveu:
> Hi All,
>
> I created an out-db raster using the following syntax:
>
> raster2pgsql -s {srid} -c -R -I -C -F -t auto {absolute_file_path}
> public.{table} | psql -h {host} -p {port} -d {database} -U {user}
>
> The table was created successfully. I wanted to confirm that the
> actual file is being read from the location in the filesystem by
> performing the following steps:
> 1) I moved the raster file to a different location.
> 2) I opened QGIS and attempted to load the raster from PostGIS table.
>
> I was surprised that QGIS could load the file. *How is this possible
> when the actual raster data is not stored in the database table?*
>
>
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>

J. Gustavo
--
Jorge Gustavo Rocha
Departamento de Inform tica
Universidade do Minho
4710-057 Braga
Tel: <a href="tel:+351%20253%20604%20480" value="+351253604480" target="_blank">+351 253604480
Fax: <a href="tel:+351%20253%20604%20471" value="+351253604471" target="_blank">+351 253604471
M vel: <a href="tel:+351%20910%20333%20888" value="+351910333888" target="_blank">+351 910333888
skype: nabocudnosor
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users



_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users



_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fwd: PostGIS Out-DB Raster Not Behaving As Expected

Osahon Oduware
Hi Tumasgiu,

Thanks a lot. You have been of great help.

On Tue, Apr 11, 2017 at 2:13 PM, Tumasgiu Rossini <[hidden email]> wrote:
Hi, I think that
     UPDATE table SET rast = ST_SetSRID(rast, mysrid) ;
would do.

However, doing so will leads to potentially inconsistent metadata between your
pg raster representation and the raster itself.

Also, when using st_srid on an out-db raster, the following notice shows up :

>NOTICE:  Changes made to raster geotransform matrix may affect out-db band data. Returned band data may be incorrect

I find this message relatively loose as it doesn't tell you how it may affect the out-db band data,
but I think that the conclusion is very clear !

So I think that it would be better if you could specify the SRID of the raster itself prior to insert it in Postgis.

Regarding the raster2pgsql tool, I think that the description of the -s option is incorrect :
the option doesn't set the srid of the raster as it is specified, it actually reprojects it !



2017-04-11 14:48 GMT+02:00 Osahon Oduware <[hidden email]>:
Hi Tumasgiu,

I agree with you regarding the warning. How do I then set the SRID for the raster so that it is part of the metadata information?

On Tue, Apr 11, 2017 at 1:31 PM, Tumasgiu Rossini <[hidden email]> wrote:
Maybe a warning showing up when using -s and -R options together could be a nice thing to do ?
Or maybe implementing another option which would use st_setsrid instead of st_transform.


2017-04-11 14:25 GMT+02:00 Tumasgiu Rossini <[hidden email]>:
Hi,

I think I found the probelm, it is the -s option which make the sql
applying a st_transfrom on the raster to reproject it into the desired CRS
before inserting it in the table, thus creating an in-db raster.

2017-04-11 12:14 GMT+02:00 Osahon Oduware <[hidden email]>:
Hi Regina,

I would like to add that the SHOW command below returns "on": 

SHOW postgis.enable_outdb_rasters;
which I believe indicates that the environment variable is SET to enable out-db raster. However, the result from your 1st query shows that the raster table isn't created with out-db. Any reason for this behaviour?


---------- Forwarded message ----------
From: Osahon Oduware <[hidden email]>
Date: Tue, Apr 11, 2017 at 10:07 AM
Subject: Re: [postgis-users] PostGIS Out-DB Raster Not Behaving As Expected
To: PostGIS Users Discussion <[hidden email]>
Cc: Regina Obe <[hidden email]>, Jorge Gustavo Rocha <[hidden email]>


Hi Regina,

I have tried the queries above. For the related raster table,

SELECT r_table_name, r_raster_column, out_db FROM raster_columns; RETURNS
"raster_table","rast","[False, False, False]"

SELECT t.rid, (md).isoutdb, (md).path
FROM nj_ned AS t, ST_BandMetaData(t.rast) AS md
limit 1; RETURNS
1,False,""

I believe from the result of the 1st query that out-db is not enabled. However, the environment variable POSTGIS_ENABLE_OUTDB_RASTERS is SET to 1. I have tried to also enable it with the following, restarted the postgres Service, and re-loaded the raster, but the 1st query gives the same result:

ALTER DATABASE <database_name> SET postgis.enable_outdb_rasters TO True;


Please, could you help me further on what I am not doing right to enable the out-db rasters.




On Mon, Apr 10, 2017 at 8:31 PM, Regina Obe <[hidden email]> wrote:
You should also check the following queries

SELECT r_table_name, r_raster_column, out_db FROM raster_columns;

Out_db should be true for all your bands for the specific table and as Jorge mentioned, you shouldn't see any over view tables (which start with o_..{table}.
I believe QGIS internally tries to use over view tables to speed things up, so it's possible it's using an overview table instead of the main outdb table if you are zoomed out enough.

For outdb, querying the meta data of the raster will tell you the path if it's an outdb.  Replace nj_ned with your table name.

SELECT t.rid, (md).isoutdb, (md).path
FROM nj_ned AS t, ST_BandMetaData(t.rast) AS md
limit 1;

Hope that helps,
Regina
http://postgis.us
http://postgis.net


-----Original Message-----
From: postgis-users [mailto:[hidden email]] On Behalf Of Jorge Gustavo Rocha
Sent: Monday, April 10, 2017 2:14 PM
To: [hidden email]
Subject: Re: [postgis-users] PostGIS Out-DB Raster Not Behaving As Expected

Hi,

According to the documentation [1], if you add the option -l (like -l
2,4,8,16 ), the overviews are created in the database, even if you use the -R option.

But since you did not use the -l option, I don't know how you can see the images.

Can you check the layer properties in QGIS? Are any pyramids available or not?

Regards,

Jorge


 s 16:52 de 10-04-2017, Osahon Oduware escreveu:
> Hi All,
>
> I created an out-db raster using the following syntax:
>
> raster2pgsql -s {srid} -c -R -I -C -F -t auto {absolute_file_path}
> public.{table} | psql -h {host} -p {port} -d {database} -U {user}
>
> The table was created successfully. I wanted to confirm that the
> actual file is being read from the location in the filesystem by
> performing the following steps:
> 1) I moved the raster file to a different location.
> 2) I opened QGIS and attempted to load the raster from PostGIS table.
>
> I was surprised that QGIS could load the file. *How is this possible
> when the actual raster data is not stored in the database table?*
>
>
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>

J. Gustavo
--
Jorge Gustavo Rocha
Departamento de Inform tica
Universidade do Minho
4710-057 Braga
Tel: <a href="tel:+351%20253%20604%20480" value="+351253604480" target="_blank">+351 253604480
Fax: <a href="tel:+351%20253%20604%20471" value="+351253604471" target="_blank">+351 253604471
M vel: <a href="tel:+351%20910%20333%20888" value="+351910333888" target="_blank">+351 910333888
skype: nabocudnosor
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users



_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users



_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users




_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fwd: PostGIS Out-DB Raster Not Behaving As Expected

Regina Obe-2
In reply to this post by Tumasgiu Rossini

Thanks for bringing this to our attention.

 

I've ticketed it - https://trac.osgeo.org/postgis/ticket/3738

 

Pierre and Bborie,

 

Were you aware of this behavior?  This is the first time I'm hearing of it.

Though that would explain one complaint I heard from someone that their raster2pgsql outdb took 10 times longer than in-db

When for me it's always been really fast.

 

I would consider this a bug that –s could be used for both setting the SRID and also transforming data and would like the behavior changed in 2.4.

Probably too late to change for 2.3 since it's an api change, but we can put a warning on at least 2.3 as Tumasgiu suggested.

 

Thanks,

Regina

http://postgis.us

http://postgis.net

 

 

 

 

 

From: postgis-users [mailto:[hidden email]] On Behalf Of Tumasgiu Rossini
Sent: Tuesday, April 11, 2017 8:32 AM
To: PostGIS Users Discussion <[hidden email]>
Subject: Re: [postgis-users] Fwd: PostGIS Out-DB Raster Not Behaving As Expected

 

Maybe a warning showing up when using -s and -R options together could be a nice thing to do ?

Or maybe implementing another option which would use st_setsrid instead of st_transform.

 

2017-04-11 14:25 GMT+02:00 Tumasgiu Rossini <[hidden email]>:

Hi,

I think I found the probelm, it is the -s option which make the sql

applying a st_transfrom on the raster to reproject it into the desired CRS

before inserting it in the table, thus creating an in-db raster.

 

2017-04-11 12:14 GMT+02:00 Osahon Oduware <[hidden email]>:

Hi Regina,

 

I would like to add that the SHOW command below returns "on": 

 

SHOW postgis.enable_outdb_rasters;

which I believe indicates that the environment variable is SET to enable out-db raster. However, the result from your 1st query shows that the raster table isn't created with out-db. Any reason for this behaviour?

 

 

---------- Forwarded message ----------
From: Osahon Oduware <[hidden email]>
Date: Tue, Apr 11, 2017 at 10:07 AM
Subject: Re: [postgis-users] PostGIS Out-DB Raster Not Behaving As Expected

To: PostGIS Users Discussion <[hidden email]>
Cc: Regina Obe <[hidden email]>, Jorge Gustavo Rocha <[hidden email]>

Hi Regina,

 

I have tried the queries above. For the related raster table,

 

SELECT r_table_name, r_raster_column, out_db FROM raster_columns; RETURNS

"raster_table","rast","[False, False, False]"

 

SELECT t.rid, (md).isoutdb, (md).path
FROM nj_ned AS t, ST_BandMetaData(t.rast) AS md
limit 1; RETURNS

1,False,""

 

I believe from the result of the 1st query that out-db is not enabled. However, the environment variable POSTGIS_ENABLE_OUTDB_RASTERS is SET to 1. I have tried to also enable it with the following, restarted the postgres Service, and re-loaded the raster, but the 1st query gives the same result:

 

ALTER DATABASE <database_name> SET postgis.enable_outdb_rasters TO True;

 

 

Please, could you help me further on what I am not doing right to enable the out-db rasters.

 

 

 

 

On Mon, Apr 10, 2017 at 8:31 PM, Regina Obe <[hidden email]> wrote:

You should also check the following queries

SELECT r_table_name, r_raster_column, out_db FROM raster_columns;

Out_db should be true for all your bands for the specific table and as Jorge mentioned, you shouldn't see any over view tables (which start with o_..{table}.
I believe QGIS internally tries to use over view tables to speed things up, so it's possible it's using an overview table instead of the main outdb table if you are zoomed out enough.

For outdb, querying the meta data of the raster will tell you the path if it's an outdb.  Replace nj_ned with your table name.

SELECT t.rid, (md).isoutdb, (md).path
FROM nj_ned AS t, ST_BandMetaData(t.rast) AS md
limit 1;

Hope that helps,
Regina
http://postgis.us
http://postgis.net


-----Original Message-----
From: postgis-users [mailto:[hidden email]] On Behalf Of Jorge Gustavo Rocha
Sent: Monday, April 10, 2017 2:14 PM
To: [hidden email]
Subject: Re: [postgis-users] PostGIS Out-DB Raster Not Behaving As Expected

Hi,

According to the documentation [1], if you add the option -l (like -l
2,4,8,16 ), the overviews are created in the database, even if you use the -R option.

But since you did not use the -l option, I don't know how you can see the images.

Can you check the layer properties in QGIS? Are any pyramids available or not?

Regards,

Jorge


 s 16:52 de 10-04-2017, Osahon Oduware escreveu:


> Hi All,
>
> I created an out-db raster using the following syntax:
>
> raster2pgsql -s {srid} -c -R -I -C -F -t auto {absolute_file_path}
> public.{table} | psql -h {host} -p {port} -d {database} -U {user}
>
> The table was created successfully. I wanted to confirm that the
> actual file is being read from the location in the filesystem by
> performing the following steps:
> 1) I moved the raster file to a different location.
> 2) I opened QGIS and attempted to load the raster from PostGIS table.
>
> I was surprised that QGIS could load the file. *How is this possible
> when the actual raster data is not stored in the database table?*
>
>
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>

J. Gustavo
--
Jorge Gustavo Rocha
Departamento de Inform tica
Universidade do Minho
4710-057 Braga
Tel: <a href="tel:+351%20253%20604%20480" target="_blank">+351 253604480
Fax: <a href="tel:+351%20253%20604%20471" target="_blank">+351 253604471
M vel: <a href="tel:+351%20910%20333%20888" target="_blank">+351 910333888
skype: nabocudnosor
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

 

 


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

 

 


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fwd: PostGIS Out-DB Raster Not Behaving As Expected

Osahon Oduware
Hi Regina,

Thanks a lot for the information. I look forward to 2.4.

On Apr 16, 2017 5:06 AM, "Regina Obe" <[hidden email]> wrote:

Thanks for bringing this to our attention.

 

I've ticketed it - https://trac.osgeo.org/postgis/ticket/3738

 

Pierre and Bborie,

 

Were you aware of this behavior?  This is the first time I'm hearing of it.

Though that would explain one complaint I heard from someone that their raster2pgsql outdb took 10 times longer than in-db

When for me it's always been really fast.

 

I would consider this a bug that –s could be used for both setting the SRID and also transforming data and would like the behavior changed in 2.4.

Probably too late to change for 2.3 since it's an api change, but we can put a warning on at least 2.3 as Tumasgiu suggested.

 

Thanks,

From: postgis-users [mailto:[hidden email]] On Behalf Of Tumasgiu Rossini
Sent: Tuesday, April 11, 2017 8:32 AM


To: PostGIS Users Discussion <[hidden email]>
Subject: Re: [postgis-users] Fwd: PostGIS Out-DB Raster Not Behaving As Expected

 

Maybe a warning showing up when using -s and -R options together could be a nice thing to do ?

Or maybe implementing another option which would use st_setsrid instead of st_transform.

 

2017-04-11 14:25 GMT+02:00 Tumasgiu Rossini <[hidden email]>:

Hi,

I think I found the probelm, it is the -s option which make the sql

applying a st_transfrom on the raster to reproject it into the desired CRS

before inserting it in the table, thus creating an in-db raster.

 

2017-04-11 12:14 GMT+02:00 Osahon Oduware <[hidden email]>:

Hi Regina,

 

I would like to add that the SHOW command below returns "on": 

 

SHOW postgis.enable_outdb_rasters;

which I believe indicates that the environment variable is SET to enable out-db raster. However, the result from your 1st query shows that the raster table isn't created with out-db. Any reason for this behaviour?

 

 

---------- Forwarded message ----------
From: Osahon Oduware <[hidden email]>
Date: Tue, Apr 11, 2017 at 10:07 AM
Subject: Re: [postgis-users] PostGIS Out-DB Raster Not Behaving As Expected

To: PostGIS Users Discussion <[hidden email]>
Cc: Regina Obe <[hidden email]>, Jorge Gustavo Rocha <[hidden email]>

Hi Regina,

 

I have tried the queries above. For the related raster table,

 

SELECT r_table_name, r_raster_column, out_db FROM raster_columns; RETURNS

"raster_table","rast","[False, False, False]"

 

SELECT t.rid, (md).isoutdb, (md).path
FROM nj_ned AS t, ST_BandMetaData(t.rast) AS md
limit 1; RETURNS

1,False,""

 

I believe from the result of the 1st query that out-db is not enabled. However, the environment variable POSTGIS_ENABLE_OUTDB_RASTERS is SET to 1. I have tried to also enable it with the following, restarted the postgres Service, and re-loaded the raster, but the 1st query gives the same result:

 

ALTER DATABASE <database_name> SET postgis.enable_outdb_rasters TO True;

 

 

Please, could you help me further on what I am not doing right to enable the out-db rasters.

 

 

 

 

On Mon, Apr 10, 2017 at 8:31 PM, Regina Obe <[hidden email]> wrote:

You should also check the following queries

SELECT r_table_name, r_raster_column, out_db FROM raster_columns;

Out_db should be true for all your bands for the specific table and as Jorge mentioned, you shouldn't see any over view tables (which start with o_..{table}.
I believe QGIS internally tries to use over view tables to speed things up, so it's possible it's using an overview table instead of the main outdb table if you are zoomed out enough.

For outdb, querying the meta data of the raster will tell you the path if it's an outdb.  Replace nj_ned with your table name.

SELECT t.rid, (md).isoutdb, (md).path
FROM nj_ned AS t, ST_BandMetaData(t.rast) AS md
limit 1;

Hope that helps,
Regina
http://postgis.us
http://postgis.net


-----Original Message-----
From: postgis-users [mailto:[hidden email]] On Behalf Of Jorge Gustavo Rocha
Sent: Monday, April 10, 2017 2:14 PM
To: [hidden email]
Subject: Re: [postgis-users] PostGIS Out-DB Raster Not Behaving As Expected

Hi,

According to the documentation [1], if you add the option -l (like -l
2,4,8,16 ), the overviews are created in the database, even if you use the -R option.

But since you did not use the -l option, I don't know how you can see the images.

Can you check the layer properties in QGIS? Are any pyramids available or not?

Regards,

Jorge


 s 16:52 de 10-04-2017, Osahon Oduware escreveu:


> Hi All,
>
> I created an out-db raster using the following syntax:
>
> raster2pgsql -s {srid} -c -R -I -C -F -t auto {absolute_file_path}
> public.{table} | psql -h {host} -p {port} -d {database} -U {user}
>
> The table was created successfully. I wanted to confirm that the
> actual file is being read from the location in the filesystem by
> performing the following steps:
> 1) I moved the raster file to a different location.
> 2) I opened QGIS and attempted to load the raster from PostGIS table.
>
> I was surprised that QGIS could load the file. *How is this possible
> when the actual raster data is not stored in the database table?*
>
>
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>

J. Gustavo
--
Jorge Gustavo Rocha
Departamento de Inform tica
Universidade do Minho
4710-057 Braga
Tel: <a href="tel:+351%20253%20604%20480" target="_blank">+351 253604480
Fax: <a href="tel:+351%20253%20604%20471" target="_blank">+351 253604471
M vel: <a href="tel:+351%20910%20333%20888" target="_blank">+351 910333888
skype: nabocudnosor
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

 

 


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

 

 


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fwd: PostGIS Out-DB Raster Not Behaving As Expected

Pierre Racine-2
In reply to this post by Regina Obe-2

I guess st_transform should be applied only when using the same syntax as shp2pgsql : -s from_srid:to_srid.

 

And it should be prohibited when used with –R. I don’t think we want to go into reprojecting the tiles in the filesystem. The only good way to do this is convert them to in-db and it is not what the users is expecting. So better prevent it. If he really want to reproject, he can do it after registering the out-db tiles, creating new in-db ones with SELECT ST_Transform(). At least at this moment it is clearer that the band will become in-db.

 

Make sence?

 

Pierre

 

De : Regina Obe [mailto:[hidden email]]
Envoyé : 16 avril 2017 00:06
À : 'PostGIS Users Discussion' <postgis-use
[hidden email]>
Cc : Pierre Racine <[hidden email]>; 'Bborie Park' <[hidden email]>
Objet : RE: [postgis-users] Fwd: PostGIS Out-DB Raster Not Behaving As Expected

 

Thanks for bringing this to our attention.

 

I've ticketed it - https://trac.osgeo.org/postgis/ticket/3738

 

Pierre and Bborie,

 

Were you aware of this behavior?  This is the first time I'm hearing of it.

Though that would explain one complaint I heard from someone that their raster2pgsql outdb took 10 times longer than in-db

When for me it's always been really fast.

 

I would consider this a bug that –s could be used for both setting the SRID and also transforming data and would like the behavior changed in 2.4.

Probably too late to change for 2.3 since it's an api change, but we can put a warning on at least 2.3 as Tumasgiu suggested.

 

Thanks,

Regina

http://postgis.us

http://postgis.net

 

 

 

 

 

From: postgis-users [[hidden email]] On Behalf Of Tumasgiu Rossini
Sent: Tuesday, April 11, 2017 8:32 AM
To: PostGIS Users Discussion <
[hidden email]>
Subject: Re: [postgis-users] Fwd: PostGIS Out-DB Raster Not Behaving As Expected

 

Maybe a warning showing up when using -s and -R options together could be a nice thing to do ?

Or maybe implementing another option which would use st_setsrid instead of st_transform.

 

2017-04-11 14:25 GMT+02:00 Tumasgiu Rossini <[hidden email]>:

Hi,

I think I found the probelm, it is the -s option which make the sql

applying a st_transfrom on the raster to reproject it into the desired CRS

before inserting it in the table, thus creating an in-db raster.

 

2017-04-11 12:14 GMT+02:00 Osahon Oduware <[hidden email]>:

Hi Regina,

 

I would like to add that the SHOW command below returns "on": 

 

SHOW postgis.enable_outdb_rasters;

which I believe indicates that the environment variable is SET to enable out-db raster. However, the result from your 1st query shows that the raster table isn't created with out-db. Any reason for this behaviour?

 

 

---------- Forwarded message ----------
From: Osahon Oduware <
[hidden email]>
Date: Tue, Apr 11, 2017 at 10:07 AM
Subject: Re: [postgis-users] PostGIS Out-DB Raster Not Behaving As Expected

To: PostGIS Users Discussion <[hidden email]>
Cc: Regina Obe <
[hidden email]>, Jorge Gustavo Rocha <[hidden email]>

Hi Regina,

 

I have tried the queries above. For the related raster table,

 

SELECT r_table_name, r_raster_column, out_db FROM raster_columns; RETURNS

"raster_table","rast","[False, False, False]"

 

SELECT t.rid, (md).isoutdb, (md).path
FROM nj_ned AS t, ST_BandMetaData(t.rast) AS md
limit 1; RETURNS

1,False,""

 

I believe from the result of the 1st query that out-db is not enabled. However, the environment variable POSTGIS_ENABLE_OUTDB_RASTERS is SET to 1. I have tried to also enable it with the following, restarted the postgres Service, and re-loaded the raster, but the 1st query gives the same result:

 

ALTER DATABASE <database_name> SET postgis.enable_outdb_rasters TO True;

 

 

Please, could you help me further on what I am not doing right to enable the out-db rasters.

 

 

 

 

On Mon, Apr 10, 2017 at 8:31 PM, Regina Obe <[hidden email]> wrote:

You should also check the following queries

SELECT r_table_name, r_raster_column, out_db FROM raster_columns;

Out_db should be true for all your bands for the specific table and as Jorge mentioned, you shouldn't see any over view tables (which start with o_..{table}.
I believe QGIS internally tries to use over view tables to speed things up, so it's possible it's using an overview table instead of the main outdb table if you are zoomed out enough.

For outdb, querying the meta data of the raster will tell you the path if it's an outdb.  Replace nj_ned with your table name.

SELECT t.rid, (md).isoutdb, (md).path
FROM nj_ned AS t, ST_BandMetaData(t.rast) AS md
limit 1;

Hope that helps,
Regina
http://postgis.us
http://postgis.net


-----Original Message-----
From: postgis-users [mailto:
[hidden email]] On Behalf Of Jorge Gustavo Rocha
Sent: Monday, April 10, 2017 2:14 PM
To:
[hidden email]
Subject: Re: [postgis-users] PostGIS Out-DB Raster Not Behaving As Expected

Hi,

According to the documentation [1], if you add the option -l (like -l
2,4,8,16 ), the overviews are created in the database, even if you use the -R option.

But since you did not use the -l option, I don't know how you can see the images.

Can you check the layer properties in QGIS? Are any pyramids available or not?

Regards,

Jorge


 s 16:52 de 10-04-2017, Osahon Oduware escreveu:
> Hi All,
>
> I created an out-db raster using the following syntax:
>
> raster2pgsql -s {srid} -c -R -I -C -F -t auto {absolute_file_path}
> public.{table} | psql -h {host} -p {port} -d {database} -U {user}
>
> The table was created successfully. I wanted to confirm that the
> actual file is being read from the location in the filesystem by
> performing the following steps:
> 1) I moved the raster file to a different location.
> 2) I opened QGIS and attempted to load the raster from PostGIS table.
>
> I was surprised that QGIS could load the file. *How is this possible
> when the actual raster data is not stored in the database table?*
>
>
> _______________________________________________
> postgis-users mailing list
>
[hidden email]
>
https://lists.osgeo.org/mailman/listinfo/postgis-users
>

J. Gustavo
--
Jorge Gustavo Rocha
Departamento de Inform tica
Universidade do Minho
4710-057 Braga
Tel:
<a href="tel:&#43;351%20253%20604%20480" target="_blank">+351 253604480
Fax:
<a href="tel:&#43;351%20253%20604%20471" target="_blank">+351 253604471
M vel:
<a href="tel:&#43;351%20910%20333%20888" target="_blank">+351 910333888
skype: nabocudnosor
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

 

 


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

 

 


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Fwd: PostGIS Out-DB Raster Not Behaving As Expected

Regina Obe-2

Yap makes sense to me.  So will change this in 2.4 and 2.3 put a warning.

 

From: Pierre Racine [mailto:[hidden email]]
Sent: Tuesday, April 18, 2017 9:15 AM
To: Regina Obe <[hidden email]>; 'PostGIS Users Discussion' <[hidden email]>
Cc: 'Bborie Park' <[hidden email]>
Subject: RE: [postgis-users] Fwd: PostGIS Out-DB Raster Not Behaving As Expected

 

I guess st_transform should be applied only when using the same syntax as shp2pgsql : -s from_srid:to_srid.

 

And it should be prohibited when used with –R. I don’t think we want to go into reprojecting the tiles in the filesystem. The only good way to do this is convert them to in-db and it is not what the users is expecting. So better prevent it. If he really want to reproject, he can do it after registering the out-db tiles, creating new in-db ones with SELECT ST_Transform(). At least at this moment it is clearer that the band will become in-db.

 

Make sence?

 

Pierre

 

De : Regina Obe [[hidden email]]
Envoyé : 16 avril 2017 00:06
À : 'PostGIS Users Discussion' <[hidden email]
>
Cc : Pierre Racine <[hidden email]>; 'Bborie Park' <[hidden email]>
Objet : RE: [postgis-users] Fwd: PostGIS Out-DB Raster Not Behaving As Expected

 

Thanks for bringing this to our attention.

 

I've ticketed it - https://trac.osgeo.org/postgis/ticket/3738

 

Pierre and Bborie,

 

Were you aware of this behavior?  This is the first time I'm hearing of it.

Though that would explain one complaint I heard from someone that their raster2pgsql outdb took 10 times longer than in-db

When for me it's always been really fast.

 

I would consider this a bug that –s could be used for both setting the SRID and also transforming data and would like the behavior changed in 2.4.

Probably too late to change for 2.3 since it's an api change, but we can put a warning on at least 2.3 as Tumasgiu suggested.

 

Thanks,

Regina

http://postgis.us

http://postgis.net

 

 

 

 

 

From: postgis-users [[hidden email]] On Behalf Of Tumasgiu Rossini
Sent: Tuesday, April 11, 2017 8:32 AM
To: PostGIS Users Discussion <
[hidden email]>
Subject: Re: [postgis-users] Fwd: PostGIS Out-DB Raster Not Behaving As Expected

 

Maybe a warning showing up when using -s and -R options together could be a nice thing to do ?

Or maybe implementing another option which would use st_setsrid instead of st_transform.

 

2017-04-11 14:25 GMT+02:00 Tumasgiu Rossini <[hidden email]>:

Hi,

I think I found the probelm, it is the -s option which make the sql

applying a st_transfrom on the raster to reproject it into the desired CRS

before inserting it in the table, thus creating an in-db raster.

 

2017-04-11 12:14 GMT+02:00 Osahon Oduware <[hidden email]>:

Hi Regina,

 

I would like to add that the SHOW command below returns "on": 

 

SHOW postgis.enable_outdb_rasters;

which I believe indicates that the environment variable is SET to enable out-db raster. However, the result from your 1st query shows that the raster table isn't created with out-db. Any reason for this behaviour?

 

 

---------- Forwarded message ----------
From: Osahon Oduware <[hidden email]>
Date: Tue, Apr 11, 2017 at 10:07 AM
Subject: Re: [postgis-users] PostGIS Out-DB Raster Not Behaving As Expected

To: PostGIS Users Discussion <[hidden email]>
Cc: Regina Obe <[hidden email]>, Jorge Gustavo Rocha <[hidden email]>

Hi Regina,

 

I have tried the queries above. For the related raster table,

 

SELECT r_table_name, r_raster_column, out_db FROM raster_columns; RETURNS

"raster_table","rast","[False, False, False]"

 

SELECT t.rid, (md).isoutdb, (md).path
FROM nj_ned AS t, ST_BandMetaData(t.rast) AS md
limit 1; RETURNS

1,False,""

 

I believe from the result of the 1st query that out-db is not enabled. However, the environment variable POSTGIS_ENABLE_OUTDB_RASTERS is SET to 1. I have tried to also enable it with the following, restarted the postgres Service, and re-loaded the raster, but the 1st query gives the same result:

 

ALTER DATABASE <database_name> SET postgis.enable_outdb_rasters TO True;

 

 

Please, could you help me further on what I am not doing right to enable the out-db rasters.

 

 

 

 

On Mon, Apr 10, 2017 at 8:31 PM, Regina Obe <[hidden email]> wrote:

You should also check the following queries

SELECT r_table_name, r_raster_column, out_db FROM raster_columns;

Out_db should be true for all your bands for the specific table and as Jorge mentioned, you shouldn't see any over view tables (which start with o_..{table}.
I believe QGIS internally tries to use over view tables to speed things up, so it's possible it's using an overview table instead of the main outdb table if you are zoomed out enough.

For outdb, querying the meta data of the raster will tell you the path if it's an outdb.  Replace nj_ned with your table name.

SELECT t.rid, (md).isoutdb, (md).path
FROM nj_ned AS t, ST_BandMetaData(t.rast) AS md
limit 1;

Hope that helps,
Regina
http://postgis.us
http://postgis.net


-----Original Message-----
From: postgis-users [mailto:[hidden email]] On Behalf Of Jorge Gustavo Rocha
Sent: Monday, April 10, 2017 2:14 PM
To: [hidden email]
Subject: Re: [postgis-users] PostGIS Out-DB Raster Not Behaving As Expected

Hi,

According to the documentation [1], if you add the option -l (like -l
2,4,8,16 ), the overviews are created in the database, even if you use the -R option.

But since you did not use the -l option, I don't know how you can see the images.

Can you check the layer properties in QGIS? Are any pyramids available or not?

Regards,

Jorge


 s 16:52 de 10-04-2017, Osahon Oduware escreveu:


> Hi All,
>
> I created an out-db raster using the following syntax:
>
> raster2pgsql -s {srid} -c -R -I -C -F -t auto {absolute_file_path}
> public.{table} | psql -h {host} -p {port} -d {database} -U {user}
>
> The table was created successfully. I wanted to confirm that the
> actual file is being read from the location in the filesystem by
> performing the following steps:
> 1) I moved the raster file to a different location.
> 2) I opened QGIS and attempted to load the raster from PostGIS table.
>
> I was surprised that QGIS could load the file. *How is this possible
> when the actual raster data is not stored in the database table?*
>
>
> _______________________________________________
> postgis-users mailing list
> [hidden email]
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>

J. Gustavo
--
Jorge Gustavo Rocha
Departamento de Inform tica
Universidade do Minho
4710-057 Braga
Tel: <a href="tel:+351%20253%20604%20480" target="_blank">+351 253604480
Fax: <a href="tel:+351%20253%20604%20471" target="_blank">+351 253604471
M vel: <a href="tel:+351%20910%20333%20888" target="_blank">+351 910333888
skype: nabocudnosor
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

 

 


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

 

 


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Loading...