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

[Heng Zhi Feng (zheng@hsr.ch)]

Hi Pierre,

Thanks for the suggestion.

But how could I get a geometry table in Postgres that is sure to intersect the raster image?
The reason why I created another raster table is so that I am able to vary the query size. How can I do it with your suggestions (using polygons) since the polygon may not intersect with the raster.

Please correct me if I am wrong, as I am still new to this concept.

Regards,
Zhi Feng 

-----Original Message-----
From: postgis-users-bounces at lists.osgeo.org [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Pierre Racine
Sent: Mittwoch, 4. Dezember 2013 17:43
To: PostGIS Users Discussion
Subject: Re: [postgis-users] [postgis] Optimal tile size for Raster

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

_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users