[Geoserver-users] FEI and postgres char/varchar types (solved)

GeoServer GeoServer User
1

Here I come with an issue... and how to solve it.

I spent a few hours on a SLD trying to filter features using <PropertyIsEqualTo>. The layer comes from a postgis datastore.

<ogc:Filter>
   <ogc:PropertyIsEqualTo>
     <ogc:PropertyName>propertyname</ogc:PropertyName>
     <ogc:Literal>foobar</ogc:Literal>
   <ogc:PropertyIsEqualTo>
</ogc:Filter>

Although postgres returned features on a manual SQL query, I couln't catch any with PropertyIsEqualTo and had to use this in the SLD :

<ogc:Filter>
   <ogc:PropertyIsLike wildCard="*" singleChar="?" escape="\">
     <ogc:PropertyName>propertyname</ogc:PropertyName>
     <ogc:Literal>foobar*</ogc:Literal>
   </ogc:PropertyIsLike>
</ogc:Filter>

... Then I realized that the ogr2ogr import created the columns like this :

ALTER TABLE mytable ADD COLUMN propertyname char(5);

I changed this to varchar(5) and voila, FEI works with PropertyIsEqualTo and my SLD eyecandy is back (trailing spaces problem ? brutal type comparsion ?)

Being datastore independant, the FEI statements don't care about datastore specific types. This can lead to errors when dealing with char and varchar types.

2

Fabrice Phung ha scritto:

Here I come with an issue... and how to solve it.

I spent a few hours on a SLD trying to filter features using <PropertyIsEqualTo>. The layer comes from a postgis datastore.

<ogc:Filter>
   <ogc:PropertyIsEqualTo>
     <ogc:PropertyName>propertyname</ogc:PropertyName>
     <ogc:Literal>foobar</ogc:Literal>
   <ogc:PropertyIsEqualTo>
</ogc:Filter>

Although postgres returned features on a manual SQL query, I couln't catch any with PropertyIsEqualTo and had to use this in the SLD :

<ogc:Filter>
   <ogc:PropertyIsLike wildCard="*" singleChar="?" escape="\">
     <ogc:PropertyName>propertyname</ogc:PropertyName>
     <ogc:Literal>foobar*</ogc:Literal>
   </ogc:PropertyIsLike>
</ogc:Filter>

... Then I realized that the ogr2ogr import created the columns like this :

ALTER TABLE mytable ADD COLUMN propertyname char(5);

I changed this to varchar(5) and voila, FEI works with PropertyIsEqualTo and my SLD eyecandy is back (trailing spaces problem ? brutal type comparsion ?)

Trailing spaces, see here: http://www.postgresql.org/docs/8.2/static/datatype-character.html

In postgres char(x) is blank padded. The above filter is translated
into a straight sql query, "x" = "x " will fail and thus the filter
will break. I don't know about your shapefile structure, but I have
the impression you may have hit a bug in ogr2ogr (wheter or not it's
bug really depends on what the original data structure was, I mean, if
the original data structure was blank padded, it's not). You may want to report it to the ogr developers.

Cheers
Andrea