<And> tag of <Filter> works against shape but not oracle spatial

[tburton@…]

It appears as though the <And> tag of a <Filter> in a SLD, when used with a 
getMapRequest gives the expected result when the source of the data is ESRI 
shape, however when the same data is in Oracle spatial, gives a 500 Internal 
Server error in the browser.

I will attach the source shape file, an export of the table from oracle, and 
Layer entries as they are in my map file.

[tburton@…]

Here is entries in my map file

[tburton@…]

here is .shp

[tburton@…]

here is .dbf

[tburton@…]

here is .shx

[tburton@…]

database sld

[tburton@…]

shape file sld

[tburton@…]

dmp file from database

[fsimon@…]

Hi,
    Sorry, but I didn't understood the problem, I never worked with SLD. 
    Can you explain more about how did you used the SLD?
    Thanks.

Fernando Simon

[tburton@…]

SLD's are part of the OGC specification.  They can be included as part of a 
getMap request to get a visual representation of the data that is different 
then the default representation.

Here is an example of a get map request that includes an SLD:

http://gp.holonics.ca/cgi-bin/mapserv.exe?
REQUEST=GetMap&VERSION=1.1.0&WIDTH=360&HEIGHT=295&SRS=EPSG%
3A4326&STYLES=&TRANSPARENT=true&BBOX=-78.196%2C60.802%2C-78.186%
2C60.812&FORMAT=image%2Fgif&BGCOLOR=0xffffff&LAYERS=Bathymetry-HOLDEMIS-KATIVIK%
2CCountries-HOLDEMIS-KATIVIK%2CTopography-HOLDEMIS-KATIVIK%2CCoastlines-
HOLDEMIS-KATIVIK%2CKRG_Communities-KATIVIK%2CAkulivik_Bodies_of_Water-KATIVIK%
2CAkulivik_Zoning_Plan-KATIVIK%2CAkulivik_Rivers-KATIVIK%2CAkulivik_Buildings-
KATIVIK%2CAkulivik_Building_Names-KATIVIK%2CAkulivik_Building_Numbers-KATIVIK%
2CAkulivik_Roads-KATIVIK%
2CSHAPE_APP_DEVELOPMENT_POINTS&SLD=http://gp.holonics.ca/mapserver/kativik_commu
nity6.xml

Note that the only difference between this request and a typical getMap request 
is the:

&SLD=http://gp.holonics.ca/mapserver/kativik_community6.xml

which will 'style' my data.

If you were to translate my SLD into a sql statement, it would look something 
like:

select 'geometry'
from KCG_APP_DEVELOPMENT_POINTS
where ID = 20
and MAP_TYPE = 1;

where 'geometry' is suppose to return the geometries that match this query and 
style those geometries as a cyan star of size 10 as you will notice by this 
portion of the SLD:

<Graphic>
<Mark>
<WellKnownName>star</WellKnownName> 
<Fill>
<CssParameter name="fill">#ff0000</CssParameter> 
</Fill>
</Mark>
<Size>10.0</Size> 
</Graphic>

As a result, these geometries would be shown on top of all the other layers 
that I have listed.  Basically, this is just a simple filter on a layer using 
OGC standards.

This seems to work fine if there is only one attribute that I query on.  For 
example, if I just ask for ID = 20, I get my expected result.  The problem here 
is that when there are 2 attributes in my where condition, which represents the 
<And> tag in the <Filter>, I get the 500 error.

[fsimon@…]

Hi, just one little thing about, I don't know but in this version (4.2) you
can't do querys using Oracle connector. 
You can use querys only in 4.4 version, but to do this you need to add little
things in your data string, now you need to specify your unique column for the
table, usually the primary key. Like: data "geom from table USING UNIQUE pcolumn".
Sorry but query functions it isn't possible in the 4.2 version. Can you try the
4.4 version?

ps: my e-mail changed to fsimon@univali.br

[tburton@…]

It appears that there are bugs with respect to Oracle Spatial in version 4.4.

I've seen other people post issues in the USERS group.

As soon as I can get my hands on a new set of Windows binaries with the fix, 
I'll retest my issue.

[fsimon@…]

Hi, 
    Sorry for the delay.
    Just one question, can you test this for 4.4.1 version ? (Win binaries)
    Thanks.
Fernando Simon

[tburton@…]

I've checked my issue against the 4.4.1 version.  Although I no longer get an 
error, the filtering now only seems to work for the first two properties in my 
and filter.

So, my original issue of the filter not allowing two properties seems to be 
fixed, but now it does not seem to allow three properties.

For example:

....
....
<Rule>
<Filter>
<And>
<PropertyIsEqualTo>
<PropertyName>MAP_TYPE</PropertyName> 
<Literal>1</Literal>
</PropertyIsEqualTo>
<PropertyIsEqualTo>
<PropertyName>GTYPE</PropertyName> 
<Literal>1</Literal>
</PropertyIsEqualTo>
<PropertyIsEqualTo>
<PropertyName>APPLICATION_ID</PropertyName> 
<Literal>61</Literal>
</PropertyIsEqualTo>
</And>
</Filter>
....
....

If I translate this into SQL it will read:

where MAP_TYPE = 1
and GTYPE = 1
and APPLICATION_ID = 61

However, by submitting this SLD, it seems to only do:

where MAP_TYPE = 1
and GTYPE = 1

and ignores APPLICATION_ID = 61

I've tried switching around the order to verify that this is indeed how it 
works.

[fsimon@…]

Hi,
    Can be a SQL error, I don't define any max size for filter string, to check 
the SQL you can define your filter with only one option, in your case you can 
try to define only: "APPLICATION_ID = 61".
    Can you test it?
    Thanks.

Fernndo Simon

[tburton@…]

I'm not sure if I understand your question but, the filter works with just one 
parameter, and the <And> filter works with two parameters, but not with three.

[tburton@…]

There is no actual error given.  I get a valid map returning, it is just not 
filtering on the third parameter in an <And> filter.

[fsimon@…]

Hi,
    Sorry, I forgot to explain more my hint. I believe that filter can be 
wrong, can you test using only one parameter? (APLICATION_ID)
    Like: 
....
....
<Rule>
<Filter>
<And>
</PropertyIsEqualTo>
<PropertyIsEqualTo>
<PropertyName>APPLICATION_ID</PropertyName> 
<Literal>61</Literal>
</PropertyIsEqualTo>
</And>
</Filter>
....
....

     To check the internal SQL you can define one wrong data string and execute 
in CGI mode, if the data is wrong the mapserver/maporclespatial returns the 
internal SQL.
     Change: DATA "GEOMETRY FROM KCG_APP_DEVELOPMENT_POINTS" 
     for: DATA "GEOMETR FROM KCG_APP_DEVELOPMENT_POINTS"
     Thanks.

Fernando Simon

[fsimon@…]

Hi,
   My last hints worked?
   Can I close this bug?
   Thanks.

Fernando Simon

[fsimon@…]

Hi Trent,
    Sorry for the delay for reply this bug. This week I spoke with Assefa about
this bug and he found the problem.
    The problem is the SLD file, the correct way to define the FILTER with more
that 2 parameter is:
<Filter>
<And>
<And>
<PropertyIsEqualTo>
<PropertyName>MAP_TYPE</PropertyName>
<Literal>1</Literal>
</PropertyIsEqualTo>
<PropertyIsEqualTo>
<PropertyName>GTYPE</PropertyName>
<Literal>1</Literal>
</PropertyIsEqualTo>
</And>
<PropertyIsEqualTo>
<PropertyName>APPLICATION_ID</PropertyName>
<Literal>61</Literal>
</PropertyIsEqualTo>
</And>
</Filter>

     I used the same example that you used in bug report. 
     I tested with the last version (CVS version) and worked correctly. Here for
my test I used your dump as source of the data, I used the same layer definition
that you defined. I used this SLD FILTER:
<Filter>
<And>
<And>
<PropertyIsEqualTo>
<PropertyName>MAP_TYPE</PropertyName>
<Literal>1</Literal>
</PropertyIsEqualTo>
<PropertyIsEqualTo>
<PropertyName>GTYPE</PropertyName>
<Literal>1</Literal>
</PropertyIsEqualTo>
</And>
<PropertyIsEqualTo>
<PropertyName>FEATURE_ID</PropertyName>
<Literal>78</Literal>
</PropertyIsEqualTo>
</And>

    I tested with many filter option: FEATURE = 78, 79, 59, 52, 4 and both
worked correctly with 3 filter parameters.
    Can you test this with your version?
    Thanks.

[fsimon@…]

Hi,
    I will close the bug, the Assefa solution is working.
    More hints?
    Thanks.