Top 5 Recent Articles

ARTICLES CATEGORIES

STCogo2Line: Creating (Multi)LineStrings geometries from COGO XML instructions

Previously I wrote about a function that converted the segments of a (Multi)LineString into a COGO XML document that contains all the instructions (bearing, distances, move deltaZ etc) to create a (Multi)LineString.

Today I present a function that does the reverse: takes the COGO XML and creates the (Multi)LineString.

The instructions are provided to the function in an XML document with the following structure:

 <Cogo srid={int}>
   <Segments>
     <Segment>
       <MoveTo></MoveTo>
       <sDegMinSec></sDegMinSec>
       <Bearing></Bearing>
       <Distance></Distance>
       <DeltaZ></DeltaZ>
     <Segment>
     <Segment>
       ....
     <Segment>
   </Segments>
 </Cogo>
 -- <MoveTo> can be either a WKT point eg POINT(0 0 -1) or a coordinate string eg "0 0 -1".
 -- If the starting <moveTo> does not exist a 2D Line starting at 0,0 is created.
 -- If it does exist, then it determines if line is 2D or 3D.
 -- Measure ordinates are currently not supported.
 -- <DegMinSec> does not have to exist if <Bearing> (decimal degrees) exists.
 -- <DeltaZ> is optional. If <DeltaZ> does exist, a starting <MoveTo> must exist and have a Z ordinate.

Here is an example of Cogo XML:

 <Cogo srid="0">
   <Segments>
     <Segment>
       <MoveTo>POINT (10 10 -1)</MoveTo>
       <Bearing>345.364</Bearing>
       <Distance>345.364</Distance>
       <DeltaZ>1</DeltaZ>
     </Segment>
     <Segment>
       <Bearing>83.0346</Bearing>
       <Distance>83.0346</Distance>
       <DeltaZ>2</DeltaZ>
     </Segment>
     <Segment>
       <Bearing>186.575</Bearing>
       <Distance>186.575</Distance>
       <DeltaZ>3</DeltaZ>
     </Segment>
   </Segments>
 </Cogo>

The function looks like this. Its documentation can be seen see here.

 CREATE FUNCTION [$(cogoowner)].[STCogo2Line]
 (
   @p_cogo        xml,
   @p_start_point geometry,
   @p_round_xy    INT = 3,
   @p_round_z     INT = 2
 )
 RETURNS geometry
 .....

Here are some examples:

 Print 'Generate XYZ linestring using ordinate string moveTo.';
 DECLARE @v_cogo xml;
 SET @v_cogo =
 '<Cogo srid="28356"><Segments>
 <Segment><MoveTo>10 10 -1</MoveTo><DegMinSec> 345°21''48.75"</DegMinSec><Distance>7.26992</Distance><DeltaZ>1</DeltaZ></Segment>
 <Segment><DegMinSec>  83° 2''4.652"</DegMinSec><Distance>151.712</Distance><DeltaZ>2</DeltaZ></Segment>
 <Segment><DegMinSec> 186°34''30.73"</DegMinSec><Distance>10.3924</Distance><DeltaZ>3</DeltaZ></Segment>
 </Segments></Cogo>';
 SELECT [cogo].[STCogo2Line] (@v_cogo, 3, 2).AsTextZM() AS cogoLine
 GO
 -- Result
 -- LINESTRING(10 10 -1,8.163 17.034 0,158.755 35.432 2,157.565 25.108 5)
 --
 Print 'Generate XYZ linestring using POINT() WKT moveTo.';
 DECLARE @v_cogo xml;
 SET @v_cogo =
 '<Cogo srid="28356"><Segments>
 <Segment><MoveTo>POINT(10 10 -1)</MoveTo><DegMinSec> 345°21''48.75"</DegMinSec><Distance>7.26992</Distance><DeltaZ>1</DeltaZ></Segment>
 <Segment><DegMinSec>  83° 2''4.652"</DegMinSec><Distance>151.712</Distance><DeltaZ>2</DeltaZ></Segment>
 <Segment><DegMinSec> 186°34''30.73"</DegMinSec><Distance>10.3924</Distance><DeltaZ>3</DeltaZ></Segment>
 </Segments></Cogo>';
 SELECT [cogo].[STCogo2Line] (@v_cogo, 3, 2).AsTextZM() AS cogoLine
 GO
 -- Result
 -- LINESTRING (10 10 -1, 8.163 17.034 0, 158.755 35.432 2, 157.565 25.108 5)
 --
 Print 'Create 2D MultiLineString using single MoveTo instruction';
 DECLARE @v_cogo xml;
 SET @v_cogo =
 '<Cogo srid="28356"><Segments>
 <Segment><DegMinSec> 345°21''48.75"</DegMinSec><Distance>7.26992</Distance><DeltaZ>1</DeltaZ></Segment>
 <Segment><DegMinSec>  83° 2''4.652"</DegMinSec><Distance>151.712</Distance><DeltaZ>2</DeltaZ></Segment>
 <Segment><MoveTo>POINT(10 10 -1)</MoveTo><DegMinSec> 186°34''30.73"</DegMinSec><Distance>10.3924</Distance><DeltaZ>3</DeltaZ></Segment>
 </Segments></Cogo>';
 SELECT [cogo].[STCogo2Line] (@v_cogo, 3, 2).AsTextZM() AS cogoLine
 GO
 -- Result
 -- MULTILINESTRING ((0 0, -1.837 7.034, 148.755 25.432), (10 10, 8.81 -0.324))
 --
 Print 'Create 2D MultiLineString with 3 elements using two styles of MoveTo';
 DECLARE @v_cogo xml;
 SET @v_cogo =
 '<Cogo srid="28356"><Segments>
 <Segment><MoveTo>POINT(10 10)</MoveTo><DegMinSec> 345°21''48.75"</DegMinSec><Distance>7.26992</Distance><DeltaZ>1</DeltaZ></Segment>
 <Segment><DegMinSec>  83° 2''4.652"</DegMinSec><Distance>151.712</Distance><DeltaZ>2</DeltaZ></Segment>
 <Segment><DegMinSec> 186°34''30.73"</DegMinSec><Distance>10.3924</Distance><DeltaZ>3</DeltaZ></Segment>
 <Segment><DegMinSec>  86° 2''48.18"</DegMinSec><Distance>13.3879</Distance><DeltaZ>4</DeltaZ></Segment>
 <Segment><MoveTo>POINT(100 100)</MoveTo><DegMinSec> 187° 5''20.73"</DegMinSec><Distance>9.09956</Distance><DeltaZ>5</DeltaZ></Segment>
 <Segment><DegMinSec>  89°20''48.85"</DegMinSec><Distance>61.061</Distance><DeltaZ>6</DeltaZ></Segment>
 <Segment><DegMinSec> 359°49''6.930"</DegMinSec><Distance>11.6861</Distance><DeltaZ>7</DeltaZ></Segment>
 <Segment><MoveTo>200 200 -15</MoveTo><DegMinSec>  96°21''55.47"</DegMinSec><Distance>11.0401</Distance><DeltaZ>8</DeltaZ></Segment>
 <Segment><DegMinSec> 355° 3''18.45"</DegMinSec><Distance>28.1448</Distance><DeltaZ>9</DeltaZ></Segment>
 <Segment><DegMinSec>  82°59''21.42"</DegMinSec><Distance>5.59584</Distance><DeltaZ>10</DeltaZ></Segment>
 </Segments></Cogo>';
 SELECT [cogo].[STCogo2Line] (@v_cogo, 3, 2).AsTextZM() AS cogoLine
 GO
 -- Result
 -- MULTILINESTRING (
 -- (10 10, 8.163 17.034, 158.755 35.432, 157.565 25.108, 170.921 26.031),
 -- (100 100, 98.877 90.97, 159.934 91.666, 159.897 103.352),
 -- (200 200, 210.972 198.776, 208.546 226.816, 214.1 227.499))
 --
 DECLARE @v_cogo xml;
 SET @v_cogo =
 '<Cogo srid="0">
 <Segments>
 <Segment><sDegMinSec> 345°21''48.75"</sDegMinSec><Distance>7.26992</Distance><DeltaZ>1</DeltaZ></Segment>
 <Segment><sDegMinSec>  83° 2''4.652"</sDegMinSec><Distance>151.712</Distance><DeltaZ>2</DeltaZ></Segment>
 <Segment><sDegMinSec> 186°34''30.73"</sDegMinSec><Distance>10.3924</Distance><DeltaZ>3</DeltaZ></Segment>
 <Segment><sDegMinSec>  86° 2''48.18"</sDegMinSec><Distance>13.3879</Distance><DeltaZ>4</DeltaZ></Segment>
 <Segment><sDegMinSec> 187° 5''20.73"</sDegMinSec><Distance>9.09956</Distance><DeltaZ>5</DeltaZ></Segment>
 <Segment><sDegMinSec>  89°20''48.85"</sDegMinSec><Distance>61.061</Distance><DeltaZ>6</DeltaZ></Segment>
 <Segment><sDegMinSec> 359°49''6.930"</sDegMinSec><Distance>11.6861</Distance><DeltaZ>7</DeltaZ></Segment>
 <Segment><sDegMinSec>  96°21''55.47"</sDegMinSec><Distance>11.0401</Distance><DeltaZ>8</DeltaZ></Segment>
 <Segment><sDegMinSec> 355° 3''18.45"</sDegMinSec><Distance>28.1448</Distance><DeltaZ>9</DeltaZ></Segment>
 <Segment><sDegMinSec>  82°59''21.42"</sDegMinSec><Distance>5.59584</Distance><DeltaZ>10</DeltaZ></Segment>
 </Segments>
 </Cogo>';
 SELECT cogoLine.AsTextZM() AS cogoLineWKT
   FROM (SELECT [$(cogoowner)].[STCogo2Line] (@v_cogo, geometry::STPointFromText('POINT(10 10 -10)',28355), 3, 2) AS cogoLine) AS f;
 GO
 -- Result
 -- LINESTRING (10 10 -10, 8.163 17.034 -9, 158.755 35.432 -7, 157.565 25.108 -4, 170.921 26.031 0, 169.798 17.001 5, 230.855 17.697 11, 230.818 29.383 18, 241.79 28.159 26, 239.364 56.199 35, 244.918 56.882 45)

Visually, the result looks like this:

Which should be the same as STLine2Cogo.

I hope this function is of interest to someone.