You're welcome ! Actually I guess that the function is intended to work on one tile and one point. So using a bbox would have no sense. Now if you use it on a whole table instead of one tile, you have to add intersect conditions and so. By the way your update is not safe because a point may intersects several tiles.
Cheers ,Rémi-C 2017-02-04 20:00 GMT+01:00 J Payne <[email protected]>: > Hi Rémi, > > > > Thanks for the suggestion. I tiled the raster and added a ST_Intersects, > and the whole operation finished in just a couple of minutes (the previous > version hadn’t completed when I left it running overnight). The command I > used was this: > > > > UPDATE hourly_positions hp SET landform = > > (SELECT ST_Value(rast,hp.geom4326) FROM landforms4326_128 lf where > (lf.rast && hp.geom4326) AND ST_Intersects(lf.rast,hp.geom4326)) > > > > So, I don’t mean to sound critical of PostGIS, because I love the program > and I have the utmost respect for everyone who works on it, but it appears > to me that ST_Value is a “dumb” function; i.e., it doesn’t know how to find > pixels in a raster with any reasonable speed. Is there any technical > reason that its speed couldn’t be improved? As I mentioned in my original > question, this operation (finding pixel values at many individual points) > is commonly needed for certain classes of problems. > > > > Thanks again for saving my bacon! > > > > John > > > > *From: *postgis-users <[email protected]> on behalf > of Rémi Cura <[email protected]> > *Reply-To: *PostGIS Users Discussion <[email protected]> > *Date: *Saturday, February 4, 2017 at 3:01 AM > *To: *PostGIS Users Discussion <[email protected]> > *Subject: *Re: [postgis-users] ST_value slow > > > > 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 <[email protected]>: > > 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 > [email protected] > https://lists.osgeo.org/mailman/listinfo/postgis-users > > > > _______________________________________________ postgis-users mailing > list [email protected] https://lists.osgeo.org/ > mailman/listinfo/postgis-users > > _______________________________________________ > postgis-users mailing list > [email protected] > https://lists.osgeo.org/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
