[Mapserver-users] Oracle/PHP mapscript issues

[chodgson at refractions.net]

Thanks for the help Fernando - but I'm ashamed to admit the problem was with my 
own code. I had the same data statement being set twice in my code, once for  
drawing the polygon layer and once for the symbology layer ( to draw hash lines 
across some of the polyogons ). I didn't notice this and mapserver wasn't 
showing the query that was in error - so I never realized that I was only 
fixing one of the two queries - until now.

Thanks,
Chris Hodgson

Quoting FERNANDO SIMON <simon at inf.univali.br>:

> Chris,
>    Well, I do not know if I can help you but I will try. The problem can be
> this, you did not  define in you query the parameter "AS" for the Geometry
> Colmun that you will use in the data.
>     Look this query work fine:
>        data "geom from (SELECT SDO_GEOM.SDO_POINTONSURFACE(c.coordenada,
> m.diminfo) AS GEOM FROM user_ident u, capta_agua c, user_sdo_geom_metadata m
> WHERE m.table_name = 'CAPTA_AGUA' AND m.column_name = 'COORDENADA' AND u.novo
> = 'SIM' AND u.codigo = c.codigo)"
> 
>     And this Mapscript code works fine to:
> 
>   foreach( $layer_array as $layer_name )
>     if (!empty( $layer_name ))
>    {
>       $layer = $map->getLayerByName( $layer_name );
>       $layer->set( 'status', MS_ON );
>       if($layer_name === 'pesq')
>         $layer->set( 'data', "coordenada from (select coordenada as
> coordenada,
> codigo_ott as codigo_ott from hidrografia_sc where codigo_ott = $otto)");
>       $layer_index[] = $layer->index;
>     }
> 
>     So, you need to define your Geometry Column:
>          $statement = "the_geom FROM (SELECT the_geom AS the_geom, a.lha_id
> as
> lha_id, $column FROM lha_combined_albers a LEFT JOIN lha_stats b ON a.lha_id
> =
> b.lha_id)";
> 
>     Well, sorry my poor english. If you need some help you can report me.
>     Thank`s.
> 
> ==================================================================
> Fernando Simon - simon at cttmar.univali.br
> ==================================================================
> DataBase Manager, Mapserver, OracleSpatial and PostGis Developer
> G10 - Laboratório de Computação Aplicada
> Fone: 047 - 341 7960
> http://g10.cttmar.univali.br
> ==================================================================
> 
> 
> chodgson at refractions.net wrote:
> 
> >I am using PHP-mapscript to dynamically set the DATA statement for my
> >oraclespatial layer as follows:
> >
> >$lha_layer = $map->getLayerByName( 'Local Health Areas' );
> >$lha_layer->set( 'status', MS_ON );
> >$statement = "the_geom FROM (SELECT the_geom, a.lha_id as lha_id, $column
> FROM
> >lha_combined_albers a LEFT JOIN lha_stats b ON a.lha_id = b.lha_id)";
> >$lha_layer->set( 'data', $statement );
> >
> >When I call $map->draw(), I get the error:
> >
> >Warning: MapServer Error in msOracleSpatialLayerWhichShapes(): Error parsing
> >OracleSpatial DATA variable. Must be 'geometry_column FROM table_name [USING
> >SRID srid#]' or 'geometry_column FROM (SELECT stmt)'.
> >
> >The select statement inside the () works fine in sqlplus, and the entire
> data
> >statement works fine in the mapfile if I use the mapserv cgi to render the
> map
> >instead of php-mapscript.
> >In fact, the whole app worked fine before when I was using postgres/postgis,
> >I'm just porting it over to oracle spatial.
> >
> >What am I doing wrong here? Any clues?
> >
> >Thanks,
> >Chris Hodgson
> >