[postgis-users] ST_value slow

[J Payne]

Hello.  I have an animal track that wanders around on a landscape, and I want to extract the pixel values for each location on the track.  This kind of operation is very common for animal studies.  The landscape is represented by a one-row raster map (“landforms4326”), and the GPS positions are in a table called “hourly_positions”.  I am using a recent version of PostGIS and a very simple query to find the pixel values:

 

UPDATE hourly_positions hp SET landform = (SELECT ST_Value(rast,hp.geom4326) FROM landforms4326)

 

I have about 450,000 points, and both the landscape raster and the point geometries are indexed.  For some reason, the operation is *incredibly* slow (it’s already been running for more than 8 hours on a nearly new Macbook Pro, and still isn’t finished).  Am I missing something?  I can’t for the life of me imagine why it would be so slow, since PostGIS is very fast with all sorts of other operations on the same data (intersections, etc.).

 

Thanks,

 

John

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170203/bed1660a/attachment.html>