Expressions with enum types in postgis do not work

[artemp]

When using postgis enum types as filter expression, no feature is drawn on the map:

CREATE TYPE alert_level AS ENUM (
    'yellow',
    'orange',
    'red'
);

CREATE TABLE test_mapnik_enum (
    id integer NOT NULL,
    test_column alert_level,
    geom geometry -- created by addgeometry
);

And the Python code:

import mapnik2 as mapnik
proj4 = '+proj=utm +zone=30 +ellps=intl +units=m +no_defs'
ds=mapnik.PostGIS(dbname='sigym3-dev',table='test_mapnik_enum')

r=mapnik.Rule()
r.symbols.append(mapnik.PointSymbolizer())
r.filter=mapnik.Expression('''[test_column] = 'red' ''') #### NOT WORKING
s = mapnik.Style()
s.rules.append(r)

lyr = mapnik.Layer('world',proj4)
lyr.datasource=ds
lyr.styles.append('My Style')

m=mapnik.Map(200,200,proj4)
m.append_style('My Style',s)
m.layers.append(lyr)
m.zoom_all()
m.layers[0].srs=proj4
m.zoom_all()
print m.envelope()
mapnik.render_to_file(m,'world.png', 'png')

If I omit the filter, the points in the table show fine.

[artemp]

[springmeyer] hmm, this is a tricky one. How important is it that you support this type?

The reason that this is happening is that postgres lists a user created enum type with a custom OID. While postgres appears to have both a built in anynum and pg_enum type, the alert level oid is custom (non system) and cannot be known ahead of time by mapnik.

For example we know, and could check for oid 3500, and only this patch would be needed:

Index: plugins/input/postgis/postgis_featureset.cpp
===================================================================
--- plugins/input/postgis/postgis_featureset.cpp    (revision 2927)
+++ plugins/input/postgis/postgis_featureset.cpp    (working copy)
@@ -112,7 +112,7 @@
                     float8net(val,buf);
                     boost::put(*feature,name,val);
                 }
-                else if (oid==25 || oid==1043) // text or varchar
+                else if (oid==25 || oid==1043 || oid==3500) // text, varchar, or enum
                 {
                     UnicodeString ustr = tr_->transcode(buf);
                     boost::put(*feature,name,ustr);
Index: plugins/input/postgis/postgis_datasource.cpp
===================================================================
--- plugins/input/postgis/postgis_datasource.cpp    (revision 2927)
+++ plugins/input/postgis/postgis_datasource.cpp    (working copy)
@@ -257,6 +257,7 @@
                 case 701:   // float8
                 case 1700:  // numeric ??
                     desc_.add_descriptor(attribute_descriptor(fld_name,mapnik::Double));
+                case 3500:  // enum, aka anyenum
                 case 1042:  // bpchar
                 case 1043:  // varchar
                 case 25:    // text

...but we cannot know 35523, as I assume it will differ each time:

SELECT oid, typname FROM pg_type where typname like '%enum%';
  oid  |      typname      
-------+-------------------
  3500 | anyenum
 10926 | pg_enum
 35523 | test_mapnik_enum
 35522 | _test_mapnik_enum
(4 rows)

The easy solution here is to cast the enum to text by changing your table parameter into a subselect. In XML that would be:

<Parameter name="table">(Select geom,test_column::text as e from test_mapnik_enum) as t</Parameter>

[artemp]

[rapto] The following patch works for us. It treats unknown types as strings, so they can be compared with strings and written as labels. I expect it won't break anything as this conversion won't be used if any column with conflictive types is not mentioned.

Index: plugins/input/postgis/postgis_featureset.cpp
===================================================================
--- plugins/input/postgis/postgis_featureset.cpp    (revisión: 2904)
+++ plugins/input/postgis/postgis_featureset.cpp    (copia de trabajo)
@@ -138,6 +138,9 @@
 #ifdef MAPNIK_DEBUG
                     std::clog << "Postgis Plugin: uknown OID = " << oid << " FIXME " << std::endl;
 #endif
+                    // We assume string conversion to handle user enum types - see #764
+                    UnicodeString ustr = tr_->transcode(buf);
+                    boost::put(*feature,name,ustr);
                 }
             }
         }
Index: plugins/input/postgis/postgis_datasource.cpp
===================================================================
--- plugins/input/postgis/postgis_datasource.cpp    (revisión: 2904)
+++ plugins/input/postgis/postgis_datasource.cpp    (copia de trabajo)
@@ -285,6 +285,8 @@
                     }
                     rs_oid->close();
 #endif
+                    // We assume string conversion to handle user enum types - see #764
+                    desc_.add_descriptor(attribute_descriptor(fld_name,mapnik::String));
                     break;
                 }
             }

[artemp]

[springmeyer] I'm a little uncomfortable with this fallback without further testing, pushing to mapnik 2.0.1

[noirbizarre]

Any news on this topic?

[springmeyer]

Hi @noirbizarre. The problem with blindly accepting any unhanded data as a string is:

1) We need to make sure to to not add a types like geometry or geography into the attributes, because this would duplicate data and bloat the features 2) What if the type is binary data? Pushing into a unicode string would corrupt it. 3) What if user defined types are used that represent numbers not strings?

A patch that handles the above issues cleanly would be accepted.