PolarDB:Point cloud model

Introduction

A point cloud is a collection of records output by 3D laser scanners that scan real-world entities. Each point contains 3D coordinates, and some may also include color (RGB) or intensity information. Point cloud data carries spatial coordinates and is characterized by large volume and complex attribute dimensions.

GanosBase PointCloud is a spatio-temporal engine extension for PostgreSQL-compatible (PolarDB for PostgreSQL. It enables efficient storage and management of point cloud data with compression, decompression, and attribute statistics, and works with otherGanosBase modules to provide spatial analysis capabilities for point cloud data.

Features

GanosBase PointCloud provides two data types: PcPoint and PcPatch. PcPoint is the basic point cloud object that supports data import and export, metadata query, and spatial query. PcPatch is a collection of point cloud objects that supports data packaging, compression and decompression, metadata query, and filtering.

Use cases

GanosBase PointCloud is commonly used in the following scenarios:

• 3D modeling and visualization

  GanosBase PointCloud can store and process 3D scan data for modeling and visualization, which is useful for architectural design, urban planning, and cultural relic protection.

• Robotics and autonomous driving

  GanosBase PointCloud can process 3D perception data in robotics and autonomous driving systems to support environment sensing, obstacle detection, and path planning.

• Industrial measurement and quality control

  In the industrial sector, GanosBase PointCloud can process 3D measurement data such as product surface profiles from laser scanners. This data can be used for quality control, product design, and manufacturing process optimization.

Point cloud schema

Raw point cloud data may have multiple dimensions, each with different precision.GanosBase Pointcloud uses the same Schema Document format as PDAL (hereafter referred to as the schema document) to describe the dimensions of each point and the data type of each dimension.

The schema document is stored in the pointcloud_formats table alongside a unique PCID.

Point cloud objects

You can store point cloud data in point cloud tables using the PcPoint or PcPatch type.

• PcPoint is the basic point cloud object. It has at least X and Y coordinate dimensions and may include additional dimensions.

  The JSON representation of a PcPoint object is:

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

  Where:

  • PCID: a foreign key to the pointcloud_formats table.

  • pt: the point cloud data that follows the format described in the schema document referenced by the PCID.

• PcPatch is a collection of nearby PcPoint objects. This reduces the number of rows in the database.

  The JSON representation of a PcPatch object is:

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

Choose the type based on your use case:

• Use PcPoint if you always process individual points.

• Use PcPatch for efficient queries and operations on large-scale point cloud data.

Spatial reference

A spatial reference system (SRS) defines how PointCloud objects are mapped to specific locations on the Earth's surface.

GanosBase uses an integer called SRID to reference an SRS definition. PointCloud objects are associated with an SRS through their SRID value.

For more information, see Spatial reference systems.

Data column view

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

Query all point cloud columns in the current database:

SELECT * FROM pointcloud_columns;

Data compression

Point cloud datasets are typically very large. In GanosBase PointCloud, you can specify a compression method in the schema document:

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

The following compression methods are supported:

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

• Dimensional: applicable to PcPatch only. Converts row-based storage to column-based storage by dimension and applies appropriate compression schemes.

• Run-length encoding: suitable for dimensions with low variability.

• Common bits removal: suitable for dimensions where values vary within a narrow range.

• Deflate (zlib): the default method when other methods are not applicable.

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

Dimensional compression

For a PcPatch with 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-size PcPatch objects with relatively uniform data, dimensional compression achieves a ratio of 3:1 to 5:1.

Introduction

The quick start guide helps you quickly understandGanosBase PointCloud usage, including extension creation, schema definition, table creation, data insertion, and attribute calculation.

Syntax reference

• Create the extensions.

  CREATE extension ganos_pointcloud cascade;
  CREATE extension ganos_pointcloud_geometry cascade;

  Note

  We recommend that you 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 schema into the table. The schema defines the attribute dimensions of point cloud data, including 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 dimensions of each point are defined by the schema.
  Each point is stored as one row.
  Example: ST_MakePoint(1, ARRAY[2,3,4,0.5]) returns a PcPoint object.
  -- Patch type: pcpatch
  CREATE type pcpatch(...);
  A patch is a collection of points that supports compression. The compression method is determined by the “compression” setting in the schema.
  Patches can compress multiple points into a single row, reducing storage space while supporting spatial queries.

• Create point cloud tables.

  -- A table of points
  CREATE TABLE points (
      id SERIAL PRIMARY KEY,
      pt PCPOINT(1)     -- (1) references the schema with PCID = 1 in pointcloud_formats
  );
  -- 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 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 complete SQL reference, see Point cloud SQL reference.