I managed to get it working. However, I receive the 'lower bound of FOR loop cannot be null'.
Why does i.e. this query give this error? It does not matter if I exclude or include no data values, or change the no data value of my raster. Query: SELECT foo.gid::integer, 1990, CAST((foo.geomval).val AS integer) as val FROM (SELECT a.rid, g.gid, ST_Intersection(a.rast, g.centroid) AS geomval FROM pop90 a, priogrid_land g WHERE ST_Intersects(a.rast, g.centroid)) AS foo; 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 Thanks, Andreas 2011/9/20 Chris Hermansen <[email protected]> > Hi Andreas, > > If you need priogrid.gid (you might want to name it more suggestively) in > your output table in my example you would leave the table definition as I > originally stated, and you would join the priogrid and the p90, p95, etc etc > tables together in each INSERT/SELECT statement: > > CREATE TABLE npopgrid ( > priogrid_gid integer, > year integer, > pop integer, > primary key (priogrid_gid, year)); > > INSERT INTO npopgrid (priogrid_gid, year, pop) > SELECT priogrid.gid, 90::integer, ST_Value(p90.rast, > SetSRID(p.centroid,4326))::integer FROM priogrid AS p, pop90 as p90 > > WHERE ST_Intersects(p90.rast, p.centroid) > UNION... > > in that case it makes sense to make the primary key (priogrid_gid,year) > because the priogrid.gid itself will re-appear for each year. > > > 2011/9/20 Andreas Forø Tollefsen <[email protected]> > >> Hi Chris, >> Thanks for your suggestions. >> It does make more sense. However, the gid thing comes from the priogrid >> table which is the point table. >> >> I think I solved the issue with the error. >> No data for the raster is -3.40282e+38, so excluding no data values makes >> the query work. >> But not excluding the no data values makesthe function crash. >> >> The population data can be downloaded here if someone wants to replicate. >> >> http://sedac.ciesin.columbia.edu/gpw/global.jsp?file=gpwv3&data=pcount&type=wrk&resolut=half&year=90&version=gpw-v3 >> >> Andreas >> >> >> >> 2011/9/20 Chris Hermansen <[email protected]> >> >>> Andreas, possibly with a more normalized data model you can make this >>> work. >>> >>> Consider a table that looks like >>> >>> CREATE TABLE npopgrid ( >>> gid integer, >>> year integer, >>> pop integer, >>> primary key (gid, year)); >>> >>> Then you can efficiently put data into it like this: >>> >>> INSERT INTO npopgrid (gid, year, pop) >>> SELECT gid, 90::integer, ST_Value(p90.rast, >>> SetSRID(p.centroid,4326))::integer from p90 >>> UNION >>> SELECT gid, 95::integer, ST_Value(p95.rast, >>> SetSRID(p.centroid,4326))::integer from p95 >>> .... >>> >>> This - to me at least - makes more sense because I assume that p90 has >>> the population from 90 at every cell in the raster, p95 at every cell in the >>> raster from 95, etc. The query the way you structured it originally >>> requires some kind of table join exercise and you really don't need that if >>> your data model is normalized like the above. >>> >>> In the end I'm not certain about the "gid" thing because I'm not sure >>> which table you expect it to come from. If your gid is unique across all of >>> your tables p90, p95, etc etc then you are ok but I suspect it's not. >>> Therefore it's good to have the primary key containing the gid and the >>> year... >>> >>> Does this make sense? >>> >>> If you really want an unnormalized version of this in the end (think >>> about that - why would you want such a thing? if you don't fully understand >>> normalized data models, perhaps this is a good moment to read about it) you >>> can denormalize it inexpensively from this structure (exercise left to the >>> reader... :-0) >>> >>> 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 >>>> >>> >>> >>> >>> -- >>> Chris Hermansen >>> *Vice President* >>> >>> TECO Natural Resource Group Limited >>> 301 · 958 West 8th Avenue >>> Vancouver BC CANADA · V5Z 1E5 >>> Tel +1.604.714.2878 · Cel +1.778.840.4625 >>> >>> _______________________________________________ >>> 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 >> >> > > > -- > Chris Hermansen > *Vice President* > > TECO Natural Resource Group Limited > 301 · 958 West 8th Avenue > Vancouver BC CANADA · V5Z 1E5 > Tel +1.604.714.2878 · Cel +1.778.840.4625 > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > >
<<teco_sig.jpg>>
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
