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

[Chris Hermansen]

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