Skip to content

TopoXL Function Reference

Jump to bottom
Cristian Buse edited this page Feb 13, 2021 · 4 revisions

Content

General Notes

  • The definitions, notations and concepts used by this library should be understood in the context of this library only.
  • A point represents a location which is defined by coordinates XY(Z), where X is the abscissa and Y is the ordinate. Z is optional and represents the applicate (e.g. elevation).
  • An Excel Array is defined as a list of values. Some functions of this library return Excel Arrays. These functions should be used as Array Formula (Ctrl + Shift + Enter). Note that in newer versions of Excel 365 the Ctrl + Shift + Enter is optional as the new Dynamic array formulas and spilled array behavior take care of returning all values from the return array.

Centerline Functions

Terms and Concepts

A Centerline is an imaginary line along the center of a road, railway, culvert, etc. It's made of one or more geometry elements of the following types: line segment, circular arc, Cornu/Euler spiral.

A geometry element has a starting point and an ending point defined which relate to the start and end of its geometry.

The measure of a point along a geometry element is the distance to the point measured from the start point (or the end point if the element is defined as reversed).

The offset of a point along a geometry element is the perpendicular distance between the point and the element. Offset values are negative if the points are on the left hand side (LHS) along the geometry element and positive if they are on the right hand side (RHS).

The geometry element, measure, offset and their properties are shown in the figure below.

Summary

Centerline referencing functions of this library deal with three scenarios:

  • for a given centerline and Point, calculate the measure and the offset
  • for a given centerline, measure and offset, calculate the Point
  • for a given centerline and coordinate (X/Y), calculate the other coordinate (Y/X)

Centerline referencing functions work only if a centerline object has been successfully initialized prior to the function call (see Initialize Centerline Objects).

Initialize Centerline Objects

A Centerline object can be initialized from an Excel table object/VBA ListObject (not Excel data range). The initialization is done automatically if at least one UDF (User-Defined Function) is calling the getUserCLs method. This method will initialize all centerlines and will also refresh all formulas automatically to account for non-volatile UDFs.

Initialization Table Description

Each row of the table represents a centerline element.

The table must meet the following requirements:

  • table name starts with tblCL (under the Design contextual ribbon tab - while table is selected)
  • the table header must include GeomType, InitType, Reversed and Measure columns. Valid values are required for all of these columns
  • the table header must include the columns relevant for the geometry properties definition. Based on the value of GeomType, the following columns and valid values are required:
    • LineSegment: StartX, StartY, EndX, EndY
    • CircularArc:
      • StartX, StartY, EndX, EndY, Radius, CurveDirection
      • StartX, StartY, CenterX, CenterY, Length, CurveDirection
    • ClothoidArc: StartX, StartY, Length, Radius, CurveDirection, StartTheta

The meaning and expected values of each column is detailed below:

  • GeomType is the centerline geometry type. The accepted values are: LineSegment, CircularArc, ClothoidArc;
  • InitType is an indicator for what geometry properties will be used to initialize the centerline element. Based on the GeomType attribute, the accepted values are as follows:
    • LineSegment: SE (Start - End)
    • CircularArc:
      • SERD (Start, End, Radius, Curve direction)
      • SCLD (Start, Center, Length, Curve direction)
    • ClothoidArc: SLRDT (Start, Length, Radius, Curve direction, Start theta)
  • StartX is the X coordinate of the geometry's start point
  • StartY is the Y coordinate of the geometry's start point
  • EndX is the X coordinate of the geometry's end point
  • EndY is the Y coordinate of the geometry's end point
  • CenterX is the X coordinate of the geometry's center point
  • CenterY is the Y coordinate of the geometry's center point
  • Length is the length of the geometry
  • Radius is the radius length of the geometry
  • CurveDirection is the direction of the curve. The accepted values are CW (clockwise) and CCW (counterclockwise)
  • StartTheta is the angle in radians, counterclockwise measured, measured between the tangent at entrance on a spiral and Ox axis
  • Reversed is an indicator which defines where the measure of a geometry starts from. Accepted values are TRUE or FALSE. If a centerline element is reversed, the measure is applied starting from the end of the geometry
  • Measure is a number which indicates the starting value for measuring lengths along geometries

An example of a valid centerline input table is provided below.

GeomType InitType StartX StartY EndX EndY CenterX CenterY Length Radius CurveDirection StartTheta Reversed Measure
LineSegment SE 198764.3459 304156.5693 198602.8152 304353.6703 FALSE 115239.0686
ClothoidArc SLRDT 198602.8152 304353.6703 100.0000 1500.0000 CCW 2.25733470 FALSE 115493.9037
CircularArc SERD 198538.5765 304430.3020 198536.2834 304432.9111 1500.0000 CCW FALSE 115593.9037

Functions Help

The centerline functions are stored in the UDF_CL module. Their names are prefixed with cl.

clPntByMeasOffset - Centerline Point by Measure and Offset

Parameters
  • clName (text): the name of the reference centerline used for computation
  • measure (number): the measure value at which the output point coordinates will be computed
  • offset (number): the offset value at which the output point coordinates will be computed
Result

Result type: Excel Array – two numbers

Returns the coordinates of the point calculated at the given measure and offset

Errors returned
  • #N/A: no centerline with the name clName was found
  • #NUM!: measure value is out of range, that is, the centerline has no element such that start measure <= measure <= end measure

clMeasOffsetOfPnt - Centerline Measure and Offset of Point

Parameters
  • clName (text): the name of the reference centerline used for computation
  • X (number): the X value of the input location
  • Y (number): the Y value of the input location
Result

Result type: Excel Array – two numbers

Returns the centerline reference, measure and offset, of the given coordinates

Errors returned
  • #N/A: no centerline with the name clName was found
  • #NUM!: coordinates not covered by the centerline, that is, the centerline has no element such that a perpendicular from the given coordinates to one of its elements exists

clYatX - Centerline Y Value at X Value

Parameters
  • clName (text): the name of the reference centerline used for computation
  • X (number): the X value for which Y value must be computed
Result

Result type: number

Returns the Y value computed at the given X value. In the scenario of multiple centerline elements which satisfy the condition Xmin <= X <= Xmax, the first one found is used for computation.

Errors returned
  • #N/A: no centerline with the name clName was found
  • #NUM!: X value not covered by the centerline, that is, the centerline has no element such that Xmin <= X <= Xmax

clXatY - Centerline X Value at Y Value

Parameters
  • clName (text): the name of the reference centerline used for computation
  • Y (number): the Y value for which X value must be computed
Result

Result type: number

Returns the X value computed at the given Y value. In the scenario of multiple centerline elements which satisfy the condition Ymin <= Y <= Ymax, the first one found is used for computation.

Errors returned
  • #N/A: no centerline with the name clName was found
  • #NUM!: Y value not covered by the centerline, that is, the centerline has no element such that Ymin <= Y <= Ymax

ACAD Script Generator Functions

Summary

Functions in this category can be used to create AutoCAD instructions for either the command line or script files (.scr file extension). The script commands include:

  • entities creation: Point, Polyline, 3D Polyline and Text
  • actions: change layer and insert blocks

Functions Help

The ACAD Script Generator functions are stored in the UDF_ACADscr module. Their names are prefixed with acadScr. Because of the carriage return/new line character used with some of the AutoCAD commands, when the output of these functions is copied from a cell within a spreadsheet, Excel adds the " (double quotes symbol) at the start and at the end. This should be removed before usage.

acadScrPnt - AutoCAD Script Point

Parameters
  • values (range/array): a list of values representing 2D/3D coordinates
Result

Result type: text

Returns a text representing the AutoCAD script for the creation of Points with the specified coordinates.

Formula result examples:

 point 10,20
 point 10,20,30
Errors returned
  • #NUM!: the values are not numeric
  • #NUM!: the values range/array doesn't have 2 or 3 columns (2D/3D coordinates)

acadScrPLine - AutoCAD Script Polyline

Parameters
  • values (range/array): a list of values representing 2D/3D coordinates
Result

Result type: text

Returns a text representing the AutoCAD script for the creation of (3D) Polylines with the specified coordinates.

Formula result examples:

pline 11,12
21,22
3dpoly 11,12,13
21,22,23
Errors returned
  • #NUM!: the values are not numeric
  • #NUM!: the values range/array doesn't have 2 or 3 columns (2D/3D coordinates)
  • #NUM!: the values range/array doesn't have at least 2 rows (minimum two points required for a polyline entity)

acadScrSText - AutoCAD Script Single Line Text

Parameters
  • values (range/array): a list of values representing options of the text AutoCAD command: coordinates 2D/3D, height, rotation angle, text
Result

Result type: text

Returns a text representing the AutoCAD script for the creation of Texts with the specified options.

Formula result examples:

-text 10,20 1 0 Sample
-text 10,20,30 1 0 Sample
Errors returned
  • #NUM!: the values in columns 1, 2, 3, 4 (or 5) are not numeric
  • #NUM!: the values range/array doesn't have 5 or 6 columns (2D/3D coordinates)

acadScrChngLyr - AutoCAD Script Change Active Layer

Parameters
  • lyr (text): a layer name
Result

Result type: text

Returns a text representing the AutoCAD script for setting/creating a layer

Formula result examples:

-layer m test_layer
Errors returned
  • no errors returned

acadScrInsBl - AutoCAD Script Insert Block

Parameters
  • values (range/array): a list of values representing options of the insert AutoCAD command: block name, coordinates 2D/3D, X scale factor, Y scale factor, rotation angle
Result

Result type: text

Returns a text representing the AutoCAD script for the insertion of Blocks with the specified options.

Formula result examples:

-insert BlockA
10,20
1
1
0
Errors returned
  • #NUM!: the values in columns 2, 3, 4, 5, 6 (or 7) are not numeric
  • #NUM!: the values range/array doesn't have 6 or 7 columns (2D/3D coordinates)

Coordinate Geometry Functions

Summary

Functions in this category can be used to do basic coordinate geometry computations:

  • intersection between two lines defined by points, points and angle to x-axis and points and deltas (coordinate variation)
  • intersection between two polylines
  • coordinates of an extended/trimmed line segment
  • coordinates of an offsetted line segment

and queries:

  • line segment length (distance between two points)
  • line angle with x-axis
  • identify the side of a point (left/right) relative to a given reference line
  • identify if a point is within the boundaries of a given bounding box
  • area of a polygon

Functions Help

cgDist2D- COGO Distance 2D

Parameters
  • x1 (number): X coordinate of the first point
  • y1 (number): Y coordinate of the first point
  • x2 (number): X coordinate of the second point
  • y2 (number): Y coordinate of the second point
Result

Result type: number

Returns the 2D distance between two points defined by their coordinates

Errors returned
  • no errors returned

cgDist3D- COGO Distance 3D

Parameters
  • x1 (number): X coordinate of the first point
  • y1 (number): Y coordinate of the first point
  • z1 (number): Z coordinate of the first point
  • x2 (number): X coordinate of the second point
  • y2 (number): Y coordinate of the second point
  • z2 (number): Z coordinate of the second point
Result

Result type: number

Returns the 3D distance between two points defined by their coordinates

Errors returned
  • no errors returned

cgTheta- COGO Theta

Parameters
  • x1 (number): X coordinate of the first point
  • y1 (number): Y coordinate of the first point
  • x2 (number): X coordinate of the second point
  • y2 (number): Y coordinate of the second point
Result

Result type: number

Returns the angle in radians between the positive x-axis and the line defined by input coordinates. The returned value is within range (-PI, PI] and it is the same as the result of ATAN2 function of Excel. A positive value represents a counterclockwise angle from x-axis and a negative value represents a clockwise angle.

Errors returned
  • #NUM!: the input coordinates define the same location

cgSide- COGO Side

Parameters
  • x1 (number): reference line first point X coordinate
  • y1 (number): reference line first point Y coordinate
  • x2 (number): reference line second point X coordinate
  • y2 (number): reference line second point Y coordinate
  • x (number): check side location X coordinate
  • y (number): check side location Y coordinate
Result

Result type: number

Returns a number representing the side of a location relative to a reference line:

  • -1, if location is on the left
  • 0, if location is on the line
  • 1, if location is on the right

Left and right are determined based on the reference line coordinates. Location 1 = start and location 2 = end point.

Errors returned
  • #NUM!: the input coordinates of the reference line define the same location

cgCooInBB- COGO Coordinates in Bounding Box

Parameters
  • x (number): X coordinate
  • y (number): Y coordinate
  • x1 (number): bounding box first corner X coordinate
  • y1 (number): bounding box first corner Y coordinate
  • x2 (number): bounding box second corner X coordinate
  • y2 (number): bounding box second corner Y coordinate

Location 1 and 2 defining the bounding box must be opposite corners

Result

Result type: Boolean

Returns TRUE if the input coordinates are inside (on the edge inclusive) the defined bounding box, FALSE otherwise.

Errors returned
  • no errors returned

cgIntLbyCooAndTh- COGO Intersection of Lines by Coordinates and Theta

Parameters
  • x1 (number): X coordinate of a point on the first line
  • y1 (number): Y coordinate of a point on the first line
  • theta1 (number): theta of the first line as returned by cgTheta/ATAN2 functions
  • x2 (number): X coordinate of a point on the second line
  • y2 (number): Y coordinate of a point on the second line
  • theta2 (number): theta of the second line as returned by cgTheta/ATAN2 functions
Result

Result type: Excel Array – two numbers

Returns the coordinates of the intersection point between the two lines defined as input.

Errors returned
  • #N/A: no intersection is found between the two input lines

cgIntLSbyCoo- COGO Intersection of Line Segments by Coordinates

Parameters
  • x1 (number): X coordinate of the first line start point
  • y1 (number): Y coordinate of the first line start point
  • x2 (number): X coordinate of the first line end point
  • y2 (number): Y coordinate of the first line end point
  • x3 (number): X coordinate of the second line start point
  • y3 (number): Y coordinate of the second line start point
  • x4 (number): X coordinate of the second line end point
  • y4 (number): Y coordinate of the second line end point
Result

Result type: Excel Array – two numbers

Returns the coordinates of the intersection point between the two line segments (not lines) defined as input.

Errors returned
  • #N/A: no intersection is found between the two input line segments

cgIntLbyCooAndDs- COGO Intersection of Lines by Coordinates and Deltas

Parameters
  • x1 (number): X coordinate of a point on the first line
  • y1 (number): Y coordinate of a point on the first line
  • dx1 (number): X coordinate variation/difference of a second point on the first line
  • dy1 (number): Y coordinate variation/difference of a second point on the first line
  • x2 (number): X coordinate of a point on the second line
  • y2 (number): Y coordinate of a point on the second line
  • dx2 (number): X coordinate variation/difference of a second point on the second line
  • dy2 (number): Y coordinate variation/difference of a second point on the second line
Result

Result type: Excel Array – two numbers

Returns the coordinates of the intersection point between the two lines defined as input.

Errors returned
  • #N/A: no intersection is found between the two input lines

cgIntPLbyCoo- COGO Intersection of Polylines by Coordinates

Parameters
  • coos1 (range/array): a list of values representing 2D coordinates of the first polyline
  • coos2 (range/array): a list of values representing 2D coordinates of the second polyline
Result

Result type: Excel Array – (2 x number of intersection points found) numbers

Returns the coordinates of all the intersection points between the two polylines defined as input.

Errors returned
  • #N/A: no intersection is found between the two input polylines

cgExtTrimLS- COGO Extend/Trim Line Segment

Parameters
  • x1 (number): X coordinate of the line segment start point
  • y1 (number): Y coordinate of the line segment start point
  • x2 (number): X coordinate of the line segment end point
  • y2 (number): Y coordinate of the line segment end point
  • length (number): the extended/trimmed length value. A negative value trims the line segment and a positive one extends it
  • part (number): the part on which the line segment is extended/trimmed (-1 = start, 0 = start and end, 1 = end of line segment)
Result

Result type: Excel Array – four numbers

Returns the coordinates of the extended/trimmed line segment.

Errors returned
  • #NUM!: the part value is not -1, 0 or 1
  • #NUM!: the input coordinates define the same location

cgOffsetLS- COGO Offset Line Segment

Parameters
  • x1 (number): X coordinate of the line segment start point
  • y1 (number): Y coordinate of the line segment start point
  • x2 (number): X coordinate of the line segment end point
  • y2 (number): Y coordinate of the line segment end point
  • offset (number): the offset value. A negative value offsets the line segment on the left and a positive one on the right
Result

Result type: Excel Array – four numbers

Returns the coordinates of the offsetted line segment.

Errors returned
  • #NUM!: the input coordinates define the same location

cgAreaByCoo- COGO Area by Coordinates

Parameters
  • coos (range/array): a list of values representing 2D coordinates of a polygon
Result

Result type: number

Returns the area of the polygon defined by the input coordinates

Errors returned
  • #NUM!: the coos range/array doesn't have 2 columns (2D coordinates)
  • #NUM!: the coos range/array doesn't have at least 3 sets of coordinates (minimum 3 points required to define a polygon)

Clone this wiki locally