PolarDB:Point cloud model

Introduction

A point cloud model is usually a record output in the form of points by a 3D laser scanner that scans the relevant entities. Each point contains 3D coordinates, and some points may also contain color information (RGB) or reflection intensity information (Intensity). Point cloud data contains spatial coordinate information and is characterized by a large number of points and complex attribute dimensions.

GanosBase PointCloud is a spatio-temporal engine extension of the object-relational database PostgreSQL Compatible Edition (PolarDB for PostgreSQL). The extension enables the preceding database to store and manage point cloud data effectively and quickly. It provides features such as point cloud data compression, decompression, and attribute statistics. It also works with other GanosBase modules to provide point cloud spatial analysis capabilities.

Feature overview

GanosBase PointCloud mainly provides two data types: PcPoint and PcPatch. PcPoint is the basic object of a point cloud. It supports capabilities such as point cloud data import and export, point cloud data metadata query, and point cloud data spatial query. PcPatch is the collection object of a point cloud. It supports capabilities such as point cloud data packaging, point cloud data packet compression and decompression, point cloud data packet metadata query, and point cloud data packet filtering.

Main use cases

GanosBase Common use cases of PointCloud include the following:

• 3D modeling and visualization

  GanosBase PointCloud can be used to store and process 3D scan data, and to perform 3D modeling and visualization based on point cloud data. This is very useful in fields such as architectural design, urban planning, and cultural relic protection.

• Robotics and autonomous driving

  GanosBase PointCloud can be used to process 3D perception data in robotics and autonomous driving systems. Based on point cloud data, you can implement features such as environment perception, obstacle detection, and path planning.

• Industrial measurement and quality control

  In the industrial field, GanosBase PointCloud can be used to process 3D measurement data, such as the product surface shape data obtained by a laser scanner. This data can be used for purposes such as quality control, product design, and manufacturing process optimization.

Point cloud format table

Raw point cloud data may have several dimension values, and different values may have different precisions.GanosBase PointCloud uses a Schema Document (referred to as the schema document in this topic) that is consistent with PDAL. The schema document describes metadata such as the dimensions contained in each point and the data type of each dimension.

The schema document is stored with its PCID in the pointcloud_formats table of the database.

Point cloud objects

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

• PcPoint is the basic point cloud object. It has at least the two dimensions of X and Y coordinates, and may have more dimensions.

  The JSON representation of a PcPoint object is as follows:

  {
    "pcid": 1,
    "pt": [0.01, 0.02, 0.03, 4]
  }

  Where:

  • PCID: the foreign key that references the pointcloud_formats table.

  • pt: the specific information of the point cloud, which follows the format described in the schema document referenced by the PCID.

• PcPatch is a collection of a series of PcPoint objects that are close to each other. This reduces the number of record rows in the database.

  The JSON representation of a PcPatch object is as follows:

  {
    "pcid": 1,
    "pts": [
      [0.02, 0.03, 0.05, 6],
      [0.02, 0.03, 0.05, 8]
    ]
  }

The choice of type depends on your business use case:

• If the data you process is always at the granularity of a single point, you can choose PcPoint.

• If you need to perform efficient queries and operations on a large amount of point cloud data, you can choose PcPatch.

Spatial reference

A spatial reference system (SRS) defines how to associate a PointCloud object with a specific location on the surface of the Earth.

GanosBase uses an integer to represent the reference to the definition of an SRS. This integer is called an SRID. A PointCloud object is associated with an SRS through its own SRID value.

For more information, see Spatial reference.

Data column view

In GanosBase PointCloud, there is also a point cloud column view similar to the geometry column view in the geometry model.

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

SELECT * FROM pointcloud_columns;

Data compression

Point cloud data often has a considerable data volume. In GanosBase PointCloud, you can add the following declaration in the definition of the schema document to specify the data compression method:

<pc:metadata>
  <Metadata name="compression">compression_method</Metadata>
</pc:metadata>

Currently, the following compression methods are supported:

• None: the default value. The PcPoint and PcPatch byte arrays are stored according to the types and formats defined in the schema document, without compression.

• Dimensional: meaningful only for PcPatch. It changes the row-based storage in PcPatch to dimension-based column storage and enables an appropriate compression scheme.

• Run-length encoding compression: applicable to dimensions with low variability.

• Common bits removal: applicable to dimensions whose values vary only within a very narrow range.

• Deflate compression using zlib: the default scheme when other compression methods are not applicable.

• LAZ/LASZip: the standard compression method for LiDAR data.

Dimensional compression

For a PcPatch data set that has 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 enabled, the PcPatch is theoretically equivalent to:

{
  "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-volume PcPatch objects with relatively uniform data, the efficiency of Dimensional compression is between 3:1 and 5:1.

Introduction

The getting started documentation helps you quickly understand the basic usage of the GanosBase PointCloud engine, including extension creation, point cloud schema definition, table creation, data insertion, and attribute calculation.

Syntax description

• Create the extensions.

  CREATE extension ganos_pointcloud cascade;
  CREATE extension ganos_pointcloud_geometry cascade;

  Note

  (Recommended) Install the extensions in the public schema to avoid permission issues.

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

• Define a point cloud schema.

  The pointcloud_formats table is created by default. Insert an XML-format schema into the table. The schema defines the attribute dimensions of the point cloud, as well as 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.

  -- Point type: pcpoint
  CREATE type pcpoint(...);
  The dimension information of a point is defined by the schema.
  Point type data can only be stored as one point per row.
  Example: ST_MakePoint(1, ARRAY[2,3,4,0.5]) returns a pcpoint object.
  -- Patch type: pcpatch
  CREATE type pcpatch(...);
  A patch consists of a series of points and supports compression. The compression method is determined by "compression" in the schema.
  The advantage of the patch type is that a series of points can be compressed and stored as a single row, which reduces storage space while supporting spatial retrieval.

• Create point cloud tables.

  -- A table of points
  CREATE TABLE points (
      id SERIAL PRIMARY KEY,
      pt PCPOINT(1)     --(1) means selecting the record whose pcid value is 1 in pointcloud_formats as the schema
  );
  -- A table of patches
  CREATE TABLE patches (
      id SERIAL PRIMARY KEY,
      pa PCPATCH(1)
  );

• Insert pcpoint data.

  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 pcpatch data.

  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 value of pcpatch attributes.

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

• Remove the extensions (optional).

  DROP extension ganos_pointcloud_geometry;
  DROP extension ganos_pointcloud cascade;

SQL reference

For the detailed SQL manual, see Point cloud SQL reference.