[postgis-devel] ST_Value performance

Sandro Santilli strk at keybit.net
Tue Jan 31 11:39:27 PST 2012


On Tue, Jan 31, 2012 at 02:07:31PM -0500, Pierre Racine wrote:
> Could you tell us more about your general goal. Seems to be a case for ST_MapAlgebra...

Yes, indeed I should be using ST_MapAlgebra.
The general goal is to produce an NTv2 grid shift file,
starting from another one. The values will depend on the
difference between two projections of each cell grid.

This step I was performing was to compare my projections with the
values stored in the raster, a kind of consistency checking.

So again, yes I should be using MapAlgebra but this is more for debugging
and checking what I'm doing.

--strk;

> > -----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
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel

-- 

  ,------o-. 
  |   __/  |    Thank you for PostGIS-2.0 Topology !
  |  / 2.0 |    http://www.pledgebank.com/postgistopology
  `-o------'




More information about the postgis-devel mailing list