Automatic label point layer from Multipolygon layer using ST_PointOnSurface on the fly

[IncidGeo]

Hi everyone,

I've got a Multipolygon geom table in Postgres and use Geodjango/Maplibre GL JS to write read in this table.
In order to Label those Geometry in Maplibre, i need a point layer with Geometry rendered as ST_PointOnSurface from the Multipolygon geometry to avoid duplicate labels in the tiles.

I could trigger a function in postgres to create/delet entry in a separate Point geom table for every create/delete of the Multipolygon table.

But i was wondering if there is a more elegant way like creating a composite source from the existing Multipolygon table and a point layer source that would be generated on the fly (no table in Db) with ST_PointOnSurface before rendering the tiles avoiding duplicates labels ?

[sharkAndshark]

Maybe function source is what you want?

[IncidGeo]

Yes exactly what i was thinking but i don't quit understand how does it work, despite I've read several time the documentation.

[sharkAndshark]

Postgres function which takes z integer (or zoom integer), x integer, y integer as arguments and return bytea will be added as vector tile source.

You need to create a database function like this:

CREATE OR REPLACE
    FUNCTION my_points(z integer, x integer, y integer)
    RETURNS bytea AS $$
DECLARE
  mvt bytea;
BEGIN
  SELECT INTO mvt ST_AsMVT(tile, 'my_points', 4096, 'geom') FROM (
    SELECT
      ST_AsMVTGeom(
          ST_Transform(ST_PointOnSurface(geom) , 3857),
          ST_TileEnvelope(z, x, y),
          4096, 64, true) AS geom
    FROM some_multipolygon_table
    WHERE geom && ST_Transform(ST_TileEnvelope(z, x, y), 4326)
  ) as tile WHERE geom IS NOT NULL;

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

You could execute it on PgAdmin, which is open source and free.

PS: On second thought, I think to create a view is better than function for this.

[sharkAndshark]

https://www.pgadmin.org/docs/pgadmin4/development/view_dialog.html

[IncidGeo]

Thank you very much sharkAndshark,
I already use Pgadmin and function in postgres/postgis with triggers, etc
I've tried to explore the view way.
Something like :
CREATE VIEW public.mygeometry_table_label AS
SELECT ST_PointOnSurface(geom) as geom, public.mygeometry_table.id, public.mygeometry_table_any_name
FROM public.mygeometry_table

View is ok in Qgis, bit does'nt appear in localhost:3000/catalog
What i'am doing wrong ?

[IncidGeo]

Ok SRID was missing

CREATE VIEW public.mygeometry_table_label AS
SELECT ST_PointOnSurface(public.mygeometry_table.geom)::geometry(Point,4326) as geom, public.mygeometry_table.id, public.mygeometry_table_any_name
FROM public.mygeometry_table

It does appear in the catalog, thank you very much !

[IncidGeo]

I'll try to investigate in the future the Function way to have a composite source in MapLibre GL JS, it could reduce the number of sources.

[nyurik]

note that this will be a very slow view because you convert your geometry to another form, so if you do converted_geometry && bbox lookup, it will have to scan through the whole table to see if each geometry is within that bounding box. The filter in the @sharkAndshark 's answer above is much better -- WHERE geom && ST_Transform(ST_TileEnvelope(z, x, y), 4326) uses the original geometry as stored in the some_multipolygon_table table -- so as long as there is a GIS index on the geom column, it will work very fast. In your case, you may want to create a materialized view instead of a regular one, and create an index on that (yes, materialized views allow you to have indexes).

[sharkAndshark]

I agree with @nyurik . I actually mean to create view is much easier than function.

[IncidGeo]

Thank you nyurik and sharkAndshark i was also wondering about speed between view and function, you answer my question before i had time to ask.
I've successfully modify to my needs and use the function above. I was much easier than i thought, i didn't get at first that the source was available like any other source on localhost:3000/my_points.
Hope this thread will help others.