I have created an enterprise geodatabase with ArcGIS 10.5 in a PostgreSQL 9.5 database. And I set the PostGIS template to my database. So when I create a feature class in database, I can add this layer in QGIS and I can see the data. But when I add the data in QGIS layer that comes from database and save, it gives an error like this:

Could not commit changes to layer mypoints

Errors:ERROR: 1 feature(s) not added.

Provider Errors:
       PostGIS error while adding features: ERROR: column "shape" 
is of type geometry but expression is of type st_geometry
  LINE 1: ...sde"."mypoints"("shape","objectid","name") VALUES (st_geomfro...
HINT: You will need to rewrite or cast the expression.

My database keywords setted ad PG_GEOMETRY like this:

UI_TEXT             "User Interface text description for POSTGIS geometry storage" 

How can I solve this problem?

  • "But when I add the data in QGIS and save" when you try and import a layer into the database throught QGIS? or just add the layer to the display? – ziggy Dec 11 '17 at 14:15
  • Layer comes from database in QGIS. – barteloma Dec 11 '17 at 14:38

First off, it is improper to use the sde login to own spatial data. The correct procedure is to create a data ownership login (with a matching schema) to manage spatial data. Secondly, because the table is registered with ArcGIS, the rowid column may be "SDE-set", which requires special handling from non-Esri clients.

The error message indicates that the table does not use PostGIS geometry, but Esri sde.st_geometry, which is not compatible with QGIS.

The proper procedure to managing client-agnostic data in PostgreSQL is to use SQL to create the table, using the geometry datatype, with a serial datatype to auto-increment the objectid column. Then you can access the table from ArcGIS through a Query Layer. The table can't be edited with Desktop, but it can be manipulated with SQL or through DA cursors.

  • I created another user named "gisuser" and setted privilages to geometry table. So ti does not give any error. Why sde user does not edit from QGIS? – barteloma Dec 13 '17 at 17:50
  • I would be struck down by lightning if I ever tried, after telling folks not to use the sde user for twenty-two years. – Vince Dec 14 '17 at 1:29

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.