CODEWITHSUNDEEP
primary-keypostgisgeoserver
zsnyder
zsnyder

Reputation: 51

GeoServer: configuring a primary key metadata table for pk assignment using a PostGIS store

THE ISSUE

I am following directions in the GeoServer docs with respect to creating a primary key metadata table, but I am unable to assign the primary key of my geographic features (stored in a PostGIS store) using the value of an attribute with the same name in a WFS-t request.

Specifically, I am creating a UUID client-side (this is necessary), and then sending it as the 'uuid' attribute of the WFS-t request XML to GeoServer. Once in GeoServer's hands, I want that UUID to be inserted into the geometry table's primary key column, also named 'uuid'. What happens instead, is that GeoServer automatically creates its own primary key, something that looks like the following:

fid--46a202d5_15320520551_-7ffe

THE CONTEXT

Here is my geometry table SQL:

CREATE TABLE test_points (uuid VARCHAR(60) PRIMARY KEY NOT NULL);
ALTER TABLE test_points ADD COLUMN title VARCHAR(30) NOT NULL;
ALTER TABLE test_points ADD COLUMN body TEXT NOT NULL;
SELECT AddGeometryColumn('public', 'test_points', 'geometry', '4326', 'POINT', 2);

Here is my primary key metadata table SQL, basically same as in docs:

CREATE TABLE public.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_policy
) VALUES (
  'public',
  'test_points',
  'uuid',
  'assigned'
);

I have had the WFS-t insert operation working correctly (without primary key assignment), but here is a representative example of my WFS-t request XML, in case it helps:

<Transaction xmlns="http://www.opengis.net/wfs" service="WFS" version="1.1.0" xsi:schemaLocation="http://www.opengis.net/wfs http://schemas.opengis.net/wfs/1.1.0/wfs.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Insert>
    <test_points xmlns="my points">
      <geometry>
        <Point xmlns="http://www.opengis.net/gml" srsName="EPSG:3857">
          <pos>-11828783.001187595 4559315.863154193</pos>
        </Point>
      </geometry>
      <uuid>18984168-27cc-4fc8-8dea-bfbd39c42b22</uuid>
      <title>A Perfect Example</title>
      <body>Here's my body.</body>
    </test_points>
  </Insert>
</Transaction>

In terms of my GeoServer configuration, on the configuration page of the data store I am using I have the "expose primary keys" flag enabled, and the value of the primary key metadata table parameter is left blank (see below for other values I have tried)

FAILED ATTEMPTS

Here is a list of other things I have tried:

After each of these changes I deleted the test_points layer and then republished it. Additionally, I have tried deleting and recreating the store a couple times.

As you can see, I'm getting sort of desperate. Any help would be very greatly appreciated!

UPDATE: GeoServer log of insertion

Great idea iant, I'm sure that may help! Here is an example of what is logged by GeoServer during an insertion. Besides the fact that idgen is set to GenerateNew, which I assume is an indication that GeoServer thinks it should generate a new pk, all looks well to me. But, this is my first project using GeoServer, so that may not mean much. A heads up: the UUID, title, body, and geometry are different from the above XML example.

2016-03-01 16:14:29,755 INFO [geoserver.gwc] - DataStoreChange:{/mypoints}test_points PreInsert
2016-03-01 16:14:29,760 INFO [geoserver.gwc] - DataStoreChange: {/mypoints}test_points PostInsert
2016-03-01 16:14:29,763 INFO [geoserver.wfs] - 
Request: transaction
    service = WFS
    version = 1.1.0
    baseUrl = http://127.0.0.1:8080/geoserver/
    group[0] = wfs:insert=net.opengis.wfs.impl.InsertElementTypeImpl@5b886147 (feature: [SimpleFeatureImpl:test_points=[SimpleFeatureImpl.Attribute: uuid<uuid id=fid-4f745b22_1532fc9fb1c_-7ffa>=853596f8-9de1-477a-b287-f3cd114db90a, SimpleFeatureImpl.Attribute: title<title id=fid-4f745b22_1532fc9fb1c_-7ffa>=Another Test, SimpleFeatureImpl.Attribute: body<body id=fid-4f745b22_1532fc9fb1c_-7ffa>=For you!, SimpleFeatureImpl.Attribute: geometry<geometry id=fid-4f745b22_1532fc9fb1c_-7ffa>=POINT (-12044029.67283865 4265797.674539117)]], handle: null, idgen: <unset>, inputFormat: <unset>, srsName: null)
    insert[0]:
        feature[0] = SimpleFeatureImpl:test_points=[SimpleFeatureImpl.Attribute: uuid<uuid id=fid-4f745b22_1532fc9fb1c_-7ffa>=853596f8-9de1-477a-b287-f3cd114db90a, SimpleFeatureImpl.Attribute: title<title id=fid-4f745b22_1532fc9fb1c_-7ffa>=Another Test, SimpleFeatureImpl.Attribute: body<body id=fid-4f745b22_1532fc9fb1c_-7ffa>=For you!, SimpleFeatureImpl.Attribute: geometry<geometry id=fid-4f745b22_1532fc9fb1c_-7ffa>=POINT (-12044029.67283865 4265797.674539117)]
        idgen = GenerateNew
        inputFormat = text/xml; subtype=gml/3.1.1
    releaseAction = ALL

Upvotes: 3

Views: 3657

Answers (1)

Steven Kalt
Steven Kalt

Reputation: 1198

Adding a fid="your_target_id" attribute to the feature and a idgen="UseExisting" attribute to the wfs:Insert allowed me to assign an uuid.

Here's the best documentation I could find on idgen. I'm unclear on its analogues in WFS 1.0.0 and 2.0.0.

Upvotes: 1

Related Questions