MVT returns for NULL "no data" which can be a problem for end use

[pramsey]

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
), '')
;

[pramsey]

In 15786:

ST_AsMVTGeom return NULL on EMPTY geometry (References #3857)

[Björn Harrtell]

Discussed on IRC and we reached the following conclusions:

1. ST_AsMVTGeom should return NULL when appropriate and not throw up on NULL input.
2. ST_AsMVT should ignore rows where geometry is NULL.
3. ST_AsMVT should return zero length bytea instead of NULL when appropriate.

On an unrelated note @pramsey was also able to make my code crash the backend with select st_astext(ST_AsMVTGeom('POINT EMPTY'::geometry, 'BOX(0 0,2 2)'::box2d));.

[pramsey]

In 15788:

Make ST_AsMVT tolerant of NULL input and return :bytea
(zero length bytea) when provided with only null inputs, or no
inputs. (References #3857)

[pramsey]

Hearing no complaints, I'm calling "done" on this ticket.