3

I have a layer in GeoServer. It is based on PostGIS view. It seems that GeoServer generates random featureIDs. The view has the same columns as the underlying table, including the PK column. But GeoServer ignores the PK column for layers based on the view.

When I created a layers based on the table itself, GeoServer used PK as featureID.

How do I make GeoServer do the same for a view?


I've accepted @iant's answer. SQL I used to create the metadata table is below. After I ran it on the PostGIS DB, I've restarted GeoServer and recreated the layer. It worked.

-- http://docs.geoserver.org/stable/en/user/data/database/primarykey.html#metadata-table-description

CREATE TABLE gt_pk_metadata_table (
  table_schema VARCHAR(32) NOT NULL,
  table_name VARCHAR(32) NOT NULL,
  pk_column VARCHAR(32) NOT NULL,
  pk_column_idx INTEGER,
  pk_policy VARCHAR(32),
  pk_sequence VARCHAR(64),
  unique (table_schema, table_name, pk_column),
  check (pk_policy in ('sequence', 'assigned', 'autoincrement'))
);


INSERT INTO gt_pk_metadata_table(
            table_schema, table_name, pk_column, pk_column_idx, pk_policy, 
            pk_sequence)
    VALUES (
    'public',
    'llsoa',
    'identifier',
    1,
    'assigned',
    null);

2 Answers 2

9

You need to provide a metadata table which contains the view name and the primary key name. The documentation here explains how.

1
  • Note that the metadata table needs to be indicated to GeoServer. This can be done by setting the value for 'Primary key metadata table' for a specific 'store' via the web UI.
    – b9s
    May 18, 2021 at 14:43
3

Except for the documented solution as linked in Ian's answer, one could also define a new SQL view in GeoServer. If the source view in the database has a column with a stable id, then ticking its "Identifier" checkbox in such view-of-a-view will promote such ids to be stable feature ids, without the use of a table gt_pk_metadata:

GeoServer view

Unlike the gt_pk_metadata-solution, the above yields a read-only layer, hence does not support WFS-T.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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