Top 5 Recent Articles

ARTICLES CATEGORIES

Create Polygons and Linestrings From Bearings And Distances (COGO)

UPDATE
The original functions have been modified and deployed via my Oracle Object code.
They original code is no longer available.

My COordinate GeOmetry (COGO) package has been around for a long time.

What I had not done was use the power of this package to construct polygons (2003) or linestrings (2002) from a starting point and a set of whole circle bearings (0-360) and distances.

The following article shows how some simple COGO functionality has been added to my Oracle Object type system.

Representing Bearings and Distances

Firstly a data type to represent bearing and distances instructions is created, and some useful Constructors are added to allow for different methods for defining bearings.

 -- Create a suitable type for the bearings and distances
 --
 CREATE OR REPLACE TYPE T_Bearing_Distance
 AUTHID DEFINER
 AS OBJECT (
   Bearing  NUMBER,
   Distance NUMBER,
   Z        NUMBER,
   Constructor FUNCTION T_BEARING_DISTANCE ( p_sDegMinSec IN varchar2,
                                             p_distance   IN NUMBER )
                 RETURN SELF AS RESULT,
   Constructor FUNCTION T_BEARING_DISTANCE ( p_bearing  IN NUMBER,
                                             p_distance IN NUMBER )
                 RETURN SELF AS RESULT,
   Constructor FUNCTION T_BEARING_DISTANCE ( p_sDegMinSec IN varchar2,
                                             p_distance   IN NUMBER,
                                             p_z          IN NUMBER )
                 RETURN SELF AS RESULT
 );
 /
 SHOW ERRORS
 .
 -- We need a collection of bearings and distances to create a polygon
 --
 CREATE TYPE tbl_bearing_distances AS TABLE OF T_Bearing_Distance;
 /
 SHOW errors

Now we can add three member functions to T_Geometry type.

  • ST_Cogo2Line – Creates single linestring from supplied bearing and distance instructions.
  • ST_Line2Cogo – Creates Cogo Instructions from linestring segments.
  • ST_Cogo2Polygon – Creates single polygon exterior ring from supplied bearing and distance instructions.

(ST_Polygon2Cogo is yet to be built as I have to add the ability to “Move To” in the bearings and distances to handle polygons with many rings.)

ST_Cogo2Line

This function starts with a point (T_GEOMETRY) object, and constructs a single linestring object from the passed in set of bearings and distances. (A dedicated Constructor will be added at some stage to enable objects to be created from scratch.

 CREATE OR REPLACE TYPE T_GEOMETRY
 AS
 ...
 .
   -- Create a function that operates on a T_GEOMETRY Point and a set of bearings and distances to create a linestring
   Member FUNCTION ST_Cogo2Line(p_bearings_and_distances IN T_BEARING_DISTANCES)
            RETURN T_Geometry Deterministic,
 .
 -- Examples
 -- Build 2D Line from default constructor
 SELECT F.line.ST_Validate() AS vLine,
        f.line.geom          AS line,
        round(f.line.ST_Length(),2) AS meters
   FROM (SELECT t_geometry(sdo_geometry(2001,NULL,sdo_point_type(0.0,3.5,NULL),NULL,NULL),0.005,2,1)
                  .ST_Cogo2Line (
                       t_bearing_distances(
                           t_bearing_distance(180.00,3.50,NULL),
                           t_bearing_distance( 90.00,3.50,NULL),
                           t_bearing_distance(  0.00,3.50,NULL),
                           t_bearing_distance( 43.02,5.43,NULL),
                           t_bearing_distance(270.00,9.50,NULL)
                       )
                  )
                  .ST_Round(8,8) AS line
           FROM dual
        ) f;
 .
 VLINE LINE                                                                                                                                                        METERS
 ----- ----------------------------------------------------------------------------------------------------------------------------------------------------------- ------
 TRUE  SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(0.0,3.5, 0.0,0.0, 3.5,0.0, 3.5,3.5, 7.2046371,7.46995768, -2.2953629,7.46995768))  25.43
 .
 -- Build 3D line by decimal degrees using default constructor
 SELECT F.line.ST_Validate()        AS vLine,
        f.line.geom                 AS line,
        round(f.line.ST_Length(),2) AS meters
   FROM (SELECT T_Geometry(sdo_geometry(3001,NULL,sdo_point_type(0,3.5,0),NULL,NULL),0.005,2,1)
                    .ST_Cogo2Line(
                        p_bearings_and_distances=>
                          t_bearing_distances(
                           t_bearing_distance(180,  3.5, 0.1),
                           t_bearing_distance(90,   3.5, 0.5),
                           t_bearing_distance(0,    3.5, 1.6),
                           t_bearing_distance(43.02,5.43,2.123),
                           t_bearing_distance(270,  9.5, 0.5)
                        )
                     )
                    .ST_Round(8,8) AS line
           FROM dual
         ) f;
 .
 VLINE LINE                                                                                                                                                                                  METERS
 ----- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------
 TRUE  SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(0.0,3.5,0.0, 0.0,0.0,0.1, 3.5,0.0,0.5, 3.5,3.5,1.6, 7.2046371,7.46995768,2.123, -2.2953629,7.46995768,0.5))  25.79
 .
 -- Line by degrees using text constructor
 SELECT F.line.ST_Validate()        AS vLine,
        f.line.geom                 AS line,
        round(f.line.ST_Length(),2) AS meters
   FROM (SELECT T_Geometry(sdo_geometry(3001,NULL,sdo_point_type(0,3.5,0),NULL,NULL),0.005,2,1)
                    .ST_Cogo2Line(
                        p_bearings_and_distances=>
                          t_bearing_distances(
                           t_bearing_distance(p_sDegMinSec=>'180',         p_distance=>3.5,  p_z=>0.1),
                           t_bearing_distance(p_sDegMinSec=>'90',          p_distance=>3.5,  p_z=>0.5),
                           t_bearing_distance(p_sDegMinSec=>'0',           p_distance=>3.5,  p_z=>1.6),
                           t_bearing_distance(p_sDegMinSec=>'43^01''21"',  p_distance=>5.43, p_z=>2.0),
                           t_bearing_distance(p_sDegMinSec=>'270',         p_distance=>9.5,  p_z=>0.5),
                           t_bearing_distance(p_sDegMinSec=>'149^58''6.3"',p_distance=>4.613,p_z=>0.1))
                       )
                   .ST_Round(8,8) AS line
           FROM dual
         ) f;
 .
 VLINE LINE                                                                                                                                                                                                             METERS
 ----- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------
 TRUE  SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(0.0,3.5,0.0, 0.0,0.0,0.1, 3.5,0.0,0.5, 3.5,3.5,1.6, 7.20481032,7.46979603,2.0, -2.29518968,7.46979603,0.5, 0.01351213,3.47609287,0.1))  30.39
 .
 -- Build 3D line using mixed constructors
 SELECT F.line.ST_Validate()        AS vLine,
        f.line.geom                 AS line,
        round(f.line.ST_Length(),2) AS Meters
   FROM (SELECT T_Geometry(sdo_geometry(3001,NULL,sdo_point_type(0,3.5,0),NULL,NULL),0.005,2,1)
                    .ST_Cogo2Line(
                        p_bearings_and_distances=>
                          t_bearing_distances(
                            t_bearing_distance(              180.0,                     3.5,       0.1),  -- << Default Constructor
                            t_bearing_distance(p_sDegMinSec=>'90',          p_distance=>3.5,  p_z=>0.5),
                            t_bearing_distance(              0.0,                       3.5,       1.6),  -- << Default Constructor
                            t_bearing_distance(p_sDegMinSec=>'43^01''21"',  p_distance=>5.43, p_z=>2.0),
                            t_bearing_distance(p_sDegMinSec=>'270',         p_distance=>9.5,  p_z=>0.5),
                            t_bearing_distance(p_sDegMinSec=>'149^58''6.3"',p_distance=>4.613,p_z=>0.1))
                       )
                   .ST_Round(8,8) AS line
           FROM dual
         ) f;
 .
 VLINE LINE                                                                                                                                                                                                             METERS
 ----- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------
 TRUE  SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(0.0,3.5,0.0, 0.0,0.0,0.1, 3.5,0.0,0.5, 3.5,3.5,1.6, 7.20481032,7.46979603,2.0, -2.29518968,7.46979603,0.5, 0.01351213,3.47609287,0.1))  30.39

ST_Line2COGO

This function starts with a single linestring (T_GEOMETRY) object, and constructs from it a set of bearings and distances.

 CREATE OR REPLACE TYPE T_GEOMETRY
 AS
 ...
 .
   Member FUNCTION ST_Line2Cogo (p_unit IN varchar2 DEFAULT NULL)
            RETURN T_BEARING_DISTANCES Pipelined,
 .
 -- Examples
 -- Create 2D Line from COGO then reverse back to COGO ....
 WITH DATA AS (
 SELECT f.line
   FROM (SELECT t_geometry(sdo_geometry(2001,NULL,sdo_point_type(0.0,3.5,NULL),NULL,NULL),0.005,2,1)
                  .ST_Cogo2Line (
                       t_bearing_distances(
                           t_bearing_distance(180.00,3.50,NULL),
                           t_bearing_distance( 90.00,3.50,NULL),
                           t_bearing_distance(  0.00,3.50,NULL),
                           t_bearing_distance( 43.02,5.43,NULL),
                           t_bearing_distance(270.00,9.50,NULL))
                       ) AS line
           FROM dual
        ) f
 )
 SELECT COGO.DD2DMS(COGO.ST_Degrees(t.bearing)) AS bearing,
        Round(t.distance,3) AS distance
   FROM DATA a,
        TABLE(a.line.ST_Line2Cogo()) t;
 .
 BEARING  DISTANCE
 -------- --------
 0�0'0"        3.5
 180�0'0"      3.5
 90�0'0"       3.5
 0�0'0"        3.5
 43�1'12"     5.43
 270�0'0"      9.5
 .
  6 ROWS selected
 .
 -- Create 3D Line from COGO then reverse back to COGO ....
 WITH DATA AS (
 SELECT f.line
   FROM (SELECT t_geometry(sdo_geometry(3001,NULL,sdo_point_type(0.0,3.5,0.2),NULL,NULL),0.005,2,1)
                  .ST_Cogo2Line (
                       t_bearing_distances(
                           t_bearing_distance(180.00,3.50,1.1),
                           t_bearing_distance( 90.00,3.50,2.0),
                           t_bearing_distance(  0.00,3.50,3.0),
                           t_bearing_distance( 43.02,5.43,4.4),
                           t_bearing_distance(270.00,9.50,5.2))
                       ) AS line
           FROM dual
        ) f
 )
 SELECT COGO.DD2DMS(COGO.ST_Degrees(t.bearing)) AS bearing,
        Round(t.distance,3)                     AS distance,
        t.z
   FROM DATA a,
        TABLE(a.line.ST_Line2Cogo()) t;
 .
 BEARING  DISTANCE   Z
 -------- -------- ---
 0�0'0"      3.506 0.2
 180�0'0"    3.614 1.1
 90�0'0"     3.614   2
 0�0'0"      3.64    3
 43�1'12"    5.608 4.4
 270�0'0"    9.534 5.2
 .
  6 ROWS selected

ST_COGO2Polygon

This function, like ST_COGO2Line starts with a point (T_GEOMETRY) object, and constructs a polygon with a single exterior ring from the passed in set of bearings and distances. (A dedicated Constructor will be added at some stage to enable objects to be created from scratch.

 CREATE OR REPLACE TYPE T_GEOMETRY
 AS
 ...
   Member FUNCTION ST_Cogo2Polygon(p_bearings_and_distances IN T_BEARING_DISTANCES)
            RETURN T_Geometry Deterministic,
 .
 -- Examples
 -- Polygon built from default constructor...
 SELECT F.poly.ST_Validate()      AS vPoly,
        f.poly.ST_AsText()        AS pWKT,
        round(f.Poly.ST_Area(),2) AS sqM
   FROM (SELECT t_geometry(sdo_geometry(2001,NULL,sdo_point_type(0,3.5,NULL),NULL,NULL),0.005,2,1)
                  .ST_Cogo2Polygon(
                       t_bearing_distances(
                           t_bearing_distance(180,3.5,NULL),
                           t_bearing_distance(90,3.5,NULL),
                           t_bearing_distance(0,3.5,NULL),
                           t_bearing_distance(43.02,5.43,NULL),
                           t_bearing_distance(270,9.5,NULL))
                   )
                  .ST_Round(3,3) AS poly
           FROM dual
        ) f;
 . 
 VPOLY PWKT                                                                               SQM
 ----- -------------------------------------------------------------------------------- -----
 TRUE  POLYGON ((0.0 3.5, 0.0 0.0, 3.5 0.0, 3.5 3.5, 7.205 7.47, -2.295 7.47, 0.0 3.5)) 38.06
 .
 -- Different way of building directions.
 -- Simple bearing/distance constructors used
 WITH DATA AS (
 SELECT CAST(MULTISET(
          SELECT bd
            FROM (SELECT 1 AS rin, t_bearing_distance(180.0,3.5) AS bd                      FROM dual UNION ALL
                  SELECT 2,        t_bearing_distance( 90.0,3.5)                            FROM dual UNION ALL
                  SELECT 3,        t_bearing_distance(  0.0,3.5)                            FROM dual UNION ALL
                  SELECT 4,        t_bearing_distance(43.02,round(SQRT(4.5*4.5+2.2*4.2),2)) FROM dual UNION ALL
                  SELECT 5,        t_bearing_distance(270.0,(4.5+3.4+1.6))                  FROM dual )
                  ORDER BY rin
        ) AS t_bearing_distances ) AS directions
   FROM dual
 )
 SELECT F.poly.ST_Validate()      AS vPoly,
        f.poly.ST_AsText()        AS pWKT,
        round(f.Poly.ST_Area(),2) AS sqM
   FROM (SELECT t_geometry(sdo_geometry(2001,NULL,sdo_point_type(0,3.5,NULL),NULL,NULL),0.005,2,1)
                  .ST_Cogo2Polygon (
                      p_bearings_and_distances=>a.directions
                   )
                  .ST_Round(3,3) AS poly
           FROM DATA a
         ) f;
 .
 VPOLY PWKT                                                                               SQM
 ----- -------------------------------------------------------------------------------- -----
 TRUE  POLYGON ((0.0 3.5, 0.0 0.0, 3.5 0.0, 3.5 3.5, 7.205 7.47, -2.295 7.47, 0.0 3.5)) 38.06
 .
 -- Mixed Constructors with Z for 3D Polygon...
 SELECT F.poly.ST_Validate()      AS vPoly,
        f.poly.geom               AS polygon,
        round(f.Poly.ST_Area(),2) AS sqM
   FROM (SELECT t_geometry(sdo_geometry(3001,NULL,sdo_point_type(0,3.5,10.0),NULL,NULL),0.005,2,1)
                  .ST_Cogo2Polygon (
                      t_bearing_distances(
                           t_bearing_distance(180,3.5,11.1),
                           t_bearing_distance(p_sDegMinSec=>'90^0''0"',p_distance=>3.5,p_z=>12.2),
                           t_bearing_distance(0.0,3.5,13.3),
                           t_bearing_distance(p_sDegMinSec=>'43^01''12"',p_distance=>5.43,p_z=>14.4),
                           t_bearing_distance(270.0,9.5,13.3)
                       )
                   )
                  .ST_Round(3,3) AS poly
           FROM dual
        ) f;
 .
 VPOLY POLYGON                                                                                                                                                                                  SQM
 ----- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----
 TRUE  SDO_GEOMETRY(3003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(0.0,3.5,10.0, 0.0,0.0,11.1, 3.5,0.0,12.2, 3.5,3.5,13.3, 7.205,7.47,14.4, -2.295,7.47,13.3, 0.0,3.5,10.0)) 43.37

I am considering adding a traverse adjustment option (p_traverse_adjust) to the creation of a polygon exterior ring.

I hope these will be of interest to someone.