[postgis-users] Fwd: PostGIS Out-DB Raster Not Behaving As Expected

Mon Apr 17 03:04:53 PDT 2017

Hi Regina,

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

On Apr 16, 2017 5:06 AM, "Regina Obe" <lr at pcorp.us> 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,

Regina

http://postgis.us

http://postgis.net

*From:* postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] *On
Behalf Of *Tumasgiu Rossini
*Sent:* Tuesday, April 11, 2017 8:32 AM

*To:* PostGIS Users Discussion <postgis-users at lists.osgeo.org>
*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 <rossini.t at gmail.com>:

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 <osahon.gis at gmail.com>:

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* <osahon.gis at gmail.com>
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 <postgis-users at lists.osgeo.org>
Cc: Regina Obe <lr at pcorp.us>, Jorge Gustavo Rocha <jgr at di.uminho.pt>

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 <lr at pcorp.us> 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:postgis-users-bounces at lists.osgeo.org] On
Behalf Of Jorge Gustavo Rocha
Sent: Monday, April 10, 2017 2:14 PM
To: postgis-users at lists.osgeo.org
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
> postgis-users at lists.osgeo.org
> 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 <+351%20253%20604%20480>
Fax: +351 253604471 <+351%20253%20604%20471>
M vel: +351 910333888 <+351%20910%20333%20888>
skype: nabocudnosor
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170417/8fd0c40e/attachment.html>