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

[Jan Hartmann]

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=functions-conditional.html

For other versions, start at the docs homepage 
(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] 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
> 
> 

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