[postgis-tickets] [PostGIS] #3857: MVT returns for NULL "no data" which can be a problem for end use

[PostGIS]

#3857: MVT returns for NULL "no data" which can be a problem for end use
-------------------------+----------------------------
 Reporter:  pramsey      |      Owner:  Björn Harrtell
     Type:  enhancement  |     Status:  assigned
 Priority:  high         |  Milestone:  PostGIS 2.4.0
Component:  postgis      |    Version:  trunk
 Keywords:               |
-------------------------+----------------------------
 A report from dmanzanares
 -----
 I've been using concatenations of St_AsMVT to generate an MVT with
 multiple layers in this way:

 {{{
 select
 (
     select st_asmvt(geom, 'layer1') FROM
     (SELECT ST_AsMVTGeom(the_geom_webmercator, CDB_XYZ_Extent(16,11,5),
 4096, 0, true) FROM
          untitled_table where the_geom_webmercator &&
 CDB_XYZ_Extent(16,11,5) ) as geom
 )
 ||
 (
     select st_asmvt(geom, 'layer2') FROM
         (SELECT ST_AsMVTGeom(the_geom_webmercator,
 CDB_XYZ_Extent(16,11,5), 4096, 0, true) FROM
              untitled_table_1 where the_geom_webmercator &&
 CDB_XYZ_Extent(16,11,5) ) as geom
 )
 }}}


 This has 2 different, yet related, problems.

 The first problem is that when St_AsMVT recieves 0 rows in geom, then it
 returns NULL, when that happens, the concatenation operator operator
 discards the bytearray of the other subquery, and sets the final result to
 NULL. I would expect that it returns a zero-sized byte array instead of
 NULL, to use the concatenation operator without wrappers/workarounds.

 The second problem is that ST_AsMVTGeom returns NULL when the clipping
 removes all the geometry from the tile. This causes this: "ERROR:
 mvt_agg_transfn: geometry column cannot be null". I would expect that it
 returns zero rows, or something that didn't trigger that error and that
 ST_AsMVT could understand without wrappers/workarounds.

 To make it easier to understand, this is a workaround that solves both
 problems:

 {{{
 select
 coalesce((
     select st_asmvt(geom, 'layer1') FROM
         (SELECT ST_AsMVTGeom(the_geom_webmercator,
 CDB_XYZ_Extent(16,11,5), 4096, 0, true) FROM
              untitled_table where the_geom_webmercator &&
 CDB_XYZ_Extent(16,11,5) ) as geom where geom is not null
 ), '')
 ||
 coalesce((
     select st_asmvt(geom, 'layer2') FROM
         (SELECT ST_AsMVTGeom(the_geom_webmercator,
 CDB_XYZ_Extent(16,11,5), 4096, 0, true) FROM
             untitled_table_1 where the_geom_webmercator &&
 CDB_XYZ_Extent(16,11,5) ) as geom where geom is not null
 ), '')
 ;
 }}}

--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/3857>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.