[postgis-users] 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 <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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170411/318f1003/attachment.html>