PostgreSQL Function Sources returning more data

[martinhamel]

Hi, martin is great and keep the good work :-)

I want to return more then vector tiles from a function source. Some other fields. Is it possible right now ? How do I do it ? I tried

CREATE OR REPLACE FUNCTION public.function_zxy_query(z integer, x integer, y integer)
 RETURNS bytea
 LANGUAGE plpgsql
 IMMUTABLE PARALLEL SAFE STRICT
AS $function$
DECLARE
  mvt bytea;
BEGIN
  SELECT INTO mvt name, kind, ST_AsMVT(tile, 'function_zxy_query', 4096, 'geom') FROM (
    SELECT
        name,
        kind,
      ST_AsMVTGeom(
          ST_Transform(ST_CurveToLine(geom), 3857),
          ST_TileEnvelope(z, x, y),
          4096, 64, true) AS geom
    FROM cycleway
    WHERE geom && ST_Transform(ST_TileEnvelope(z, x, y), 4326)
  ) as tile WHERE tile.geom IS NOT NULL;

  RETURN mvt;
END
$function$;

How do I get name and kind?

[nyurik]

You cannot return anything except tiles from a regular martin request -- simply because you won't be able to handle it without a lot of hackery. For example, you tell MapLibre GL JS to access a specific tile source. For example, you will get tilejson from http://example.com/my_source, which will instruct MapLibre to load tiles like http://example.com/my_source/13/3123/8978. The tile will be consumed directly by MapLibre - no place for any extra info there unless you want to handle the whole tile access process yourself (e.g. via addProtocol approach, and fetch things yourself, ...).

This said, you may want to simply add your values into the tile itself - assuming you need those extra fields for rendering... which bring the most important question - why do you need these fields?

[martinhamel]

They are for rendering. I use them to filter the layers that I show. I show bike paths and I have a score that users are suggesting. I change the color depending on it.

Right now I have a view but it is too slow. I would prefer to have a function so that I can be faster.

[martinhamel]

This is the view I use. But joining is kind of slow.

CREATE OR REPLACE VIEW public.bike_path
AS SELECT t.way_id,
    t.name,
    t.winter_service,
    t.geom,
    t.source,
    t.target,
    t.kind,
    t.tags,
    t.score,
    t.rn
   FROM ( SELECT cycleway.way_id,
            cycleway.name,
            cycleway.winter_service,
            cycleway.geom,
            cycleway.source,
            cycleway.target,
            cycleway.kind,
            cycleway.tags,
            recent_cyclability_score.score,
            row_number() OVER (PARTITION BY cycleway.way_id ORDER BY recent_cyclability_score.created_at DESC) AS rn
           FROM cycleway
             LEFT JOIN cyclability_score recent_cyclability_score ON cycleway.way_id = ANY (recent_cyclability_score.way_ids)) t
  WHERE t.rn = 1;

[nyurik]

Not exactly what I meant - I understand you will use it for some rendering. The question is if you can put the needed data inside the tile itself. For example, you could use different layer names (in your case above -- function_zxy_query). This way different layers will be styled differently because the layer name is different.

[martinhamel]

That's a good idea! I will try it :-)