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

Tue Apr 11 05:48:36 PDT 2017

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 <rossini.t at gmail.com>
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 <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/20170411/66746b4f/attachment.html>