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

[Benjamin Wragg]

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] 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