[Mapserver-users] Classes based on Postgis && function
bwragg at tpg.com.au
bwragg at tpg.com.au
Wed Feb 19 21:37:19 PST 2003
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
More information about the MapServer-users
mailing list