[postgis-users] ST_value slow

[Rémi Cura]

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 <jcpayne at uw.edu>:

> 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 <postgis-users-bounces at lists.osgeo.org> on behalf
> of Rémi Cura <remi.cura at gmail.com>
> *Reply-To: *PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> *Date: *Saturday, February 4, 2017 at 3:01 AM
> *To: *PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> *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 <jcpayne at 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 at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________ postgis-users mailing
> list postgis-users at lists.osgeo.org https://lists.osgeo.org/
> mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170204/252d0b8b/attachment.html>