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

Tue Sep 20 07:26:17 PDT 2011

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