[Mapserver-users] Classes based on Postgis && function
Jan Hartmann
jhart at frw.uva.nl
Thu Feb 20 04:12:38 PST 2003
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]
> 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=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
>
Jan Hartmann
Department of Geography
University of Amsterdam
jhart at frw.uva.nl
More information about the MapServer-users
mailing list