ArcGIS Server Featurelayer: Error on adding features via JavaScript API after switch from Oracle db to PostGIS

Question

We have a webapp which consumes services published on ArcGIS Server. All database relevant operations are done with ESRI ArcGIS abilities. We now switched from Oracle 11g to PostgreSQL 9.4 with PostGIS 2.5. Although everything is done with Esri ArcGIS abilities it is necessary to make changes on the code (this may be bad design of the app or maybe it's unavoidable... doesn't matter at the moment). I made these changes as far as I can see and almost everything is working fine, except adding/editing features via the exposed feature layers.

When trying to add a feature ArcGIS Server will report an error like this in the log:

Error: Underlying DBMS-Error[Error: Geometry has Z dimension but column does not ({featureclass})::SQLSTATE=22023].

The table is defined with Point and another table with Polygon. So it is correct that the column does not support Z dimension. But I'm using the ArcGIS JavaScript API 3 which doesn't support Z coordinates at all according to the point documentation. The featurelayers also don't support Z and M values.

When looking with Chrome developer tools what data are sent to the server I get something like this:

[{
    "geometry": {
        "x": 6359.682509019719,
        "y": 7720831.9973746035,
        "spatialReference": {
            "wkid": 3857
        }
    },
    "attributes": {
       <mylist of attributes>
    }
}]

The geometry looks fine and has nothing with Z. I checked the attributes multiple times that they have the correct spelling, having valid values and are all lower case (what is default for identifiers in PostgreSQL).

When using this example (of course with the attributes) on the FeatureServer REST website and adding the feature via "addFeatures" I get following JSON feedback:

{
 "error": {
 "code": 400,
 "message": "Unable to complete operation.",
 "details": [
   "Rowbuffer creation failed."
  ]
 }
}

This is some more information but I still don't know what does cause this problem. In the server logs I will again have the error message above.

What could cause such an error message when using featurelayers in combination with PostGIS?

Edit:

The problem only occurs when adding new features to the layer. If I'm deleting or editing a feature everything works fine and the changes are passed to the database without errors.

Answer 1

So, after some digging I now found out that this seems to be a bug by ESRI. Since our experiences with issues like that, there ESRI stated a "won't fix" even if they approved the issue as bug, I won't make the effort to report it. Expectation is again a "won't fix"... it's not worth the time.

For all others running into this issue I will describe it here:

I tried logging all SQL-statements by PostgreSQL to find out what the problem is. Unfortunately PostgreSQL did only log the INSERT-statement with parameter placeholders. It looks like that parameters are only logged on successfull statements and if a rollback occurs they aren't logged.

So the next try was to look into the packages sent to PostgreSQL with Wireshark. I was able to identify the packages containing the parameter values and did some comparisons with the queries from operations, like updating the geometry of a feature which already had a geometry, which caused no problems. Only those operations failed where the geometry was added initially.

The conclusion is, that on adding a new feature ArcGIS Server first generates an empty dataset filled with an empty geometry and some forced default values. I assume that the ArcGIS Server would send afterwards on success an UPDATE-statement with the actual parameters. Anyway this INSERT-statement gets something like this as value for the geometry/shape:

01040000c000000000

According to this geometric formats converter this value means:

MULTIPOINT EMPTY

While a working query will conatain something like this as parameter value for geometry/shape:

0101000000b0a44ea0b3033c417ca52cc5d6736041
POINT(1835955.6262000017 8625846.161700003)

Earlier in the migration process I had also trouble with an insert cursor causing the same error message like I get at the current problem. As reminder:

Error: Underlying DBMS-Error[Error: Geometry has Z dimension but column does not ({featureclass})::SQLSTATE=22023].

In that earlier case the error message was caused by empty geometries. In this cases I'm now setting the shape-property to null and PostgreSQL gets also NULL as parameter value now. This works fine.

Conclusion is that ESRI / the ArcGIS Server doesn't fullfil the expactation of PostGIS for a valid and non empty geometry or a null value. It's not possible to force another behaviour so I will now build some REST endpoints for the failing operations and execute them with direct access to PostgreSQL directly in our backend.

Answer 2

I had problems adding data from Arcgis enterprise,with the same error. Editing worked but adding a point did not. Guess arcgis enterprise = javascript?

Found a workaround using FME. Use the transformers 3DForcer and then Measuresetter on your table. (IF you don't have FME I guess there is a function in Postgis but haven't tried, probably ST_Force_3dm.)

Only tried with a simple point layer. I'm also able to add points and edit them in Qgis version 22. Did not work in Qgis version 16.

UPDATE:

This SQL works, don't have to use FME. You have to use ST_Force4D and not ST_Force3dm.

ALTER TABLE your_table ALTER COLUMN your_geom_column TYPE geometry(POINTZM, your_epsg) USING ST_Force4D(your_geom_column);

---

I have not tried line or polygon layers