I also tested with adding the SetSRID in the ST_Intersects(), but i got the same error. DROP TABLE IF EXISTS popgrid;
SELECT p.gid, ST_Value(pop90.rast, ST_SetSRID(p.centroid,4326)) INTO popgrid FROM priogrid p, pop90 WHERE ST_Intersects(pop90.rast, ST_SetSRID(p.centroid,4326)); Could this be because of some no data issues? 2011/9/19 Andreas Forø Tollefsen <[email protected]>: > Somehow that result in an error. > > Query: > 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 > WHERE ST_Intersects(p90.rast, p.centroid) > AND ST_Intersects(p95.rast, p.centroid) > AND ST_Intersects(p00.rast, p.centroid) > AND ST_Intersects(p05.rast, p.centroid) > AND ST_Intersects(p10.rast, p.centroid) > AND ST_Intersects(p15.rast, p.centroid) > ; > > Result: > ERROR: lower bound of FOR loop cannot be null > CONTEXT: PL/pgSQL function "_st_intersects" line 96 at FOR with > integer loop variable > > ********** Error ********** > > ERROR: lower bound of FOR loop cannot be null > SQL state: 22004 > Context: PL/pgSQL function "_st_intersects" line 96 at FOR with > integer loop variable > > Something fishy going on here? > > Rev 7862. > > 2011/9/19 Paragon Corporation <[email protected]>: >> 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 >> > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
