[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