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: [email protected] [mailto:[email protected]] 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: [email protected] [mailto:postgis-users- > [email protected]] On Behalf Of Heng Zhi Feng ([email protected]) > Sent: Wednesday, December 04, 2013 10:56 AM > To: [email protected] > 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 [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
