点云数据通常是由3D扫描仪扫描资料并以点的形式输出的记录,每一个点包含有三维坐标,有些可能含有颜色信息(RGB)或反射强度信息(Intensity),点云数据含有空间坐标信息,且具有数量众多、属性维度复杂的特点。
概述
Ganos PointCloud是对象关系型数据库PostgreSQL的一个扩展,使PostgreSQL能够有效快速存储和管理点云数据,并提供点云数据压缩、解压缩、属性统计等功能,同时联合Ganos 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参考。
文档内容是否对您有帮助?