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: [email protected] > [mailto:[email protected]] 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 > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
