STLine2Cogo: Converting LineStrings to COGO XML
I have just written a useful function for my new package of TSQL functions for SQL Server Spatial called STLine2Cogo which converts (exports) a (Multi)LineString into a set of instructions (bearing, distances etc) that can be shared with other COGO functions.
The function’s documentation can be seen at this STLine2Cogo
Here are some examples:
-- Write 2D with DMS string bearings
SELECT [cogo].[STLine2Cogo] (
geometry::STGeomFromText('LINESTRING (10 10, 8.163 17.034, 158.755 35.432, 157.565 25.108)',0),
CHAR(176),CHAR(39),'"')
GO
<Cogo srid="0"> <Segments> <Segment> <MoveTo>POINT (10 10)</MoveTo> <DegMinSec> 345°21'48.75"</DegMinSec> <Distance>7.26992</Distance> </Segment> <Segment> <DegMinSec> 83° 2'4.652"</DegMinSec> <Distance>151.712</Distance> </Segment> <Segment> <DegMinSec> 186°34'30.73"</DegMinSec> <Distance>10.3924</Distance> </Segment> </Segments> </Cogo>
-- Write decimal degrees bearings and maintain 3D.
SELECT [cogo].[STLine2Cogo] (
geometry::STGeomFromText('LINESTRING (10 10 -1, 8.163 17.034 0, 158.755 35.432 2, 157.565 25.108 5)',0),
NULL,NULL,NULL)
GO
<Cogo srid="0"> <Segments> <Segment> <MoveTo>POINT (10 10 -1)</MoveTo> <Bearing>345.364</Bearing> <Distance>7.26992</Distance> <DeltaZ>1</DeltaZ> </Segment> <Segment> <Bearing>83.0346</Bearing> <Distance>151.712</Distance> <DeltaZ>2</DeltaZ> </Segment> <Segment> <Bearing>186.575</Bearing> <Distance>10.3924</Distance> <DeltaZ>3</DeltaZ> </Segment> </Segments> </Cogo>
The following code converts the shown LineString to a table of COGO values.
SELECT [cogo].[STLine2COGO](geometry::STGeomFromText('LINESTRING (382.875 -422.76, 381.038 -415.726, 531.63 -397.328, 530.44 -407.652, 543.796 -406.729, 542.673 -415.759, 603.73 -415.063, 603.693 -403.377, 614.665 -404.601, 612.239 -376.561, 617.793 -375.878)',28356),
NULL,NULL,NULL) AS t
GO
The result is:
<Cogo srid="28356"> <Segments> <Segment> <MoveTo>POINT (382.875 -422.76)</MoveTo> <Bearing>345.364</Bearing> <Distance>7.26992</Distance> </Segment> <Segment> <Bearing>83.0346</Bearing> <Distance>151.712</Distance> </Segment> <Segment> <Bearing>186.575</Bearing> <Distance>10.3924</Distance> </Segment> <Segment> <Bearing>86.0467</Bearing> <Distance>13.3879</Distance> </Segment> <Segment> <Bearing>187.089</Bearing> <Distance>9.09956</Distance> </Segment> <Segment> <Bearing>89.3469</Bearing> <Distance>61.061</Distance> </Segment> <Segment> <Bearing>359.819</Bearing> <Distance>11.6861</Distance> </Segment> <Segment> <Bearing>96.3654</Bearing> <Distance>11.0401</Distance> </Segment> <Segment> <Bearing>355.055</Bearing> <Distance>28.1448</Distance> </Segment> <Segment> <Bearing>82.9893</Bearing> <Distance>5.59584</Distance> </Segment> </Segments> </Cogo>
A final test it to convert a line to Cogo and then create the line from that Cogo.
They should be the same:
WITH line AS (
SELECT geometry::STGeomFromText('LINESTRING (10 10 -1, 8.163 17.034 0, 158.755 35.432 2, 157.565 25.108 5)',0) AS line
)
SELECT a.line.STEquals(
[cogo].[STCogo2Line] (
[cogo].[STLine2Cogo] (a.line,CHAR(176),CHAR(39), '"'),
3, 2
)) AS isEquals
FROM line AS a
GO
isEquals
--------
1
I hope this is of interest to someone.