I'm trying to add new geography points with Z and M values to a geography linestring.This has to be SQL 2008 R2 compatible.
I've found some references to .STUnion() and tried that. The linestrings get combined but the Z and M values are lost.
Any ideas why this is happening or suggestions how to do this instead?
Thank you!
See example sql below (I do most of this in a series of functions, but the result is the same)
DECLARE @Linestring AS sys.geographyDECLARE @LinestringExt AS sys.geography --temporary linestring to extend @Linestring--Declaring and setting 3 geography pointsDECLARE @Point1 AS sys.geography = geography::STPointFromText('POINT(' + LTRIM(STR(15.444444,9,6)) + ' ' + LTRIM(STR(54.333333,8,6)) + ' ' + LTRIM(STR(10)) + ' ' + LTRIM(STR(1)) + ')', 4326)DECLARE @Point2 AS sys.geography = geography::STPointFromText('POINT(' + LTRIM(STR(13.222222,9,6)) + ' ' + LTRIM(STR(43.222222,8,6)) + ' ' + LTRIM(STR(20)) + ' ' + LTRIM(STR(2)) + ')', 4326)DECLARE @Point3 AS sys.geography = geography::STPointFromText('POINT(' + LTRIM(STR(12.111111,9,6)) + ' ' + LTRIM(STR(32.111111,8,6)) + ' ' + LTRIM(STR(30)) + ' ' + LTRIM(STR(3)) + ')', 4326)--Return Points to validate Z and M data is thereSELECT 'Point1' AS 'Point', @Point1.STAsText() AS 'STAsText', @Point1.ToString() AS 'ToString', @Point1.AsTextZM() AS 'AsTextZM()'--Point STAsText ToString AsTextZM()--Point1 POINT (54 15) POINT (54 15 10 1) POINT (54 15 10 1)SELECT 'Point2' AS 'Point', @Point2.STAsText() AS 'STAsText', @Point2.ToString() AS 'ToString', @Point2.AsTextZM() AS 'AsTextZM()'--Point STAsText ToString AsTextZM()--Point2 POINT (54 15) POINT (54 15 10 1) POINT (54 15 10 1)SELECT 'Point2' AS 'Point', @Point3.STAsText() AS 'STAsText', @Point3.ToString() AS 'ToString', @Point3.AsTextZM() AS 'AsTextZM()'--Point STAsText ToString AsTextZM()--Point2 POINT (54 15) POINT (54 15 10 1) POINT (54 15 10 1)--JOIN @Point1 and @Point2 to a LinestringSET @Linestring = geography::STLineFromText('LINESTRING(' + CAST(@Point1.Long AS varchar(32)) + ' ' + CAST(@Point1.Lat AS varchar(32)) + ' ' + LTRIM(STR(@Point1.Z)) + ' ' + LTRIM(STR(@Point1.M)) + ',' + CAST(@Point2.Long AS varchar(32)) + ' ' + CAST(@Point2.Lat AS varchar(32)) + ' ' + LTRIM(STR(@Point2.Z)) + ' ' + LTRIM(STR(@Point2.M)) + ')', @Point1.STSrid)--Return @Linestring to Z and M data is thereSELECT 'Linestring' AS 'Line', @Linestring.STAsText() AS 'STAsText', @Linestring.ToString() AS 'ToString', @Linestring.AsTextZM() AS 'AsTextZM()'--Line STAsText ToString AsTextZM()--Linestring LINESTRING (54 15, 43 13) LINESTRING (54 15 10 1, 43 13 20 2) LINESTRING (54 15 10 1, 43 13 20 2)--Add @Point3 to @LinestringSET @LinestringExt = geography::STLineFromText('LINESTRING(' + CAST(@Linestring.STEndPoint().Long AS varchar(32)) + ' ' + CAST(@Linestring.STEndPoint().Lat AS varchar(32)) + ' ' + LTRIM(STR(@Linestring.STEndPoint().Z)) + ' ' + LTRIM(STR(@Linestring.STEndPoint().M)) + ',' + CAST(@Point3.Long AS varchar(32)) + ' ' + CAST(@Point3.Lat AS varchar(32)) + ' ' + LTRIM(STR(@Point3.Z)) + ' ' + LTRIM(STR(@Point3.M)) + ')', @Linestring.STSrid)--Return @LinestringExtSELECT 'LinestringExt' AS 'Line', @LinestringExt.STAsText() AS 'STAsText', @LinestringExt.ToString() AS 'ToString', @LinestringExt.AsTextZM() AS 'AsTextZM()'--Union @Linestring and @LinestringExtSET @Linestring = @Linestring.STUnion(@LinestringExt)--Return Extended @LinestringSELECT 'Linestring (Ext)' AS 'Line', @Linestring.STAsText() AS 'STAsText', @Linestring.ToString() AS 'ToString', @Linestring.AsTextZM() AS 'AsTextZM()'--Line STAsText ToString AsTextZM()--Linestring (Ext) LINESTRING (12.1111 32.1111, 13.2222 43.2222, 15.4444 54.3333) LINESTRING (12.1111 32.1111, 13.2222 43.2222, 15.4444 54.3333) LINESTRING (12.1111 32.1111, 13.2222 43.2222, 15.4444 54.3333)
أكثر...
I've found some references to .STUnion() and tried that. The linestrings get combined but the Z and M values are lost.
Any ideas why this is happening or suggestions how to do this instead?
Thank you!
See example sql below (I do most of this in a series of functions, but the result is the same)
DECLARE @Linestring AS sys.geographyDECLARE @LinestringExt AS sys.geography --temporary linestring to extend @Linestring--Declaring and setting 3 geography pointsDECLARE @Point1 AS sys.geography = geography::STPointFromText('POINT(' + LTRIM(STR(15.444444,9,6)) + ' ' + LTRIM(STR(54.333333,8,6)) + ' ' + LTRIM(STR(10)) + ' ' + LTRIM(STR(1)) + ')', 4326)DECLARE @Point2 AS sys.geography = geography::STPointFromText('POINT(' + LTRIM(STR(13.222222,9,6)) + ' ' + LTRIM(STR(43.222222,8,6)) + ' ' + LTRIM(STR(20)) + ' ' + LTRIM(STR(2)) + ')', 4326)DECLARE @Point3 AS sys.geography = geography::STPointFromText('POINT(' + LTRIM(STR(12.111111,9,6)) + ' ' + LTRIM(STR(32.111111,8,6)) + ' ' + LTRIM(STR(30)) + ' ' + LTRIM(STR(3)) + ')', 4326)--Return Points to validate Z and M data is thereSELECT 'Point1' AS 'Point', @Point1.STAsText() AS 'STAsText', @Point1.ToString() AS 'ToString', @Point1.AsTextZM() AS 'AsTextZM()'--Point STAsText ToString AsTextZM()--Point1 POINT (54 15) POINT (54 15 10 1) POINT (54 15 10 1)SELECT 'Point2' AS 'Point', @Point2.STAsText() AS 'STAsText', @Point2.ToString() AS 'ToString', @Point2.AsTextZM() AS 'AsTextZM()'--Point STAsText ToString AsTextZM()--Point2 POINT (54 15) POINT (54 15 10 1) POINT (54 15 10 1)SELECT 'Point2' AS 'Point', @Point3.STAsText() AS 'STAsText', @Point3.ToString() AS 'ToString', @Point3.AsTextZM() AS 'AsTextZM()'--Point STAsText ToString AsTextZM()--Point2 POINT (54 15) POINT (54 15 10 1) POINT (54 15 10 1)--JOIN @Point1 and @Point2 to a LinestringSET @Linestring = geography::STLineFromText('LINESTRING(' + CAST(@Point1.Long AS varchar(32)) + ' ' + CAST(@Point1.Lat AS varchar(32)) + ' ' + LTRIM(STR(@Point1.Z)) + ' ' + LTRIM(STR(@Point1.M)) + ',' + CAST(@Point2.Long AS varchar(32)) + ' ' + CAST(@Point2.Lat AS varchar(32)) + ' ' + LTRIM(STR(@Point2.Z)) + ' ' + LTRIM(STR(@Point2.M)) + ')', @Point1.STSrid)--Return @Linestring to Z and M data is thereSELECT 'Linestring' AS 'Line', @Linestring.STAsText() AS 'STAsText', @Linestring.ToString() AS 'ToString', @Linestring.AsTextZM() AS 'AsTextZM()'--Line STAsText ToString AsTextZM()--Linestring LINESTRING (54 15, 43 13) LINESTRING (54 15 10 1, 43 13 20 2) LINESTRING (54 15 10 1, 43 13 20 2)--Add @Point3 to @LinestringSET @LinestringExt = geography::STLineFromText('LINESTRING(' + CAST(@Linestring.STEndPoint().Long AS varchar(32)) + ' ' + CAST(@Linestring.STEndPoint().Lat AS varchar(32)) + ' ' + LTRIM(STR(@Linestring.STEndPoint().Z)) + ' ' + LTRIM(STR(@Linestring.STEndPoint().M)) + ',' + CAST(@Point3.Long AS varchar(32)) + ' ' + CAST(@Point3.Lat AS varchar(32)) + ' ' + LTRIM(STR(@Point3.Z)) + ' ' + LTRIM(STR(@Point3.M)) + ')', @Linestring.STSrid)--Return @LinestringExtSELECT 'LinestringExt' AS 'Line', @LinestringExt.STAsText() AS 'STAsText', @LinestringExt.ToString() AS 'ToString', @LinestringExt.AsTextZM() AS 'AsTextZM()'--Union @Linestring and @LinestringExtSET @Linestring = @Linestring.STUnion(@LinestringExt)--Return Extended @LinestringSELECT 'Linestring (Ext)' AS 'Line', @Linestring.STAsText() AS 'STAsText', @Linestring.ToString() AS 'ToString', @Linestring.AsTextZM() AS 'AsTextZM()'--Line STAsText ToString AsTextZM()--Linestring (Ext) LINESTRING (12.1111 32.1111, 13.2222 43.2222, 15.4444 54.3333) LINESTRING (12.1111 32.1111, 13.2222 43.2222, 15.4444 54.3333) LINESTRING (12.1111 32.1111, 13.2222 43.2222, 15.4444 54.3333)
أكثر...