WKT output is no longer compatible with SQLServer

[aaime]

Currently generating a linestring with z values results in "POINT Z(x y z)". This has been introduced in https://github.com/locationtech/jts/commit/b464d54960cf0c99195a0f93a26ccfeab55c7f55

However, SQLServer does not understand the Z specifier, and would like to get a "POINT(x y z,)" instead.

E.g.:

1> DECLARE @g geometry;  
2> SET @g = geometry::STGeomFromText('POINT(1 2 3 4)', 0);  
3> SELECT @g.STAsText();  
4> SELECT @g.AsTextZM();  
5> go
POINT (1 2)
POINT (1 2 3 4)

while trying to use "POINT Z" results in the following

1> DECLARE @g2 geometry= 'POINT Z(1 1 0)'; 
2> GO
Msg 6522, Level 16, State 1, Server 4dd2f9838640, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry": 
System.FormatException: 24142: Expected "(" at position 6. The input has "Z".
System.FormatException: 
   at Microsoft.SqlServer.Types.WellKnownTextReader.RecognizeToken(Char token)
   at Microsoft.SqlServer.Types.WellKnownTextReader.ParsePointText(Boolean parseParentheses)
   at Microsoft.SqlServer.Types.WellKnownTextReader.ParseTaggedText(OpenGisType type)
   at Microsoft.SqlServer.Types.WellKnownTextReader.Read(OpenGisType type, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeometry.GeometryFromText(OpenGisType type, SqlChars text, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeometry.Parse(SqlString s)

As far as I can see, there is no way to get the desired output, if the writer is not setup to produce Z values, it won't output the Z ordinates. I don't see a way to subclass and override this behavior, all relevant methods are private...

[dr-jts]

This was caused as a result of PR #291. It's not clear to me if this change was intended (@FObermaier @jodygarnett comments?).

Seems like there are at least two options to mitigate this:

• Revert to the old WKT output for XYZ, and provide a WKTWriter.setXXX method to request GEOM Z format
• Keep GEOM Z as the default, and provide a WKTWriter.setXXX method to request the old format

Any thoughts on what the resolution should be? (I have lost track of where the standards ended up for WKT).

[FObermaier]

I think that feeding a database server with WKT is not a good choice in the first place. String.replace(“Z“, ““) will probably help.

I am no expert in BNF interpretation but to me writing the Z literal is correct. (OGC SFA-CA 1.2.1, Ch. 8). So -for my part- this was intentional.

[jodygarnett]

It was also intensional on my part and agrees with PostGIS WKT output. We may wish to report a bug to SQLServer?

If you wanted a flag to use POINT (vs POINTZ, POINTM, POINTZM) writer.setDimensionalitySuffix(false) would be appropriate (based on reading postgis documentation).

[dr-jts]

Thanks for confirming the spec, @FObermaier . And good point about PostGIS supporting that version of WKT for output (although I note that it also supports the old-style syntax for read, which is more relevant).

So the minimal fix for this is to add a flag for the WKTWriter. I lean towards setDimensionSuffix. Presumably this should NOT drop an M tag, since that would cause ambiguity? And might as well support ZM too?

@aaime Any comments on whether this works for your use case? And how compatible it is with SQL Server?

[aaime]

@dr-jts, I believe that having such setter would work. I found this while working on curves support in SQL Server. After this report I switched the data store to using the GeoTools WKTWriter2 (which can write curves), and the problem went away.... sorry for the noise :-D

In the meantime I've also opened a ticket against the Microsoft JDBC driver (mostly because it's open source and had a clear way to report issues, although the report should probably have gone against SQL Server itself): https://github.com/microsoft/mssql-jdbc/issues/1228

[airbreather]

Any thoughts on what the resolution should be?

For reference, on the NetTopologySuite side, we did: NetTopologySuite/NetTopologySuite@0810335