Adding support for MVTs from Postgres directly #1978
Description
Is your feature request related to a problem? Please describe.
As part of the OGC Open Standards March 2025 Code Sprint, we propose to add support for on-the-fly MVT generation from Postgres/PostGIS feature tables. This is currently supported by using the MVT-Proxy provider along with Martin or pg_tileserv.
This feature should remove the need of Martin/pg_tileserv. It will also make it easy for users of pygeoapi to offer MVT tile support for existing collections on PostGIS that are served by the OGC Features API.
Describe the solution you'd like
We hope to add Support for on the fly generation of MVTs on Postgres/PostGIS tables as part of the sprint.
What we've done so far
-
Implementing this is more or less straightforward according to our estimation.
pg_tileservserves as an excellent resource to understand how to use PostGIS functions likeST_AsMVT,ST_TileEnvelopeetc. to generate an MVT givenz,xandy. It is also trivial to add support for both theWebMercatorQuadandWorldCRS84Quadtiling schemes. -
We've been able to extend the
BaseMVTProviderin a separate class calledMVTPostgresProvider. However, thePostgreSQLProviderhas a lot of appropriate fields and methods likeget_fieldsand the SQLAlchemy engine which we can be used to form and execute the query required for generating the MVT. -
We are unsure if
MVTPostgresProviderinheriting bothBaseMVTProviderandPostgreSQLProvideris a good idea. So our solution has tried to instantiate aPostgreSQLProvideras a field instead of relying on multiple inheritance. This has already caused a few issues since thedataandoptionsconfig fields for both these providers conflict with each other. Below is a snippet of the constructor of ourMVTPostgresProviderclass.
class MVTPostgresProvider(BaseMVTProvider):
def __init__(self, provider_def):
"""
Initialize object
:param provider_def: provider definition
:returns: pygeoapi.provider.MVT.MVTPostgresProvider
"""
super().__init__(provider_def)
pg_def = deepcopy(provider_def)
# remove the zoom option when passing config to PostgreSQL provider as it does not like dict options
del pg_def["options"]["zoom"]
self.postgres = PostgreSQLProvider(pg_def)
self.layer_name = provider_def["table"]
...Honestly, this approach does not feel or look right, so we'd be happy to get some feedback on how to do it in a way that complements the rest of the providers.
We've been able to implement the metadata APIs. The get tile API (/z/x/y?f=mvt) is next, and we should hopefully be done by the end of the code sprint.
Thanks!