[postgis-users] ST_value slow

[J Payne]

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170204/3d1149ce/attachment.html>