Bug report #14621

linestring being created as linestringZ (MSSQL)

Added by Andre Jesus over 3 years ago. Updated over 2 years ago.

Status:Closed
Priority:Severe/Regression
Assignee:-
Category:Data Provider/MSSQL
Affected QGIS version:2.18.4 Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:not reproducable
Crashes QGIS or corrupts data:No Copied to github as #:22587

Description

I don't know why but QGIS is setting my linestring and point MSSQL layers as linestringZ and pointZ setting 0 as Z value, blocking me of saving any edit I make as MSSQL 2008 does not support linestringZ or pointZ geometry types.

I check my database and I get only linestring, but If I create a new field and set It to show me the WKT, half of my data is linestringZ.

Imagem_855.png (270 KB) Andre Jesus, 2016-05-05 06:31 AM

History

#1 Updated by Giovanni Manghi over 3 years ago

  • Category set to Data Provider/MSSQL
  • Target version deleted (Version 2.14)

#2 Updated by Andre Jesus over 3 years ago

Here's a screenshot.

For some reason the problem when trying to save a edit is gone.

#3 Updated by Andre Jesus over 3 years ago

  • Target version set to Version 2.14

Ok, so the problem is not solved at all.

It's now affecting several users. They can not edit the geometry anymore because QGIS add Z values to the coordinates and MSSQL does not accept it.

Here is the error:

Layer <table_name>: [Microsoft][ODBC SQL Server Driver][SQL Server]A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry": System.FormatException: 24114: The label PointZ (601929.60017 in the input well-known text (WKT) is not valid. Valid labels are POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, or GEOMETRYCOLLECTION. System.FormatException: at Microsoft.SqlServer.Types.OpenGisWktReader.ParseTaggedText(OpenGisType type) at Microsoft.SqlServer.Types.OpenGisWktReader.Read(OpenGisType type, Int32 srid) at Microsoft.SqlServer.Types.SqlGeometry.GeometryFromText(OpenGisType type, SqlChars text, Int32 srid) . [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated. QODBC3: Unable to execute statement

WKT from file in Shapefile or MSSQL:

wkt_geom objectid
Point (601929.60017591051291674 8280563.19315726682543755) 22

WKT from QGIS after a move:

wkt_geom OBJECTID
PointZ (601929.60017591051291674 8280563.19315726682543755 0) 22

This problem is happening in 2.14.0, 2.14.1 and 2.14.2. It affect all geometry types, not only linestring.

#4 Updated by Andre Jesus over 3 years ago

Sorry to keep bothering but I still can't use Master to edit geometries with MSSQL.
I noticed some differences between the way 2.8.x and 2.14.x reads the geometry: https://goo.gl/r9sLq3

I'm not sure this is any help, but there you go

#5 Updated by Giovanni Manghi over 3 years ago

  • Priority changed from High to Severe/Regression

tagging as regression.

#6 Updated by Jürgen Fischer about 3 years ago

  • Status changed from Open to Feedback

Andre Jesus wrote:

Sorry to keep bothering but I still can't use Master to edit geometries with MSSQL.
I noticed some differences between the way 2.8.x and 2.14.x reads the geometry: https://goo.gl/r9sLq3

I'm not sure this is any help, but there you go

What are you doing exactly and on what data? I loaded airports and railroads from the demo dataset to mssql (via 2.8.9's browser, but to MSS 2012) and can update both points and line layers using the nodetool in master and 2.14.3 without problems.

#7 Updated by Andre Jesus about 3 years ago

I'm just trying to edit a geometry, simple editions like to move. Sometimes create a geometry algo adds Z values.

It's happening in my SQLSERVER 2016 test server too.

Here is the data:
INSERT [dbo].[TUB_DISTRIBUICAO] ([OBJECTID], [GEOMETRY] ) VALUES (5, 0x0000000001158465C8A99E6122410DB59A90E7925F4131E2FE1DAC612241CFD91234E8925F4100000000000000000000000000000000 )
INSERT [dbo].[TUB_DISTRIBUICAO] ([OBJECTID], [GEOMETRY] ) VALUES (18913, 0x00000000011531E2FE1DAC612241CFD91234E8925F41D7C05B14E06122414EA0C180EA925F4100000000000000000000000000000000)

EPSG:29191
Using

#8 Updated by Jürgen Fischer about 3 years ago

Andre Jesus wrote:

I'm just trying to edit a geometry, simple editions like to move. Sometimes create a geometry algo adds Z values.

Unfortunately I can't reproduce. Moving and editing with the node tool doesn't produce Z geometries here.

#9 Updated by Andre Jesus about 3 years ago

This is not a isolated situation, all users in my network have the same problem. That's the reason we are stuck with 2.8.9.

If you wish I can set up a Teamviewer for you, so you can see what is going on, enable a debug, check the database... your call.

E-mail me.

#10 Updated by Giovanni Manghi about 3 years ago

  • Target version changed from Version 2.14 to Version 2.16

#11 Updated by Giovanni Manghi about 3 years ago

  • Status changed from Feedback to Open

#12 Updated by Jürgen Fischer about 3 years ago

  • Status changed from Open to Feedback

#13 Updated by Giovanni Manghi about 3 years ago

The reporter has already left feedback and offered a way to have the issue checked, why tagging as feedback again?

#14 Updated by Jürgen Fischer about 3 years ago

Giovanni Manghi wrote:

The reporter has already left feedback and offered a way to have the issue checked, why tagging as feedback again?

We still need information to reproduce the problem.

#15 Updated by Andre Jesus about 3 years ago

Confirmed the same problem in 2.14.4 and 2.16.0

Installed 2.14.4, tested (error). uninstalled , cleared folders and windows registry.
Installed 2.16.0 , tested (error). uninstalled , cleared folders and windows registry.
Installed 2.14.4 x86, tested (error). uninstalled , cleared folders and windows registry.
Installed 2.8.9, tested (okey).

So, ever since the connection dialog changed for MSSQL, the problem with the geometry type started.

#16 Updated by Giovanni Manghi about 3 years ago

  • Status changed from Feedback to Open
  • Affected QGIS version changed from 2.14.0 to 2.16.0

#17 Updated by Andre Jesus about 3 years ago

Problem persists: 2.16.1


This is how I manage to work around this problem:

I had to recreate all problematic geometries from WKT using the script below:

declare @txt varchar(max), @g geometry, @a int, @b int
set @a = (select min(oid) from TABLE)
set @b = (select max(oid) from TABLE)
While (@a <= @b)
begin
set @txt = (select geometry.STAsText() from TABLE where oid = @a)
set @g = geometry::STGeomFromText(@txt, 0);
update TABLE set geometry = @g where oid = @a
set @a = (select min(oid) from TABLE where oid > @a)
end

I'm working for a couple of hours and It all seems ok.
Does anyone knows why does It solved the problem?

#18 Updated by Giovanni Manghi over 2 years ago

  • Target version changed from Version 2.16 to Version 2.18
  • Status changed from Open to Feedback
  • Affected QGIS version changed from 2.16.0 to 2.18.4

Andre Jesus wrote:

Problem persists: 2.16.1

I believe the problem persist also in 2.18.4, correct?

#19 Updated by Giovanni Manghi over 2 years ago

I have a report from a MS SQL Server 2014 user that this issue in not confirmed, can anyone else give it a try?

#20 Updated by Andre Jesus over 2 years ago

Giovanni Manghi wrote:

I have a report from a MS SQL Server 2014 user that this issue in not confirmed, can anyone else give it a try?

Ever since I recreated all geometries in the database the problem never happened afterwards.

My theory is the geometries stored in the database had no SRID added to them, they all were SRID: 0.
When I recreated them I also validated invalid geometries and set the right SRID.

No problems so far.

#21 Updated by Giovanni Manghi over 2 years ago

  • Status changed from Feedback to Closed
  • Resolution set to not reproducable

Andre Jesus wrote:

Giovanni Manghi wrote:

I have a report from a MS SQL Server 2014 user that this issue in not confirmed, can anyone else give it a try?

Ever since I recreated all geometries in the database the problem never happened afterwards.

My theory is the geometries stored in the database had no SRID added to them, they all were SRID: 0.
When I recreated them I also validated invalid geometries and set the right SRID.

No problems so far.

Good! I'm closing this issue then.

Also available in: Atom PDF