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

Mon May 29 09:38:12 PDT 2017

Hi All,

I loaded a 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>

After the loading I got the following for the raster table:
Total Rows in <raster_table> = 1,040,000
Tile Size = 250 x 250

Going by the previous explanation from Regina, I would expect the following
number of rows for the overviews:

o_7_<raster_table> = 1,040,000/Power(2,7) = 1,040,000/128 = 8,125 rows

o_6_<raster_table> = 1,040,000/Power(2,6) = 1,040,000/64 = 16,250 rows

o_5_<raster_table> = 1,040,000/Power(2,5) = 1,040,000/32 = 32,500 rows

o_4_<raster_table> = 1,040,000/Power(2,4) = 1,040,000/16 = 65,000 rows

o_3_<raster_table> = 1,040,000/Power(2,3) = 1,040,000/8 = 130,000 rows

o_2_<raster_table> = 1,040,000/Power(2,2) = 1,040,000/4 = 260,000 rows

However, this is what I got after loading the raster with the raster2pgsql
tool following the syntax provided above:

o_7_<raster_table> = 23,400 rows

o_6_<raster_table> = 31,850 rows

o_5_<raster_table> = 41,600 rows

o_4_<raster_table> = 65,000 rows

o_3_<raster_table> = 127,400 rows

o_2_<raster_table> = 260,000 rows

*Question 1*
>From the above, only the overview tables for overview factor 2 and 4 (i.e.
o_4_<raster_table> and o_2_<raster_table>) are given the expected result
(number of rows). Is it a coincidence that these overview-factors, 2 and 4,
happen to be powers of 2?

*Question 2*
I stopped at overview-factor 7, because, going by the previous explanation
by Regina, it appears going above that (e.g overview-factor 8) would
produce scrap tiles i.e.
o_8_<raster_table> = 1,040,000/Power(2,8) = 1,040,000/256 = 4,062 + 128/256
= 4,063 rows (which would result in 128 slots being filled with no data
filler)
How do I generate more overview tables (e.g o_8_<raster_table>,
o_9_<raster_table>, ...) without getting scrap tiles?

*NB: *When I try to view the raster in QGIS, it is taking 'forever' to load
the initial full-extent zoom

I would be glad if someone could help me understand this by providing
answers to the above questions.

On May 26, 2017 10:07 AM, "Osahon Oduware" <osahon.gis at gmail.com> wrote:

> Hi All,
>
> I loaded a 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>
>
> After the loading I got the following for the raster table:
> Total Rows in <raster_table> = 1,040,000
> Tile Size = 250 x 250
>
> Going by the previous explanation from Regina, I would expect the
> following number of rows for the overviews:
>
> o_7_<raster_table> = 1,040,000/Power(2,7) = 1,040,000/128 = 8,125 rows
>
> o_6_<raster_table> = 1,040,000/Power(2,6) = 1,040,000/64 = 16,250 rows
>
> o_5_<raster_table> = 1,040,000/Power(2,5) = 1,040,000/32 = 32,500 rows
>
> o_4_<raster_table> = 1,040,000/Power(2,4) = 1,040,000/16 = 65,000 rows
>
> o_3_<raster_table> = 1,040,000/Power(2,3) = 1,040,000/8 = 130,000 rows
>
> o_2_<raster_table> = 1,040,000/Power(2,2) = 1,040,000/4 = 260,000 rows
>
>
> However, this is what I got after loading the raster with the raster2pgsql
> tool following the syntax provided above:
>
> o_7_<raster_table> = 23,400 rows
>
> o_6_<raster_table> = 31,850 rows
>
> o_5_<raster_table> = 41,600 rows
>
> o_4_<raster_table> = 65,000 rows
>
> o_3_<raster_table> = 127,400 rows
>
> o_2_<raster_table> = 260,000 rows
>
> *Question 1*
> From the above, only the overview tables for overview factor 2 and 4 (i.e.
> o_4_<raster_table> and o_2_<raster_table>) are given the expected result
> (number of rows). Is it a coincidence that these overview-factors, 2 and 4,
> happen to be powers of 2?
>
> *Question 2*
> I stopped at overview-factor 7, because, going by the previous explanation
> by Regina, it appears going above that (e.g overview-factor 8) would
> produce scrap tiles i.e.
> o_8_<raster_table> = 1,040,000/Power(2,8) = 1,040,000/256 = 4,062 +
> 128/256 = 4,063 rows (which would result in 128 slots being filled with no
> data filler)
> How do I generate more overview tables (e.g o_8_<raster_table>,
> o_9_<raster_table>, ...) without getting scrap tiles?
>
> *NB: *When I try to view the raster in QGIS, it is taking 'forever' to
> load the initial full-extent zoom
>
>
> I would be glad if someone could help me understand this by providing
> answers to the above questions.
>
>
>
>
> 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/20170529/02afee10/attachment.html>