[Mapserver-users] Classes based on Postgis && function
Benjamin Wragg
bwragg at tpg.com.au
Fri Feb 21 08:55:00 EST 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
)'<BR><BR>Postgresql reports the error 'ERROR: parser: parse error at or =
near=20
"from" '<BR><BR>More Help:<BR><BR>Error with POSTGIS data variable. You=20
specified '<check your .map file>'.<BR>Standard ways of =
specifiying are=20
:</FONT></FONT></P>
<P><FONT size=3D2><FONT face=3DArial>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'.</FONT></FONT></P>
<P><FONT size=3D2><FONT face=3DArial color=3D#0000ff>Any=20
suggestions?</FONT></FONT></P>
<P><FONT size=3D2><FONT face=3DArial =
color=3D#0000ff>Thanks,</FONT></FONT></P>
<P><FONT size=3D2><FONT face=3DArial color=3D#0000ff>Benjamin</FONT></P>
<P><BR><BR><BR><BR>-----Original Message-----<BR>From:=20
mapserver-users-admin at lists.gis.umn.edu [</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 Hartmann<BR>Sent: Thursday, 20 February 2003 10:43 =
PM<BR>To:=20
mapserver-users at lists.gis.umn.edu<BR>Subject: Re: [Mapserver-users] =
Classes=20
based on Postgis && function<BR><BR><BR>I reformatted your =
select=20
statement (always a good idea with SQL that<BR>complex), and it looks=20
OK.<BR><BR><BR>select the_geom from<BR> (select =
sheet.the_geom=20
as=20
the_geom,<BR> =
=20
userpolygon.the_geom as=20
polygeom,<BR> =
=20
sheet.oid,<BR>  =
; =20
case when=20
sheet.seriesid=3D1<BR> &nb=
sp; =20
and=20
distance(sheet.the_geom,userpolygon.the_geom)=3D0<BR> &n=
bsp; =20
then 1 end as myclass<BR> from sheet,=20
userpolygon<BR> where sheet.the_geom &&=20
userpolygon.the_geom<BR> and=20
sheet.seriesid=3D1<BR> and=20
userpolygon.id=3D2<BR> ) as foo<BR><BR><BR>If, =
as you say,=20
PSQL accepts it, the problem must be with<BR>MapServer/PostGIS. This =
could very=20
well be the case, as you seem to be<BR>using an old version of =
mappostgis.c,=20
which had problems with subselects<BR>from non-georeferenced data =
(SRID=3D-1).=20
There have been quite a few<BR>postings on the MapServer and PostGIS =
mailing=20
lists on this. So perhaps<BR>upgrading to the newest version will give =
you a map=20
at last.<BR><BR>Jan<BR><BR><BR>bwragg at tpg.com.au wrote:<BR>> Thanks =
for=20
pointing out the problems with my logic. I've decided to<BR>> put the =
second=20
class into<BR>> its own layer. I've got the following sql working and =
returning the right thing from psql:<BR>><BR>> select the_geom =
from(select=20
sheet.the_geom as the_geom,<BR>> userpolygon.the_geom as<BR>> =
polygeom,=20
sheet.oid ,case when sheet.seriesid=3D1 and =
distance(sheet.the_geom,<BR>>=20
userpolygon.the_geom)=3D0 then 1 end as myclass from sheet, userpolygon=20
where<BR>> sheet.the_geom && userpolygon.the_geom and=20
sheet.seriesid=3D1 and userpolygon.id=3D2 ) as<BR>> =
foo<BR>><BR>> If I add=20
this to the data property of a mapobj it crashes with the<BR>> =
following=20
error on the<BR>> mapObj->draw() function.<BR>><BR>> =
Warning:=20
MapServer Error in msPOSTGISLayerWhichShapes(): prep_DB:Error<BR>>=20
executing<BR>> POSTGIS DECLARE statement (0.6 failed - retried 0.5 =
and it=20
failed too). DECLARE mycursor<BR>> BINARY CURSOR FOR SELECT=20
asbinary(force_collection(force_2d(the_geom from(select<BR>> =
sheet.the_geom=20
as the_geom, userpolygon.the_geom as polygeom, sheet.oid ,case =
when<BR>>=20
sheet.seriesid=3D1 and distance(sheet.the_geom, =
userpolygon.the_geom)=3D0 then 1 end=20
as<BR>> myclass)),'NDR'),OID::text from sheet, userpolygon where=20
sheet.the_geom &&<BR>> userpolygon.the_geom and =
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 polygeom,<BR>> 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<BR>> (sheet.the_geom, =
userpolygon.the_geom)=3D0=20
then 1 end as myclass &&<BR>> setSRID('BOX3D(-180 - 135,180=20
135)'::BOX3D, find_srid('','sheet,<BR>> userpolygon where =
sheet.the_geom=20
&& userpolygon.the_geom and<BR>> sheet.seriesid=3D1 and=20
userpolygon.id=3D2 ) as foo','the_geom from (select<BR>> =
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') )<BR>> ERROR: parser: parse error at or near=20
"from"<BR>> DEBUG: AbortCurrentTransaction<BR>><BR>> I =
tried it=20
with the "using unique=3Dtable1.oid using srid=3D-1" on the end<BR>> =
but I get a=20
similar error:<BR>><BR>> Warning: MapServer Error in=20
msPOSTGISLayerWhichShapes(): prep_DB:Error<BR>> executing<BR>> =
POSTGIS=20
DECLARE statement (0.6 failed - retried 0.5 and it failed too). DECLARE=20
mycursor<BR>> BINARY CURSOR FOR SELECT=20
asbinary(force_collection(force_2d(the_geom from(select<BR>> =
sheet.the_geom=20
as the_geom, userpolygon.the_geom as polygeom, sheet.oid ,case =
when<BR>>=20
sheet.seriesid=3D1 and distance(sheet.the_geom, =
userpolygon.the_geom)=3D0 then 1 end=20
as<BR>> myclass)),'NDR'),OID::text from sheet, userpolygon where=20
sheet.the_geom &&<BR>> userpolygon.the_geom and =
sheet.seriesid=3D1 and=20
userpolygon.id=3D2 ) as foo using<BR>> unique=3Dtable1.oid 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 sheet.seriesid=3D1 and distance<BR>>=20
(sheet.the_geom, userpolygon.the_geom)=3D0 then 1 end as myclass =
&&=20
setSRID('BOX3D(-180 -<BR>> 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<BR>> ERROR: =
parser: parse=20
error at or near "from"<BR>><BR>> I can't figure out what "from" =
its=20
complaining about. Any ideas?<BR>><BR>> Thanks<BR>><BR>>=20
Benjamin<BR>><BR>><BR>> -----Original Message-----<BR>> =
From: Jan=20
Hartmann [</FONT><A href=3D"mailto:jhart at frw.uva.nl"><FONT=20
size=3D2>mailto:jhart at frw.uva.nl</FONT></A><FONT size=3D2>]<BR>> =
Sent: Thursday,=20
20 February 2003 12:10 AM<BR>> To: bwragg at tpg.com.au<BR>> Cc:=20
mapserver-users at lists.gis.umn.edu<BR>> Subject: Re: [Mapserver-users] =
Classes=20
based on Postgis && function<BR>><BR>><BR>> And of =
course the=20
inevitable typo: there should be a , after<BR>> table1.oid<BR>> in =
line=20
2.<BR>><BR>> The general syntax is:<BR>><BR>> select =
var1,var2,var3=20
from table<BR>><BR>> Each variable can be replaced by a named =
expression,=20
e.g.:<BR>><BR>> (var1+var2) as computedsum<BR>><BR>>=20
or:<BR>><BR>> case when <expression1> then=20
result1<BR>> when =
<expression2>=20
then result2<BR>> end as computedvar<BR>><BR>> The whole "case =
... end=20
as <name>" has to be be handled as a single<BR>> variable. This =
means=20
it should be surrounded by semicolons if preceded<BR>> or followed by =
another=20
variable.<BR>><BR>> This is reasonably difficult to find in the =
docs. It's=20
in the (7.2)<BR>> user's guide, chapter 4 (functions and operators), =
section=20
12<BR>> (conditional expressions). The complete URL for this section=20
is:<BR>><BR>> </FONT><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</FONT></A><BR><FONT=20
size=3D2>> ons-conditional.html<BR>><BR>> For other versions, =
start at=20
the docs homepage<BR>> (</FONT><A=20
href=3D"http://www.postgresql.org/docs/"><FONT=20
size=3D2>http://www.postgresql.org/docs/</FONT></A><FONT size=3D2>), or =
download the=20
PDFs.<BR>><BR>><BR>> As for your refined example, if I =
understand it=20
correctly, the second<BR>> class selects items that are already in =
the first=20
one. This won't work,<BR>> neither in MapServer nor in PostgreSQL. =
Once=20
MapServer or PG have<BR>> assigned an item to a class, they will =
proceed with=20
the next item. So<BR>> the second class will never be reached. You =
could=20
revert the classes<BR>> (put the most restricted one first), or =
exclude=20
explicitly the items for<BR>> class 2 from class 1:<BR>><BR>> =
select=20
case when type=3D1 and distance(geom1,geom2) > 0 ... then 1<BR>>=20
case when type=3D1 and =
distance(geom1,geom2) =3D 0=20
then 2<BR>> end as myvar<BR>><BR>> In the second case however,=20
"distance()" is called twice, and this is<BR>> horribly inefficient. =
To do=20
this efficiently, you would probably have to<BR>> write a stored =
procedure,=20
e.g. in PL/PGSQL.<BR>><BR>><BR>> Good luck,<BR>><BR>>=20
Jan<BR>><BR>><BR>> Benjamin Wragg wrote:<BR>><BR>>>Hi=20
Jan,<BR>>><BR>>>Thanks for your response. I tried putting =
the sql=20
you provided into my<BR>>>php script but received some errors =
regarding=20
the 'case' word. I set<BR>>>it up as shown=20
below:<BR>>><BR>>>...<BR>>>$layer->set("data","the_g=
eom=20
from (select 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=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>>=
;> =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>>=
;>><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--
More information about the mapserver-users
mailing list