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

Fri Feb 21 05:55:00 PST 2003

This is a multi-part message in MIME format.

------=_NextPart_000_0001_01C2DA08.D6F047E0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

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

-----Original Message-----
From: mapserver-users-admin at lists.gis.umn.edu [
<mailto:mapserver-users-admin at lists.gis.umn.edu>
mailto:mapserver-users-admin at lists.gis.umn.edu] On Behalf Of Jan
Hartmann
Sent: Thursday, 20 February 2003 10:43 PM
To: mapserver-users at lists.gis.umn.edu
Subject: Re: [Mapserver-users] Classes based on Postgis && function

I reformatted your select statement (always a good idea with SQL that
complex), and it looks OK.

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

If, as you say, PSQL accepts it, the problem must be with
MapServer/PostGIS. This could very well be the case, as you seem to be
using an old version of mappostgis.c, which had problems with subselects
from non-georeferenced data (SRID=-1). There have been quite a few
postings on the MapServer and PostGIS mailing lists on this. So perhaps
upgrading to the newest version will give you a map at last.

Jan

bwragg at tpg.com.au wrote:
> Thanks for pointing out the problems with my logic. I've decided to
> put the second class into
> its own layer. I've got the following sql working and returning the
right thing from 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
>
> If I add this to the data property of a mapobj it crashes with the
> following error on the
> mapObj->draw() function.
>
> Warning: MapServer Error in msPOSTGISLayerWhichShapes(): prep_DB:Error
> executing
> POSTGIS DECLARE statement (0.6 failed - retried 0.5 and it failed
too). 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 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,
find_srid('','sheet,
> userpolygon where sheet.the_geom && userpolygon.the_geom and
sheet.seriesid=1 and
> userpolygon.id=2 ) as foo','the_geom from(select sheet.the_geom as
the_geom,
> userpolygon.the_geom as polygeom, she in...
>
> Postgres gives the following error at this same point:
>
> DEBUG:  query: 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 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, find_srid('','sheet,
> userpolygon where sheet.the_geom && userpolygon.the_geom and
> sheet.seriesid=1 and userpolygon.id=2 ) as foo','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') )
> ERROR:  parser: parse error at or near "from"
> DEBUG:  AbortCurrentTransaction
>
> I tried it with the "using unique=table1.oid using srid=-1" on the end
> but I get a similar error:
>
> Warning: MapServer Error in msPOSTGISLayerWhichShapes(): prep_DB:Error
> executing
> POSTGIS DECLARE statement (0.6 failed - retried 0.5 and it failed
too). 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=table1.oid using srid=-1 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, find_srid('','sheet, userpolygon where
sheet.the_geom &&
> userpolygon.the_geom and sheet.seriesid=1 and userpolygon.id=2 ) as
foo using
> unique=table1.oid using srid=-1','the_geom f in ...
>
> And Postgres gives the following error:
>
> DEBUG:  query: 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=table1.oid
> using srid=-1 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 && 'BOX3D(-180 -135,180 135)'::BOX3D
> ERROR:  parser: parse error at or near "from"
>
> I can't figure out what "from" its complaining about. Any ideas?
>
> Thanks
>
> Benjamin
>
>
> -----Original Message-----
> From: Jan Hartmann [ <mailto:jhart at frw.uva.nl>
mailto:jhart at frw.uva.nl]
> Sent: Thursday, 20 February 2003 12:10 AM
> To: bwragg at tpg.com.au
> Cc: mapserver-users at lists.gis.umn.edu
> Subject: Re: [Mapserver-users] Classes based on Postgis && function
>
>
> And of course the inevitable typo: there should be a , after
> table1.oid
> in line 2.
>
> The general syntax is:
>
> select var1,var2,var3 from table
>
> Each variable can be replaced by a named expression, e.g.:
>
> (var1+var2) as computedsum
>
> or:
>
> case when <expression1> then result1
>       when <expression2> then result2
> end as computedvar
>
> The whole "case ... end as <name>" has to be be handled as a single
> variable. This means it should be surrounded by semicolons if preceded
> or followed by another variable.
>
> This is reasonably difficult to find in the docs. It's in the (7.2)
> user's guide, chapter 4 (functions and operators), section 12
> (conditional expressions). The complete URL for this section is:
>
>
<http://www.postgresql.org/docs/view.php?version=7.2&idoc=0&file=functi>
http://www.postgresql.org/docs/view.php?version=7.2&idoc=0&file=functi
> ons-conditional.html
>
> For other versions, start at the docs homepage
> ( <http://www.postgresql.org/docs/> http://www.postgresql.org/docs/),
or download the PDFs.
>
>
> As for your refined example, if I understand it correctly, the second
> class selects items that are already in the first one. This won't
work,
> neither in MapServer nor in PostgreSQL. Once MapServer or PG have
> assigned an item to a class, they will proceed with the next item. So
> the second class will never be reached. You could revert the classes
> (put the most restricted one first), or exclude explicitly the items
for
> class 2 from class 1:
>
> select case when type=1 and distance(geom1,geom2) > 0 ... then 1
>       case when type=1 and distance(geom1,geom2) = 0 then 2
> end as myvar
>
> In the second case however, "distance()" is called twice, and this is
> horribly inefficient. To do this efficiently, you would probably have
to
> write a stored procedure, e.g. in PL/PGSQL.
>
>
> Good luck,
>
> Jan
>
>
> Benjamin Wragg wrote:
>
>>Hi Jan,
>>
>>Thanks for your response. I tried putting the sql you provided into my
>>php script but received some errors regarding the 'case' word. I set
>>it up as shown below:
>>
>>...
>>$layer->set("data","the_geom from (select table1.the_geom from
>>     ( select table1.the_geom,table2.the_geom, table1.oid
>>         case when table1.fieldX = 'x' then 1
>>              when table1.the_geom && table2.the_geom then 2
>>         end as myclass
>>         from table1,table2
>>         where table1.id = table2.id
>>     ) as foo using unique=table1.oid using srid=-1)");
>>...
>>$layer->set("classitem", "myclass"); $class=ms_newClassObj($layer);
>>$class->set("expression", "1"); $class->set("name", "All");
>>$colorId=$GLOBALS['map']->addColor(255,0,0);
>>$class->set("color", $colorId);
>>$class->set("size", 20);
>>$class->set("symbol", 2);
>>
>>$class=ms_newClassObj($layer);
>>$class->set("expression", "2");
>>$class->set("name", "Other");
>>$colorId=$GLOBALS['map']->addColor(255,0,0);
>>$class->set("color", $colorId);
>>$class->set("size", 20);
>>$class->set("symbol", 2);
>>
>>It complains about the 'case' word. I can't find the 'case' statement
>>in any psql docs. Is it a psql or a mapscript statement?
>>
>>I thought I should refine what I trying to do a little better.  I'm
>>trying to display, in one class, all the features in a table where a
>>field equals a certain value. This will return a couple hundred
>>features. Then in a second class I'm trying to display all the
>>features where the same field equals this same value but only features
>>that are intersected by a feature in another table.
>>
>>So I need to get the following sql statement translated into a
>>layer/class setup in MapScript:
>>
>>      SELECT sheet.thegeom
>>      FROM sheet, userdefinedshape
>>      WHERE sheet.the_geom && userdefinedshape.the_geom
>>              AND distance(sheet.the_geom,
>>userdefinedshape.the_geom)=0
>>              AND userdefinedshape.id='1'
>>              AND sheet.type='something';
>>
>>I played around with your example but couldn't get my problem to fit.
>>Any suggestions?
>>
>>Thanks,
>>
>>Benjamin
>>
>>-----Original Message-----
>>From: mapserver-users-admin at lists.gis.umn.edu
>>[ <mailto:mapserver-users-admin at lists.gis.umn.edu>
mailto:mapserver-users-admin at lists.gis.umn.edu] On Behalf Of Jan
>>Hartmann
>>Sent: Tuesday, 18 February 2003 11:39 PM
>>To: bwragg at tpg.com.au
>>Cc: mapserver-users at lists.gis.umn.edu
>>Subject: Re: [Mapserver-users] Classes based on Postgis && function
>>
>>
>>Benjamin,
>>
>>You can use named computed variables in PostgreSQL:
>>
>>select table1.the_geom from
>>     ( select table1.the_geom,table2.the_geom, table1.oid
>>         case when table1.fieldX = 'x' then 1
>>              when table1.the_geom && table2.the_geom then 2
>>         end as myclass
>>         from table1,table2
>>         where table1.id = table2.id
>>     ) as foo using unique=table1.oid using srid=...
>>
>>after which you can use the new computed variable "myclass" as a
>>regular
>>
>>MapServer CLASSITEM. The computed values of this variable go into the
>>EXPRESSION statement:
>>
>>CLASSITEM myclass
>>CLASS
>>    EXPRESSION 1
>>    COLOR ...
>>    ...
>>END
>>CLASS
>>    EXPRESSION 2
>>    COLOR ...
>>    ...
>>END
>>
>>Jan
>>
>>Benjamin Wragg wrote:
>>
>>
>>>Hi guys,
>>>
>>>Does anyone know if its possible to have a layer with two classes,
>>>one with a simple SQL expression and the other with a SQL statement
>>>that uses a PostGIS spatial function. Something like the following
>>>logic:
>>>
>>>class 1 = where table1.fieldX = 'x'
>>>class 2 = where table1.fieldX = 'x' and table1.the_geom &&
>>>table2.the_geom and table2.fieldY='Y'
>>>
>>>I can do it in two layers, but is it possible in one layer with two
>>>classes?
>>>
>>>Cheers,
>>>
>>>Benjamin
>>
>>
>>Jan Hartmann
>>Department of Geography
>>University of Amsterdam
>>jhart at frw.uva.nl
>>
>>_______________________________________________
>>Mapserver-users mailing list Mapserver-users at lists.gis.umn.edu
>> <http://lists.gis.umn.edu/mailman/listinfo/mapserver-users>
http://lists.gis.umn.edu/mailman/listinfo/mapserver-users
>>
>>
>
>
> Jan Hartmann
> Department of Geography
> University of Amsterdam
> jhart at frw.uva.nl
>
Jan Hartmann
Department of Geography
University of Amsterdam
jhart at frw.uva.nl

_______________________________________________
Mapserver-users mailing list
Mapserver-users at lists.gis.umn.edu
<http://lists.gis.umn.edu/mailman/listinfo/mapserver-users>
http://lists.gis.umn.edu/mailman/listinfo/mapserver-users

------=_NextPart_000_0001_01C2DA08.D6F047E0
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">
<TITLE>Message</TITLE>

<META content=3D"MSHTML 6.00.2800.1106" name=3DGENERATOR></HEAD>
<BODY><!-- Converted from text/plain format -->
<P><FONT size=3D2><FONT face=3DTahoma><FONT face=3DArial>I've tried the =
code on=20
another machine, which has the 3.6.4 release and I get a slightly better =

error:</FONT><BR>       <BR></FONT><FO=
NT=20
face=3DCourier>Warning: MapServer Error in prep_DB(): Error executing =
POSTGIS=20
DECLARE (the actual query) statement: 'DECLARE mycursor BINARY CURSOR =
FOR SELECT=20
sbinary(force_collection(force_2d(the_geom from(select sheet.the_geom as =

the_geom, userpolygon.the_geom as polygeom, sheet.oid, case when=20
sheet.seriesid=3D1 and distance(sheet.the_geom, =
userpolygon.the_geom)=3D0 then 1 end=20
as myclass)),'NDR'),OID::text from sheet,userpolygon where =
sheet.the_geom=20
&& userpolygon.the_geom and sheet.seriesid=3D1 and =
userpolygon.id=3D2) as=20
foo using UNIQUE=3Dsheet.oid WHERE the_geom from(select sheet.the_geom =
as=20
the_geom, userpolygon.the_geom as polygeom, sheet.oid, case when=20
sheet.seriesid=3D1 and distance(sheet.the_geom,userpolygon.the_geom)=3D0 =
then 1 end=20
as myclass && setSRID('BOX3D(-180 -135,180 135)'::BOX3D, -1=20
)' Postgresql reports the error 'ERROR: parser: parse error at or =
near=20
"from" ' More Help: Error with POSTGIS data variable. You=20
specified '<check your .map file>'. Standard ways of =
specifiying are=20
:
The 'from' part is the same error =
that the=20
database gives back. I copied the above sql and ran in over the database =
and it=20
seems to be complaining about the first 'from'.
Any=20
suggestions?
Thanks,
Benjamin
 -----Original Message----- From:=20
mapserver-users-admin at lists.gis.umn.edu [<A=20
href=3D"mailto:mapserver-users-admin at lists.gis.umn.edu"><FONT=20
size=3D2>mailto:mapserver-users-admin at lists.gis.umn.edu</A>]=20
On Behalf Of Jan Hartmann Sent: Thursday, 20 February 2003 10:43 =
PM To:=20
mapserver-users at lists.gis.umn.edu Subject: Re: [Mapserver-users] =
Classes=20
based on Postgis && function I reformatted your =
select=20
statement (always a good idea with SQL that complex), and it looks=20
OK. select the_geom from     (select =
sheet.the_geom=20
as=20
the_geom,           =
 =20
userpolygon.the_geom as=20
polygeom,           =
 =20
sheet.oid,          &nbsp=
; =20
case when=20
sheet.seriesid=3D1         &nb=
sp;       =20
and=20
distance(sheet.the_geom,userpolygon.the_geom)=3D0    &n=
bsp;       =20
then 1 end as myclass      from sheet,=20
userpolygon      where sheet.the_geom &&=20
userpolygon.the_geom        and=20
sheet.seriesid=3D1        and=20
userpolygon.id=3D2      ) as foo If, =
as you say,=20
PSQL accepts it, the problem must be with MapServer/PostGIS. This =
could very=20
well be the case, as you seem to be using an old version of =
mappostgis.c,=20
which had problems with subselects from non-georeferenced data =
(SRID=3D-1).=20
There have been quite a few postings on the MapServer and PostGIS =
mailing=20
lists on this. So perhaps upgrading to the newest version will give =
you a map=20
at last. Jan bwragg at tpg.com.au wrote: > Thanks =
for=20
pointing out the problems with my logic. I've decided to > put the =
second=20
class into > its own layer. I've got the following sql working and =

returning the right thing from psql: > > select the_geom =
from(select=20
sheet.the_geom as the_geom, > userpolygon.the_geom as > =
polygeom,=20
sheet.oid ,case when sheet.seriesid=3D1 and =
distance(sheet.the_geom, >=20
userpolygon.the_geom)=3D0 then 1 end as myclass from sheet, userpolygon=20
where > sheet.the_geom && userpolygon.the_geom and=20
sheet.seriesid=3D1 and userpolygon.id=3D2 ) as > =
foo > > If I add=20
this to the data property of a mapobj it crashes with the > =
following=20
error on the > mapObj->draw() function. > > =
Warning:=20
MapServer Error in msPOSTGISLayerWhichShapes(): prep_DB:Error >=20
executing > POSTGIS DECLARE statement (0.6 failed - retried 0.5 =
and it=20
failed too). DECLARE mycursor > BINARY CURSOR FOR SELECT=20
asbinary(force_collection(force_2d(the_geom from(select > =
sheet.the_geom=20
as the_geom, userpolygon.the_geom as polygeom, sheet.oid ,case =
when >=20
sheet.seriesid=3D1 and distance(sheet.the_geom, =
userpolygon.the_geom)=3D0 then 1 end=20
as > myclass)),'NDR'),OID::text from sheet, userpolygon where=20
sheet.the_geom && > userpolygon.the_geom and =
sheet.seriesid=3D1 and=20
userpolygon.id=3D2 ) as foo WHERE the_geom > from(select =
sheet.the_geom as=20
the_geom, userpolygon.the_geom as polygeom, > sheet.oid ,case when =

sheet.seriesid=3D1 and distance(sheet.the_geom, =
userpolygon.the_geom)<BR>> =3D0=20
then 1 end as myclass && setSRID('BOX3D(-180 -135,180 =
135)'::BOX3D,=20
find_srid('','sheet,<BR>> userpolygon where sheet.the_geom && =

userpolygon.the_geom and sheet.seriesid=3D1 and<BR>> =
userpolygon.id=3D2 ) as=20
foo','the_geom from(select sheet.the_geom as the_geom,<BR>>=20
userpolygon.the_geom as polygeom, she in...<BR>><BR>> Postgres =
gives the=20
following error at this same point:<BR>><BR>> DEBUG:  query: =
DECLARE=20
mycursor BINARY CURSOR FOR SELECT<BR>> asbinary(force_collection=20
(force_2d(the_geom from(select<BR>> sheet.the_geom as the_geom,=20
userpolygon.the_geom as polygeom,<BR>> sheet.oid ,case when =
sheet.seriesid=3D1=20
and distance(sheet.the_geom,<BR>> userpolygon.the_geom)=3D0 then 1 =
end as=20
myclass)),'NDR'),OID::text from<BR>> sheet, userpolygon where =
sheet.the_geom=20
&& userpolygon.the_geom and<BR>> sheet.seriesid=3D1 and=20
userpolygon.id=3D2 ) as foo WHERE the_geom<BR>> from(select =
sheet.the_geom as=20
the_geom, userpolygon.the_geom as<BR>> polygeom, sheet.oid ,case when =

sheet.seriesid=3D1 and distance > (sheet.the_geom, =
userpolygon.the_geom)=3D0=20
then 1 end as myclass && > setSRID('BOX3D(-180 - 135,180=20
135)'::BOX3D, find_srid('','sheet, > userpolygon where =
sheet.the_geom=20
&& userpolygon.the_geom and > sheet.seriesid=3D1 and=20
userpolygon.id=3D2 ) as foo','the_geom from (select > =
sheet.the_geom as=20
the_geom, userpolygon.the_geom as polygeom, sheet.oid ,case when=20
sheet.seriesid=3D1 and distance(sheet.the_geom, =
userpolygon.the_geom)=3D0 then 1 end=20
as myclass') ) > ERROR:  parser: parse error at or near=20
"from" > DEBUG:  AbortCurrentTransaction > > I =
tried it=20
with the "using unique=3Dtable1.oid using srid=3D-1" on the end > =
but I get a=20
similar error: > > Warning: MapServer Error in=20
msPOSTGISLayerWhichShapes(): prep_DB:Error > executing > =
POSTGIS=20
DECLARE statement (0.6 failed - retried 0.5 and it failed too). DECLARE=20
mycursor > BINARY CURSOR FOR SELECT=20
asbinary(force_collection(force_2d(the_geom from(select > =
sheet.the_geom=20
as the_geom, userpolygon.the_geom as polygeom, sheet.oid ,case =
when >=20
sheet.seriesid=3D1 and distance(sheet.the_geom, =
userpolygon.the_geom)=3D0 then 1 end=20
as > myclass)),'NDR'),OID::text from sheet, userpolygon where=20
sheet.the_geom && > userpolygon.the_geom and =
sheet.seriesid=3D1 and=20
userpolygon.id=3D2 ) as foo using > unique=3Dtable1.oid using =
srid=3D-1 WHERE=20
the_geom from(select sheet.the_geom as the_geom, > =
userpolygon.the_geom as=20
polygeom, sheet.oid ,case when sheet.seriesid=3D1 and distance >=20
(sheet.the_geom, userpolygon.the_geom)=3D0 then 1 end as myclass =
&&=20
setSRID('BOX3D(-180 - > 135,180 135)'::BOX3D, find_srid('','sheet, =

userpolygon where sheet.the_geom &&<BR>> userpolygon.the_geom =
and=20
sheet.seriesid=3D1 and userpolygon.id=3D2 ) as foo using<BR>> =
unique=3Dtable1.oid=20
using srid=3D-1','the_geom f in ...<BR>><BR>> And Postgres gives =
the=20
following error:<BR>><BR>> DEBUG:  query: DECLARE mycursor =
BINARY=20
CURSOR FOR SELECT<BR>> asbinary(force_collection (force_2d(the_geom=20
from(select<BR>> sheet.the_geom as the_geom, userpolygon.the_geom as=20
polygeom,<BR>> sheet.oid ,case when sheet.seriesid=3D1 and=20
distance(sheet.the_geom,<BR>> userpolygon.the_geom)=3D0 then 1 end as =

myclass)),'NDR'),OID::text from<BR>> sheet, userpolygon where =
sheet.the_geom=20
&& userpolygon.the_geom and<BR>> sheet.seriesid=3D1 and=20
userpolygon.id=3D2 ) as foo using unique=3Dtable1.oid<BR>> using =
srid=3D-1 WHERE=20
the_geom from(select sheet.the_geom as the_geom,<BR>> =
userpolygon.the_geom as=20
polygeom, sheet.oid ,case when<BR>> sheet.seriesid=3D1 and=20
distance(sheet.the_geom, userpolygon.the_geom)=3D0 then 1 end as myclass =

&& 'BOX3D(-180 -135,180 135)'::BOX3D > ERROR:  =
parser: parse=20
error at or near "from" > > I can't figure out what "from" =
its=20
complaining about. Any ideas? > > Thanks > >=20
Benjamin > > > -----Original Message----- > =
From: Jan=20
Hartmann [<A href=3D"mailto:jhart at frw.uva.nl"><FONT=20
size=3D2>mailto:jhart at frw.uva.nl</A>] > =
Sent: Thursday,=20
20 February 2003 12:10 AM > To: bwragg at tpg.com.au > Cc:=20
mapserver-users at lists.gis.umn.edu > Subject: Re: [Mapserver-users] =
Classes=20
based on Postgis && function > > > And of =
course the=20
inevitable typo: there should be a , after > table1.oid > in =
line=20
2. > > The general syntax is: > > select =
var1,var2,var3=20
from table > > Each variable can be replaced by a named =
expression,=20
e.g.: > > (var1+var2) as computedsum > >=20
or: > > case when <expression1> then=20
result1 >       when =
<expression2>=20
then result2 > end as computedvar > > The whole "case =
... end=20
as <name>" has to be be handled as a single > variable. This =
means=20
it should be surrounded by semicolons if preceded > or followed by =
another=20
variable. > > This is reasonably difficult to find in the =
docs. It's=20
in the (7.2) > user's guide, chapter 4 (functions and operators), =
section=20
12 > (conditional expressions). The complete URL for this section=20
is: > > <A=20
href=3D"http://www.postgresql.org/docs/view.php?version=3D7.2&idoc=3D=
0&file=3Dfuncti"><FONT=20
size=3D2>http://www.postgresql.org/docs/view.php?version=3D7.2&idoc=3D=
0&file=3Dfuncti</A> <FONT=20
size=3D2>> ons-conditional.html > > For other versions, =
start at=20
the docs homepage > (<A=20
href=3D"http://www.postgresql.org/docs/"><FONT=20
size=3D2>http://www.postgresql.org/docs/</A>), or =
download the=20
PDFs. > > > As for your refined example, if I =
understand it=20
correctly, the second > class selects items that are already in =
the first=20
one. This won't work, > neither in MapServer nor in PostgreSQL. =
Once=20
MapServer or PG have > assigned an item to a class, they will =
proceed with=20
the next item. So > the second class will never be reached. You =
could=20
revert the classes > (put the most restricted one first), or =
exclude=20
explicitly the items for > class 2 from class 1: > > =
select=20
case when type=3D1 and distance(geom1,geom2) > 0 ... then 1 >=20
      case when type=3D1 and =
distance(geom1,geom2) =3D 0=20
then 2 > end as myvar > > In the second case however,=20
"distance()" is called twice, and this is > horribly inefficient. =
To do=20
this efficiently, you would probably have to > write a stored =
procedure,=20
e.g. in PL/PGSQL. > > > Good luck, > >=20
Jan > > > Benjamin Wragg wrote: > >>Hi=20
Jan, >> >>Thanks for your response. I tried putting =
the sql=20
you provided into my >>php script but received some errors =
regarding=20
the 'case' word. I set >>it up as shown=20
below: >> >>... >>$layer->set("data","the_g=
eom=20
from (select table1.the_geom from >>     ( =
select=20
table1.the_geom,table2.the_geom,=20
table1.oid >>         =
case when=20
table1.fieldX =3D 'x' then=20
1 >>          =
   =20
when table1.the_geom && table2.the_geom then=20
2 >>         end as=20
myclass >>         from =

table1,table2<BR>>>        =
 where=20
table1.id =3D table2.id<BR>>>     ) as foo =
using=20
unique=3Dtable1.oid using=20
srid=3D-1)");<BR>>>...<BR>>>$layer->set("classitem", =
"myclass");=20
$class=3Dms_newClassObj($layer);<BR>>>$class->set("expression", =
"1");=20
$class->set("name",=20
"All");<BR>>>$colorId=3D$GLOBALS['map']->addColor(255,0,0);<BR>&=
gt;>$class->set("color",=20
$colorId);<BR>>>$class->set("size",=20
20);<BR>>>$class->set("symbol",=20
2);<BR>>><BR>>>$class=3Dms_newClassObj($layer);<BR>>>$c=
lass->set("expression",=20
"2");<BR>>>$class->set("name",=20
"Other");<BR>>>$colorId=3D$GLOBALS['map']->addColor(255,0,0);<BR=
>>>$class->set("color",=20
$colorId);<BR>>>$class->set("size",=20
20);<BR>>>$class->set("symbol", 2);<BR>>><BR>>>It =
complains=20
about the 'case' word. I can't find the 'case' statement<BR>>>in =
any psql=20
docs. Is it a psql or a mapscript statement?<BR>>><BR>>>I =
thought I=20
should refine what I trying to do a little better.  =
I'm<BR>>>trying=20
to display, in one class, all the features in a table where =
a<BR>>>field=20
equals a certain value. This will return a couple =
hundred<BR>>>features.=20
Then in a second class I'm trying to display all the<BR>>>features =
where=20
the same field equals this same value but only features<BR>>>that =
are=20
intersected by a feature in another table.<BR>>><BR>>>So I =
need to=20
get the following sql statement translated into a<BR>>>layer/class =
setup=20
in MapScript:<BR>>><BR>>>      =
SELECT=20
sheet.thegeom<BR>>>      FROM sheet,=20
userdefinedshape<BR>>>      WHERE =
sheet.the_geom=20
&& =
userdefinedshape.the_geom<BR>>>     =20
        AND=20
distance(sheet.the_geom,<BR>>>userdefinedshape.the_geom)=3D0<BR>&gt=
;>     =20
        AND=20
userdefinedshape.id=3D'1'<BR>>>     =20
        AND=20
sheet.type=3D'something';<BR>>><BR>>>I played around with =
your example=20
but couldn't get my problem to fit.<BR>>>Any=20
suggestions?<BR>>><BR>>>Thanks,<BR>>><BR>>>Benjam=
in<BR>>><BR>>>-----Original=20
Message-----<BR>>>From:=20
mapserver-users-admin at lists.gis.umn.edu<BR>>>[</FONT><A=20
href=3D"mailto:mapserver-users-admin at lists.gis.umn.edu"><FONT=20
size=3D2>mailto:mapserver-users-admin at lists.gis.umn.edu</FONT></A><FONT =
size=3D2>]=20
On Behalf Of Jan<BR>>>Hartmann<BR>>>Sent: Tuesday, 18 =
February 2003=20
11:39 PM<BR>>>To: bwragg at tpg.com.au<BR>>>Cc:=20
mapserver-users at lists.gis.umn.edu<BR>>>Subject: Re: =
[Mapserver-users]=20
Classes based on Postgis &&=20
function<BR>>><BR>>><BR>>>Benjamin,<BR>>><BR>>=
>You=20
can use named computed variables in =
PostgreSQL:<BR>>><BR>>>select=20
table1.the_geom from<BR>>>     ( select=20
table1.the_geom,table2.the_geom,=20
table1.oid<BR>>>         =
case when=20
table1.fieldX =3D 'x' then=20
1<BR>>>          =
   =20
when table1.the_geom && table2.the_geom then=20
2<BR>>>         end as=20
myclass<BR>>>         from =

table1,table2<BR>>>        =
 where=20
table1.id =3D table2.id<BR>>>     ) as foo =
using=20
unique=3Dtable1.oid using srid=3D...<BR>>><BR>>>after which =
you can use=20
the new computed variable "myclass" as=20
a<BR>>>regular<BR>>><BR>>>MapServer CLASSITEM. The =
computed=20
values of this variable go into the<BR>>>EXPRESSION=20
statement:<BR>>><BR>>>CLASSITEM=20
myclass<BR>>>CLASS<BR>>>    EXPRESSION=20
1<BR>>>    COLOR ...<BR>>>   =20
...<BR>>>END<BR>>>CLASS<BR>>>    =
EXPRESSION=20
2<BR>>>    COLOR ...<BR>>>   =20
...<BR>>>END<BR>>><BR>>>Jan<BR>>><BR>>>Benj=
amin=20
Wragg wrote:<BR>>><BR>>><BR>>>>Hi=20
guys,<BR>>>><BR>>>>Does anyone know if its possible to =
have a=20
layer with two classes,<BR>>>>one with a simple SQL expression =
and the=20
other with a SQL statement<BR>>>>that uses a PostGIS spatial =
function.=20
Something like the=20
following<BR>>>>logic:<BR>>>><BR>>>>class 1 =
=3D where=20
table1.fieldX =3D 'x'<BR>>>>class 2 =3D where table1.fieldX =3D =
'x' and=20
table1.the_geom &&<BR>>>>table2.the_geom and=20
table2.fieldY=3D'Y'<BR>>>><BR>>>>I can do it in two =
layers, but=20
is it possible in one layer with=20
two<BR>>>>classes?<BR>>>><BR>>>>Cheers,<BR>&gt=
;>><BR>>>>Benjamin<BR>>><BR>>><BR>>>Jan =

Hartmann<BR>>>Department of Geography<BR>>>University of=20
Amsterdam<BR>>>jhart at frw.uva.nl<BR>>><BR>>>____________=
___________________________________<BR>>>Mapserver-users=20
mailing list Mapserver-users at lists.gis.umn.edu<BR>>></FONT><A=20
href=3D"http://lists.gis.umn.edu/mailman/listinfo/mapserver-users"><FONT =

size=3D2>http://lists.gis.umn.edu/mailman/listinfo/mapserver-users</FONT>=
</A><BR><FONT=20
size=3D2>>><BR>>><BR>><BR>><BR>> Jan =
Hartmann<BR>>=20
Department of Geography<BR>> University of Amsterdam<BR>>=20
jhart at frw.uva.nl<BR>><BR>Jan Hartmann<BR>Department of=20
Geography<BR>University of=20
Amsterdam<BR>jhart at frw.uva.nl<BR><BR>____________________________________=
___________<BR>Mapserver-users=20
mailing list<BR>Mapserver-users at lists.gis.umn.edu </FONT><A=20
href=3D"http://lists.gis.umn.edu/mailman/listinfo/mapserver-users"><FONT =

size=3D2>http://lists.gis.umn.edu/mailman/listinfo/mapserver-users</FONT>=
</A><BR></P></BODY></HTML>

------=_NextPart_000_0001_01C2DA08.D6F047E0--