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

Tue Sep 20 08:37:38 PDT 2011

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110920/e43a7fc7/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/20110920/e43a7fc7/attachment.jpg>
-------------- 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/20110920/e43a7fc7/attachment-0001.jpg>