Hey, I suppose your raster is correctly cut into small pieces. You should first try to clock one point value retrieval. It should be < 50ms (or you have an index issue)
Then you could force the use of index by writting a function update_one_point(point) $$ update XXX set XXX WHERE ST_Intersects(rast,point);$$ You would then call your function on all points: SELECT update_one_point(hp.geom4326) FROM hp Cheers Rémi-C 2017-02-04 6:44 GMT+01:00 J Payne <jcpa...@uw.edu>: > 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 > > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org > https://lists.osgeo.org/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users