ST_value slow

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

ST_value slow

John Payne

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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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 <[hidden email]>:

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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ST_value slow

John 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 <[hidden email]> on behalf of Rémi Cura <[hidden email]>
Reply-To: PostGIS Users Discussion <[hidden email]>
Date: Saturday, February 4, 2017 at 3:01 AM
To: PostGIS Users Discussion <[hidden email]>
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 <[hidden email]>:

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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

 

_______________________________________________ postgis-users mailing list [hidden email] https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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 <[hidden email]>:

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 <[hidden email]> on behalf of Rémi Cura <[hidden email]>
Reply-To: PostGIS Users Discussion <[hidden email]>
Date: Saturday, February 4, 2017 at 3:01 AM
To: PostGIS Users Discussion <[hidden email]>
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 <[hidden email]>:

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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

 

_______________________________________________ postgis-users mailing list [hidden email] https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ST_value slow

John 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 <[hidden email]> on behalf of Rémi Cura <[hidden email]>
Reply-To: PostGIS Users Discussion <[hidden email]>
Date: Saturday, February 4, 2017 at 11:23 AM
To: PostGIS Users Discussion <[hidden email]>
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 <[hidden email]>:

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 <[hidden email]> on behalf of Rémi Cura <[hidden email]>
Reply-To: PostGIS Users Discussion <[hidden email]>
Date: Saturday, February 4, 2017 at 3:01 AM
To: PostGIS Users Discussion <[hidden email]>
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 <[hidden email]>:

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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

 

_______________________________________________ postgis-users mailing list [hidden email] https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

 

_______________________________________________ postgis-users mailing list [hidden email] https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Loading...