In SQL Server, when need to convert geometry data for export to text file or operat with DISTINCT, be mindful the method you choose to do conversion to minimise inaccurate data introduced by conversion.
When run following query using four different conversion methods:
- convert the geometry data to string using CONVERT then back to geometry and compare with orginal geometry
- convert the geometry data to string using ToString then back to geometry and compare with orginal geometry
- convert the geometry data to binary and then back to geometry again and compare with the original geometry
- convert the geometry data to binary and then to varchar and then back to binary and geometry and compare with the original geometry
SELECT
Location
,GeometryData
--method 1
,GeometryData.STEquals(GEOMETRY::STGeomFromText(CONVERT(VARCHAR(MAX),GeometryData),GeometryData.STSrid).MakeValid())
--method 2
,GeometryData.STEquals(GEOMETRY::STGeomFromText(GeometryData.ToString(),GeometryData.STSrid).MakeValid())
--method 3
,GeometryData.STEquals(CONVERT(GEOMETRY,CONVERT(VARBINARY(MAX),GeometryData)).MakeValid())
--method 4
,GeometryData.STEquals(CONVERT(GEOMETRY,CONVERT(VARBINARY(MAX),CONVERT(VARCHAR(MAX),CONVERT(VARBINARY(MAX),GeometryData),1),1)).MakeValid())
FROM
ShapFileTable T
where
GeometryData.STEquals(GEOMETRY::STGeomFromText(CONVERT(VARCHAR(MAX),GeometryData),GeometryData.STSrid).MakeValid()) = 0
OR GeometryData.STEquals(GEOMETRY::STGeomFromText(GeometryData.ToString(),GeometryData.STSrid).MakeValid()) = 0
OR GeometryData.STEquals(CONVERT(GEOMETRY,CONVERT(VARBINARY(MAX),GeometryData)).MakeValid()) = 0
OR GeometryData.STEquals(CONVERT(GEOMETRY,CONVERT(VARBINARY(MAX),CONVERT(VARCHAR(MAX),CONVERT(VARBINARY(MAX),GeometryData),1),1)).MakeValid()) = 0
It turns out that method 1) and 2) both introduced 0.51% (188 out of 36888) inaccurate data during the conversion, whereas method 3) and 4) didn’t.
This is only based on testing of my 36888 records. At least, it already indicates that convert geometry directly to string may loose precision when converting that string back to geometry. If need to transfer geometry data via text file or operations doesn’t support geometry data type, it’s better to convert to string representation of the binary data to mainten the accuracy of the data when convert it back to geometry.