[postgis-users] ST_value slow

[Rémi Cura]

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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170204/1b03f3bc/attachment.html>