ArcGIS Server – Feature Service from Plain PostGIS in ArcGIS Server

arcgis-serverfeature-servicepostgis

Using 10.2 ArcMap/ArcGIS Server on Windows I've successfully published some map services from my PostgreSQL/PostGIS data and now I want to enable editing the features through a JavaScript web app. This is NOT through SDE tables (i.e., creating a geodatabase). Yes, I am using PostgreSQL 9.3.1 and PostGIS 2.1… but everything else seems to work fine but this PK error. How do I get ArcGIS Server to trust me that the incrementing PK I've created is OK?

"00166: Layer's ObjectID is not maintained by the database and the feature service is being published with Create capability enabled"

So, following the JS API examples I am enabling both the geometry service and the Feature Access service with all the Operations and Capabilities enabled.

I began using the serial data type for the OBJECTID primary key which appears to be a shortcut for creating an integer, auto-incrementing key as follows:

CREATE TABLE test_schema.volmonwgs84e
(
  objectid serial NOT NULL,
  volmon24_i integer,
  site_id integer,
  waterbody character varying(30),
  geom geometry(MultiPoint,4326),
  CONSTRAINT volmonwgs84e_pkey PRIMARY KEY (objectid)
)
WITH (
      OIDS=FALSE
);
ALTER TABLE test_schema.volmonwgs84e
  OWNER TO postgres;


CREATE INDEX volmonwgs84e_geom_gist
  ON test_schema.volmonwgs84e
  USING gist
  (geom);

However, when I go to publish the service (from ArcMap) the Analyzer shows the error above.

Right-clicking the error kindly offers to "Add an auto-incrementing ID" for me, to which it later responds with the following. AFAIK, I ALREADY have an auto-incrementing field anyhow.

"Executing: AddIncrementingIDField testing.test_schema.volmonwgs84e BOO
Start Time: Tue Dec 10 15:55:06 2013
ERROR 000152: You cannot add an incrementing ID field to a table registered with the geodatabase.
Failed to execute (AddIncrementingIDField).
Failed at Tue Dec 10 15:55:07 2013 (Elapsed Time: 0.70 seconds)[/CODE]

So, what data type and rules need to be on this auto-incrementing field, and what is AddIncrementingIDField trying to do for me?

UPDATE:

Thinking that my issue was the "new" PostGIS/PostgreSQL approaches to things I've also tried creating a table with these older approach as follows:

  1. manually setting the sequence on integer PK column instead of using the SERIAL type,
  2. using "SELECT AddGeometryColumn" to create the geometry column, and therein using the old constraints approach with the final argument = false (http://postgis.net/docs/AddGeometryColumn.html) 
CREATE SEQUENCE test_schema.mypoint_const_b_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
 START 1
CACHE 1;

 ALTER TABLE test_schema.mypoint_const_b_seq
 OWNER TO postgres;

CREATE TABLE test_schema.mypoint_const_b
(
  objectid integer NOT NULL DEFAULT nextval('test_schema.mypoint_const_b_seq'::regclass),
  volmon24_i integer,
  site_id integer,
  waterbody character varying(30),
  CONSTRAINT mypoint_const_b_pkey PRIMARY KEY (objectid)
)
WITH (
  OIDS=FALSE
);

SELECT AddGeometryColumn ('test_schema','mypoint_const_b','geom',4326,'MULTIPOINT',2,     false);

ALTER TABLE test_schema.mypoint_const_b
  OWNER TO postgres;

CREATE INDEX mypoint_const_b_geom_gist
  ON test_schema.mypoint_const_b
  USING gist
  (geom);

But I still get a fatal error when trying to create a service with Feature Access "Create" capability enabled with the same message: [CODE]"00166: Layer's ObjectID is not maintained by the database and the feature service is being published with Create capability enabled"
[/CODE]

I know that the database IS maintaining the PK because I can add features using QGIS just fine. AND, ArcMap's properties for the indexes on the layer shows objectid is a unique index.

PS, buried in this excellent link (http://proceedings.esri.com/library/userconf/proc13/tech-workshops/tw_192.pdf) I see that Arc doesn't like fields with quotes (which is how you force CAPS on OBJECTID. Lowercase objectid appears to be just fine to ArcMap, but perhaps not ArcServer.

Best Answer

You need to have previously registered the PostGIS table with the Geodatabase in ArcCatalog.

Right-click menu in Catalog Tree

Image from right-click menu in Catalog Tree on table.

Be sure that, when you reach the "Fields" tab during registration, you have your "objectid" field recognized as having "Object ID" type, as the GID field in following image.

Fields tab shown in registration process

Next, you shouldn't use a sequence of your own to populate the objectid values. You should use a ON INSERT trigger that calls a SDE database function named "sde.next_rowid". Please refer to these ArcGIS Help items ArcGIS Help - Editing geodatabase data using SQL.

Perhaps you should get some additional background information, like the one you can get in ESRI presentations (as this for ArcGIS 9.3)

Related Question