Defining min/maxzoom in PG function and autodetecting with Martin

[dericke]

Hi, new to Martin! As far as I've worked out, the best way to enforce a min and/or maxzoom on a PG Function layer is to add a clause at the beginning of the function that returns an empty tile if the requested zoom level is out of bounds. So, the sample function from the docs might become (for minzoom 7/maxzoom 15):

CREATE OR REPLACE
    FUNCTION function_zxy_query(z integer, x integer, y integer)
    RETURNS bytea AS $$
DECLARE
  mvt bytea;
BEGIN
  IF z < 7 OR z > 15 THEN 
    RETURN '';
  END IF;
  SELECT INTO mvt ST_AsMVT(tile, 'function_zxy_query', 4096, 'geom') FROM (
    SELECT
      ST_AsMVTGeom(
          ST_Transform(ST_CurveToLine(geom), 3857),
          ST_TileEnvelope(z, x, y),
          4096, 64, true) AS geom
    FROM table_source
    WHERE geom && ST_Transform(ST_TileEnvelope(z, x, y), 4326)
  ) as tile WHERE geom IS NOT NULL;

  RETURN mvt;
END
$$ LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE;

I wanted to see if this is the best way to do it, and if so, would it be a feasible feature request for Martin to pick up on such a clause and set the minzoom/maxzoom in the TileJSON accordingly?

[nyurik]

@dericke take a look at customizing tilejson using a sql comment. You should still do the zoom filtering in the function, but adding it to tilejson will make sure maplibre won't even request it.

[dericke]

Thanks @nyurik, that's what I'm using currently. I was thinking that if it could be defined automatically from the function code, it would be one (or two) fewer things to potentially get out of date.

[nyurik]

@dericke take a look at customizing tilejson using a sql comment. You should still do the zoom filtering in the function, but adding it to tilejson will make sure maplibre won't even request it. You only do it from SQL - next to your function you can create a SQL comment that will be stored in the same database attached to your function. Martin should not be analyzing function code to see if it checks the zoom or not -- as this cannot be solved in a general case by definition (see Halting problem). So to keep things simple - an SQL comment is really an ideal solution IMO, as this is the closest thing to an attribute that SQL has.

[dericke]

Thanks, this is just the kind of insight I was looking for.