PolarDB:GanosBase PointCloud

What is a point cloud

A point cloud is a set of 3D points captured when a 3D scanner scans an object. Each point has at minimum an x, y, and z coordinate; many datasets also carry additional attributes such as RGB color values or intensity.

3D scanners vary widely in the variables they capture and how those variables are stored — intensity might be a 4-byte integer in one dataset and a single byte in another; x/y/z values might be doubles or scaled integers. GanosBase PointCloud handles this variability through a schema document that explicitly describes the dimensions of each point and the data type of each dimension. Without a schema, the database has no way to interpret the raw bytes stored for each point.

Data types

GanosBase PointCloud introduces two data types:

• PcPoint — a single point cloud object. Each PcPoint contains at least x and y coordinates and can carry additional dimensions. PcPoint supports import and export of point cloud data, query of point cloud schema, and spatial query of point cloud data. Use PcPoint when you need to process individual points.

• PcPatch — a collection of nearby PcPoint objects compressed into a single database row. PcPatch supports packaging of point cloud data, compression and decompression of data packets, schema query of data packets, and filtering of data packets. Use PcPatch for efficient storage and querying of large point cloud datasets.

Schema document and the pointcloud_formats table

A schema document describes the structure of a point cloud: how many dimensions each point has, the name and data type of each dimension, and the compression method to apply. Schema documents use the same XML format as the PDAL library.

All schema documents are stored in the pointcloud_formats table alongside a PCID (point cloud identifier). Rather than embedding the full schema with every stored object, each PcPoint or PcPatch carries only the PCID — a foreign key that references the corresponding schema in pointcloud_formats. This is the same pattern that PostGIS uses for spatial reference systems: the SRID references an SRS definition stored once in a separate table, rather than being duplicated in every geometry object.

Spatial reference system

A spatial reference system (SRS) defines how a point cloud object maps to a real-world location on Earth. GanosBase identifies each SRS with an integer SRID. Every point cloud object stores an SRID that references a definition in the spatial_ref_sys table.

For details, see Spatial reference.

Point cloud column view

The pointcloud_columns view lists all point cloud columns in the current database, similar to the geometry column view in PostGIS.

Query all point cloud columns in the current database:

SELECT * FROM pointcloud_columns;

Compression

PcPatch objects can be compressed. Specify the compression method in the schema document's <pc:metadata> block:

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

The following compression methods are supported:

How dimensional compression works

Dimensional compression reorganizes a PcPatch from point-major order into dimension-major order, then applies the most effective sub-algorithm to each dimension independently. Consider 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, the data is 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 patches of points sampled from similar areas, dimensional compression achieves a compression ratio of 3:1 to 5:1.

Step 1: Create the extensions

Install in the public schema to avoid permission issues:

CREATE EXTENSION ganos_pointcloud CASCADE;
CREATE EXTENSION ganos_pointcloud_geometry CASCADE;

Alternatively, specify the schema explicitly:

CREATE EXTENSION ganos_pointcloud WITH SCHEMA public;
CREATE EXTENSION ganos_pointcloud_geometry WITH SCHEMA public;

Step 2: Define a point cloud schema

Insert an XML schema into pointcloud_formats. The schema below defines four dimensions — X, Y, Z (each a 4-byte scaled integer), and Intensity (a 2-byte unsigned integer) — with dimensional compression enabled:

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. Use the scale and offset
                    from the header to get 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. Use the scale and offset
                    from the header to get 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. Use the scale and offset
                    from the header to get 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>Pulse return magnitude. Include whenever 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>');

PCID=1 and SRID=4326 (WGS 84) are used in all subsequent examples.

Step 3: Create point and patch tables

-- Table of individual points; PCPOINT(1) references the schema with PCID=1
CREATE TABLE points (
    id SERIAL PRIMARY KEY,
    pt PCPOINT(1)
);

-- Table of patches (compressed collections of points)
CREATE TABLE patches (
    id SERIAL PRIMARY KEY,
    pa PCPATCH(1)
);

Step 4: Insert PcPoint data

Use ST_MakePoint(pcid, array) to create PcPoint objects:

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;

Verify a single point:

SELECT ST_MakePoint(1, ARRAY[-127, 45, 124.0, 4.0]);
-- 010100000064CEFFFF94110000703000000400

SELECT ST_AsText('010100000064CEFFFF94110000703000000400'::pcpoint);
-- {"pcid":1,"pt":[-127,45,124,4]}

Step 5: Insert PcPatch data

Group points into patches using ST_Patch. Each group becomes one compressed row:

INSERT INTO patches (pa)
SELECT ST_Patch(pt) FROM points GROUP BY id/10;

Verify a patch:

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]]}

Step 6: Run attribute statistics

Calculate the average value of each dimension across all points in a patch:

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

Step 7: Drop the extensions (optional)

DROP EXTENSION ganos_pointcloud_geometry;
DROP EXTENSION ganos_pointcloud CASCADE;