PolarDB:Surface mesh model

Introduction

Overview

Surface mesh modeling is a common 3D modeling technique which is used to represent a 3D object as a mesh of contiguous surfaces. Textures and materials can be applied to surfaces to enhance the visual details of the object's 3D model.

Standard SQL can be used to query and manipulate data of the surface mesh model's data types. Data can be expressed and exchanged in text and binary formats.

GanosBase Sfmesh is a spatial-temporal extension of PolarDB for PostgreSQL. It offers a set of data types, functions and stored procedures to help users efficiently manage, retrieve, and analyze three-dimensional surface data.

Features

The surface mesh model offers multiple built-in data types and functions to create, access, edit, process, aggregate, import, and export data, and supports the determination of 3D spatial relationships, 3D analysis and calculation, and 3D indexes.

With the GanosBase Utility extension, the surface mesh model supports the import of 3D data in multiple formats, such as IFC, gITF, and OBJ. The extension also allows for the conversion of a surface mesh model into 3D tiles.

For more information, see SfMesh SQL references.

Scenarios

A surface mesh model can be used for spatial data visualization and analysis.

• 3D maps and data visualization

  A 3D map shows the distribution, trends, and relationships of geospatial data, helping you identify meaning and patterns in the data. Visualization of 3D spatial data can represent three-dimensional objects, such as buildings, pipelines, and cables. With added visual elements including materials and textures, it can mimic real-world situations for display and informed decision making.

• 3D spatial query

  Spatial queries can be used in scenarios such as spatial data mining and urban planning. They allow you to quickly filter data based on spatial relationships and thus make informed decisions.

  The surface mesh model supports 3D spatial queries to filter spatial data based on spatial relationships including intersection and containment. Spatial queries can also be used to determine the relationships between two objects, such as whether they are intersected.

• 3D spatial analysis and calculation

  3D spatial analysis and calculations are useful in diverse measurement activities, such as measuring the building area and volume.

  The surface mesh model supports a variety of spatial analytical and calculation tasks, such as calculating area, length, and distance, as well as the intersection and union of two objects.

In the following example, the surface mesh model is used in the Building Information Modeling (BIM) scenario.

Example

BIM provides a digital representation of physical and functional characteristics of a facility. BIM is used for the facility's design, construction, and operation.

BIM produces a large amount of three-dimensional spatial data and property data, which are suitable for central storage, management, and query in databases.

The GanosBase Utility extension can import a model in the IFC format generated by BIM programs and convert it to a surface mesh model.

3D visualization

Compared to a 2D drawing, a 3D model enhances realism, presenting information in a more intuitive way and vividly showing the spatial relationships within a facility.

Storing BIM data as a surface mesh allows for the creation of 3D models using visualization technology. This improves the intuitiveness of BIM data, especially large BIM files.

Attribute query

As BIM data is usually stored in specialized file formats, it cannot be centrally managed or used in join operations along with other 2D and 3D data. To facilitate business and spatial querying, it is necessary to segment BIM elements into a structured format.

Databases are ideal for managing segmented BIM data, as they store the BIM data in a structured way and allow for easy querying of both attributes and spatial relationships.

For example, you can query components categorized as the "System Panel" 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

The BIM model accurately reflects the dimensions of the physical facility. Therefore, BIM data can be used for various analytical tasks, including calculating the floor area ratio and the building coverage ratio, verifying net floor-to-floor height and floor area, and detecting design clashes with spatial objects from other 2D and 3D datasets.

Components

What is a surface mesh model

In solid modeling and computer-aided design, a solid object is usually represented as a set of connected surface elements, which define the boundaries between internal and external spaces.

These surfaces are often composed of triangles, quadrangles, or other polygons that, when combined, accurately depict a solid object's shape.

A solid object constructed using these connected faces is a surface mesh model.

Spatial reference system

The spatial reference system (SRS) defines how to associate an Sfmesh object with a specific location on the earth's surface.

GanosBase uses an SRID to link an Sfmesh object to its respective SRS definition.

Fore more information, see Spatial reference.

Data models

In addition to OGC's Simple Feature Mode, the surface mesh model of GanosBase provides three new data models as follows.

INDEXSURFACE

IndexSurface is used for describing a polyhedron with indexes. For example, the following polyhedron can be represented by a vertice sequence (p1, p2, p3, p4, p5) or vertice-defining faces such as (p1, p2, p3), (p2, p3, p4), and (p4, p5, p3).

TRIANGLESTRIP

TriangleStrip is used for describing a polyhedron with contiguous points. For example, the following polyhedron can be represented by a vertice sequence (p1, p2, p3, p4, p5) or vertice-defining triangular faces (p1, p2, p3), (p2, p3, p4), and (p3, p4, p5).

TRIANGLEFAN

TriangleFan is used for describing a polyhedron by connecting contiguous points to a common central point. For example, the following polyhedron can be represented by a vertice sequence (p3, p1, p2, p4, p5) or vertice-defining triangular faces (p3, p1, p2), (p3, p2, p4), and (p3, p4, p5).

Data types

The surface mesh model of GanosBase has the following data types.

• sfmesh: a collection of data types. It contains information about the geometry, materials, textures, and texture coordiantes.

• meshgeom: the geometric object. It is used to record coordinates, shape description, texture coordinates, and normal vectors.

• texture: texture mapping information, including width, height, compression method, and binary texture data.

• material: the collection of visual attributes on the sfmesh object's surface, including color, texture, smoothness, transparency, reflectivity, refractive index, and luminosity.

The surface mesh model also supports the reference type, which is a special data type that does not contain data but only points to another data object.

Interchange formats

Two interchange formats are supported by the surface mesh model:

• Well-known text (WKT): an easy-to-read text format For more information, see WKT.

• Well-known binary (WKB): a portable, accuracy-preserving binary format For more information, see WKB.

Data column view

In GanosBase, the sfmesh_columns view contains all columns related to the surface mesh model and derives this information from the database system's catalog tables. It follows OGC's Simple Features Specification for SQL while also incorporating additional extensions.

The view's structure is as follows:

Query all columns of the surface mesh model in the current database:

SELECT * FROM sfmesh_columns;

Filter columns:

SELECT * FROM sfmesh_columns
WHERE g_sfmesh_type = 'sfmesh';

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 provides a GiST-based, three-dimensional spatial index for Sfmesh.

Create a spatial index as follows:

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

Quick start

Overview

This section describes the fundamentals of using the ganos_sfmesh extension, such as extension installation, table and index creation, data insert, data query, and spatial query.

Syntax

• Install the ganos_sfmesh extension:

  CREATE EXTENSION ganos_sfmesh CASCADE;

  Note

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

  CREATE extension ganos_sfmesh WITH schema public cascade CASCADE;

• Create a table with a column of the sfmesh type:

  CREATE TABLE t_mesh(
    id integer,
    mesh sfmesh
  );

• Insert data:

   -- Insert data into the table
  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);

• Perform a query:

  SELECT id, ST_AsText(mesh)
  FROM t_mesh;
  
  -------------------------
  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" : [{"pr
   imitive" : 0}]}

• Create an index:

   CREATE INDEX idx_t_mesh
   ON t_mesh
   USING GIST(mesh);

• Perform a spatial query:

   -- Query data that intersects with a specified 3D object
  SELECT id
  FROM t_mesh
  WHERE ST_3DIntersects(mesh, ST_SetSRID('BOX3D(0 0 0, 10 10 10)'::box3d::sfmesh, 4326));

• (Optional) Drop the extension:

  DROP EXTENSION ganos_sfmesh CASCADE;

SQL statements

For more information, see SfMesh SQL references.