点云数据通常是由3D扫描仪扫描资料并以点的形式输出的记录,每一个点包含有三维坐标,有些可能含有颜色信息(RGB)或反射强度信息(Intensity),点云数据含有空间坐标信息,且具有数量众多、属性维度复杂的特点。
概述
Ganos PointCloud是对象关系型数据库PostgreSQL的一个扩展,使PostgreSQL能够有效快速存储和管理点云数据,并提供点云数据压缩、解压缩、属性统计等功能,同时联合Ganos Geometry模块提供点云空间分析的能力。
点云数据类型
点云数据类型主要分为两种,一种是pcpoint数据类型,一个点一行记录存储。点的维度信息在元数据中定义。另一种是pcpatch数据类型,该类型将点以集合的方式进行存储,支持压缩,减少存储空间,支持空间检索。压缩方式由元数据中的“compression”决定。
点云元数据
表pointcloud_formats记录了点云的schema(元数据)信息。元数据包括点云的属性维度,以及每个维度的数据大小、类型、名称以及解释说明等。
快速入门
- 创建扩展
12
Create extension ganos_pointcloud cascade;
Create extension ganos_pointcloud_geometry cascade;
- 插入点云schema
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849
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"
<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>');
- 创建点云表
1234567891011
-- 使用pcpoint数据类型
CREATE TABLE points (
id SERIAL PRIMARY KEY,
pt PCPOINT(
1
)
);
-- 使用pcpatch数据类型
CREATE TABLE patches (
id SERIAL PRIMARY KEY,
pa PCPATCH(
1
)
);
- 插入pcpoint类型数据
123456789101112131415161718
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类型数据
12345678
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属性平均值计算
123
SELECT ST_AsText(ST_PatchAvg(pa)) FROM patches WHERE id =
7
;
-------------------------
{
"pcid"
:
1
,
"pt"
:[-
126.46
,
45.54
,
54.5
,
5
]}
- 删除扩展
12
Drop extension ganos_pointcloud_geometry;
Drop extension ganos_pointcloud cascade;
SQL参考
详细SQL手册请参见 PointCloud SQL参考。