[postgis-users] Simple ST_Value(rast, point)

[Paragon Corporation]

Andreas,

Off hand I think you are missing some intersects checks and are therefore
doing much more work than you need to

Don't you want to check only raasters where 

ST_Intersects(p90.rast, p.centroid) AND ST_Intersects(p95.rast, p.centroid) 

etc etc.

Regina
http://www.postgis.us
 

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net 
> [mailto:postgis-users-bounces at postgis.refractions.net] On 
> Behalf Of Andreas Forø Tollefsen
> Sent: Monday, September 19, 2011 11:51 AM
> To: PostGIS Users Discussion
> Subject: [postgis-users] Simple ST_Value(rast, point)
> 
> Hi all,
> 
> I just wanted to get some feedback on my query. Basically, it 
> is simply to create a new table with the raster values of my 
> 6 population rasters overlapping my regularly separated point dataset.
> 
> Is this an optimal way of doing this? Reason I ask is that it 
> does take a lot of time.
> 
> DROP TABLE IF EXISTS popgrid;
> 	
> SELECT gid,
> ST_Value(p90.rast, SetSRID(p.centroid,4326)) as pop90, 
> ST_Value(p95.rast, SetSRID(p.centroid,4326)) as pop95, 
> ST_Value(p00.rast, SetSRID(p.centroid,4326)) as pop00, 
> ST_Value(p05.rast, SetSRID(p.centroid,4326)) as pop05, 
> ST_Value(p10.rast, SetSRID(p.centroid,4326)) as pop10, 
> ST_Value(p15.rast, SetSRID(p.centroid,4326)) as pop15 INTO 
> popgrid FROM pop90 p90, pop95 p95, pop00 p00, pop05 p05, 
> pop10 p10, pop15 p15, priogrid p ;
> 
> Best,
> Andreas
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>