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

[Osahon Oduware]

Hi All,

I am trying to utilize the ST_CreateOverview function and I need to know
how to include the schema name for the raster table in the function. It
appears the default is the public schema.

Please, I need help on this.

On Tue, May 30, 2017 at 2:12 AM, Regina Obe <lr at pcorp.us> wrote:

> Osahon,
>
>
>
> What is the original size of your file tiles?  I haven't looked at the
> code that closely of raster2pgsql, so not sure how exactly it splits things
> up.
>
> From what I remember way back, I think it might consider each tile as
> separate and scale each tile individually in which case each row of any
> overview can't contain more than one file.  I may be completely wrong
> though or code might have changed since I last looked. If my assumption is
> right this would make the counts very sensitive to your file tile size.
>
>
>
> The other consequence is if your factors don't fit in evenly with your
> tilesize, you may get some odd counts. So yes that would make going by
> factors of 2 more likely to match expected if your file tile size is even.
>
>
>
> I'm really puzzled about your o_3 answer though I would have expected that
> to be higher than the estimated
>
>
>
> If you look at for example the ST_CreateOverview function, which should be
> doing much the same thing as raster2pgsql, you will see for scale factor of
> width  and height it is x*factor, y*factor.
>
>
>
> I'd be very interested to know if  ST_CreateOverview against your main
> table gives you similar results.  That one I know does do a lot of unioning
> and clipping so could behave differently since it would be more sensitive
> to extent of coverage than original file size.
>
>
>
>
>
> Thanks,
>
> Regina
>
>
>
>
>
>
>
>
>
> *From:* Osahon Oduware [mailto:osahon.gis at gmail.com]
> *Sent:* Monday, May 29, 2017 12:38 PM
> *To:* PostGIS Development Discussion <postgis-devel at lists.osgeo.org>
> *Cc:* Regina Obe <lr at pcorp.us>; Pierre Racine <pierre.racine at sbf.ulaval.ca
> >
> *Subject:* [URGENT] PostGIS Rasters Overview Factor
>
>
>
> 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/20170602/613fee00/attachment.html>