How to generate MBTiles with multiple PostGIS function-layers using martin-cp

[justb4]

I am using Martin, Docker ghcr.io/maplibre/martin:main, latest version (72h). My data is in PostGIS, using a Function for each layer. This works fine when generating tiles directly, visible e.g. with MapLibre JS. Fetching multiple Layers by appending their names comma-separated in the Martin URL path.

In the config:

  functions:
    landcover:
      schema: map5
      function: vt_landcover

    place:
      schema: map5
      function: vt_place
etc

Now I like to generate a single MBTiles file with multiple Layers, where Layers are those same PostGIS Functions.
This works for a single layer (Source), like landcover, MBTiles get generated:

docker compose run --name martin-cp --entrypoint "" --rm martin /bin/ash -c "martin-cp \
   -c /martin_etc/config.yml -o /map5/map5.mbtiles \
   --min-zoom 4 --max-zoom 5 --source landcover"

but I cannot figure out how to include multiple layers. My docker-compose.yml snippet, that runs the Martin server ok, which I invoke with docker compose run ..., overriding Entrypoint:

services:

  martin:
    image: ghcr.io/maplibre/martin:main

    container_name: martin

    command: "--config /martin_etc/config.yml"

    environment:
      - DATABASE_URL=postgresql://${PG_USER:}:${PG_PASSWORD}@${DB_HOST}/${DB_NAME}
      - RUST_LOG=debug

    volumes:
      - ./etc:/martin_etc:rw
      - ${DATA_DIR}/map5:/map5:rw

Should I generate an MBTiles file for each layer and then merge e.g.? Something I can do in the config? A UNION Function?

[nyurik]

• you can use martin's composite sources - e.g. --source landcover,place (I haven't personally tried it, but all the functionality is there to support it i think)
• you can write a new PostGIS function that uses STRING_AGG to join the layers. Note the ORDER BY in a few places - to ensure the layer and feature ordering inside the tile is always predictable

SELECT STRING_AGG(mvtl, '' ORDER BY layer_ord) AS mvt
FROM (
    SELECT 0 AS layer_ord,
        COALESCE(ST_AsMvt(t, 'landcover', /* ... */ ORDER BY id), '') AS mvtl
    FROM (SELECT l.id
               , ST_AsMvtGeom(l.geom, z, x, y) AS geom
               ...
          FROM landcover_source l
          WHERE ...
          ) t

    UNION ALL

    SELECT 1 AS layer_ord,
        COALESCE(ST_AsMvt(t, 'place', /* ... */ ORDER BY id), '') AS mvtl
    FROM (SELECT l.id
               , ST_AsMvtGeom(l.geom, z, x, y) AS geom
               ...
          FROM place_source l
          WHERE ...
         ) t
);

[CommanderStorm]

now at least I do not get an error message

I don't see an error. Could you attach which error you are getting?

The maxzoom info message could be more actionable, agree.
I am going to look into what would be a better wording on the way home.

The message about the cache is entirely misleading, it does not exist for martin-cp.

[justb4]

Sorry, meant the log lines:

[2025-08-28T10:46:52Z INFO  martin_cp] Copying 11888 application/x-protobuf tiles from landcover,place to /map5/map5.mbtiles
[2025-08-28T10:46:53Z INFO  mbtiles::update] Metadata value minzoom is already set to correct value 8
[2025-08-28T10:46:53Z INFO  mbtiles::update] Metadata value maxzoom=17 is greater than the computed maxzoom 14 in tiles table, not updating

Finding then only a few tiles in the MBTiles file i.s.o 11888, thought that "not updating" was related.
But I am now tiling the entire NL, making the bbox in the config and martin-cp args match and seeing the MBTiles file growing.
That is why I thought of some interplay between zoomlevels and bbox in both config and martin-cp args.

[CommanderStorm]

🤔 @nyurik

I think I am missing a bit of context/ the underlying idea in #1081.

when copying to an existing file, martin-cp [..] should NOT shrink it (lower max and increase min) if the tiles table has those zooms, even if they are not being copied in the correct operation

I think I am missing the basic idea here.
Why not just always update the metadata based on the content of the generated mbtiles file?

[nyurik]

martin-cp can copy tiles into an existing mbtiles file - so if you use martin-cp to copy zoom 14 only (via params), but the file already has zoom 1-13, the resulting metadata should expand maxzoom to 14, but it shouldn't shrink the minzoom to 14

[justb4]

When mapping same bbox and min-max zooms in both config and martin-cp, the a 5.6GB MBTiles file is generated for NL, and final loglines are:

[2025-08-28T14:47:10Z INFO  martin_cp] [11163.2s] 99% @ 407.0/s | ✓ 3126200 □ 1416737 | 3s left
[2025-08-28T14:47:12Z INFO  martin_cp] [11165.0s] 100% @ 407.0/s | ✓ 3127483 □ 1416743 | done
[2025-08-28T14:47:29Z WARN  sqlx::query] slow statement: execution time exceeded alert threshold summary="SELECT min(zoom_level) AS min_zoom, …" db.statement="\n\n\nSELECT min(zoom_level) AS min_zoom,\n       max(zoom_level) AS max_zoom\nFROM tiles;\n" rows_affected=0 rows_returned=1 elapsed=17.000678232s elapsed_secs=17.000678232 slow_threshold=1s
[2025-08-28T14:47:29Z INFO  mbtiles::update] Metadata value minzoom is already set to correct value 8
[2025-08-28T14:47:29Z INFO  mbtiles::update] Metadata value maxzoom is already set to correct value 17
[2025-08-28T14:47:29Z INFO  martin_cp] Computing agg_tiles_hash value...
[2025-08-28T15:09:44Z WARN  sqlx::query] slow statement: execution time exceeded alert threshold summary="SELECT coalesce( md5_concat_hex( cast(zoom_level …" db.statement="\n\n\nSELECT coalesce(\n           md5_concat_hex(\n               cast(zoom_level AS text),\n               cast(tile_column AS text),\n               cast(tile_row AS text),\n               tile_data\n               ORDER BY zoom_level, tile_column, tile_row),\n           md5_hex(''))\nFROM tiles;\n\n" rows_affected=0 rows_returned=1 elapsed=1334.777550682s elapsed_secs=1334.777550682 slow_threshold=1s
[2025-08-28T15:09:44Z INFO  mbtiles::validation] Adding a new metadata value agg_tiles_hash = CDC1FFC703A4617CC9C552A803826A78 in /map5/map5.mbtiles
[2025-08-28T15:09:45Z WARN  sqlx::query] slow statement: execution time exceeded alert threshold summary="INSERT OR REPLACE INTO …" db.statement="\n\nINSERT OR REPLACE INTO metadata(name, value) VALUES(?, ?)\n" rows_affected=1 rows_returned=0 elapsed=1.408077839s elapsed_secs=1.408077839 slow_threshold=1s

The mbtiles::update lines seem to agree.
But overall my initial question, "how to combine PostGIS function layers with martin-cp" has been answered: "composite sources, comma-separate function names, like the Martin-direct URL". Possibly the "zoom issues" can be split off in separate discussion.