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

[Bborie Park]

On Fri, Dec 2, 2011 at 10:30 AM, 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
>
>

You need to give an alias to your second sub-query.

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
) BAR
) foo;

In your query (reformatted), I added the BAR for the second subquery.

-bborie


-- 
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkpark at ucdavis.edu