Win, Check the error message you get... > Postgresql reports the error as 'ERROR: subquery in FROM must have an alias > HINT: For example, FROM (SELECT ...) [AS] foo. '
You have you DATA set as > 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')" See what's missing? Try something like this: 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') as new_table" For more info on this, please look at the "Using MapServer" section of the PostGIS documentation: http://postgis.refractions.net/docs/ch04.html#id2747066 You should read the entire section but pay attention to the "Advanced Usage" subsection. Good luck! -Perry On Thursday 01 March 2007 20:37, Win Myint Aung wrote: > 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::te >xt 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/ -- Perry Nacionales Dept. of Forest Resources University of Minnesota [EMAIL PROTECTED]
