PolarDB:Point cloud model

Data types

GanosBase PointCloud introduces two data types: PcPoint and PcPatch.

PcPoint is the basic unit of point cloud data. Each PcPoint holds at least an X and Y coordinate and can include additional dimensions such as Z (elevation), intensity, or RGB values. 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 or query individual points.

PcPatch is a collection of nearby PcPoint objects stored as a single database row. You can use PcPatch objects to reduce the number of rows in the database, making storage and spatial queries significantly more efficient. PcPatch supports 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. Use PcPatch for large-scale point cloud storage and bulk spatial queries.

The JSON representation of each type is:

// PcPoint: a single point
{"pcid": 1, "pt": [0.01, 0.02, 0.03, 4]}

// PcPatch: a collection of points
{"pcid": 1, "pts": [[0.02, 0.03, 0.05, 6], [0.02, 0.03, 0.05, 8]]}

Point cloud schema

LIDAR sensors and 3D scanners vary widely: some capture only X/Y/Z values, others add intensity, return number, RGB, or dozens more variables. Storage formats differ too — intensity might be a 4-byte integer in one dataset and a single byte in another. The schema document solves this heterogeneity problem by declaring exactly what dimensions each point contains and how each dimension is encoded.

GanosBase PointCloud uses the same schema format as the PDAL library. Each schema is an XML document that defines:

• The dimensions of each point (name, position, data type, scale)

• The compression method for the patch

Schemas are stored in the pointcloud_formats table alongside a unique integer identifier called PCID (point cloud ID). Every PcPoint and PcPatch object references a PCID as a foreign key to identify its schema.

Spatial reference system

A spatial reference system (SRS) maps point cloud coordinates to real-world locations on Earth. GanosBase references SRS definitions by an integer called SRID, which acts as a foreign key to the spatial_ref_sys table.

For more information, see Spatial reference.

Data column view

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

Query all point cloud columns in the current database:

SELECT * FROM pointcloud_columns;

Data compression

Point cloud datasets are large. GanosBase PointCloud supports the following compression methods, specified in the schema's <pc:metadata> block:

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

None (default): No compression. Both PcPoint and PcPatch objects are stored as raw byte arrays using the types and formats defined in the schema.

Dimensional: Applies to PcPatch only. Points are reorganized by dimension — all X values together, all Y values together, and so on — then each dimension array is compressed independently using the most appropriate sub-algorithm:

For small-size PcPatch objects that sample similar areas, dimensional compression achieves a 3:1 to 5:1 compression ratio. PcPoint objects in a dimensional-compressed schema are stored the same as the None method.

How dimensional compression works

Before compression, a PcPatch with 4 dimensions and 6 points is stored in row-major order:

{
  "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, values are reorganized into separate arrays per dimension:

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

Each dimension array is compressed independently — the last dimension (all zeros) compresses to near-zero bytes with run-length encoding.

Install the extensions

Install both extensions using the following commands:

CREATE EXTENSION ganos_pointcloud CASCADE;
CREATE EXTENSION ganos_pointcloud_geometry CASCADE;

To avoid permission issues, install the extensions in the public schema:

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

Define a point cloud schema

Insert a schema into the pointcloud_formats table. The schema below defines four dimensions — X, Y, Z (each as a 4-byte signed integer with 0.01 scale), and Intensity (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. 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. 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>');

Create point cloud tables

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

-- A table of point patches (PcPatch)
CREATE TABLE patches (
    id SERIAL PRIMARY KEY,
    pa PCPATCH(1)
);

Insert PcPoint data

All examples below use ST_MakePoint to construct PcPoint objects.

-- Insert 100 generated points
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;

-- Construct a single PcPoint and inspect it
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

Group points into patches using ST_Patch, then inspect with ST_MakePatch:

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

-- Construct a patch from a flat coordinate array and inspect it
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]]}

Query patch statistics

Calculate the average value of every attribute dimension in a PcPatch:

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

Remove the extensions

DROP EXTENSION ganos_pointcloud_geometry;
DROP EXTENSION ganos_pointcloud CASCADE;