[postgis-users] ST_value slow
jcpayne at uw.edu
Sat Feb 4 11:00:18 PST 2017
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!
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
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:
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.).
postgis-users mailing list
postgis-users at lists.osgeo.org
_______________________________________________ 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...
More information about the postgis-users