Usage query_params in Function Source Tiles

[liskaole]

How can I pass query_params in Function Source Tiles from tile source url MapBox?

source: { type: 'vector', url: 'http://localhost:3000/rpc/public.function_source.json?param1=param1' // doesn't work for me }

And how do I need to use query_params here? I want to filter my table by condition from query_params.

CREATE OR REPLACE FUNCTION public.function_source_test(z integer, x integer, y integer, query_params json) RETURNS bytea AS ...

[stepankuzmin]

Hi @liskaole! You should add a vector source with:

{
  type: 'vector',
  url: 'http://localhost:3000/rpc/public.function_source.json?param1=param1'
}

And then, you can access param1 in your function with query_params->>'param1'

Eg.

CREATE OR REPLACE FUNCTION public.function_source(z integer, x integer, y integer, query_params json) RETURNS bytea AS $$
DECLARE
  bounds geometry;
  mvt bytea;
BEGIN
  SELECT INTO bounds TileBBox(z, x, y, 3857);

  SELECT INTO mvt ST_AsMVT(tile, 'public.function_source', 4096, 'geom') FROM (
    SELECT
      ST_AsMVTGeom(geom, bounds, 4096, 64, true) AS geom
    FROM public.table_source
    WHERE geom && bounds
    AND param1 = query_params->>'param1' -- filter records
  ) as tile WHERE geom IS NOT NULL;

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

[mapsgeek]

@stepankuzmin this is still not clear, where does this postgis function lives ?!!

[stepankuzmin]

Hey @mapsgeek! What do you mean by "lives"? It's just a user-defined PostgreSQL function

[mapsgeek]

@stepankuzmin i need to make sql like queries for tables to generate the tiles for the matching criteria only, i thought that this function is required to be on the server side somehow but i understand now what is it mean, it's on the database level but i still don't understand where does it get the table_source or what is the function should look like to be able to just run sql queries per table
like points.json?id=100, i understand this function generates a bbox geometry and trying to do one similar for what i have mentioned

[stepankuzmin]

The public.table_source is just an example for your PostGIS table with geometry. You can look for example tables in the fixture directory.

[mapsgeek]

so in my case i need to repeat the same function for each table i want to query, is there away to implement sql like queries without the need to these functions ?

[stepankuzmin]

You can pass your table as an argument into query_params and use it to query your tables

[donnyv]

The only way I've seen this done is by using EXECUTE and feeding it a SQL string. Is there any other way to do this? I would think using EXECUTE would not be as performant as a compiled function.

[mapsgeek]

@stepankuzmin can you please help ?
i created a function on postgres using the bbox example in the documentation, replaced the table_source with my table name, but when i make a request to http://localhost:8080/rpc/public.function_source/0/0/0.pbf
it returns 500 error

[stepankuzmin]

Hey @mapsgeek! Could you please provide more info on this? You can enable debug mode and share logs here.

[mapsgeek]

[2020-01-20T11:15:12Z INFO  martin] Starting martin v0.5.0
[2020-01-20T11:15:12Z INFO  martin] Config is not set, scanning database
[2020-01-20T11:15:12Z INFO  martin] Connected to postgres://postgres:postgres@localhost:5432/farcloser
[2020-01-20T11:15:12Z DEBUG postgres] executing query:
[2020-01-20T11:15:12Z DEBUG postgres] preparing query with name ``: WITH columns AS (
      SELECT
        ns.nspname AS table_schema,
        class.relname AS table_name,
        attr.attname AS column_name,
        trim(leading '_' from tp.typname) AS type_name
      FROM pg_attribute attr
        JOIN pg_catalog.pg_class AS class ON class.oid = attr.attrelid
        JOIN pg_catalog.pg_namespace AS ns ON ns.oid = class.relnamespace
        JOIN pg_catalog.pg_type AS tp ON tp.oid = attr.atttypid
      WHERE NOT attr.attisdropped AND attr.attnum > 0)
    SELECT
      f_table_schema, f_table_name, f_geometry_column, srid, type,
        COALESCE(
          jsonb_object_agg(columns.column_name, columns.type_name) FILTER (WHERE columns.column_name IS NOT NULL),
          '{}'::jsonb
        ) as properties
    FROM geometry_columns
    LEFT JOIN columns ON
      geometry_columns.f_table_schema = columns.table_schema AND
      geometry_columns.f_table_name = columns.table_name AND
      geometry_columns.f_geometry_column != columns.column_name
    GROUP BY f_table_schema, f_table_name, f_geometry_column, srid, type;
[2020-01-20T11:15:12Z DEBUG postgres] executing statement  with parameters: []
[2020-01-20T11:15:12Z INFO  martin::table_source] Found public.cities table source
[2020-01-20T11:15:12Z INFO  martin::table_source] Found public.fire table source
[2020-01-20T11:15:12Z INFO  martin::table_source] Found public.routes table source
[2020-01-20T11:15:12Z INFO  martin::table_source] Found public.routes_merged table source
[2020-01-20T11:15:12Z DEBUG postgres] preparing query with name ``: SELECT
      routines.specific_schema,
      routines.routine_name
    FROM information_schema.routines
      LEFT JOIN information_schema.parameters ON routines.specific_name=parameters.specific_name
    WHERE
      routines.data_type = 'bytea'
    GROUP BY
      routines.specific_schema, routines.routine_name, routines.data_type
    HAVING
      array_agg(array[parameters.parameter_name::text, parameters.data_type::text]) @>
      array[array['z', 'integer'], array['x', 'integer'], array['y', 'integer'], array['query_params', 'json']];
[2020-01-20T11:15:12Z DEBUG postgres] executing statement  with parameters: []
[2020-01-20T11:15:12Z INFO  martin::function_source] Found public.function_source function source
[2020-01-20T11:15:12Z DEBUG postgres] executing query:
[2020-01-20T11:15:12Z DEBUG postgres] preparing query with name ``: select (regexp_matches(postgis_lib_version(), '^(\d+\.\d+\.\d+)', 'g'))[1] as postgis_lib_version
[2020-01-20T11:15:12Z DEBUG postgres] executing statement  with parameters: []
[2020-01-20T11:15:12Z INFO  martin] Watch mode enabled
[2020-01-20T11:15:12Z INFO  martin] Martin has been started on 0.0.0.0:8080.
[2020-01-20T11:15:40Z DEBUG postgres] executing query:
[2020-01-20T11:15:40Z DEBUG postgres] preparing query with name ``: SELECT public.function_source(z => 0, x => 0, y => 0, query_params => '{}'::json);
[2020-01-20T11:15:40Z DEBUG postgres] executing statement  with parameters: []
[2020-01-20T11:15:40Z INFO  actix_web::middleware::logger] 127.0.0.1:15556 "GET /rpc/public.function_source/0/0/0.pbf HTTP/1.1" 500 0 "-" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.117 Safari/537.36" 0.002944

[garretteklof]

@mapsgeek if you're copy/pasting from the example in the documentation, you have to remember to manually load the TileBBox to your database.

[CptHolzschnauz]

In the Martin docs (https://maplibre.org/martin/sources-pg-functions.html), there is still the remark "TODO: Modify this example to actually use the query parameters.". I can't help because I'm also searching for answers.
@stepankuzmin : It would be probably helpful to use your example for the docs? If possible, may I suggest an example with multiple variables like {lat,lng} to conclude and simplify the actual docs with the -curl part which is IMHO confusing.

[stepankuzmin]

Hey @CptHolzschnauz,

There is a test case that you could use as an example https://github.com/maplibre/martin/blob/main/tests/fixtures/functions/function_zxy_query_test.sql

martin/tests/fixtures/functions/function_zxy_query_test.sql

Lines 9 to 11 in fb3e439

	IF (query_params->>'token')::varchar IS NULL THEN
	RAISE EXCEPTION 'the `token` json parameter does not exist in `query_params`';
	END IF;

[CptHolzschnauz]

Hey @stepankuzmin
Thank you, this leads to the question how to do on the client side with MapBox/MapLibre?

I'll try get points out of a GEOMETRY column from a PSQL table based on a query parameter. So points where foo=bar in another column of this table for example.

Do I have to modify somehow the addSource, somethin like adding a variable name:
map.addSource('locations', {
type: 'vector',
tiles: ['http://localhost:3000/loc/{z}/{x}/{y}/bar']
});

For a simple curl it's clear, but for MapBox I am a bit confused.
Thx

[stepankuzmin]

@CptHolzschnauz this should work #249 (comment)

{
  "type": "vector",
  "url": "http://localhost:3000/points?param1=param1"
}