beginner:problem in Dynamic query with postGIS

[Win Myint Aung]

Dear members,

I would like request you to help me the dynamic query problems. I created map file to show the point for one day on map. Because the table in PostGIS has many rows for many different days. I tested with FILTER but it is not the one I want. I tried to make criteria in DATA by WHERE clause. Please see map file here.

This part make errors in map file. Other part is ok.

LAYER #Confidence
  CONNECTIONTYPE postgis
  NAME "firepoint"
  CONNECTION "user=postgres password=kyawsannoo1234 dbname=hotspot_db host=localhost port=5432"
#  DATA "the_geom from firepoint"
  DATA "the_geom FROM (SELECT hotspot_tmp.the_geom as the_geom,hotspot_tmp.gid as oid,hotspot_tmp.date as date 
 FROM hotspot_tmp WHERE hotspot_tmp.date='2006-08-15')"
#  FILTER "f_posibl >=75"
#  HEADER '/ms4w/projects/hotspot/htdocs/templates/HFVS_header.html'
#  FOOTER '/ms4w/projects/hotspot/htdocs/templates/HFVS_footer.html'
  TOLERANCE 3
  STATUS ON
  TYPE POINT
  CLASSITEM "f_posibl"
  TEMPLATE '/ms4w/projects/hotspot/htdocs/templates/HFVS.html'
  CLASS
    NAME "Low [0-40]"
    EXPRESSION ([f_posibl] <= 40)
    STYLE
     COLOR 255 255 0      
     SYMBOL "square"
     OUTLINECOLOR 0 0 0
     SIZE 5
    END
  END
  CLASS
    NAME "Moderate [41 - 70]"
    EXPRESSION (([f_posibl] > 40) and ([f_posibl] <= 70))
    STYLE
     COLOR 255 155 55      
     SYMBOL "square"
     OUTLINECOLOR 0 0 0
     SIZE 5
    END      
  END
  CLASS
    NAME "High [71-100]"
    EXPRESSION ([f_posibl] > 70)
    STYLE
     COLOR 255 0 0      
     SYMBOL "square"
     OUTLINECOLOR 0 0 0
     SIZE 5
    END      
  END
END

==============
ERRORS make


msDrawMap(): Image handling error. Failed to draw layer named 'firepoint'. 
prepare_database(): Query error. Error executing POSTGIS DECLARE (the actual query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT f_posibl::text,asbinary(force_collection(force_2d(the_geom)),'NDR'),OID::text from (SELECT hotspot_tmp.the_geom as the_geom,hotspot_tmp.gid as oid,hotspot_tmp.date as date FROM hotspot_tmp WHERE hotspot_tmp.date='2006-08-15') WHERE the_geom && setSRID('BOX3D(-15 -80,195 60)'::BOX3D, find_srid('','(SELECT hotspot_tmp.the_geom as the_geom,hotspot_tmp.gid as oid,hotspot_tmp.date as date FROM hotspot_tmp WHERE hotspot_tmp.date='2006-08-15')','the_geom') )' 

Postgresql reports the error as 'ERROR: subquery in FROM must have an alias HINT: For example, FROM (SELECT ...) [AS] foo. '

More Help:

Error with POSTGIS data variable. You specified '<check your .map file>'.
Standard ways of specifiying are : 
(1) 'geometry_column from geometry_table' 
(2) 'geometry_column from (<sub query>) as foo using unique <column name> using SRID=<srid#>' 

Make sure you put in the 'using unique <column name>' and 'using SRID=#' clauses in. 

For more help, please see http://postgis.refractions.net/documentation/ 

Mappostgis.c - version of Jan 23/2004. 
msPOSTGISLayerRetrievePK(): Query error. Error executing POSTGIS statement (msPOSTGISLayerRetrievePK():select attname from pg_attribute, pg_constraint, pg_class where pg_constraint.conrelid = pg_class.oid and pg_class.oid = pg_attribute.attrelid and pg_constraint.contype = 'p' and pg_constraint.conkey[1] = pg_attribute.attnum and pg_class.relname = '(SELECT hotspot_tmp.the_geom as the_geom,hotspot_tmp.gid as oid,hotspot_tmp.date as date FROM hotspot_tmp WHERE hotspot_tmp.date='2006-08-15')' and pg_constraint.conkey[2] is null


 
____________________________________________________________________________________
It's here! Your new message!  
Get new email alerts with the free Yahoo! Toolbar.
http://tools.search.yahoo.com/toolbar/features/mail/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20070301/390330e6/attachment.htm>