Overview

Point cloud data consists of the points generated by a 3D scanner when it scans an object. Each point is represented by a set of 3D coordinates, and some may contain RGB or intensity information. Point cloud data contains spatial coordinate information, and has a large number of points and complex attribute dimensions.

Ganos PointCloud is an extension of PostgreSQL. It enables PostgreSQL to store and manage point cloud data efficiently and fast. Ganos PointCloud provides features such as point cloud data compression, decompression, and attribute statistics collection. It can also work with Ganos Geometry to support a spatial analysis of point cloud data.

Point cloud data types

Point cloud data types include pcpoint and pcpatch. For the pcpoint type, each point is stored as a row. The dimensions of a point are defined in the point cloud schema. For the pcpatch type, points are stored as a collection. Point cloud data of the pcpatch type can be compressed to reduce storage space and supports spatial queries. The compression method is specified by the compression parameter in the point cloud schema.

Point cloud schema

The pointcloud_formats table stores point cloud schemas. Each schema lists the attribute dimensions of a point cloud object and provides the information about each dimension, such as the size, type, name, and description.

Quick start

  • Create extensions.
    Create extension ganos_pointcloud cascade;
    Create extension ganos_pointcloud_geometry cascade;
  • Insert a point cloud schema.
    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>');
  • Create a point cloud table.
    -- Use the pcpoint data type.
    CREATE TABLE points (
        id SERIAL PRIMARY KEY,
        pt PCPOINT(1)
    );
    
    -- Use the pcpatch data type.
    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]
    ]}
  • Compute the average values of all attribute dimensions in a pcpatch object.
    SELECT ST_AsText(ST_PatchAvg(pa)) FROM patches WHERE id = 7;
    -------------------------
    {"pcid":1,"pt":[-126.46,45.54,54.5,5]}
  • Delete extensions.
    Drop extension ganos_pointcloud_geometry;
    Drop extension ganos_pointcloud cascade;

SQL reference

For more information, see Point cloud SQL reference.