[Mapserver-users] Classes based on Postgis && function

[Jan Hartmann]

Hmm, this looks like a bug: the two SQL statements are exactly the same, 
except for the USING statement. Also, the SQL in the error message is 
different from the original one (and definitely erroneous). Looks like 
the parser in mappostgis.c chokes on the combination of CASE WHEN and 
USING. In my own setup I have always run CASE statements without USING. 
I added USING now, and got the same error message. I guess the PostGIS 
folks should have a look at this.

Meanwhile, you could create a VIEW in your database with the working SQL 
  and use that in the DATA statement. In PostgreSQL 7.3 you can even 
create parametrized functions, returning recordsets based on these 
complicated queries. I have not tried out this yet, but I think that 
they are better suited for such nested queries than DATA statements with 
interminable string constants. All the same, complex DATA statements 
should work too.


Jan


> Here's the SQL statement that worked in psql:
> 
> select the_geom from
>     (select sheet.the_geom as the_geom,
>             userpolygon.the_geom as polygeom,
>             sheet.oid,
>             case when sheet.seriesid=1
>                  and distance(sheet.the_geom,userpolygon.the_geom)=0
>             then 1 end as myclass
>      from sheet, userpolygon
>      where sheet.the_geom && userpolygon.the_geom
>        and sheet.seriesid=1
>        and userpolygon.id=2
>      ) as foo
> 
> This returns:
> 
> SRID=-1;MULTIPOLYGON(((111.9315812656 -15.4736587584903,126.82736749376 
> -15.8806474532487,128.129731316987 -37.6952414923032,111.605990309793 
> -37.0440595806897,111.9315812656 -15.4736587584903)))
> SRID=-1;MULTIPOLYGON(((124.466833064161 
> -10.9153853771953,137.897459991191 -11.4037718109055,138.792835119659 
> -26.3809557780175,121.862105417707 -26.787944472776,124.466833064161 
> -10.9153853771953)))
> SRID=-1;MULTIPOLYGON(((111.768785787696 
> -13.5201130236496,145.874438408457 -15.7992497142971,157.595712817501 
> -36.8812641027863,108.757069446484 -40.4627646166609,111.768785787696 
> -13.5201130236496)))
> 
> I then construct a new ms_newLayerObj and translate the sql above to the 
> following data property:
> 
> $layer->set("data","the_geom from(select sheet.the_geom as the_geom, 
> userpolygon.the_geom as polygeom, sheet.oid, case when sheet.seriesid=1 
> AND distance(sheet.the_geom,userpolygon.the_geom)=0 then 1 end as 
> myclass from sheet, userpolygon where sheet.the_geom && 
> userpolygon.the_geom and sheet.seriesid=1 and userpolygon.id=2) as foo 
> using UNIQUE=sheet.oid using SRID=-1");
> 
> On my linux server with 3.6.4 release I get the following displayed on 
> the web page:
> 
> Warning: MapServer Error in prep_DB(): Error executing POSTGIS DECLARE 
> (the actual query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT 
> asbinary(force_collection(force_2d(the_geom from(select sheet.the_geom 
> as the_geom, userpolygon.the_geom as polygeom, sheet.oid, case when 
> sheet.seriesid=1 AND distance(sheet.the_geom,userpolygon.the_geom)=0 
> then 1 end as myclass)),'NDR'),OID::text from sheet, userpolygon where 
> sheet.the_geom && userpolygon.the_geom and sheet.seriesid=1 and 
> userpolygon.id=2) as foo using UNIQUE=sheet.oid WHERE the_geom 
> from(select sheet.the_geom as the_geom, userpolygon.the_geom as 
> polygeom, sheet.oid, case when sheet.seriesid=1 AND 
> distance(sheet.the_geom,userpolygon.the_geom)=0 then 1 end as myclass && 
> setSRID('BOX3D(-180 -135,180 135)'::BOX3D, -1 )'
> 
> Postgresql reports the error 'ERROR: parser: parse error at or near "from" '
> 
> More Help:
> 
> Error with POSTGIS data variable. You specified '<check your .map file>'.
> Standard ways of specifiying are :
> ( in /var/mapsearch/postgis/index.php on line 16
> 
> Fatal error: Call to a member function on a non-object in 
> /var/mapsearch/postgis/index.php on line 17
> 
> Thanks,
> 
> Benjamin Wragg
> 
> 
> -----Original Message-----
> From: mapserver-users-admin at lists.gis.umn.edu 
> [mailto:mapserver-users-admin at lists.gis.umn.edu] On Behalf Of Jan Hartmann
> Sent: Saturday, 22 February 2003 2:39 AM
> To: mapserver-users at lists.gis.umn.edu
> Subject: Re: [Mapserver-users] Classes based on Postgis && function
> 
> 
> I don't see it: the parentheses don't match up, and "using UNIQUE"
> stands somewhere in the middle, before the "WHERE" clause (the PostGIS
> error messages really got better, didn't they). Could you please post
> 
> 1) the SQL statement that worked when you put it to PSQL
> 2) the exact DATA statement in you Mapfile or PHP script
> 
> Jan
> 
> 
> Benjamin Wragg wrote:
>  > I've tried the code on another machine, which has the 3.6.4 release
>  > and
>  > I get a slightly better error:
>  >       
>  > Warning: MapServer Error in prep_DB(): Error executing POSTGIS DECLARE
>  > (the actual query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT
>  > sbinary(force_collection(force_2d(the_geom from(select sheet.the_geom as
>  > the_geom, userpolygon.the_geom as polygeom, sheet.oid, case when
>  > sheet.seriesid=1 and distance(sheet.the_geom, userpolygon.the_geom)=0
>  > then 1 end as myclass)),'NDR'),OID::text from sheet,userpolygon where
>  > sheet.the_geom && userpolygon.the_geom and sheet.seriesid=1 and
>  > userpolygon.id=2) as foo using UNIQUE=sheet.oid WHERE the_geom
>  > from(select sheet.the_geom as the_geom, userpolygon.the_geom as
>  > polygeom, sheet.oid, case when sheet.seriesid=1 and
>  > distance(sheet.the_geom,userpolygon.the_geom)=0 then 1 end as myclass &&
>  > setSRID('BOX3D(-180 -135,180 135)'::BOX3D, -1 )'
>  >
>  > Postgresql reports the error 'ERROR: parser: parse error at or near
>  > "from" '
>  >
>  > More Help:
>  >
>  > Error with POSTGIS data variable. You specified '<check your .map
>  > file>'. Standard ways of specifiying are :
>  >
>  > The 'from' part is the same error that the database gives back. I
>  > copied
>  > the above sql and ran in over the database and it seems to
>  > be complaining about the first 'from'.
>  >
>  > Any suggestions?
>  >
>  > Thanks,
>  >
>  > Benjamin
>  >
> 
> _______________________________________________
> Mapserver-users mailing list
> Mapserver-users at lists.gis.umn.edu 
> http://lists.gis.umn.edu/mailman/listinfo/mapserver-users
> 

Jan Hartmann
Department of Geography
University of Amsterdam
jhart at frw.uva.nl