pointcloud

pig repo add pgdg -u          # 添加 PGDG 仓库并更新缓存

CREATE EXTENSION pointcloud;

CREATE EXTENSION pointcloud;
-- 用于 PostGIS 集成：
CREATE EXTENSION pointcloud_postgis;

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 坐标，以长整数表示。需要使用头部的
                    缩放和偏移信息来确定双精度值。</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 坐标，以长整数表示。需要使用头部的
                    缩放和偏移信息来确定双精度值。</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 坐标，以长整数表示。需要使用头部的
                    缩放和偏移信息来确定双精度值。</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>强度值是脉冲回波幅度的整数表示。
                    该值是可选的且与系统相关。但如果可用，
                    应始终包含在内。</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>');

{
    "pcid" : 1,
      "pt" : [0.01, 0.02, 0.03, 4]
}

{
    "pcid" : 1,
     "pts" : [
              [0.02, 0.03, 0.05, 6],
              [0.02, 0.03, 0.05, 8]
             ]
}

-- 需要上面的 schema 条目，使 pcid==1 存在。

-- 点表
CREATE TABLE points (
    id SERIAL PRIMARY KEY,
    pt PCPOINT(1)
);

-- Patch 表
CREATE TABLE patches (
    id SERIAL PRIMARY KEY,
    pa PCPATCH(1)
);

SELECT * FROM pointcloud_columns;

 schema |    table    | column | pcid | srid |  type
--------+-------------+--------+------+------+---------
 public | points      | pt     |    1 | 4326 | pcpoint
 public | patches     | pa     |    1 | 4326 | pcpatch

<pc:metadata>
  <Metadata name="compression">dimensional</Metadata>
</pc:metadata>

SELECT PC_SchemaGetNDims(1);
-- 18

SELECT PC_SchemaIsValid(schema) FROM pointcloud_formats LIMIT 1;
-- t

SELECT PC_MakePoint(1, ARRAY[-127, 45, 124.0, 4.0]);

-- 010100000064CEFFFF94110000703000000400

INSERT INTO points (pt)
SELECT PC_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 PC_AsText('010100000064CEFFFF94110000703000000400'::pcpoint);
-- {"pcid":1,"pt":[-127,45,124,4]}

SELECT PC_Get('010100000064CEFFFF94110000703000000400'::pcpoint);
-- {-127,45,124,4}

SELECT PC_Get('010100000064CEFFFF94110000703000000400'::pcpoint, 'Intensity');
-- 4

SELECT PC_MemSize(PC_MakePoint(1, ARRAY[-127, 45, 124.0, 4.0]));
-- 25

SELECT PC_PCId('010100000064CEFFFF94110000703000000400'::pcpoint);
-- 1

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

SELECT PC_AsText(PC_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]
-- ]}

SELECT PC_AsText(pa) FROM patches LIMIT 1;

-- {"pcid":1,"pts":[
--  [-126.99,45.01,1,0],[-126.98,45.02,2,0],[-126.97,45.03,3,0],
--  [-126.96,45.04,4,0],[-126.95,45.05,5,0],[-126.94,45.06,6,0],
--  [-126.93,45.07,7,0],[-126.92,45.08,8,0],[-126.91,45.09,9,0]
-- ]}

SELECT PC_Summary(pa) FROM patches LIMIT 1;

-- {"pcid":1, "npts":9, "srid":4326, "compr":"dimensional",
--  "dims":[{"pos":0,"name":"X","size":4,"type":"int32_t","compr":"sigbits",
--           "stats":{"min":-126.99,"max":-126.91,"avg":-126.95}}, ...]}

SELECT PC_NumPoints(pa) FROM patches LIMIT 1;
-- 9

SELECT PC_AsText(PC_Explode(pa)), id
FROM patches WHERE id = 7;

              pc_astext               | id
--------------------------------------+----
 {"pcid":1,"pt":[-126.5,45.5,50,5]}   |  7
 {"pcid":1,"pt":[-126.49,45.51,51,5]} |  7
 {"pcid":1,"pt":[-126.48,45.52,52,5]} |  7
 ...

SELECT PC_NumPoints(PC_Union(pa)) FROM patches;
-- 100

SELECT PC_PatchAvg(pa, 'intensity') FROM patches WHERE id = 7;
-- 5.0000000000000000

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

SELECT PC_AsText(PC_FilterGreaterThan(pa, 'y', 45.57))
FROM patches WHERE id = 7;

-- {"pcid":1,"pts":[[-126.42,45.58,58,5],[-126.41,45.59,59,5]]}

CREATE EXTENSION postgis;
CREATE EXTENSION pointcloud;
CREATE EXTENSION pointcloud_postgis;

SELECT ST_AsText(PC_MakePoint(1, ARRAY[-127, 45, 124.0, 4.0])::geometry);
-- POINT Z (-127 45 124)

SELECT ST_AsText(PC_EnvelopeGeometry(pa)) FROM patches LIMIT 1;
-- POLYGON((-126.99 45.01,-126.99 45.09,-126.91 45.09,-126.91 45.01,-126.99 45.01))

CREATE INDEX ON patches USING GIST(PC_EnvelopeGeometry(patch));

SELECT ST_AsText(PC_BoundingDiagonalGeometry(pa)) FROM patches;
-- LINESTRING Z (-126.99 45.01 1,-126.91 45.09 9)

CREATE INDEX ON patches USING GIST(PC_BoundingDiagonalGeometry(patch) gist_geometry_ops_nd);

SELECT PC_AsText(PC_Explode(PC_Intersection(
      pa,
      'SRID=4326;POLYGON((-126.451 45.552, -126.42 47.55, -126.40 45.552, -126.451 45.552))'::geometry
)))
FROM patches WHERE id = 7;

             pc_astext
--------------------------------------
 {"pcid":1,"pt":[-126.44,45.56,56,5]}
 {"pcid":1,"pt":[-126.43,45.57,57,5]}
 {"pcid":1,"pt":[-126.42,45.58,58,5]}
 {"pcid":1,"pt":[-126.41,45.59,59,5]}

SELECT PC_Intersects('SRID=4326;POINT(-126.451 45.552)'::geometry, pa)
FROM patches WHERE id = 7;
-- t

SELECT PC_Version();           -- 1.2.5
SELECT PC_Lib_Version();       -- 1.2.5 2346cc2
SELECT PC_Script_Version();    -- 1.2.5
SELECT PC_Full_Version();
-- POINTCLOUD="1.2.5 2346cc2" PGSQL="170" LIBXML2="2.14.3 LAZPERF enabled=false

{
  "pipeline":[
    {
      "type":"readers.las",
      "filename":"/home/lidar/st-helens-small.las",
      "spatialreference":"EPSG:26910"
    },
    {
      "type":"filters.chipper",
      "capacity":400
    },
    {
      "type":"writers.pgpointcloud",
      "connection":"host='localhost' dbname='pc' user='lidar' password='lidar' port='5432'",
      "table":"sthsm",
      "compression":"dimensional",
      "srid":"26910"
    }
  ]
}

pdal pipeline --input pipelinefile.json

{
  "pipeline":[
    {
      "type":"readers.pgpointcloud",
      "connection":"host='localhost' dbname='pc' user='lidar' password='lidar' port='5432'",
      "table":"sthsm",
      "column":"pa",
      "spatialreference":"EPSG:26910",
      "where":"PC_Intersects(pa, ST_MakeEnvelope(560037.36, 5114846.45, 562667.31, 5118943.24, 26910))"
    },
    {
      "type":"writers.text",
      "filename":"/home/lidar/st-helens-small-out.txt"
    }
  ]
}