[postgis-users] Summarizing the temperature values for a buffer within my tables

[maduako ikechukwu]

Hi, guys,
Hi Piere,
yesterday no one replied me. I have tried to rewrite the code this way

SELECT sum(ST_Area(the_geom)*val)/(sum(ST_Area(the_geom))) AS meantemp
>From (Select(ST_Intersection(R.rast,(ST_Transform(ST_Buffer(I.the_geom,
1000), 32613)))).geom AS the_geom,
(ST_Intersection(R.rast,(ST_Transform(ST_Buffer(I.the_geom, 1000),
32613)))).val AS val
FROM in_situ_lst I, lst_utm_day R
  WHERE the_geom && R.rast
  AND ST_Intersects(R.rast,the_geom)
  AND I.temp_lst_id = 2
) foo;

It runs but brings a blank result, please could you help me look at the
code. I'm trying to obtain the mean temperature of a buffer from my vector
table in_situ_lst I, intersected on the raster temperature data lst_utm_day
R. Just like you explained in one of your tutorials.
But in this case, I don't want to create the tables one after the other
like you explained, just wanted to send a single SQL statement to solve the
problem. The code above looks pretty good but I guess PostGIS gets confused
somehow and brings a blank result.

Regards.
Iyke Maduako


On Fri, Dec 2, 2011 at 7:30 PM, maduako ikechukwu <iykemadu84 at gmail.com>wrote:

> Hi Piere,
> Thanks for yesterday, right now am trying to summarize the temperature
> values within a particular buffer just like you did in the tutorial using
> the codes below but it seems troublesome.
> could you help me please. I dont have to create tables one after the other
> like you did in the tutorial becuase I dont have that priveledge , so I
> have to only write a select statement to do that.
> Here is my code:
>
> SELECT I.temp_lst_id, meantemp
> From (Select  sum(ST_Area(the_geom)*val)/(sum(ST_Area(the_geom))) AS
> meantemp, I.temp_lst_id
> From (Select(ST_Intersection(R.rast,(ST_Transform(ST_Buffer(I.the_geom,
> 100), 32613)))).geom AS the_geom,
>      (ST_Intersection(R.rast,(ST_Transform(ST_Buffer(I.the_geom, 100),
> 32613)))).val AS val
>   FROM in_situ_lst I, lst_utm_day R
>   WHERE the_geom && R.rast
>   AND ST_Intersects(R.rast,the_geom)
>   AND I.temp_lst_id = 2
> )) foo;
>
> the Error in this code is  "ERROR:  subquery in FROM must have an alias
> LINE 3: From (Select(ST_Intersection(R.rast,(ST_Transform(ST_Buffer(..."
>                     ^
> Regards.
> --
> Iyke Maduako
>
> Masters in Geospatial Technologies
>  Institute for Geoinformatics,IfGI
>  University of Muenster Germany
> Phone: +4915129048460
> Alternative Email:iykefirstclass at yahoo.com
>
>
>


-- 
Iyke Maduako

Masters in Geospatial Technologies
Institute for Geoinformatics,IfGI
University of Muenster Germany
Phone: +4915129048460
Alternative Email:iykefirstclass at yahoo.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20111203/030b2682/attachment.html>