1

I have a layer on GeoServer, taking very detailed data from a PostGIS table. I can see the size of the table is quite big

Size 977MB, External Size 977Mb

becoming the performance really low. GIST indexes and normal index to unique id have been applied.

As the user zooms in, the speed becomes worse (normal, more detailed data appears)

I have implemented JVM optimization https://docs.geoserver.org/stable/en/user/production/container.html#optimize-your-jvm as well as postGIS https://postgis.net/workshops/postgis-intro/tuning.html

I can cache the initial layer view but then the user has the possibility to apply feature filtering, so at some poing caching is useless.

The geometry types are MULTIPOLYGONS: that is, very few rows on the table but each of these with huge data covering a lot of area.

I was thinking maybe splitting these multipolygons to POLYGONS could help on the performance? so each time user zooms in, GeoServer will find faster which polygons are in the view port and has not to deal with so big (MULTI)polygons.

Another possibility could be to at some zoom level show a simplified layer/table, and 'closer' we are, switch to a more detailed layer/table.

Does some of these options make sense? other advises to take into account? Moreover, my users are supposed to be on areas with low internet connectivity..

UPDATE: ST_Subdivide() helps a lot on performance! but newly created polygons border cannot be hidden from Geoserver (even setting polygon border to 0 or transparent), they are slightly visible

enter image description here

3
  • 1
    Normally when zoomed in the speed is better because there are less features to render. Selecting the features within bbox is fast when spatial index exists. It may really be that big multipolygons make the issue because spatial index is not effective if is cannot discard features. Because data are in PostGIS I would make a test with ST_Subdivide postgis.net/docs/ST_Subdivide.html.
    – user30184
    Oct 8 '20 at 19:55
  • ST_Subdivide works great, much faster the visualization now! the only problem is that when there is no way to completely hide the newly created border polygons when visualizing on Geoserver, even setting up a no-border on the SLD styling... I updated my question with an image Oct 15 '20 at 10:11
  • You need to color the borders to match the fill (not leave them empty)
    – Ian Turton
    Mar 17 at 8:53
1

Cutting up your MultiPolygons might help with rendering at higher zoom levels. I must say its odd you get worse speed the more you zoom in, that sounds like postgis is struggling to cut of the large polygons and serve to geoserver.

Something like this would work to quickly separate the large MultiPolygons. Using ST_COLLECTIONEXTRACT will return only a certain type of geometry if thats something you require, otherwise all geometry types will be returned without it.

    INSERT INTO new_table (geometries)
    SELECT 
        ST_COLLECTIONEXTRACT((ST_DUMP(geom_column)).geom, 3) as geometries
    FROM
        table

The ideal solution would be to create a grid on the area over your data and cut your geometries even further so that postgis will mostly serve small squares to geoserver for rendering, unless the user zooms out far enough.

Something like this

    SELECT create_grid_table(ST_geomfromText('your data bounding box'), 1, 1, 'grid'); -- 1km width and height
    INSERT INTO new_table (geometries)
    SELECT 
        ST_COLLECTIONEXTRACT(ST_Intersection((ST_DUMP(geom_column)).geom), 3) as geometries
    FROM
        table, grid
    WHERE ST_Intersects(geometries, grid.geoms)

This obviously requires a grid. I recommend creating one in a separate table like below (or check here). Note your squares won't be exactly fishnet-looking

create function create_grid_table(bound_polygon geometry, width_step integer, height_step integer, table_name text) returns integer
    language plpgsql
as
$$
DECLARE
  Xmin    DOUBLE PRECISION;
  Xmax    DOUBLE PRECISION;
  Ymax    DOUBLE PRECISION;
  X       DOUBLE PRECISION;
  Y       DOUBLE PRECISION;
  NextX   DOUBLE PRECISION;
  NextY   DOUBLE PRECISION;
  CPoint  public.geometry;
  tile_counter       INTEGER;
  SRID    INTEGER;
BEGIN
  Xmin := ST_XMin(bound_polygon);
  Xmax := ST_XMax(bound_polygon);
  Ymax := ST_YMax(bound_polygon);
  SRID := ST_SRID(bound_polygon);

  Y := ST_YMin(bound_polygon); --current sector's corner coordinate
  tile_counter := -1;

  --prepare a table for output
  IF (
    SELECT EXISTS(
               SELECT 1
               FROM pg_catalog.pg_class c
                      JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
               WHERE n.nspname = 'static'     -- schema name
                 AND c.relname = table_name -- table name
                 AND c.relkind = 'r' -- only tables
             )
  ) THEN
    EXECUTE 'TRUNCATE static.' || table_name || ';';
  ELSE
    EXECUTE 'CREATE TABLE static.' || table_name || ' (' ||
            'gid serial PRIMARY KEY NOT NULL,' ||
            'wkb_tile geometry(POLYGON,' || SRID || ') NOT NULL, ' ||
            'wkb_tile_centroid geometry(POINT,' || SRID || ') NOT NULL, ' ||
            'wkt_tile TEXT' ||
            '); ';
    EXECUTE 'CREATE INDEX static_' || table_name || '_gix ON static.' || table_name || ' USING GIST (wkb_tile);';
  END IF;

  --start generating tiles
  <<yloop>>
    LOOP
      IF (Y > Ymax) THEN
        EXIT;
      END IF;

      -- noinspection SqlUnreachable
      X := Xmin;
      -- noinspection SqlUnreachable
      <<xloop>>
        LOOP
          IF (X > Xmax) THEN
            EXIT;
          END IF;

          CPoint := ST_SetSRID(ST_MakePoint(X, Y), SRID);
          NextX := ST_X(ST_Project(CPoint, $2, radians(90))::geometry);
          NextY := ST_Y(ST_Project(CPoint, $3, radians(0))::geometry);

          tile_counter := tile_counter + 1;

          -- insert the generated tile
          EXECUTE 'INSERT INTO static.' || table_name || ' (wkb_tile, wkb_tile_centroid) ' ||
                  'VALUES ' ||
                  '(' ||
                  'ST_MakeEnvelope(' || X || ', ' || Y || ', ' || NextX || ', ' || NextY || ', ' || SRID || '),' ||
                  'ST_Centroid(ST_MakeEnvelope(' || X || ', ' || Y || ', ' || NextX || ', ' || NextY || ', ' || SRID || '))' ||
                  ')';
          X := NextX;
        END LOOP xloop;
      -- noinspection SqlUnreachable
      CPoint := ST_SetSRID(ST_MakePoint(X, Y), SRID);
      -- noinspection SqlUnreachable
      NextY := ST_Y(ST_Project(CPoint, $3, radians(0))::geometry);
      -- noinspection SqlUnreachable
      Y := NextY;
    END LOOP yloop;

  EXECUTE 'UPDATE static.' || table_name || ' SET wkb_tile = ST_SnapToGrid(wkb_tile,0.00001);';
  EXECUTE 'UPDATE static.' || table_name || ' SET wkt_tile = ST_AsText(wkb_tile);';
  EXECUTE 'CLUSTER static.' || table_name || ' USING static_' || table_name || '_gix;';

  RETURN tile_counter;
END;
$$;
2
  • your answer is great, Kevin. Did not try it but i am afraid the border ot the new created polygons (the grid) will be slightly visible on Geoserver like what I tested with ST_Subdivide() (see new image on my edited question) Oct 15 '20 at 10:22
  • the grid is only used for cutting isn't part of the final dataset. there are a few css options you can use to not show borders on geoserver. you may have yours enabled. i use the process above which also uses geoserver to display the final result and we have no borders, which is why i think it may be a css setting on your geoserver Oct 16 '20 at 12:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.