[postgis-devel] ST_Value performance

[Pierre Racine]

Could you tell us more about your general goal. Seems to be a case for ST_MapAlgebra...

> -----Original Message-----
> From: postgis-devel-bounces at postgis.refractions.net [mailto:postgis-devel-
> bounces at postgis.refractions.net] On Behalf Of Sandro Santilli
> Sent: Tuesday, January 31, 2012 1:27 PM
> To: postgis-devel at postgis.refractions.net
> Subject: [postgis-devel] ST_Value performance
> 
> I'm willing to dump the contents of a raster to a table to get:
> 
>  x,y,point,value1,value2,valueN
> 
> Thats for an N band raster.
> 
> After first using ST_PixelAsPolygon call, which was relatively fast I ended up with
> 206893 rows in a new table, one per pixel.
> 
> Now I'm running an update adding the value to each of those rows, using
> ST_Value. I do understand that over 200k calls are a lot to do, but fetching a
> value itself shouldn't take long, right ?
> It's a 661x313 pixels raster.
> 
> Well... it's been running at 100% CPU for a good amount of time now.
> 
> The query:
> 
> =# update chenyx06a_pixels set shift_lat = ST_Value(o.rast, 1, st_centroid) from
> chenyx06a o;
> 
> The time it is taking:
> 
>   PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
> 11518 postgres  20   0  309m  51m  42m R  100  1.3  36:55.82 postgres
> 
> That's almost 40 minutes with CPU at 100%.
> Is it expected ?
> 
> --strk;
> 
>   ,------o-.
>   |   __/  |    Thank you for PostGIS-2.0 Topology !
>   |  / 2.0 |    http://www.pledgebank.com/postgistopology
>   `-o------'
> 
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel