[postgis-users] [postgis] Optimal tile size for Raster

[Pierre Racine]

You are getting fast result because you are not operating at the pixel level. Only at the raster extent level (converted to geometry). A more usual query would be to intersect a raster with some polygons:

SELECT (ST_Intersection(r.rast, q.geom)).*
FROM ilatlon32x32 as r, geometrytable as q
WHERE ST_Intersects(r.rast, q.geom)

Why do you want to intersect two rasters? Generally it is to mask one raster with another.

Pierre

> -----Original Message-----
> From: postgis-users-bounces at lists.osgeo.org [mailto:postgis-users-
> bounces at lists.osgeo.org] On Behalf Of Heng Zhi Feng (zheng at hsr.ch)
> Sent: Wednesday, December 04, 2013 10:56 AM
> To: postgis-users at lists.osgeo.org
> Subject: [postgis-users] [postgis] Optimal tile size for Raster
> 
> Hello,
> 
> 
> 
> I am having to import Raster (.tiff) into Postgres using the raster2pgsql tool.
> The raster used is 12MB in size and the Postgres version is 9.1 on Ubuntu
> 13.10.
> 
> 
> 
> The virtual machine has the following hardware specs.
> 
> -          17408 Memory
> 
> -          4 Processors
> 
> -          16GB HDD
> 
> The command used to import (with the parameters) is: raster2pgsql -s
> 4326 -I -C -M -R -d -l 4 U:\Desktop\ilatlon_float.tif -F -t 8x8 ilatlon8x8|psql -
> d Raster
> 
> And the tile size varies from 8x8 all the way up till 2048 (power of 2s).
> 
> 
> 
> My results from the query ran showed that with larger tile size, the query
> has a faster returned timing.
> 
> This trend is however opposite to some of the similar tests conducted by
> others (from the internet), where their conclusion was smaller tile size will
> have better performance.
> 
> 
> 
> This is my one of the query I ran:
> 
> EXPLAIN (ANALYZE,BUFFERS)
> 
> SELECT DISTINCT ST_Intersection(r.rast::geometry,q.rast::geometry)
> 
> FROM ilatlon32x32 as r, boundedilatlon32x32 as q;
> 
> 
> 
> Is someone able to give advice on this, if this result that I obtained was
> legit? Or suggest where could the problem be that gave this result?
> 
> 
> 
> Regards,
> 
> Zhi Feng