beginner:problem in Dynamic query with postGIS

[Pericles S. Nacionales]

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
naci0002 at umn.edu