[postgis-users] 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 <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
> Fax: +351 253604471
> M vel: +351 910333888
> 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170411/aed2b3e7/attachment.html>