search_path option for mvt_postgresql

[gislkvr]

Description
The mvt_postgresql provider lacks the option to provide a search_path.

[webb-ben]

Hi there - Could you provide a bit more context on the issue you are running into?

I believe we run our tests against a Postgres server that makes use of the search_path (ref). I believe we only make use of the first schema in the search path if the issue is that there are multiple schemas you are trying to register?

[gislkvr]

Hello and thanks for your answer.

The problem is, the table is in schema ‘dienste’.
The PostGIS functions are in schema ‘public’.
As soon as I prefix the SQL from the error message below with set search_path = dienste, public;, it works..."partially".

"Partially" because the next problem lies in the condition WHERE ST_Intersects(wkb_geometry, ST_Transform(bounds.boundgeom, 4326)). “storage_crs” is http://www.opengis.net/def/crs/EPSG/0/25833 and not 4326, which is why the database returns an error message.
It would be correct to transform to "storage_crs" in the condition.

Below is the configuration of the resource:

resources:
    gemarkungen:
        type: collection
        title: Gemarkungen
        description: something
        keywords:
            - Gemarkung
        crs:
            - http://www.opengis.net/def/crs/OGC/1.3/CRS84
            - http://www.opengis.net/def/crs/EPSG/0/4326
            - http://www.opengis.net/def/crs/EPSG/0/3857
            - http://www.opengis.net/def/crs/EPSG/0/25833
        extents:
            spatial:
                bbox:
                    - 12.22
                    - 53.94
                    - 13.77
                    - 54.69
                crs: http://www.opengis.net/def/crs/OGC/1.3/CRS84
        providers:
            - type: feature
                name: PostgreSQL
                data:
                    host: <host>
                    port: <port>
                    dbname: <dbname>
                    user: <user>
                    password: <password>
                    search_path:
                        - dienste
                        - public
                table: gemarkungen
                id_field: schluessel_gemarkung
                geom_field: wkb_geometry
                properties:
                    - schluessel_gemarkung
                    - bezeichnung_gemarkung
                crs:
                    - http://www.opengis.net/def/crs/OGC/1.3/CRS84
                    - http://www.opengis.net/def/crs/EPSG/0/4326
                    - http://www.opengis.net/def/crs/EPSG/0/25833
                storage_crs: http://www.opengis.net/def/crs/EPSG/0/25833
            - type: tile
                name: MVT-postgresql
                data:
                    host: <host>
                    port: <port>
                    dbname: <dbname>
                    user: <user>
                    password: <password>
                    search_path:
                        - dienste
                        - public
                table: gemarkungen
                id_field: schluessel_gemarkung
                geom_field: wkb_geometry
                options:
                    zoom:
                        min: 6
                        max: 22
                format:
                    name: pbf
                    mimetype: application/vnd.mapbox-vector-tile
                storage_crs: http://www.opengis.net/def/crs/EPSG/0/25833

And here is the excerpt from the log file:

[2025-07-03T08:11:11Z] {/usr/local/lib/python3.10/dist-packages/flask/app.py:875} ERROR - Exception on /collections/gemarkungen/tiles/WebMercatorQuad/10/368/297 [GET]
Traceback (most recent call last):
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 1963, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/default.py", line 943, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.UndefinedTable: relation "gemarkungen" does not exist
LINE 8:                         FROM "gemarkungen", bounds
                                     ^


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.10/dist-packages/flask/app.py", line 1511, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python3.10/dist-packages/flask/app.py", line 919, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python3.10/dist-packages/flask_cors/extension.py", line 176, in wrapped_function
    return cors_after_request(app.make_response(f(*args, **kwargs)))
  File "/usr/local/lib/python3.10/dist-packages/flask/app.py", line 917, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.10/dist-packages/flask/app.py", line 902, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)  # type: ignore[no-any-return]
  File "/pygeoapi/pygeoapi/flask_app.py", line 378, in get_collection_tiles_data
    return execute_from_flask(
  File "/pygeoapi/pygeoapi/flask_app.py", line 153, in execute_from_flask
    headers, status, content = api_function(actual_api, api_request, *args)
  File "/pygeoapi/pygeoapi/api/tiles.py", line 244, in get_collection_tiles_data
    content = p.get_tiles(layer=p.get_layer(), tileset=matrix_id,
  File "/pygeoapi/pygeoapi/provider/mvt_postgresql.py", line 186, in get_tiles
    result = session.execute(query, {
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 1415, in execute
    return meth(
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/sql/elements.py", line 523, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 1637, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 1842, in _execute_context
    return self._exec_single_context(
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 1982, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 2351, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 1963, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/default.py", line 943, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) relation "gemarkungen" does not exist
LINE 8:                         FROM "gemarkungen", bounds
                                     ^

[SQL: 
                WITH
                    bounds AS (
                        SELECT ST_TileEnvelope(%(z)s, %(x)s, %(y)s) AS boundgeom
                    ),
                    mvtgeom AS (
                        SELECT ST_AsMVTGeom(ST_Transform(ST_CurveToLine(wkb_geometry), 3857), bounds.boundgeom) AS geom ,"schluessel_gemarkung", "bezeichnung_gemarkung"
                        FROM "gemarkungen", bounds
                        WHERE ST_Intersects(wkb_geometry, ST_Transform(bounds.boundgeom, 4326))
                    )
                SELECT ST_AsMVT(mvtgeom, 'default') FROM mvtgeom;
            ]
[parameters: {'z': '10', 'x': '297', 'y': '368'}]
(Background on this error at: https://sqlalche.me/e/20/f405)

Finally, the requested tile envelopes are located somewhere in Canada and not within the specified spatial extent, which is in the north-east of Germany. I can see the following parameters in the log file:

[parameters: {'z': '10', 'x': '296', 'y': '367'}]
[parameters: {'z': '10', 'x': '296', 'y': '368'}]
[parameters: {'z': '10', 'x': '296', 'y': '369'}]
[parameters: {'z': '10', 'x': '297', 'y': '367'}]
[parameters: {'z': '10', 'x': '297', 'y': '368'}]
[parameters: {'z': '10', 'x': '297', 'y': '369'}]
[parameters: {'z': '10', 'x': '298', 'y': '367'}]
[parameters: {'z': '10', 'x': '298', 'y': '368'}]
[parameters: {'z': '10', 'x': '298', 'y': '369'}]
[parameters: {'z': '10', 'x': '299', 'y': '367'}]
[parameters: {'z': '10', 'x': '299', 'y': '368'}]
[parameters: {'z': '10', 'x': '299', 'y': '369'}]
[parameters: {'z': '10', 'x': '300', 'y': '367'}]
[parameters: {'z': '10', 'x': '300', 'y': '368'}]
[parameters: {'z': '10', 'x': '300', 'y': '369'}]

In PostgreSQL/PostGIS you can view the tile envelopes as follows:

select
	z, x, y,
	st_tileenvelope(z, x, y) as boundgeom,
	st_makeenvelope(12.22, 53.94, 13.77, 54.69) as bbox
from
	cast(10 as int) as z,
	generate_series(296, 300) as x,
	generate_series(367, 369) as y;

The correct tile envelopes in the area of the spatial extent would be the following:

select
	z, x, y,
	st_tileenvelope(z, x, y) as boundgeom,
	st_makeenvelope(12.22, 53.94, 13.77, 54.69) as bbox
from
	cast(10 as int) as z,
	generate_series(546, 551) as x,
	generate_series(325, 329) as y;

I can't find an explanation for this and maybe the problem lies in my configuration, but I would be extremely grateful for any help.

[webb-ben]

Can you confirm that you are working against the master branch? You might need to run
pip install git+https://github.com/geopython/pygeoapi.git ensure you are up to date.

#1979 introduced Postgres-MVT which only supported EPSG:4326 geometry. From the logs it appears this is the version you are running. #2022 built on the previous effort to correctly transform the bbox to the configured storage_crs.

I will note the MVT-Postgresql provider has limitations because it generates tiles on the fly with no built in caching or feature dropping logic at low zooms.

[gislkvr]

We are running pygeoapi with Docker. The used image is geopython/pygeoapi:latest. Last pull was today...

However, since today we have encountered other problems. Since the last pull nothing works anymore.
The problem seems to be in our pygeoapi-config.yml, because it works with the resources of this file.

I can see from the log file there is an error around the provider name: PostgreSQL. When I look here, I can no longer find postgresql.py.

I can't understand what has changed since the last pull and why our configuration file suddenly stopped working, but this is probably a different problem and not related to mvt_postgresql.

[webb-ben]

postgresql.py was renamed to sql.py when it was updated to support MySQL as well. That being said, plugin.py maps the "short names" to their corresponding python module. It was also updated to map PostgreSQL to the new location without needing to change configuration.

Unless you are importing the absolute path, I am not sure why this would have created an issue.