PolarDB:Surface mesh model

Example: Building Information Modeling

Building Information Modeling (BIM) produces large volumes of 3D spatial data and property data that are well suited to central storage and querying in a database.

The GanosBase Utility extension imports IFC files from BIM programs and converts them to surface mesh models. Once stored, the data supports:

3D visualization — A 3D model conveys spatial relationships more intuitively than a 2D drawing, which is especially valuable for large BIM files.

Attribute queries — BIM data stored in a structured format can be queried with standard SQL alongside other 2D and 3D datasets. For example, to retrieve all components of the "System Panel" type:

SELECT project_uuid, project_name, parent_uuid, uuid, "family", "name", attrs, props_set
FROM ifc_demo_ifc_elem
WHERE
(((props_set->'Other')::json)->'Type')::text like '%System Panel: %'

Spatial analysis — BIM data accurately reflects physical dimensions, so you can calculate floor area ratios, verify net floor-to-floor heights, and detect design clashes with objects from other datasets.

What is a surface mesh model

In solid modeling and computer-aided design, a solid object is represented as a set of connected surface elements that define the boundary between its interior and exterior spaces.

These surfaces are composed of triangles, quadrangles, or other polygons. Together, they form a surface mesh model that accurately depicts the shape of a solid object.

Data models

GanosBase Sfmesh extends OGC's Simple Features with three data models for representing polyhedra:

IndexSurface

IndexSurface describes a polyhedron using a vertex array and a separate index array. For example, the polyhedron below is represented by vertices (p1, p2, p3, p4, p5) and index-defined faces such as (p1, p2, p3), (p2, p3, p4), and (p4, p5, p3).

TriangleStrip

TriangleStrip describes a polyhedron as a strip of contiguous triangles. The polyhedron below is represented by the vertex sequence (p1, p2, p3, p4, p5), forming triangular faces (p1, p2, p3), (p2, p3, p4), and (p3, p4, p5).

TriangleFan

TriangleFan describes a polyhedron by connecting contiguous points to a common center point. The polyhedron below is represented by the vertex sequence (p3, p1, p2, p4, p5), forming triangular faces (p3, p1, p2), (p3, p2, p4), and (p3, p4, p5).

Data types

The sfmesh data type is a composite type that bundles geometry, materials, textures, and texture coordinates into a single object. The following types make up an sfmesh object:

GanosBase Sfmesh also supports a reference type — a special type that does not contain data but points to another data object.

Spatial reference system

The spatial reference system (SRS) defines how to associate an sfmesh object with a specific location on Earth. GanosBase uses an SRID to link an sfmesh object to its SRS definition.

For more information, see Spatial reference.

Interchange formats

sfmesh objects can be expressed in two text/binary formats:

• Well-Known Text (WKT) — A human-readable text format. For more information, see WKT.

• Well-Known Binary (WKB) — A compact, portable binary format that preserves precision. For more information, see WKB.

Spatial index

GanosBase provides a Generalized Search Tree (GiST)-based three-dimensional spatial index for sfmesh columns. It organizes spatial data in a search tree, enabling fast traversal and retrieval without a full table scan.

Create a spatial index on an sfmesh column:

CREATE INDEX <index_name>
ON <table_name>
USING GIST(<column_name>);

Metadata view

The sfmesh_columns view lists all sfmesh-typed columns in the current database. It follows OGC's Simple Features Specification for SQL with additional extensions, and derives its information from the database system catalog.

Query all sfmesh columns in the current database:

SELECT * FROM sfmesh_columns;

Filter by a specific subtype:

SELECT * FROM sfmesh_columns
WHERE g_sfmesh_type = 'sfmesh';

Install the extension

CREATE EXTENSION ganos_sfmesh CASCADE;

Install the extension into the public schema to avoid potential permission issues.

CREATE EXTENSION ganos_sfmesh WITH SCHEMA public CASCADE;

Create a table

Create a table with an sfmesh column:

CREATE TABLE t_mesh(
  id integer,
  mesh sfmesh
);

Insert mesh data

Insert a TriangleStrip object and an IndexSurface object:

INSERT INTO t_mesh(id, mesh)
VALUES
  (1, '{
    "version": 1, "srid": 4326, "root": 0,
    "meshgeoms": ["MESHGEOM(PATCH(TRIANGLESTRIP(0 0 0,0 10 10,10 10 10,10 0 0)))"],
    "primitives": [{"meshgeom": 0}],
    "nodes": [{"primitive": 0}]
  }'::sfmesh),
  (2, '{
    "version": 1, "srid": 4326, "root": 0,
    "meshgeoms": ["MESHGEOM(PATCH(INDEXSURFACE(VERTEX(0 0 1,0 10 2,10 10 3,10 0 4),INDEX((0,1,2),(1,2,3)))))"],
    "primitives": [{"meshgeom": 0}],
    "nodes": [{"primitive": 0}]
  }'::sfmesh);

Query mesh data

SELECT id, ST_AsText(mesh)
FROM t_mesh;

Expected output:

1 | {"version" : 1, "srid" : 4326, "root" : 0, "meshgeoms" : ["MESHGEOM(PATCH(TRIANGLESTRIP(0 0 0,0 10 10,10 10 10,10 0 0)))"], "primitives" : [{"meshgeom" : 0}], "nodes" : [{"primitive" : 0}]}
2 | {"version" : 1, "srid" : 4326, "root" : 0, "meshgeoms" : ["MESHGEOM(PATCH(INDEXSURFACE Z (VERTEX(0 0 1,0 10 2,10 10 3,10 0 4),INDEX((0,1,2),(1,2,3)))))"], "primitives" : [{"meshgeom" : 0}], "nodes" : [{"primitive" : 0}]}

Create a spatial index

CREATE INDEX idx_t_mesh
ON t_mesh
USING GIST(mesh);

Run a spatial query

Query rows that intersect a specified 3D bounding box:

SELECT id
FROM t_mesh
WHERE ST_3DIntersects(mesh, ST_SetSRID('BOX3D(0 0 0, 10 10 10)'::box3d::sfmesh, 4326));

Drop the extension

DROP EXTENSION ganos_sfmesh CASCADE;