Top 5 Recent Articles

ARTICLES CATEGORIES

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.