Add additional features attributes into mvt_postgis layers

[geraldo]

I'm using a _mvtpostgis provider and everything works fine using a query like:

SELECT ST_AsMVTGeom(geom,!BBOX!) AS geom, name, id FROM mobility.bus WHERE geom && !BBOX!

Now I need to add some additional features attributes into the vector tiles. As I understand the documentation, only one feature attribute can added, as by my example that is name. I tried to add additional feature attributes with something like that, but without success:

SELECT ST_AsMVTGeom(geom,!BBOX!), json_each_text('{"name":name, "description":description}') AS geom, properties, id FROM mobility.bus WHERE geom && !BBOX!

Mapbox Vector Tile format can store features with varying sets of attributes. ST_AsMVT states that you have to "supply a JSONB column in the row data containing Json objects one level deep". Any idea how to do that with Tegola?

[ARolek]

@geraldo I haven't tried this approach myself, but I suspect the issue is that you're using json text rather than jsonb. It also looks like the JSON is being referenced as the geom. Try this modified query:

SELECT ST_AsMVTGeom(geom,!BBOX!) AS geom, jsonb_build_object('{"name":name, "description":description}') , properties, id FROM mobility.bus WHERE geom && !BBOX!

Let me know if that works or if you settle on a solution as I know others will encounter this.

[geraldo]

@ARolek Thanks for your answer. So after using this query (without double quotes!): SELECT ST_AsMVTGeom(geom,!BBOX!) AS geom, jsonb_build_object('{name:name, description:description}') , properties, id FROM mobility.bus WHERE geom && !BBOX!

I get the following error in Tegola logs:

[ERROR] handle_map_layer_zxy.go:165: error marshalling tile: ERROR: argument list must have even number of elements (SQLSTATE 22023)

[ARolek]

@geraldo I had the syntax wrong. Try this:

SELECT ST_AsMVTGeom(geom,!BBOX!) AS geom, jsonb_build_object('name',name, 'description',description) , properties, id FROM mobility.bus WHERE geom && !BBOX!

[geraldo]

@ARolek Thanks a lot for your help, the modified syntax worked out perfectly.

Now I'm able to add feature identification on vector tiles with OpenLayers, great!

[ARolek]

Excellent!