[postgis-devel] [postgis-users] PostGIS Rasters Overview Factor

Thu May 25 02:40:27 PDT 2017

Hi Regina,

I reloaded the raster successfully using the raster2pgsql tool with the
following syntax:

raster2pgsql -s 26332 -d -Y -e -I -C -F -M -l 2,3,4,5,6,7 /path/to/*.tif -t
250x250 <table_name> | psql -h <host> -U <username> -p 5432 -d <database>

However, when I try to view the raster in QGIS, it is taking 'forever' to
load the initial full-extent zoom. I am suspecting that it has to do with
the overviews (maybe I need to create more) as I tried limiting the
overview-factors to 2,3,4,5,6,7 to avoid the scrap tiles that occurred
previously.

I would need suggestions on how I could create more overviews without
getting scrap tiles. I have provided related info below:

Number of Raster Rows: 1,040,000
Current Tile Size: 250 x 250

I would be glad if you could help me out with this.

On Thu, May 11, 2017 at 4:50 PM, Osahon Oduware <osahon.gis at gmail.com>
wrote:

> Hi Regina,
>
> Thanks a lot. You have helped my understanding a great deal with your
> explanation. I would do my calculations to avoid the scrap tiles, reload
> the rasters, and get back to you with my findings.
>
>
> On Thu, May 11, 2017 at 2:31 PM, Regina Obe <lr at pcorp.us> wrote:
>
>> Osahon,
>>
>>
>>
>> 1)      The overview factors do not have to be factors of 2.
>>
>> 2)      You get fewer rows the higher the factor because the factor
>> gives you lower resolution.
>>
>> So if your original number of rows (tiles) = 1000
>>
>>
>>
>> An overview factor of 3 = 1000/Power(2,3)  = 1000/(2*2*2) = 125 rows
>>
>>
>>
>>
>>
>> 3)      If you tiled your main raster in 250x250 then all factors have
>> tile sizes 250x250, but the higher rasters are packing more of the original
>> tiles (geometry space)  in each tile because they are lower resolution.
>>
>> So in the over view factor =2 case – each tile contains 4 tiles of the
>> original raster (but at 1/4 the resolution).
>>
>>
>>
>> 4)      You get scrap tiles because each tile must maintain the same
>> size but includes more than 1 tile from original, but must contain a whole
>> tile (not a half tile for example)
>>
>> So lets say you had not 1000 tiles but 1001 tiles, that last tile of your
>> over view 3 would have only 1 tile of the original and the remaining 7
>> slots would be filled in with no data filler
>>
>> so that the tile size of 250x250
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> *From:* Osahon Oduware [mailto:osahon.gis at gmail.com]
>> *Sent:* Thursday, May 11, 2017 6:37 AM
>> *To:* PostGIS Development Discussion <postgis-devel at lists.osgeo.org>;
>> Regina Obe <lr at pcorp.us>; Pierre Racine <pierre.racine at sbf.ulaval.ca>
>> *Subject:* Fwd: [postgis-users] PostGIS Rasters Overview Factor
>>
>>
>>
>> Could anyone help with the questions below?
>>
>>
>>
>> ---------- Forwarded message ----------
>> From: *Osahon Oduware* <osahon.gis at gmail.com>
>> Date: Thu, May 11, 2017 at 11:00 AM
>> Subject: Re: [postgis-users] PostGIS Rasters Overview Factor
>> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
>> Cc: David Haynes <haynesd2 at gmail.com>, Regina Obe <lr at pcorp.us>, Pierre
>> Racine <pierre.racine at sbf.ulaval.ca>
>>
>> Hi David,
>>
>>
>>
>> Thanks for your response. Sorry, I forgot to mention that I also
>> performed the loading using the tile flag *-t 250x250* using the syntax
>> below:
>>
>>
>>
>> raster2pgsql -s <SRID> -d -Y -e -I -C -F -M -l
>> 2,4,8,16,32,64,128,256,512,1024,2048,4096,8192,16384,32768,65536
>> /path/to/raster/file
>>
>> -t 250x250 <table_name> | psql -h <host_address> -U postgres -p 5432 -d
>> <database>
>>
>>
>>
>> I obtained the quoted information below from this link:
>>
>> http://postgis.net/docs/manual-2.2/using_raster_dataman.
>> html#RT_Raster_Overviews
>>
>>
>>
>> "overview_factor - this is the pyramid level of the overview table. The
>> higher the number the lower the resolution of the table. raster2pgsql if
>> given a folder of images, will compute overview of each image file and load
>> separately. Level 1 is assumed and always the original file. Level 2 is
>> will have each tile represent 4 of the original. So for example if you have
>> a folder of 5000x5000 pixel image files that you chose to chunk 125x125,
>> for each image file your base table will have (5000*5000)/(125*125) records
>> = 1600, your (l=2) o_2 table will have ceiling(1600/Power(2,2)) = 400 rows,*
>> your (l=3) o_3 will have ceiling(1600/Power(2,3) ) = 200 rows*. If your
>> pixels aren't divisible by the size of your tiles, you'll get some scrap
>> tiles (tiles not completely filled). Note that each overview tile generated
>> by raster2pgsql has the same number of pixels as its parent, but is of a
>> lower resolution where each pixel of it represents
>> (Power(2,overview_factor) pixels of the original)."
>>
>>
>>
>> *Question 1*
>>
>> The example given in the document above states that "*... *your (l=2)
>> o_2 table will have ceiling(1600/Power(2,2)) = 400 rows,* your (l=3) o_3
>> will have ceiling(1600/Power(2,3) ) = 200 rows ...." *It appears the *Power
>> *function is being used internally to compute the number of rows for the
>> overview table based on the overview_factor i.e.
>> Power(2,<overview_factor>). If this is the case, *does it imply that the
>> factors could be numbers other than power of 2 (e.g. 3 in this case), but
>> cannot be greater than 1,000 ?*
>>
>>
>>
>> *Question 2*
>>
>> After doing a bulk loading of *650 raster source files* with a tiling
>> option flag, *-t 250x250*, and overview option flag, *-l
>> 2,4,8,16,32,64,128,256*  my raster table had *1,040,000 rows* and the
>> highest factor overview table, o_256_<table_name>, had *650 rows, *while
>> the lowest factor table, o_2_<table_name>, had *260,000 rows.*
>>
>> Going by the example given in the document above, I would expect the
>> opposite of what I am getting i.e. the highest factor overview table with
>> more records (rows) than the lowest factor overview table. *What could
>> be responsible for this?*
>>
>>
>>
>> *Question 3*
>>
>> The document above states that "... If your pixels aren't divisible by
>> the size of your tiles, you'll get some scrap tiles (tiles not completely
>> filled) ...."
>>
>> When I viewed the raster on QGIS (*see attached images*), I discovered
>> that I get black portions on a large portion of the image when completely
>> zoomed out. I only get to see the raster image when I zoom-in sufficiently
>> (even for black portions).
>>
>> *What could be responsible for this black portions (see attached images)
>> as my pixels are obviously divisible by the tile size (250x250)?*
>>
>>
>>
>> I would be glad if someone could help out with the above questions.
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> On Wed, May 10, 2017 at 5:12 PM, David Haynes <haynesd2 at gmail.com> wrote:
>>
>> You are correct overviews are powers based. Also it does not look like
>> you specified tile size in your raster2pgsql statement
>>
>>
>>
>>
>>
>> raster2pgsql -s <SRID> -d -Y -e -I -C -F -M -t <pixels> x <pixels> -l
>> 2,4,8,16,32
>>
>>
>>
>> On Wed, May 10, 2017 at 11:04 AM, Osahon Oduware <osahon.gis at gmail.com>
>> wrote:
>>
>> Hi All,
>>
>>
>>
>> I tried loading a raster with overviews using the raster2pgsql tool using
>> the syntax below:
>>
>>
>>
>> raster2pgsql -s <SRID> -d -Y -e -I -C -F -M -l
>> 2,4,8,16,32,64,128,256,512,1024,2048,4096,8192,16384,32768,65536
>> /path/to/raster/file <table_name> | psql -h <host_address> -U postgres -p
>> 5432 -d <database>
>>
>>
>>
>> but it gave an error message stating that the overview factor cannot be
>> more than 1,000.
>>
>>
>>
>> I would like to know how the Overview-factor works. Must the value for
>> the Overview-factor be in Powers of 2 (i.e. 2,4,8,16,...)?
>>
>>
>>
>> I would be glad if someone could help my understanding.
>>
>>
>>
>> _______________________________________________
>> 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-devel mailing list
>> postgis-devel at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-devel
>>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20170525/77ebb0e2/attachment.html>