How to use GanosBase PointCloud - PolarDB - Alibaba Cloud Documentation Center

This topic describes the details and usage of the point cloud model.

Introduction

Overview

A point cloud model usually consists of points that are generated when a 3D scanner scans an object. Each point contains a set of 3D coordinates. Some points may contain RGB information or intensity. Point cloud data contains spatial coordinates and a large number of points that are presented in complex attribute dimensions.

GanosBase PointCloud is an extension of PostgreSQL (PolarDB for PostgreSQL. GanosBase PointCloud provides features, such as point cloud data compression, point cloud data decompression, and attribute statistics collection, that allows PostgreSQL to store and manage point cloud data in an efficient and fast manner. GanosBase PointCloud can work with other GanosBase modules to perform spatial analysis on point cloud data.

Features

GanosBase PointCloud provides PcPoint and PcPatch data types. PcPoint is the basic object of point cloud, which supports the import and export of point cloud data, query of point cloud schema, and spatial query of point cloud data. PcPatch is a collection of point cloud data, which support the packaging of point cloud data, compression and decompression of point cloud data packets, schema query of point cloud data packets, and filtering of point cloud data packets.

Business scenarios

GanosBase PointCloud is commonly used in the following scenarios:

3D Modeling and visualization
GanosBase PointCloud can be used to store and process 3D scan data, and perform 3D modeling and visualization by using point cloud data. This is useful in fields including architectural design, urban planning, and cultural relic protection.
Robotics and autonomous driving
GanosBase PointCloud can be used to process the 3D perception data in robotics and autonomous driving systems. Features including environment perception, obstacle detection, and path planning can be achieved by using point cloud data.
Industrial measurement and quality assurance
In the industrial sector, GanosBase PointCloud can be used to process 3D measurement data, such as the surface shape data of a product that is obtained by using 3D scanners. Such data can be used for quality assurance, product design, and manufacturing process optimization.

Components

Point cloud format table

The original point cloud data may contain several dimension values. Different values provide different levels of precision. The format of the schema document used by GanosBase PointCloud is the same as that used by the PDAL library. The schema document describes the metedata including the dimensions of each point, and the data type of each dimension.

The schema document and the corresponding PCID are stored in the pointcloud_formats table.

Point cloud objects

You can store data of the PcPoint or PcPatch type in a point cloud table.

PcPoint is the basic point cloud object. Each PcPoint object contains at least an x coordinate and a y coordinate. It can contain more dimensions.
See the following sample JSON code of a PcPoint object:
```
{
  "pcid": 1,
  "pt": [0.01, 0.02, 0.03, 4]
}
```
Take note of the following parameters:
- PCID: the foreign key that points to the pointcloud_formats table.
- pt: the information of the point cloud in the format described by the schema document in the pointcloud_formats table that is specified by PCID.
A PcPatch object is a collection of PcPoint objects that are located close to each other. You can use PcPatch objects to reduce the number of rows in the database.
See the following sample JSON code of a PcPatch object:
```
{
  "pcid": 1,
  "pts": [
    [0.02, 0.03, 0.05, 6],
    [0.02, 0.03, 0.05, 8]
  ]
}
```

Use the data type based on your business requirements:

To process the data of single points, use the PcPoint data type.
To perform efficient queries and operations on a large amount of point cloud data, use the PCPatch data type.

Spatial reference system

The spatial reference system (SRS) defines how a PointCloud object is associated with a specific location on the surface of the Earth.

GanosBase uses an integer, which is called SRID, to reference an SRS definition. A PointCloud object uses an SRID value to associate with SRS.

For more information, see Spatial reference.

Data column view

GanosBase PointCloud provides the point cloud column view, which is similar to the geometry column view in a geometry model.

Column name	Type	Description
schema	varchar(256)	The schema of the table.
table	varchar(63)	The name of the table.
column	varchar(63)	The name of a point cloud column in the table.
pcid	integer	The ID of the schema document that corresponds to the point cloud column, which is the foreign key that points to the pointcloud_formats table.
srid	integer	The SRID of the point cloud column, which is the foreign key that points to the spatial_ref_sys table.
type	varchar(30)	Uses the PcPoint or PcPatch data type.

You can execute the following statement to query all geometry data columns in the current database:

SELECT * FROM pointcloud_columns;

Data compression

In most cases, the amount of point cloud data is large. GanosBase PointCloud allows you to specify the data compression method by adding the following lines to the definition of the schema document:

<pc:metadata>
  <Metadata name="compression"> Compression method </Metadata>
</pc:metadata>

The following compression methods are supported:

None: Data is not compressed. PcPoint and PcPatch objects are stored as byte arrays by using the type and format defined in the schema document. This is the default value.
Dimensional: PcPacth objects are stored as collections of dimensional data arrays with appropriate compression method enabled. This value applies to only PcPatch objects. PcPoint objects are stored in the same way as that of the None value.
Run-length encoding: used for dimensions with low variability.
Common bits removal: used for dimensions with variability in a small range.
Deflate compression by using zlib: used for dimensions to which other compression methods are not applicable.
LAZ or LASZip: the standard compression method for LIDAR data.

Dimensional compression

The following PcPatch object contains four dimensions and six points:

{
  "pcid": 1,
  "pts": [
    [-126.99, 45.01, 1, 0],
    [-126.98, 45.02, 2, 0],
    [-126.97, 45.03, 3, 0],
    [-126.96, 45.04, 4, 0],
    [-126.95, 45.05, 5, 0],
    [-126.94, 45.06, 6, 0]
  ]
}

After dimensional compression is used, the PcPacth object is theoretically equivalent to the following object:

{
  "pcid": 1,
  "dims": [
    [-126.99, -126.98, -126.97, -126.96, -126.95, -126.94],
    [45.01, 45.02, 45.03, 45.04, 45.05, 45.06],
    [1, 2, 3, 4, 5, 6],
    [0, 0, 0, 0, 0, 0]
  ]
}

For small-size PcPatch objects of PcPoint objects that sample similar areas, the compression ratio of dimensional compression is between 3:1 and 5:1.

Quick start

Overview

Follow the instructions to use the GanosBase PointCloud engine, including extension creation, point cloud schema definition, table creation, data insertion, and attribute calculation.

Syntax

Create extensions

CREATE extension ganos_pointcloud cascade;
CREATE extension ganos_pointcloud_geometry cascade;

Note

Install the extension in the public mode to avoid permission issues.

CREATE extension ganos_pointcloud WITH schema public;
CREATE extension ganos_pointcloud_geometry WITH schema public;

Define the point cloud schema

Insert the schema in XML format to the pointcloud_formats table that is created by default. The schema defines the attribute dimensions of the point cloud, and the data size, type, name, and description of each dimension.

INSERT INTO pointcloud_formats (pcid, srid, schema) VALUES (1, 4326,
'<?xml version="1.0" encoding="UTF-8"?>
<pc:PointCloudSchema xmlns:pc="http://pointcloud.org/schemas/PC/1.1"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <pc:dimension>
    <pc:position>1</pc:position>
    <pc:size>4</pc:size>
    <pc:description>X coordinate as a long integer. You must use the
                    scale and offset information of the header to
                    determine the double value.</pc:description>
    <pc:name>X</pc:name>
    <pc:interpretation>int32_t</pc:interpretation>
    <pc:scale>0.01</pc:scale>
  </pc:dimension>
  <pc:dimension>
    <pc:position>2</pc:position>
    <pc:size>4</pc:size>
    <pc:description>Y coordinate as a long integer. You must use the
                    scale and offset information of the header to
                    determine the double value.</pc:description>
    <pc:name>Y</pc:name>
    <pc:interpretation>int32_t</pc:interpretation>
    <pc:scale>0.01</pc:scale>
  </pc:dimension>
  <pc:dimension>
    <pc:position>3</pc:position>
    <pc:size>4</pc:size>
    <pc:description>Z coordinate as a long integer. You must use the
                    scale and offset information of the header to
                    determine the double value.</pc:description>
    <pc:name>Z</pc:name>
    <pc:interpretation>int32_t</pc:interpretation>
    <pc:scale>0.01</pc:scale>
  </pc:dimension>
  <pc:dimension>
    <pc:position>4</pc:position>
    <pc:size>2</pc:size>
    <pc:description>The intensity value is the integer representation
                    of the pulse return magnitude. This value is optional
                    and system specific. However, it should always be
                    included if available.</pc:description>
    <pc:name>Intensity</pc:name>
    <pc:interpretation>uint16_t</pc:interpretation>
    <pc:scale>1</pc:scale>
  </pc:dimension>
  <pc:metadata>
    <Metadata name="compression">dimensional</Metadata>
  </pc:metadata>
</pc:PointCloudSchema>');

Point cloud data types

-- The PcPoint data type
CREATE type pcpoint(...);
The dimension information of a point is defined by the schema.
For the PcPoint data type, each point is stored as a row. 
For example, ST_MakePoint(1, ARRAY[2,3,4,0.5]) returns a PcPoint object.
-- The PcPatch data type.
CREATE type pcpatch(...);
A PcPatch object consists of a set of points and supports compression. The compression method is specified by using the compression parameter in the schema.
You can use a PcPatch object to compress a set of points and store the data in one row to save storage. Spatial query is supported.

Create a point cloud table

-- A table of points
CREATE TABLE points (
    id SERIAL PRIMARY KEY,
    pt PCPOINT(1) -- (1) indicates the schema whose PCID is 1 in the pointcloud_formats table.
);
-- A table of patches
CREATE TABLE patches (
    id SERIAL PRIMARY KEY,
    pa PCPATCH(1)
);

Insert data of the PcPoint type.

INSERT INTO points (pt)
SELECT ST_MakePoint(1, ARRAY[x,y,z,intensity])
FROM (
  SELECT
  -127+a/100.0 AS x,
    45+a/100.0 AS y,
         1.0*a AS z,
          a/10 AS intensity
  FROM generate_series(1,100) AS a
) AS values;
SELECT ST_MakePoint(1, ARRAY[-127, 45, 124.0, 4.0]);
-------------------------
010100000064CEFFFF94110000703000000400
SELECT ST_AsText('010100000064CEFFFF94110000703000000400'::pcpoint);
-------------------------
{"pcid":1,"pt":[-127,45,124,4]}

Insert data of the PcPatch type.

INSERT INTO patches (pa)
SELECT ST_Patch(pt) FROM points GROUP BY id/10;
SELECT ST_AsText(ST_MakePatch(1, ARRAY[-126.99,45.01,1,0, -126.98,45.02,2,0, -126.97,45.03,3,0]));
-------------------------
{"pcid":1,"pts":[
 [-126.99,45.01,1,0],[-126.98,45.02,2,0],[-126.97,45.03,3,0]
]}

Calculate the average values of all attribute dimensions in a PcPatch object.

SELECT ST_AsText(ST_PatchAvg(pa)) FROM patches WHERE id = 7;
-------------------------
{"pcid":1,"pt":[-126.46,45.54,54.5,5]}

Delete the extensions (optional)

DROP extension ganos_pointcloud_geometry;
DROP extension ganos_pointcloud cascade;

SQL statements

For more information, see Point cloud SQL reference.