Re: [postgis-users] ST_value slow

2017-02-04 Thread 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-boun...@lists.osgeo.org> on behalf of Rémi 
Cura <remi.c...@gmail.com>
Reply-To: PostGIS Users Discussion <postgis-users@lists.osgeo.org>
Date: Saturday, February 4, 2017 at 11:23 AM
To: PostGIS Users Discussion <postgis-users@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 <jcpa...@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-boun...@lists.osgeo.org> on behalf of Rémi 
Cura <remi.c...@gmail.com>
Reply-To: PostGIS Users Discussion <postgis-users@lists.osgeo.org>
Date: Saturday, February 4, 2017 at 3:01 AM
To: PostGIS Users Discussion <postgis-users@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 <jcpa...@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

Re: [postgis-users] ST_value slow

2017-02-04 Thread 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-boun...@lists.osgeo.org> on behalf of Rémi 
Cura <remi.c...@gmail.com>
Reply-To: PostGIS Users Discussion <postgis-users@lists.osgeo.org>
Date: Saturday, February 4, 2017 at 3:01 AM
To: PostGIS Users Discussion <postgis-users@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 <jcpa...@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@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

 

___ postgis-users mailing list 
postgis-users@lists.osgeo.org 
https://lists.osgeo.org/mailman/listinfo/postgis-users

___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] ST_value slow

2017-02-04 Thread 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 :

> 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@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users