Geoserver WFS-T not saving edits from QGIS to MS SQL layer

Question

I added a MS SQL layer from my Geoserver to QGIS. It will save changes for attributes in the table and it will save the file if I delete features. However, if I add a feature or change a feature, it will not save the changes and Geoserver returns a commit error:

Errors: ERROR: 1 geometries not changed. Provider errors: unsuccessful service response: Update error: Error occured updating features

I do not see anything specific in the Geoserver documentation for allowing geometry edits to MS SQL layers. Am I missing something? Why does it save deletes and attribute changes but not geometry edits? Is there a special setting somewhere in Geoserver or MS SQL Sever that allows geometry edits to be saved to a MS SQL layer?

Answer 1

I have used WFS-T through GeoServer to edit data on a MS SQL server before. It was through a Leaflet.js plugin, not through QGIS, but my experience should at least confirm that it works.

I remember that there were some field types that GeoServer couldn't edit though, including UUID fields and possibly some types of Geometry (maybe it was Geography or Multi-Polylines/Multi-Polygons?). If the offending fields were present in the row the whole record couldn't be edited.

GeoServer is probably logging the error. Log in as an admin and go to Settings -> Global -> Logging Profile and change the VERBOSE or one of the *_DEVELOPER logging profiles and then watch the log as you make a save request.

Answer 2

This problem was caused by not correctly uploading shapefiles into a MS SQL database with the SharpGIS Shape2SQL tool.

The Shape2SQL tool sets a constraint on the STSrid property of the geometry field (geom.STSrid) when it creates a database table. If you look at a table created by Shape2SQL in Microsoft SQL Server Manager, you will see a constraint called "enforce_srid_geometry_filename" in the constraints folder under the expanded database table in the object explorer. To see the value of the geom.STSrid property in the table records, you can run the query: Select geom.STSrid From "your table".

MS SQL Geography data does not work in Geoserver, so you have to upload your data as projected Geometry data. However, if you do this with the Shape2SQL tool, the "Set SRID" option is unchecked by default. In this case, it sets the geom.STSrid property to 0 and sets the constraint to keep it to 0. If you then try to make an edit from QGIS through WFS-T to the MS SQL layer in Geoserver, it will try to set the geom.STSrid to the declared EPSG projection of the file and it will fail because the constraint is set to 0.

To fix this, check the "Set SRID" option in Shape2SSQL and set the correct EPSG number for the projection of the shapefile. This will set the constraint to enforce the correct EPSG number instead of 0. The EPSG you set will show up in the Native SRS under Coordinate Reference Systems when you publish the layer in Goeserver and the features will display correctly in the layer preview with the openlayers format.

Edits made with QGIS using the proper EPSG number will then go through and be saved to the MS SQL layer.

Another stumbling block to mention is the fact that the "Create Spatial Index" is checked on by default in the Shape2SQL tool. Shape2SQL won’t even create a table in the database if it is checked on, so this should be unchecked.

In summary, when using the Shape2SQL tool to create an editable MS SQL layer in Geoserver:

1) Load projected shapefiles as MS SQL Geometry data.

2) Check the "Set SRID" option in Shape2SQL and set the proper EPSG number so that the geom.STSrid property and the geom.STSrid constraint will be correct.

3) Uncheck the "Create Spatial Index" option in Shape2SQL to create the tables.