Try this:
SELECT I_t,
(tv).val as R_t,
(I_t - R_t) as D_t
FROM (SELECT ST_intersection(R.rast, I.the_geom) AS tv,
I.temp_value as I_t
FROM in_situ_lst I, lst_day R
WHERE I.the_geom && R.rast AND ST_intersects(R.rast,I.the_geom)
And I.temp_lst_id = 2
) foo;
You don't have to put I.the_geom && R.rast in the WHERE...
However if your vector table is a table of point you should use ST_Value
instead of ST_Intersection. If your table is of polygon ST_Intersection might
return many values for one polygon.
Pierre
> -----Original Message-----
> From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-
> boun...@postgis.refractions.net] On Behalf Of maduako ikechukwu
> Sent: Thursday, December 01, 2011 8:30 AM
> To: postgis-users@postgis.refractions.net
> Subject: [postgis-users] ERROR: missing FROM-clause entry for table
>
> Hi guys,
> please could someone help me look at this SQL code, I cant figure out what
> is wrong with it and it gives me this error,
>
> "ERROR: missing FROM-clause entry for table "i"
> LINE 3: Select I.temp_lst_id,I.temp_value as I_t, R.tv.val as R_t, (..."
> ^
> Here is the code;
>
> Select I.temp_value as I_t, R.tv.val as R_t, (I_t - R_t) as D_t From ( Select
> ST_intersection(R.rast,I.the_geom) AS tv From in_situ_lst I, lst_day R
> Where I.the_geom && R.rast AND ST_intersects(R.rast,I.the_geom) And
> I.temp_lst_id = 2 )foo;
>
> I was just trying to get the difference between a temperature value in a
> vector temperature table, in_situ_lst and a raster temperature table,
> lst_day.
>
> Regards
> --
>
> Iyke Maduako
>
> Masters in Geospatial Technologies
>
> Institute for Geoinformatics,IfGI
>
> University of Muenster Germany
>
> Phone: +4915129048460
>
> Alternative Email:iykefirstcl...@yahoo.com
> <mailto:email%3aiykefirstcl...@yahoo.com>
>
>
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
