vector tiles from sqlite datasource - getting rid of rowid

[rinigus]

I am generating vector tiles using tilelive copy (from bridge to mbtiles). Data is served from SQLite database(s) that were generated from OSM data (ogr2ogr) and preprocessed to arrange for simplifying import. While tiles seem to be fine, all layers have an additional rowid key that seem just to waste space in the tile. I wonder whether I am missing some parameter in Mapnik XML to disable rowid propagation into the generated tile.

Used Mapnik XML:

<?xml version='1.0' encoding='utf-8'?>
    <!DOCTYPE Map>
    <Map srs="+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs +over">
  <Parameters>
    <Parameter name="attribution">Data (c) OpenStreetMap (and) contributors, ODbL</Parameter>
    <Parameter name="bounds">-180,-85.0511,180,85.0511</Parameter>
    <Parameter name="center">0,20,4</Parameter>
    <Parameter name="format">pbf</Parameter>
    <Parameter name="maxzoom">20</Parameter>
    <Parameter name="minzoom">0</Parameter>
  </Parameters>
  <Layer name="landcover" srs="+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0.0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs +over">
    <Datasource>
      <Parameter name="file">data/europe-estonia.sqlite</Parameter>
      <Parameter name="key_field">rowid</Parameter>
      <Parameter name="table">(SELECT rowid, geometry, class, subclass FROM
osm_landcovers
ORDER BY area DESC
) AS data</Parameter>
      <Parameter name="type">sqlite</Parameter>
      <Parameter name="use_spatial_index">true</Parameter>
    </Datasource>
  </Layer>
</Map>

tileinfo --verbose example.pbf:

message: gzip compressed
layer: landcover
  version: 2
  extent: 4096
  keys: class,rowid,subclass
  values: grass,1547,park,441,meadow,wood,28343,forest,2428,28567,26360,28322,483,28319,28873,29816,rock,29068,bare_rock,28327,2426,29702,31058,39964,28803,28815,28309,38198,28872,32711,28310,28870,28337,28308,28311,wetland,28554,28345,32993,32989,29386,29375,980,35723,29704,39536,32983,32984,29376,33629,34250,32987,35716,32244,28871,33681,28335,35728,28778,34251,35720,32991,28338,38463,35721,33641,35730,32985,29418,33647,31800,29661,32986,33648,38462,32243,32990,28606,35731,34252,33244,35733,29383,38469,35734,33676,29531,30333,32992,29379,33680,33624,35724,32601,33622,33241,32995,35713,29378,28804,652,33653,29663,29377,35710,29381,28816,33626,35744,28809,28607,33628,28802,29385,33247,32996,38461,35747,28321,30334,33242,35676,34248,33643,34249,32994,38495,33673,34088,35675,30335,33649,33636,650,38497,28814,38775,35748,38776,33665,33243,33677,33620,33679,35749,38498,35735,29382,33246,33618,35630,35718,35714,651,33675,33664,33682,35729,38471,32519,33668,33672,38489,35742,1081,33625,33655,1250,38481,33619,33634,29380,38492,38491,38482,28320,30022,33645,35743,33238,38487,28808,33237,38484,33621,33632,32988,1249,38488,33667,33251,33674,32804,33638,33631,33630,29384,33644,38483,33654,32997,33663,38485,33627,33652,35711,35741,36858,38486,38494,33635,32520,29374,38774,38467,33248,33670,33666,33646,38464,38496,35726,35739,38470,33669,29533,33240,33678,35715,33671,35746,38773,33633,35712,35745,33236,33623,38772,32998,38465,31059,31060,33252,33245,38480,38479,38477,38490,38475,38466,38478,33637,33259,38472,33639,38473,33258,35727,38474,35719,35740,33239,38493,38468,30336,33642,35750,33254,33253,33250,33249,33257,33640,35717,33255,35722,35725,33256
  feature: 1547
    type: Polygon
    tags: 0,0,0,0,0,0
    geometries: 9,3981,1432,354,267,640,136,5,88,17,184,41,238,73,486,205,13,93,332,39,64,100,182,368,279,186,564,612,650,662,465,512,67,250,1233,934,429,170,67,46,169,148,97,128,1019,123,405,91,2075,727,415,133,17,25,20,135,78,26,166,501,92,161,252,199,144,67,56,57,414,235,50,41,694,419,275,447,59,21,43,79,288,175,272,123,256,73,572,129,414,111,242,85,15
  feature: 441
    type: Polygon
    tags: 0,0,0,0,0,0
    geometries: 9,5720,5941,546,124,6,132,34,176,62,144,96,46,54,302,73,154,10,76,40,168,158,8,136,114,50,122,15,34,57,94,33,44,46,210,99,126,31,136,75,154,53,266,17,144,45,224,37,88,36,180,22,168,7,140,19,34,109,486,48,138,24,522,160,1,220,661,165,293,2206,123,182,149,104,1733,728,55,175,295,104,66,180,857,360,127,5,37,26,35,66,1463,654,153,86,51,47,112,171,184,99,154,119,101,165,214,511,273,181,164,4,202,34,138,163,112,195,70,143,13,103,50,53,20,61,110,58,726,1155,173,473,507,433,141,193,147,111,31,223,150,319,15,9,742,2274,226,95,58,133,130,28,110,74,50,53,86,11,68,104,102,144,52,84,16,120,43,82,52,104,44,4,56,59,60,27,102,76,98,124,10,50,75,17,93,73,67,19,89,0,99,369,105,179,151,58,67,5,113,36,73,38,43,15,9,2664,703,154,137,8,97,42,65,56,43,104,68,52,74,80,20,74,116,15,76,6,58,72,108,44,98,58,26,77,41,83,71,95,119,39,89,13,1,95,32,91,15
  feature: 28343
...

[springmeyer]

There is not currently a way to drop that field (that I recall). There was an early effort to provide an option, in C++, to do this https://github.com/mapbox/mapnik-vector-tile/pull/78. The problem is that ideally you need a way to signal what fields you want from the XML. We added the ability to control the key field specifically for the postgis database in https://github.com/mapnik/mapnik/issues/3115. Something similar could likely be added for sqlite, but I don't personally have time to work on this.

[rinigus]

Thank you for the fast reply! While not implemented in C++, is it possible later to filter this field out using some other approach? Like processing all tiles via tilegrinder or filter the field out in tilelive pipeline? I am quite new to this, sorry if these questions have been replied somewhere already.

[flippmoke]

Closing this issue as it is something related to plugins in mapnik.

[rinigus]

Thank you for closing it. I was wondering what's the policy regarding closing the issues and wasn't sure whether to do it.

If someone stumbles on it: I went around it by using PostGIS instead and would recommend to do so to others as well.

[am2222]

@rinigus Hi, could not you use spatialite?

[rinigus]

It was spatialite database when I was discussing sqlite datasource.

[am2222]

@rinigus thanks, can you please tell me how you converted your sqlite database to vector tiles, you used tilelive copy or bridge?

[rinigus]

I ended up tuning with my own scripts on the basis of approaches available around. The project is available at https://github.com/rinigus/mapbox-gl-importer and is now based on PostGIS. But it should be possible to adjust to SQLite if you change Mapnik source definitions.