PostGIS Performance – How to Increase Speed in PG_Tileserv

pg-tileservpostgispostgresqlvector-tiles

I need to increase the drawing speed of the tables/functions served up by pg_tileserv for a leaflet map.

I am running pg_tileserv on a digital ocean server with

2cpus, 4GB memory, 25 GB SSD and 4TB transfer

I am serving fairly dense parcel data county and state wide.

I know I can set a min zoom level on the front end in leaflet or in pg_tileserv config files but I need the parcels to be seen at least to zoom 14 on the map.

To serve up the larger parcel sets right now I generating the mbtiles with tippecanoe then serving them with tileserver-gl…its working fairly good but this parcel updates frequently and it takes time to export, generate the tiles and spin up the tleserver-gl

I'd like to avoid using geoserver, had a load of trouble trying to figure out how to setup .pbf on geoserver and I gave up.

Is there anything I can do in the postgres settings to increase rendering speed? and tips to tune pg_tileserv? or increase server transfer speed?

Best Answer

I came up with two function that significantly helped the layers draw on the leaflet map

1.Building off what @robin loche and @Paul Ramsey mentioned I threw together a crude example of a function that assesses the map zoom level and weeds out features that based on square footage. For the function below if the map zoom Z level is 14 or greater (zoomed in) then any geometry with a square footage of 100 or greater will be displayed. if its zoom levels 1-13 then anything under 10000 square feet will not be displayed. One can add different Z level square footage logic depending on their use case and data. So far it works fairly quick and I'm happy

CREATE OR REPLACE
FUNCTION public.zoom_removal(z integer, x integer, y integer)
RETURNS bytea
AS $$
DECLARE
    result bytea;
BEGIN
    WITH
    bounds AS (
      SELECT ST_TileEnvelope(z, x, y) AS geom,
     (CASE 
        when z >= 14 then 100           
        ELSE 10000 END
       ) as min_area
    ),
    mvtgeom AS (
      SELECT 
        ST_AsMVTGeom(ST_Transform(t.geom, 3857), bounds.geom) AS geom,
        t.parcelid,t.sqft
      FROM oh.open_parcels t join bounds
      on t.geom && st_transform(bounds.geom,3735)
      where t.sqft > bounds.min_area
    )
    SELECT ST_AsMVT(mvtgeom, 'public.zoom_removal')
    INTO result
    FROM mvtgeom;
    RETURN result;
END;
$$
LANGUAGE 'plpgsql'
STABLE
PARALLEL SAFE;

2. This function helped speed the drawing up even quicker than the above function. Depending on the map zoom level it will apply a tolerance to the st_simplify https://postgis.net/docs/ST_Simplify.html function. For my example these parameters worked really well because they were parcel buffers and as you zoomed out the exact shapes were not necessary to display so accurately

CREATE OR REPLACE FUNCTION nj.buffers(z integer, x integer, y integer, county_map character varying, buffer_type character varying)
 RETURNS bytea
 LANGUAGE plpgsql
 STABLE PARALLEL SAFE
AS $function$
DECLARE
    result bytea;
BEGIN
    WITH
    bounds AS (
      SELECT ST_TileEnvelope(z, x, y) AS geom,
     (CASE 
        when z >= 16 then 5
        when z = 15 then 50
        when z = 14 then 150
        when z = 13 then 250
        when z <= 12 then 350
        ELSE 1 END
       ) as simplify_tolerance
    ),
    mvtgeom AS (
      SELECT 
        ST_AsMVTGeom(ST_Transform(st_simplify(t.geom,simplify_tolerance), 3857), bounds.geom) AS geom,
        t.id,t.category
      FROM nj.all_category_buffers t join bounds
      on t.geom && st_transform(bounds.geom,3424)
      where county = county_map and category = buffer_type 
    )
    SELECT ST_AsMVT(mvtgeom, 'nj.buffers')
    INTO result
    FROM mvtgeom;
    RETURN result;
END;
$function$
;
Related Question