[postgis-users] Simple ST_Value(rast, point)
Andreas Forø Tollefsen
andreasft at gmail.com
Thu Sep 22 08:59:50 PDT 2011
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 <chris.hermansen at tecogroup.ca>
> 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 <andreasft at gmail.com>
>
>> 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 <chris.hermansen at tecogroup.ca>
>>
>>> 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 <andreasft at gmail.com>
>>>
>>>> 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 <lr at pcorp.us>:
>>>> > 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
>>>> >>
>>>> >
>>>> >
>>>> > _______________________________________________
>>>> > postgis-users mailing list
>>>> > postgis-users at postgis.refractions.net
>>>> > http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>> >
>>>> _______________________________________________
>>>> postgis-users mailing list
>>>> postgis-users at postgis.refractions.net
>>>> 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
>>> postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>
>>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> 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
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110922/3069cc45/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: teco_sig.jpg
Type: image/jpeg
Size: 4928 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110922/3069cc45/attachment.jpg>
More information about the postgis-users
mailing list