PolarDB:Surface mesh model

Install the extension

CREATE EXTENSION ganos_sfmesh CASCADE;

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

CREATE EXTENSION ganos_sfmesh WITH SCHEMA public CASCADE;

Create a table

Create a table with a column of the sfmesh type:

CREATE TABLE t_mesh(
  id      integer,
  mesh    sfmesh
);

Insert data

Insert rows using Well-Known Text (WKT) strings cast to sfmesh. The examples below use TriangleStrip and IndexSurface geometries with SRID 4326:

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 data

Retrieve all rows and display geometry as text:

SELECT id, ST_AsText(mesh)
FROM t_mesh;

Create a spatial index

After inserting data, create a GiST-based spatial index on the mesh column to accelerate spatial queries:

CREATE INDEX idx_t_mesh
ON t_mesh
USING GIST(mesh);

Run a spatial query

Find all rows that intersect with a given 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 (optional)

DROP EXTENSION ganos_sfmesh CASCADE;

What is a surface mesh model

In solid modeling and computer-aided design (CAD), a solid object is represented as a set of connected surface elements — triangles, quadrangles, or other polygons — that define the boundaries between its interior and exterior spaces.

A solid object built from these connected faces is a surface mesh model.

Data models

GanosBase Sfmesh extends OGC's Simple Feature Mode with three additional data models.

IndexSurface

Describes a polyhedron using indexed vertices. The same polyhedron can be expressed as a vertex sequence (p1, p2, p3, p4, p5) or as index-defined faces (p1,p2,p3), (p2,p3,p4), (p4,p5,p3).

WKT syntax:

MESHGEOM(PATCH(INDEXSURFACE(VERTEX(0 0 1, 0 10 2, 10 10 3, 10 0 4), INDEX((0,1,2),(1,2,3)))))

TriangleStrip

Describes a polyhedron as a strip of contiguous triangles. The vertex sequence (p1, p2, p3, p4, p5) defines the triangular faces (p1,p2,p3), (p2,p3,p4), (p3,p4,p5).

WKT syntax:

MESHGEOM(PATCH(TRIANGLESTRIP(0 0 0, 0 10 10, 10 10 10, 10 0 0)))

TriangleFan

Describes a polyhedron by connecting contiguous points to a common central vertex. The vertex sequence (p3, p1, p2, p4, p5) defines the triangular faces (p3,p1,p2), (p3,p2,p4), (p3,p4,p5).

Data types

GanosBase Sfmesh also supports a reference type — a special type that points to another data object rather than holding data directly.

Spatial reference system

A spatial reference system (SRS) defines how an Sfmesh object maps to a location on the earth's surface. GanosBase uses a Spatial Reference Identifier (SRID) to link each Sfmesh object to its SRS definition.

For details, see Spatial reference.

Interchange formats

• Well-Known Text (WKT): a human-readable text format. For details, see WKT.

• Well-Known Binary (WKB): a portable, accuracy-preserving binary format. For details, see WKB.

Data column view

sfmesh_columns is a system view that lists all surface mesh columns in the current database. It follows OGC's Simple Features Specification for SQL and includes the following columns:

Query all surface mesh columns in the current database:

SELECT * FROM sfmesh_columns;

Filter by subtype:

SELECT * FROM sfmesh_columns
WHERE g_sfmesh_type = 'sfmesh';

Spatial index

A spatial index greatly enhances the efficiency of querying a large spatial dataset without the need of a full database sequential scan. It achieves this by organizing spatial data in a search tree, which allows for rapid traversal and retrieval of records. GanosBase Sfmesh provides a GiST (Generalized Search Tree)-based 3D spatial index for Sfmesh.

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

3D maps and data visualization

3D maps show the distribution, trends, and spatial relationships in geospatial data. With materials and textures applied, surface mesh models can represent buildings, pipelines, and cables in a way that mirrors real-world conditions.

3D spatial query

Spatial queries filter data based on spatial relationships such as intersection and containment. Surface mesh models support 3D spatial queries, letting you quickly identify which objects intersect or contain a given region — useful in spatial data mining and urban planning.

3D spatial analysis and calculation

Surface mesh models support area, length, and distance calculations, as well as geometric operations such as intersection and union between two objects. These capabilities apply to measurement tasks including building area and volume estimation.

3D visualization

Storing BIM data as a surface mesh enables 3D visualization technology to render models with accurate spatial relationships, improving comprehension of complex or large BIM files compared to 2D drawings.

Attribute query

BIM data is typically stored in specialized file formats that cannot participate in join operations with other 2D and 3D datasets. Breaking BIM elements into a structured relational format makes both attribute and spatial querying straightforward.

Query all components of a given type using standard SQL:

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

Because BIM models accurately reflect physical dimensions, BIM data supports a variety of analytical tasks: calculating floor area ratio and building coverage ratio, verifying net floor-to-floor height and usable floor area, and detecting design clashes with spatial objects from other 2D and 3D datasets.