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

Tue Apr 11 06:37:04 PDT 2017

Hi Tumasgiu,

Thanks a lot. You have been of great help.

On Tue, Apr 11, 2017 at 2:13 PM, Tumasgiu Rossini <rossini.t at gmail.com>
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 <osahon.gis at gmail.com>:
>
>> 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/0ffbcce5/attachment.html>