Adding support for MVTs from Postgres directly

[ThorodanBrom]

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_tileserv serves as an excellent resource to understand how to use PostGIS functions like ST_AsMVT, ST_TileEnvelope etc. to generate an MVT given z, x and y. It is also trivial to add support for both the WebMercatorQuad and WorldCRS84Quad tiling schemes.

• We've been able to extend the BaseMVTProvider in a separate class called MVTPostgresProvider. However, the PostgreSQLProvider has a lot of appropriate fields and methods like get_fields and the SQLAlchemy engine which we can be used to form and execute the query required for generating the MVT.

• We are unsure if MVTPostgresProvider inheriting both BaseMVTProvider and PostgreSQLProvider is a good idea. So our solution has tried to instantiate a PostgreSQLProvider as a field instead of relying on multiple inheritance. This has already caused a few issues since the data and options config fields for both these providers conflict with each other. Below is a snippet of the constructor of our MVTPostgresProvider class.

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!

[ThorodanBrom]

The GitHub repo link is here - https://github.com/ThorodanBrom/pygeoapi/tree/add-mvt-postgres-providerOn 27 Mar 2025 1:02 am, Jo ***@***.***> wrote: @ThorodanBrom thanks for bringing this up. Do you have a link for the GitHub repo where you are developing this code?—Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you were mentioned.Message ID: ***@***.***> doublebyte1 left a comment (geopython/pygeoapi#1978) @ThorodanBrom thanks for bringing this up. Do you have a link for the GitHub repo where you are developing this code? —Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you were mentioned.Message ID: ***@***.***>

[ThorodanBrom]

We were generally just suspicious of the idea of multiple inheritance! We read a bit about it for Python and came to a hasty conclusion that it wasn't a good approach.We also couldn't find any instances of multiple being used in pygeoapi in any of the providers (we didn't see any mixin classes).It was more of a subjective decision I guess. If you think that it's OK to use it here, we'll try it out.On 27 Mar 2025 1:03 am, Jo ***@***.***> wrote: Can I ask why you discarded the idea of multiple inheritance?—Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you were mentioned.Message ID: ***@***.***> doublebyte1 left a comment (geopython/pygeoapi#1978) Can I ask why you discarded the idea of multiple inheritance? —Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you were mentioned.Message ID: ***@***.***>