Utility

Introduction

Overview

GanosBase Utility is a spatial-temporal extension for PolarDB for PostgreSQL (Compatible with Oracle) that can be used to import, export, and validate 3D files in popular formats, such as OBJ, GLB, IFC, and OSGB. You can use the functions provided by GanosBase Utility to import 3D models into the database for processing.

GanosBase Utility seamlessly integrates with other GanosBase extensions. After importing 3D models to the database using GanosBase Utility, you can perform further operations like modeling, rendering, and simulation, streamlining the workflow and enhancing development efficiency. The following figures demonstrate the effectiveness of using GanosBase Utility with other GanosBase extensions.

• Import and view IFC files:

  

• Import and view OSGB files:

  

• Perform Field of View (FOV) analysis on OSGB data:

  

Scenarios

• Import and export 3D files

  GanosBase Utility can be used to import, export, and verify 3D files in popular formats, including OBJ, GLB, IFC, and OSGB. This offers you flexibility in processing 3D data.

• View and manage 3D data

  GanosBase Utility makes it easy to manage and view 3D models by providing tools for rapid data import and export. This significantly streamlines the workflow and enhances efficiency.

• Validate 3D data

  GanosBase Utility can be used to check the formats, strcutures, and integrity of 3D files, ensuring their validity and integrity.

Components

GanosBase Utility provides functions to import, export, and validate data for use in other GanosBase extensions, such as GanosBase Sfmesh and GanosBase Scene.

Data import

• IFC

  The Industry Foundation Classes (IFC) is an open, industry-standard format that is used to describe and exchange architectural and infrastructure information. It is developed by the International Standard Organization (ISO) to facilitate collaboration and interoperability in Building Information Modeling (BIM) projects.

  The IFC format is an object-oriented file format used for representing components, properties, and relationships in construction projects. It stores information that describes shapes, materials, properties, and spatial relationships, as well as information about the functions, construction process, and maintenance procedures of a facility.

• OBJ

  The OBJ file format (also called Wavefront OBJ) is a widely used file format that stores information about the shapes and materials of static 3D models.

  Each OBJ file is accompanied by an MTL file. The OBJ file holds information about a model's vertices, normals, texture coordinates, and faces. It describes this information using keywords, such as v (vertices), vn (normals), and vt (texture coordinates), and defines each face using vertex indexes, which can describe how vertices are connected to form polygons. The companion MTL file contains information about a 3D model's material properties, such as color, texture, and lighting.

• GLB

  gITF Binary (GLB) is a binary file format for 3D models. It contains information about a model's shapes, materials, textures, and animations.

  Because GLB stores the data in a binary format, file sizes are smaller and loading times are shorter, making GLB an ideal format for transmitting and rendering 3D models in real-time applications, such as virtual reality (VR), augmented reality (AR), and WebGL.

• OSGB

  The OpenSceneGraph Binary (OSGB) format is used for storing 3D scenes. It is part of the OpenSceneGraph (OSG) framework and holds information about 3D models, including their textures, and material properties.

  Because OSGB stores data in a binary format, it achieves smaller file sizes and faster loading compared to formats such OBJ and FBX. These advantages make OSGB the ideal format for dealing with massive scenes.

Data export

3D Tiles

3D Tiles is an open, standard-based format designed for efficiently streaming massive amounts of 3D geospatial data such as city models, terrains, and point clouds on web applications.

The hierarchical structure of 3D Tiles makes it ideal for use cases that demand rapid rendering and visualization of massive amounts of geospatial data. Tiles are organized based on geospatial position and level of detail (LOD). As users zoom in, the system seamlessly transitions from lower LOD tiles to higher LOD tiles, providing more details. This dynamic approach minimizes loading times, allowing users to efficiently view and interact with immense datasets.

Data validation

OSGB

OSGB files store data in a binary format, which is difficult to validate. GanosBase Utility offers functions to validate the effectiveness and integrity of OSGB file data.

Other components

For more information, see Utility SQL references.

Quick start

Overview

This section describes the usage of GanosBase Utility, such as how to install the extension, and import, export, and validate data.

Basic usage

• Install the extension:

  -- Install the ganos_utility extension.
  CREATE EXTENSION ganos_utility CASCADE;

  Note

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

  Create extension ganos_utility with schema public CASCADE;

• Import data:

  -- Import a gITF file into the gltf_table table and set the id to 1.
  SELECT ST_ImportGLTF('gltf_table', 'OSS://<ak>:<ak_secret>@oss-cn-beijing-internal.aliyuncs.com/bim_wall.gltf', '1');
  
   st_importgltf
  ---------------
   t
  (1 row)
  
  -- Import an IFC file and generate an ifc_ifc_elem table.
  SELECT ST_ImportIFC('ifc', 'OSS://<ak>:<ak_secret>@oss-cn-beijing-internal.aliyuncs.com/CE1314-ACM-C3_LA-ST07_WB-AR-M3_N.ifc'::cstring);
  
   st_importifc
  --------------
   t
  (1 row)
  
  -- Import an OBJ file into the obj_table table.
  SELECT ST_ImportOBJ('obj_table', 'OSS://<ak>:<ak_secret>@oss-cn-beijing-internal.aliyuncs.com/obj/j3sn.obj', '1');
  
   st_importobj
  --------------
   t
  (1 row)
  
  -- Import an OSGB file into the osgb table.
  SELECT ST_ImportOSGB('osgb', 'OSS://<ak>:<ak_secret>@oss-cn-beijing-internal.aliyuncs.com/osgb');
  
   st_importosgb
  ---------------
   t
  (1 row)

  Note

  • You need to access OSS through OSS domain names. For more information, see Object storage service paths.

  • Replace <ak> and <ak_secret> with the actual access key and access secret.

• Export data:

  -- Convert IFC data into the 3D Tiles format and store the output in the tile table.
  SELECT ST_As3DTiles(element, 'tile') from ifc_ifc_elem where family = 'IfcProject';
  
   st_as3dtiles
  --------------
   t
  (1 row)

• Validate data:

  -- Validate data within the OSGB file.
  SELECT * FROM ST_ValidateOSGB('OSS://<ak>:<ak_secret>@oss-cn-beijing-internal.aliyuncs.com/osgb');
  
   valid | reason
  -------+--------
   t     |
  (1 row)

• (Optional) Drop the extension:

  DROP EXTENSION ganos_utility CASCADE;

Advanced usage

GanosBase Utility provides advanced parameters, enabling you to customize configurations.

• Import an IFC file

  -- Import a specified project.
  SELECT ST_ImportIFC('Building', 'OSS://<ak>:<ak_secret>@oss-cn-beijing-internal.aliyuncs.com/mybucket/path_to_ifc'::cstring, '{"project":"building_1"}');
  
  -- Disable parallel import.
  SELECT ST_ImportIFC('local', 'OSS://<ak>:<ak_secret>@oss-cn-beijing-internal.aliyuncs.com/CE1314-ACM-C3_LA-ST07_WB-AR-M3_N.ifc'::cstring, '{"disable_multi_thread":true}');

• Import an OBJ file

  -- Disable automatic triangulation.
  SELECT ST_ImportObj('test_obj', 'OSS://<ak>:<ak_secret>@oss-cn-beijing-internal.aliyuncs.com/mybucket/path_to_obj.obj', '{"force_triangulate": false}');

• Import a gLTF/GLB file

  -- Flip the y-axis with the z-axis during data import.
  SELECT ST_ImportGLTF('test_gltf', 'OSS://<ak>:<ak_secret>@oss-cn-beijing-internal.aliyuncs.com/mybucket/path_to_gltf.glb', 'my_glb', '{"flip_y_z": true}');

• Import an OSGB file

  -- Specify the number of parallel tasks.
  SELECT ST_ImportOSGB('test_osgb', 'OSS://<ak>:<ak_secret>@oss-cn-beijing-internal.aliyuncs.com/mybucket/path_to_oblique_project/', '{"parallel": 4}');
  
  -- Turn on the gateway mode, where the database stores paths and no data.
  SELECT ST_ImportOSGB('test_osgb', 'OSS://<ak>:<ak_secret>@oss-cn-beijing-internal.aliyuncs.com/mybucket/path_to_oblique_project/', '{"gateway": true}');

• Validate data within an OSGB file:

  -- Perform the validation through parallel execution.
  SELECT ST_ValidateOSGB('OSS://<ak>:<ak_secret>@oss-cn-beijing-internal.aliyuncs.com/mybucket/path_to_oblique_project/', '{"parallel": 4}');

• Export a 3D Tile file

  -- Perform the export through parallel execution.
  SELECT ST_As3DTiles(element, 'test', '{"parallel": 4}') from ifc_ifc_elem where family = 'IfcProject';
  
  -- Specify the maximum tile size in KB. Geometry objects larger than this will be segmented.
  SELECT ST_As3DTiles(element, 'test', '{"size_threshold": 1024}') from ifc_ifc_elem where family = 'IfcProject';
  
  -- Export the coordinate system of the 3D Tiles file.
  SELECT ST_As3DTiles(element, 'test', '{"srid": 4326}') from ifc_ifc_elem where family = 'IfcProject';
  
  -- Set how 3D tile space is organized. Valid values: oct (an octree), quad (a quadtree), and bsp (BSP tree).
  SELECT ST_As3DTiles(element, 'test', '{"method": "oct"}') from ifc_ifc_elem where family = 'IfcProject';

SQL reference

For more information, see Utility SQL references.