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

[Andreas Forø Tollefsen]

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 <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
>>
>