[postgis-users] ERROR: missing FROM-clause entry for table

[Pierre Racine]

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-bounces at postgis.refractions.net [mailto:postgis-users-
> bounces at postgis.refractions.net] On Behalf Of maduako ikechukwu
> Sent: Thursday, December 01, 2011 8:30 AM
> To: postgis-users at 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:iykefirstclass at yahoo.com
> <mailto:Email%3Aiykefirstclass at yahoo.com>
> 
>