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
