[postgis-users] ST_value slow

[J Payne]

Hi Rémi,

 

My query was:

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))

 

You wrote: “your update is not safe because a point may intersect several tiles.”  I’d like to understand that better.  Is this an edge-condition problem (i.e., a point falls right on an edge between two tiles)?  If so, my query would fail because the subquery would return more than one value, correct?  

 

If a point is right on the edge between two tiles, is there any situation where ST_Value(rast,point) might return different values from the two tiles?  I saw your reply to a similar problem in this post,

http://lists.osgeo.org/pipermail/postgis-users/2014-February/038660.html,

where you suggested adding something like this:

 

WITH (your computing)

SELECT DISTINCT ON (line_id, poly_id) , poly, line

FROM your computing

ORDER BY ST_Length(line) ASC

 

…but if ST_Value() ever returns more than one value, DISTINCT will return both(all), which might just add to your problems.  I notice that the questioner responded “my fix was to detect such lines and intersect them with the original polygon instead of the tiled one.”  I guess I’m just confused about what is really happening with the tiles.

 

Thanks,

 

John

 

P.S. I’m not sure why, but using the bbox did speed up the query a little bit.  

 

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 11:23 AM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: Re: [postgis-users] ST_value slow

 

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

 

_______________________________________________ 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/e1f97707/attachment.html>