ECSQL Built-in Geometry Functions

ECSQL statements can use builtin functions to query and analyze element geometry. These functions can be used in SELECT and WHERE clauses. The functions that return primitive types can be in expressions with other values.

Types

The geometry builtin functions work with custom data types, such as bounding boxes, points, angles, and placements, allowing you to work with the structured data that appears in the spatial index and in element geometry.

These custom data types are described here in a conceptual way. You do not work directly with them. Instead, in ECSQL, you use builtin functions to create, analyze, and extract or compute primitive values from them. If you return a structured data type value from an ECSQL statement, it will have the type of ArrayBuffer in script. You will normally have to convert a returned ArrayBuffer to the appropriate geometry type before you can work with it in script. See, for example, Range3d.fromArrayBuffer.

iModel_point

A point in the iModel's Cartesian coordinate system. All coordinates are in meters. If the point represents a location in a 2D model, then the z-coordinate will be zero. @see iModel_point_value, iModel_point_distance, iModel_point_min_distance_to_bbox

struct iModel_point
{
double x;   // The x-coordinate
double y;   // The y-coordinate
double z;   // The z-coordinate
};

iModel_angles

An object that contains Yaw, Pitch, and Roll angles in degrees. If this object represents a rotation in a 2D model, then the pitch and roll members will be zero. @see iModel_angles_value, iModel_angles_maxdiff

struct iModel_angles
{
double yaw;  // The Yaw angle in degrees
double pitch;// The Yaw angle in degrees
double roll; // The Yaw angle in degrees
};

iModel_bbox

An object that defines a range. If the box represents a range in a 3-D model, then the box will have 8 corners and will have width(X), depth(Y), and height(Z). If the box represents a range in a 2-D model, then the box will still have 8 corners but the z-coordinates will be all be zero, and the height will be zero. All coordinates are in meters. @see iModel_bbox_value, iModel_bbox_width, iModel_bbox_height, iModel_bbox_depth, iModel_bbox_volume, iModel_bbox_areaxy, iModel_bbox_overlaps, iModel_bbox_contains, iModel_bbox_union, iModel_point_min_distance_to_bbox

struct iModel_bbox
{
double XLow;  // The low X coordinate of the bounding box
double YLow;  // The low Y coordinate of the bounding box
double Zlow;  // The low Z coordinate of the bounding box
double XHigh; // The high X coordinate of the bounding box
double YHigh; // The high Y coordinate of the bounding box
double ZHigh; // The high Z coordinate of the bounding box
};

iModel_placement

An object that contains an origin and rotation angles, plus a bounding box. You can obtain an element's placement by selecting the placement column of the ElementGeom table. @see iModel_placement_origin, iModel_placement_angles, iModel_placement_eabb, iModel_placement_aabb

struct iModel_placement
{
iModel_point origin;   // Origin
iModel_angles angles;  // Angles
iModel_bbox bbox;      // Element-aligned bounding box
};

Functions

The builtin geometry functions include functions to extract geometric information from elements, as well as functions to create, analyze, and extract or compute primitive values from the custom structured data types.

Most of the builtin functions just perform a function and return a result.

Aggregate

Some of the builtin geometry functions are aggregate functions. They accumulate results, reducing all of the values passed to them by the statement to a single resultant value. Also see SQLite Aggregate Functions.

iModel_placement_aabb

iModel_placement_aabb(placement)

Get the axis-aligned bounding box from a placement

iModel_placement_eabb

iModel_placement_eabb(placement)

Get the element-aligned bounding box from a placement

iModel_placement_origin

iModel_placement_origin(placement)

Get the placement origin

iModel_placement_angles

iModel_placement_angles(placement)

Get the placement angles

iModel_angles

iModel_angles(yaw,pitch,roll)

Construct a iModel_angles from 3 values

Example:

    // This is an example of accumulating the union of bounding boxes.
    const bboxUnionStmtECSQL = `
      SELECT
        iModel_bbox_union(
          iModel_placement_aabb(
            iModel_placement(
              iModel_point(g.Origin.X, g.Origin.Y, g.Origin.Z),
              iModel_angles(g.Yaw, g.Pitch, g.Roll),
              iModel_bbox(g.BBoxLow.X, g.BBoxLow.Y, g.BBoxLow.Z, g.BBoxHigh.X, g.BBoxHigh.Y, g.BBoxHigh.Z)
            )
          )
        )
      FROM ${Element.classFullName} AS e, ${GeometricElement3d.classFullName} AS g
        WHERE e.model.id=? AND e.ecinstanceid=g.ecinstanceid
    `;

    const rangeSum: Range3dProps = iModel.withPreparedStatement(bboxUnionStmtECSQL,
      (stmt: ECSqlStatement) => {
        stmt.bindId(1, modelId!);
        if (stmt.step() !== DbResult.BE_SQLITE_ROW)
          return {} as Range3dProps;
        // Note that the the ECSQL value is a blob. Its data must be extracted and interpreted as a Range3d.
        return Range3d.fromArrayBuffer(stmt.getValue(0).getBlob().buffer as ArrayBuffer);
      });
    reportRange(rangeSum);

iModel_angles_value

iModel_angles_value(angles,member)

Get a member of a iModel_angles object

iModel_angles_maxdiff

iModel_angles_maxdiff(angle1,angle2)

Return the maximum absolute difference among the angles in degrees.

iModel_bbox

iModel_bbox(XLow,YLow,Zlow,XHigh,YHigh,ZHigh)

Create a bounding box from 6 valuesAll coordinates are in meters.

Example:

    // This is an example of accumulating the union of bounding boxes.
    const bboxUnionStmtECSQL = `
      SELECT
        iModel_bbox_union(
          iModel_placement_aabb(
            iModel_placement(
              iModel_point(g.Origin.X, g.Origin.Y, g.Origin.Z),
              iModel_angles(g.Yaw, g.Pitch, g.Roll),
              iModel_bbox(g.BBoxLow.X, g.BBoxLow.Y, g.BBoxLow.Z, g.BBoxHigh.X, g.BBoxHigh.Y, g.BBoxHigh.Z)
            )
          )
        )
      FROM ${Element.classFullName} AS e, ${GeometricElement3d.classFullName} AS g
        WHERE e.model.id=? AND e.ecinstanceid=g.ecinstanceid
    `;

    const rangeSum: Range3dProps = iModel.withPreparedStatement(bboxUnionStmtECSQL,
      (stmt: ECSqlStatement) => {
        stmt.bindId(1, modelId!);
        if (stmt.step() !== DbResult.BE_SQLITE_ROW)
          return {} as Range3dProps;
        // Note that the the ECSQL value is a blob. Its data must be extracted and interpreted as a Range3d.
        return Range3d.fromArrayBuffer(stmt.getValue(0).getBlob().buffer as ArrayBuffer);
      });
    reportRange(rangeSum);

iModel_bbox_width

iModel_bbox_width(bb)

Compute the "width" of a bounding box

iModel_bbox_height

iModel_bbox_height(bb)

Compute the "height" of a bounding box

iModel_bbox_depth

iModel_bbox_depth(bb)

Compute the "depth" of a bounding box

iModel_bbox_volume

iModel_bbox_volume(bb)

Compute the volume of the bounding box

iModel_bbox_areaxy

iModel_bbox_areaxy(bb)

Compute the depth times the width of a bounding box

Example:

    // Compute the largest element area in the X-Y plane.
    let maxArea: number = 0;
    iModel.withPreparedStatement(`SELECT iModel_bbox_areaxy(iModel_bbox(BBoxLow.X,BBoxLow.Y,BBoxLow.Z,BBoxHigh.X,BBoxHigh.Y,BBoxHigh.Z)) FROM ${GeometricElement3d.classFullName}`,
      (stmt: ECSqlStatement) => {
        while (stmt.step() === DbResult.BE_SQLITE_ROW) {
          const thisArea: number = stmt.getValue(0).getDouble();
          if (thisArea > maxArea)
            maxArea = thisArea;
        }
      });
    // Report the result
    reportArea(maxArea);

    // Use the standard SUM operator to accumulate the results of the iModel_bbox_areaxy function. This shows that
    // ECSQL treats the built-in geometry functions as normal expressions.
    const areaSum: number = iModel.withPreparedStatement(`SELECT SUM(iModel_bbox_areaxy(iModel_bbox(BBoxLow.X,BBoxLow.Y,BBoxLow.Z,BBoxHigh.X,BBoxHigh.Y,BBoxHigh.Z))) FROM ${GeometricElement3d.classFullName}`,
      (stmt: ECSqlStatement) => {
        if (stmt.step() !== DbResult.BE_SQLITE_ROW)
          return 0; // ?
        return stmt.getValue(0).getDouble();
      });
    // Report the result
    reportArea(areaSum);

iModel_bbox_overlaps

iModel_bbox_overlaps(bb1,bb2)

Determine if the areas enclosed by two 3-D bounding boxes overlap

iModel_bbox_contains

iModel_bbox_contains(bb_outer,bb_inner)

Determine of the first bounding box contains the second bounding box

iModel_bbox_value

iModel_bbox_value(bb,member)

Get a member of a iModel_bbox object

iModel_bbox_union

iModel_bbox_union(X1)

Aggregate function that computes the union of a series of bounding boxes

Example:

    // This is an example of accumulating the union of bounding boxes.
    const bboxUnionStmtECSQL = `
      SELECT
        iModel_bbox_union(
          iModel_placement_aabb(
            iModel_placement(
              iModel_point(g.Origin.X, g.Origin.Y, g.Origin.Z),
              iModel_angles(g.Yaw, g.Pitch, g.Roll),
              iModel_bbox(g.BBoxLow.X, g.BBoxLow.Y, g.BBoxLow.Z, g.BBoxHigh.X, g.BBoxHigh.Y, g.BBoxHigh.Z)
            )
          )
        )
      FROM ${Element.classFullName} AS e, ${GeometricElement3d.classFullName} AS g
        WHERE e.model.id=? AND e.ecinstanceid=g.ecinstanceid
    `;

    const rangeSum: Range3dProps = iModel.withPreparedStatement(bboxUnionStmtECSQL,
      (stmt: ECSqlStatement) => {
        stmt.bindId(1, modelId!);
        if (stmt.step() !== DbResult.BE_SQLITE_ROW)
          return {} as Range3dProps;
        // Note that the the ECSQL value is a blob. Its data must be extracted and interpreted as a Range3d.
        return Range3d.fromArrayBuffer(stmt.getValue(0).getBlob().buffer as ArrayBuffer);
      });
    reportRange(rangeSum);

iModel_point_distance

iModel_point_distance(point1,point2)

Compute the distance between two iModel_Points, in meters.

iModel_point_min_distance_to_bbox

iModel_point_min_distance_to_bbox(point,bbox)

Compute the minimum distance from a point to a bounding box, in meters.

iModel_point_value

iModel_point_value(point,member)

Get a member of a iModel_Point object.

iModel_spatial_overlap_aabb

iModel_spatial_overlap_aabb(X1)

An rtree MATCH function that only accepts objects from the spatial index whose range overlap an aabb (axis-aligned bounding box).

Example:

  public static queryBarriersHitByRobot(iModelDb: IModelDb, rid: Id64String): Id64String[] {
    const robot1 = iModelDb.elements.getElement(rid) as Robot;

    const selStmt =
      `SELECT rt.ECInstanceId FROM BisCore.SpatialIndex rt WHERE rt.ECInstanceId MATCH iModel_spatial_overlap_aabb(:bbox) AND rt.ECInstanceId <> :thisRobot`;

    return iModelDb.withPreparedStatement(selStmt, (stmt: ECSqlStatement) => {
      stmt.bindRange3d("bbox", robot1.placement.calculateRange());
      stmt.bindId("thisRobot", rid);
      const hits: Id64String[] = [];
      while (stmt.step() === DbResult.BE_SQLITE_ROW) {
        hits.push(stmt.getValue(0).getId());
      }
      return hits;
    });
  }

Last Updated: 13 June, 2024