ApsaraDB RDS:Point cloud model

Data types

GanosBase PointCloud provides two data types designed to work together:

Use pcpoint when you need to work with individual points. Use pcpatch for production workloads: the reduced row count and built-in compression make large LIDAR datasets practical to store and query.

Point cloud schemas

LIDAR sensors vary widely: one sensor may capture only X, Y, Z coordinates; another may add intensity, return number, and RGB values; a third may encode the same values in different numeric types and byte sizes. A point cloud schema defines exactly what data each point carries and how it is encoded, so the database can interpret the binary data correctly.

All schemas are stored in the pointcloud_formats table. Each schema entry records the following for every dimension:

The schema also specifies a compression method for pcpatch data. The compression parameter in the schema specifies the method that is used to compress point cloud data.

Step 1: Enable the extensions

Run the following commands to load GanosBase PointCloud and its geometry integration:

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

Step 2: Insert a point cloud schema

A schema defines the dimensions for your points. The following example defines a schema with X, Y, Z coordinates and an intensity dimension, using dimensional compression:

INSERT INTO pointcloud_formats (pcid, srid, schema) VALUES (1, 4326,
'<?xml version="1.0" encoding="UTF-8"?>
<pc:PointCloudSchema xmlns:pc="http://example.org/schemas/PC/1.1"
    xmlns:xsi="http://www.example.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>');

The pcid value (1) links this schema to the tables you create in the next step. The srid (4326) specifies the spatial reference system (WGS 84).

Step 3: Create point cloud tables

Create a table for individual points (pcpoint) and a table for point patches (pcpatch). Both reference schema ID 1 defined above:

-- Table for individual points
CREATE TABLE points (
    id SERIAL PRIMARY KEY,
    pt PCPOINT(1)
);

-- Table for point patches (recommended for production workloads)
CREATE TABLE patches (
    id SERIAL PRIMARY KEY,
    pa PCPATCH(1)
);

Step 4: Insert pcpoint data

The following example generates 100 synthetic points using ST_MakePoint and inserts them into the points table:

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;

To create a single point and view its text representation:

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

SELECT ST_AsText('010100000064CEFFFF94110000703000000400'::pcpoint);
-- Output: null

You can also create a patch directly from coordinate arrays using ST_MakePatch:

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]));
-- Output:
-- {"pcid":1,"pts":[
--  [-126.99,45.01,1,0],[-126.98,45.02,2,0],[-126.97,45.03,3,0]
-- ]}

Step 5: Aggregate points into patches

Group the individual points in the points table into patches and insert them into the patches table. ST_Patch aggregates nearby pcpoint values into a single pcpatch:

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

Step 6: Query patch statistics

Use ST_PatchAvg to compute the average value of every attribute dimension across all points in a patch:

SELECT ST_AsText(ST_PatchAvg(pa)) FROM patches WHERE id = 7;
-- Output: null

Step 7: Disable the extensions

To remove GanosBase PointCloud from your database, drop the extensions in reverse dependency order:

DROP EXTENSION ganos_pointcloud_geometry;
DROP EXTENSION ganos_pointcloud CASCADE;