云数据库 RDS：点云模型

概述

GanosBase PointCloud是对象关系型数据库PostgreSQL的一个扩展，使PostgreSQL能够有效快速存储和管理点云数据，并提供点云数据压缩、解压缩、属性统计等功能，同时联合GanosBase Geometry模块提供点云空间分析的能力。

点云数据类型

点云数据类型主要分为两种，一种是pcpoint数据类型，一个点一行记录存储。点的维度信息在元数据中定义。另一种是pcpatch数据类型，该类型将点以集合的方式进行存储，支持压缩，减少存储空间，支持空间检索。压缩方式由元数据中的“compression”决定。

点云元数据

表pointcloud_formats记录了点云的schema（元数据）信息。元数据包括点云的属性维度，以及每个维度的数据大小、类型、名称以及解释说明等。

快速入门

• 创建扩展

  Create extension ganos_pointcloud with schema public cascade;
  Create extension ganos_pointcloud_geometry with schema public cascade;

• 插入点云schema

  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>');

• 创建点云表

  -- 使用pcpoint数据类型
  CREATE TABLE points (
      id SERIAL PRIMARY KEY,
      pt PCPOINT(1)
  );
  
  -- 使用pcpatch数据类型
  CREATE TABLE patches (
      id SERIAL PRIMARY KEY,
      pa PCPATCH(1)
  );

• 插入pcpoint类型数据

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

• 插入pcpatch类型数据

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

• pcpatch属性平均值计算

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

• 删除扩展

  Drop extension ganos_pointcloud_geometry;
  Drop extension ganos_pointcloud cascade;

SQL参考

详细SQL手册请参见 PointCloud SQL参考。