PGPOINTCLOUD(1)

📦
version 1.2.5 · 2 versions
, in nixos 26.05

💖 NAME 💖

okay bestie, welcome to the pgpointcloud(1) manual page — this is pgPointcloud 1.2.5 and it’s literally the whole mood. ✨

LIDAR (http://en.wikipedia.org/wiki/LIDAR) point cloud (http://en.wikipedia.org/wiki/Point_cloud) data is becoming more and more available. Devices are easy to get, not too expensive, and provide very accurate 3D points. pgPointCLoud is an open source PostgreSQL (https://www.postgresql.org/) extension for storing point cloud data and use it with PostGIS (https://postgis.net/). It is very easy to use, robust and efficient. 💅

By storing LIDAR points in a PostgreSQL database, pgPointcloud eases many problems and allows a good integration with other geo-spatial data (vector, raster) into one common framework : PostGIS. 🫶

📰 NEWS 📰

19-09-2023 📅

pgPointcloud 1.2.5 has been released. 🎉

🧠 CONCEPTS 🧠

Concepts ✨

LIDAR sensors quickly produce millions of points with large numbers of variables measured on each point. The challenge for a point cloud database extension is efficiently storing this data while allowing high fidelity access to the many variables stored. That’s the assignment, and it’s giving database engineering. 💀

Schemas 🗂️

Much of the complexity in handling LIDAR comes from the need to deal with multiple variables per point. The variables captured by LIDAR sensors varies by sensor and capture process. Some data sets might contain only X/Y/Z values. Others will contain dozens of variables: X, Y, Z; intensity and return number; red, green, and blue values; return times; and many more. There is no consistency in how variables are stored: intensity might be stored in a 4-byte integer, or in a single byte; X/Y/Z might be doubles, or they might be scaled 4-byte integers. 😵‍💫

PostgreSQL Pointcloud deals with all this variability by using a "schema document" to describe the contents of any particular LIDAR point. Each point contains a number of dimensions, and each dimension can be of any data type, with scaling and/or offsets applied to move between the actual value and the value stored in the database. The schema document format used by PostgreSQL Pointcloud is the same one used by the PDAL (https://pdal.io/) library. 👑

Here is a simple 4-dimensional schema document you can insert into pointcloud_formats to work with the examples below:

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

Schema documents are stored in the pointcloud_formats table, along with a pcid or "pointcloud identifier". Rather than store the whole schema information with each database object, each object just has a pcid, which serves as a key to find the schema in pointcloud_formats. This is similar to the way the srid is resolved for spatial reference system support in PostGIS (http://postgis.net/). 🗝️

The central role of the schema document in interpreting the contents of a point cloud object means that care must be taken to ensure that the right pcid reference is being used in objects, and that it references a valid schema document in the pointcloud_formats table. ⚠️

Point Cloud Objects ☁️

PcPoint 📍

The basic point cloud type is a PcPoint. Every point has a (large?) number of dimensions, but at a minimum an X and Y coordinate that place it in space. 🌍

Points can be rendered in a human-readable JSON form using the PC_AsText(pcpoint) function. The "pcid" is the foreign key reference to the pointcloud_formats table, where the meaning of each dimension in the "pt" array of doubles is explained. The underlying storage of the data might not be double, but by the time it has been extracted, scaled and offset, it is representable as doubles. ✨

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

PcPatch 🧩

The structure of database storage is such that storing billions of points as individual records in a table is not an efficient use of resources. Instead, we collect a group of PcPoint into a PcPatch. Each patch should hopefully contain points that are near together. 🫂

Instead of a table of billions of single PcPoint records, a collection of LIDAR data can be represented in the database as a much smaller collection (10s of millions) of PcPatch records. 🔥

Patches can be rendered into a human-readable JSON form using the PC_AsText(pcpatch) function. The "pcid" is the foreign key reference to the pointcloud_formats table. 💖

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

Tables 🪑

Usually you will only be creating tables for storing PcPatch objects, and using PcPoint objects as transitional objects for filtering, but it is possible to create tables of both types. PcPatch and PcPoint columns both require an argument that indicate the pcid that will be used to interpret the column. 🛠️

-- This example requires the schema entry from the previous
-- section to be loaded so that pcid==1 exists.

-- A table of points
CREATE TABLE points (
    id SERIAL PRIMARY KEY,
    pt PCPOINT(1)
);

-- A table of patches
CREATE TABLE patches (
    id SERIAL PRIMARY KEY,
    pa PCPATCH(1)
);

In addition to any tables you create, you will find two other system-provided point cloud tables:

  • the pointcloud_formats table that holds all the pcid entries and schema documents
  • the pointcloud_columns view, that displays all the columns in your database that contain point cloud objects

Now that you have created two tables, you'll see entries for them in the pointcloud_columns view:

SELECT * FROM pointcloud_columns;

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

Compressions 🗜️

One of the issues with LIDAR data is that there is a lot of it. To deal with data volumes, PostgreSQL Pointcloud allows schemas to declare their preferred compression method in the <pc:metadata> block of the schema document. In the example schema, we declared our compression as follows: 😌

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

There are currently three supported compressions:

  • None, which stores points and patches as byte arrays using the type and formats described in the schema document.
  • Dimensional, which stores points the same as 'none' but stores patches as collections of dimensional data arrays, with an "appropriate" compression applied. Dimensional compression makes the most sense for smaller patch sizes, since small patches will tend to have more homogeneous dimensions.
  • LAZ or "LASZip". You must build Pointcloud with laz-perf support to make use of the LAZ compression. If no compression is declared in <pc:metadata>, then a compression of "none" is assumed.

Dimensional Compression 📐

Dimensional compression first flips the patch representation from a list of N points containing M dimension values to a list of M dimensions each containing N values. 🔄

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

Becomes, notionally:

{"pcid":1,"dims":[
      [-126.99,-126.98,-126.97,-126.96,-126.95,-126.94],
      [45.01,45.02,45.03,45.04,45.05,45.06],
      [1,2,3,4,5,6],
      [0,0,0,0,0,0]
     ]}

The potential benefit for compression is that each dimension has quite different distribution characteristics, and is amenable to different approaches. In this example, the fourth dimension (intensity) can be very highly compressed with run-length encoding (one run of six zeros). The first and second dimensions have relatively low variability relative to their magnitude and can be compressed by removing the repeated bits. 🧠

Dimensional compression currently uses only three compression schemes:

  • run-length encoding, for dimensions with low variability
  • common bits removal, for dimensions with variability in a narrow bit range
  • raw deflate compression using zlib, for dimensions that aren't amenable to the other schemes

For LIDAR data organized into patches of points that sample similar areas, the dimensional scheme compresses at between 3:1 and 5:1 efficiency. 🎯

Binary Formats 💾

In order to preserve some compactness in dump files and network transmissions, the binary formats need to retain their native compression. All binary formats are hex-encoded before output. 🔥

So like, both the point format and the patch format kick things off with a shared common header, and that header hands you these two things:

  • an endianness flag — this is the whole portability slay between architectures ✨
  • a pcid number — you use it to look up the schema info in the pointcloud_formats table

Then the patch binary formats get extra standard header bits on top:

  • the compression number, which tells you how to read the data
  • how many points are in the patch

💾 Point Binary

byte:     endianness (1 = NDR, 0 = XDR)
uint32:   pcid (key to POINTCLOUD_SCHEMAS)
uchar[]:  pointdata (interpret relative to pcid)

🧩 Patch Binary

Uncompressed

byte:         endianness (1 = NDR, 0 = XDR)
uint32:       pcid (key to POINTCLOUD_SCHEMAS)
uint32:       0 = no compression
uint32:       npoints
pointdata[]:  interpret relative to pcid

Dimensional

byte:          endianness (1 = NDR, 0 = XDR)
uint32:        pcid (key to POINTCLOUD_SCHEMAS)
uint32:        2 = dimensional compression
uint32:        npoints
dimensions[]:  dimensionally compressed data for each dimension

Every single compressed dimension starts with a byte that tells you the compression type, and then a uint32 that tells you the size of the segment in bytes. 💅

byte:           dimensional compression type (0-3)
uint32:         size of the compressed dimension in bytes
data[]:         the compressed dimensional values

There are literally four possible compression types in dimensional compression:

  • no compression = 0,
  • run-length compression = 1,
  • significant bits removal = 2,
  • deflate = 3

No dimension compress

For dimensional compression 0 (the no-compression one), the values just show up in order, no drama. You gotta get the length of words in this dimension from the schema document, bestie — it's not written here.

word[]:

Run-length compress dimension

For run-length compression the data stream is a set of pairs, okay? A byte value that says the length of the run, and a data value that says what value is getting repeated. 🫶

byte:          number of times the word repeats
word:          value of the word being repeated
....           repeated for the number of runs

And again: the length of words in this dimension has to come from the schema document.

Significant bits removal on dimension

Significant bits removal opens with two words. The first word is just the number of bits that are "significant" — meaning how many bits are left once the common bits are removed from any given word. The second word is a bitmask of the common bits, with the final, variable bits zeroed out. 🎀

word1:          number of variable bits in this dimension
word2:          the bits that are shared by every word in this dimension
data[]:         variable bits packed into a data buffer

Deflate dimension

When the simple compression schemes just aren't giving, general purpose compression gets applied to the dimension using zlib. The data area is a raw zlib buffer, suitable for passing directly to the inflate() function — no cap. The size of the input buffer is given in the common dimension header. The size of the output buffer can be derived from the patch metadata by multiplying the dimension word size by the number of points in the patch. 🧮

🗜️ LAZ

byte:          endianness (1 = NDR, 0 = XDR)
uint32:        pcid (key to POINTCLOUD_SCHEMAS)
uint32:        2 = LAZ compression
uint32:        npoints
uint32:        LAZ data size
data[]:        LAZ data

Use the laz-perf library (https://github.com/hobu/laz-perf) to read the LAZ data buffer out into a LAZ buffer. That's the whole vibe. 💜

📥 DOWNLOAD

Download

Contents

  • Download
    • Current Release(s)
    • Past Releases
    • Development Source
    • Docker Hub
    • Binaries

Current Release(s)

Past Releases

Development Source

The source code repo for pgPointcloud lives on GitHub — https://github.com/pgpointcloud/pointcloud/. You can grab the development version with git, like so:

$ git clone https://github.com/pgpointcloud/pointcloud/

Docker Hub

There's an official Docker image chilling on Docker Hub — https://hub.docker.com/r/pgpointcloud/pointcloud. To retrieve the development version:

$ docker pull pgpointcloud/pointcloud

Binaries

Windows

pgPointcloud is directly included in the PostGIS bundle (https://postgis.net/windows_downloads/). iconic tbh.

Debian

Debian packages are now available (https://tracker.debian.org/pkg/pgpointcloud) on:

  • Ubuntu 20.04
  • Ubuntu 22.04
  • Debian Testing
  • Debian Unstable
  • Raspbian Testing

AUR

pgPointcloud is available on Arch Linux through the user repository (https://aur.archlinux.org/packages/pgpointcloud). 🌈

🌈 GETTING STARTED 🌈

💖 Getting Started

🌸 Introduction

ok so once pgPointcloud is installed, the very first step is literally just loading a point cloud into PostgreSQL so you can start playing with PcPatch and PcPoint. that's it, that's the vibe check 🫶

to do that you can write your own loader using the uncompressed WKB format, or — way easier — you can load existing LIDAR files using the PDAL processing and format conversion library: https://pdal.io/

🧚 Install PDAL

to install PDAL, check out the PDAL development documentation: https://pdal.io/development/

with PDAL installed you're ready to run a PDAL import straight into PostgreSQL PointCloud thanks to the dedicated pgPointcloud writer: https://pdal.io/stages/writers.pgpointcloud.html#writers-pgpointcloud

✨ Running a pipeline

PDAL includes a command line program (https://pdal.io/apps/index.html) that allows both simple format translations and more complex "pipelines" of transformation. the pdal translate does simple format transformations. in order to load data into Pointcloud we use a "PDAL pipeline", by calling pdal pipeline. a pipeline combines a format reader, and format writer, with filters that can alter or group the points together 💅

PDAL pipelines are JSON files, which declare readers, filters, and writers forming a processing chain that will be applied to the LIDAR data.

to execute a pipeline file, run it through the pdal pipeline command:

$ pdal pipeline --input pipelinefile.json

here's a simple example pipeline that reads a LAS file and writes into a PostgreSQL Pointcloud database 👑

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

PostgreSQL Pointcloud storage of LIDAR works best when each "patch" of points consists of points that are close together, and when most patches do not overlap. in order to convert unordered data from a LIDAR file into patch-organized data in the database, we need to pass it through a filter to "chip" the data into compact patches. the "chipper" is one of the filters we need to apply to the data while loading 🎀

similarly, reading data from a PostgreSQL Pointcloud uses a Pointcloud reader and a file writer of some sort. this example reads from the database and writes to a CSV text file:

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

note that we do not need to chip the data stream when reading from the database, as the writer does not care if the points are blocked into patches or not 🦋

you can use the "where" option to restrict a read to just an envelope, allowing partial extracts from a table:

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

🦋 pgpointcloud reader/writer

the PDAL writers.pgpointcloud (https://pdal.io/stages/writers.pgpointcloud.html) for PostgreSQL Pointcloud takes the following options:

  • connection — the PostgreSQL database connection string. e.g. host=localhost user=username password=pw db=dbname port=5432
  • table — the database table create to write the patches to.
  • schema — the schema to create the table in. [Optional]
  • column — the column name to use in the patch table. [Optional: "pa"]
  • compression — the patch compression format to use [Optional: "dimensional"]
  • overwrite — replace any existing table [Optional: true]
  • srid — the spatial reference id to store data in [Optional: 4326]
  • pcid — an existing PCID to use for the point cloud schema [Optional]
  • pre_sql — before the pipeline runs, read and execute this SQL file or command [Optional]
  • post_sql — after the pipeline runs, read and execute this SQL file or command [Optional]

the PDAL readers.pgpointcloud (https://pdal.io/stages/readers.pgpointcloud.html) for PostgreSQL Pointcloud takes the following options:

  • connection — the PostgreSQL database connection string. e.g. host=localhost user=username password=pw db=dbname port=5432
  • table — the database table to read the patches from.
  • schema — the schema to read the table from. [Optional]
  • column — the column name in the patch table to read from. [Optional: "pa"]
  • where — SQL where clause to constrain the query [Optional]
  • spatialreference — overrides the database declared SRID [Optional]

💜 FUNCTIONS 💜

🌟 Functions

⭐ PcPoint

🌸 PC_MakePoint

PC_MakePoint(pcid integer, vals float8[]) returns pcpoint

given a valid pcid schema number and an array of doubles that matches the schema, construct a new pcpoint 💫

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

010100000064CEFFFF94110000703000000400

insert some test values into the points table:

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;

🎀 PC_AsText

PC_AsText(p pcpoint) returns text

return a JSON version of the data in that point ✨

SELECT PC_AsText('010100000064CEFFFF94110000703000000400'::pcpoint);

{"pcid":1,"pt":[-127,45,124,4]}

🦋 PC_PCId

PC_PCId(p pcpoint) returns integer (from 1.1.0)

return the pcid schema number of this point 💅

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

1

🍓 PC_Get

PC_Get(pt pcpoint) returns float8[]

return values of all dimensions in an array:

SELECT PC_Get('010100000064CEFFFF94110000703000000400'::pcpoint);

{-127,45,124,4}

PC_Get(pt pcpoint, dimname text) returns numeric

return the numeric value of the named dimension. the dimension name must exist in the schema, no cap 🧐

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

4

💀 PC_MemSize

PC_MemSize(pt pcpoint) returns int4

return the memory size of a pcpoint 🫶

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

25

👑 PcPatch

🌈 PC_Patch

PC_Patch(pts pcpoint[]) returns pcpatch

aggregate function that collects a result set of pcpoint values into a pcpatch 💖

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

⭐ PC_MakePatch

PC_MakePatch(pcid integer, vals float8[]) returns pcpatch

given a valid pcid schema number and an array of doubles that matches the schema, construct a new pcpatch. the array size must be a multiple of the number of dimensions — that part is not optional bestie, it's giving math 😭

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

🦋 PC_NumPoints

PC_NumPoints(p pcpatch) returns integer

return the number of points in this patch ✨

SELECT PC_NumPoints(pa) FROM patches LIMIT 1;

9

💜 PC_PCId

PC_PCId(p pcpatch) returns integer

return the pcid schema number of points in this patch:

SELECT PC_PCId(pa) FROM patches LIMIT 1;

1

🎀 PC_AsText

PC_AsText(p pcpatch) returns text

return a JSON version of the data in that patch 🌸

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

🧚 PC_Summary

PC_Summary(p pcpatch) returns text (from 1.1.0)

return a JSON formatted summary of the data in that point 🔥

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}},{"pos":1,"name":"Y","size":4,"type":"int32_t","compr":"sigbits","stats":{"min":45.01,"max":45.09,"avg":45.05}},{"pos":2,"name":"Z","size":4,"type":"int32_t","compr":"sigbits","stats":{"min":1,"max":9,"avg":5}},{"pos":3,"name":"Intensity","size":2,"type":"uint16_t","compr":"rle","stats":{"min":0,"max":0,"avg":0}}]}

🙈 PC_Uncompress

PC_Uncompress(p pcpatch) returns pcpatch

returns an uncompressed version of the patch (compression type none). in order to return an uncompressed patch on the wire, this must be the outer function with return type pcpatch in your SQL query. all other functions that return pcpatch will compress output to the schema-specified compression before returning ⚠️ so like, put it on the outside, okay?

SELECT PC_Uncompress(pa) FROM patches
WHERE PC_NumPoints(pa) = 1;

01010000000000000001000000C8CEFFFFF8110000102700000A00

🍓 PC_Union

PC_Union(p pcpatch[]) returns pcpatch

aggregate function merges a result set of pcpatch entries into a single pcpatch 💫

-- Compare npoints(sum(patches)) to sum(npoints(patches))
SELECT PC_NumPoints(PC_Union(pa)) FROM patches;
SELECT Sum(PC_NumPoints(pa)) FROM patches;

100

⭐ PC_Intersects

PC_Intersects(p1 pcpatch, p2 pcpatch) returns boolean

returns true if the bounds of p1 intersect the bounds of p2 🦋

-- Patch should intersect itself
SELECT PC_Intersects(
         '01010000000000000001000000C8CEFFFFF8110000102700000A00'::pcpatch,
         '01010000000000000001000000C8CEFFFFF8110000102700000A00'::pcpatch);

t

🔥 PC_Explode

PC_Explode(p pcpatch) returns SetOf[pcpoint]

set-returning function, converts patch into result set of one point record for each point in the patch 💥

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
           {"pcid":1,"pt":[-126.47,45.53,53,5]} |  7
           {"pcid":1,"pt":[-126.46,45.54,54,5]} |  7
           {"pcid":1,"pt":[-126.45,45.55,55,5]} |  7
           {"pcid":1,"pt":[-126.44,45.56,56,5]} |  7
           {"pcid":1,"pt":[-126.43,45.57,57,5]} |  7
           {"pcid":1,"pt":[-126.42,45.58,58,5]} |  7
           {"pcid":1,"pt":[-126.41,45.59,59,5]} |  7

🌸 PC_PatchAvg 🌸

PC_PatchAvg(p pcpatch, dimname text) returns numeric

Okay so this one reads every single value of the dimension you name, for every point in the whole patch, and hands you back the average of all of them. 😌 The dimension name has got to actually exist in the schema — no making up dims, bestie.

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

👑 PC_PatchMax 👑

PC_PatchMax(p pcpatch, dimname text) returns numeric

Same energy as above but instead of the average it's giving maximum — the biggest value of that dimension across every point in the patch. 🔥 Dimension name must exist in the schema, obviously.

SELECT PC_PatchMax(pa, 'x')
FROM patches WHERE id = 7;
-126.41

🦋 PC_PatchMin 🦋

PC_PatchMin(p pcpatch, dimname text) returns numeric

The lil baby minimum of the family — reads all values of the dimension you ask for across the patch and gives you back the smallest one. Dimension name must exist in the schema. 💅

SELECT PC_PatchMin(pa, 'y')
FROM patches WHERE id = 7;
45.5

🎀 PC_PatchMin (again, but as a point this time) 🎀

PC_PatchMin(p pcpatch) returns pcpoint

No dimname here! This version is like "gimme the whole vibe" and returns a PcPoint whose values are the minimum of each individual dimension in the patch. So like, the floor of everything, all at once. 😮‍💨

SELECT PC_PatchMin(pa)
FROM patches WHERE id = 7;
{"pcid":1,"pt":[-126.5,45.5,50,5]}

💖 PC_PatchAvg (the point version) 💖

PC_PatchAvg(p pcpatch) returns pcpoint

Yup, PC_PatchAvg has a second form! With no dimension named, it returns a PcPoint holding the average value of each dimension in the patch — like a little "centre of the vibes" point. ✨

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

⭐ PC_PatchMax (the point version) ⭐

PC_PatchMax(p pcpatch) returns pcpoint

And of course, the matching one — returns a PcPoint carrying the maximum values of every dimension in the patch. The ceiling of the whole situation. 👑

SELECT PC_PatchMax(pa)
FROM patches WHERE id = 7;
{"pcid":1,"pt":[-126.41,45.59,59,5]}

🔥 PC_FilterGreaterThan 🔥

PC_FilterGreaterThan(p pcpatch, dimname text, float8 value) returns pcpatch

Filtering time, bestie! 🎉 Returns a patch containing only the points whose values are greater than the value you supply, for the dimension you named.

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

💜 PC_FilterLessThan 💜

PC_FilterLessThan(p pcpatch, dimname text, float8 value) returns pcpatch

The mirror image — returns a patch with only the points whose values are less than the supplied value for the requested dimension. It's giving less-than, literally. 🫶

🌈 PC_FilterBetween 🌈

PC_FilterBetween(p pcpatch, dimname text, float8 value1, float8 value2)
returns pcpatch

Returns a patch with only the points whose values land between (excluding) the supplied values for the requested dimension. So like value1 and value2 are the vibe boundaries and they're not included — exclusive only, okay? No cap. 🚧

🌸 PC_FilterEquals 🌸

PC_FilterEquals(p pcpatch, dimname text, float8 value) returns pcpatch

Returns a patch with only the points whose values are the same as the supplied values for the requested dimension. Exact match only, bestie — it's giving "if you know you know" but for numbers. 💅

🧚 PC_Compress 🧚

PC_Compress(p pcpatch,global_compression_scheme text,compression_config
text) returns pcpatch

Compresses a patch using a scheme you specify manually. 🗜️ The compression_config means different things depending on the global compression scheme. The allowed global compression schemes are:

  • auto — determined by pcid
  • laz — no compression config supported
  • dimensional — the configuration is a comma-separated list of per-dimension compressions, picked from this list:
    • auto — determined automatically from values stats
    • zlib — deflate compression
    • sigbits — significant bits removal
    • rle — run-length encoding
Heads up: with laz the config is literally not supported, so don't come for me if you try to pass one. 🙈

🍓 PC_PointN 🍓

PC_PointN(p pcpatch, n int4) returns pcpoint

Returns the n-th point of the patch, using 1-based indexing (so the first point is 1, not 0 — I know, I know). And if n is negative, it counts the point from the end. Cute, right? 💫

💀 PC_IsSorted 💀

PC_IsSorted(p pcpatch, dimnames text[], strict boolean default true)
returns boolean

Checks whether a pcpatch is sorted lexicographically along the dimensions you list. The strict option (defaults to true, bestie) gives it a deeper check — that the ordering is strict, i.e. no duplicates allowed. So strict is the vibe check within the vibe check. 😭

🎀 PC_Sort 🎀

PC_Sort(p pcpatch, dimnames text[]) returns pcpatch

Returns a copy of the input patch, lexicographically sorted along the dimensions you give it. Original stays untouched, glowed-up copy comes out. Slay. ✨

🦋 PC_Range 🦋

PC_Range(p pcpatch, start int4, n int4) returns pcpatch

Returns a patch containing n points, selected starting from the start-th point — and yep, that's 1-based indexing again. So like, you're grabbing a lil slice of the patch. 🍰

👑 PC_SetPCId 👑

PC_SetPCId(p pcpatch, pcid int4, def float8 default 0.0) returns pcpatch

Sets the schema on a PcPatch, given a valid pcid schema number. 🪄

For dimensions that are in the "new" schema but not in the "old" schema, the value def gets set in the points of the output patch. def is optional and its default value is 0.0 — so if you don't say anything, it's zeroes for the newcomers. 🫶

🧚 PC_Transform 🧚

PC_Transform(p pcpatch, pcid int4, def float8 default 0.0) returns pcpatch

Returns a new patch with its data transformed based on the schema whose identifier is pcid. 🔄

Same deal as above for the new dims: dimensions that exist in the "new" schema but not the "old" one get def set on their points in the output patch. def is optional, default 0.0. 🌸

Important distinction, bestie: contrary to PC_SetPCId, PC_Transform may actually change (transform) the patch data if the dimension interpretations, scales or offsets are different in the new schema. So like — it's not just relabelling, it's a whole glow-up. 💅

🌟 PC_MemSize 🌟

PC_MemSize(p pcpatch) returns int4

Returns the memory size of a pcpatch. Simple, honest, iconic. 💾

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

💾 WKB 💾

🧊 PC_AsBinary 🧊

PC_AsBinary(p pcpoint) returns bytea

Returns the OGC "well-known binary" format for the point. Bytes, baby. 🔢

SELECT PC_AsBinary('010100000064CEFFFF94110000703000000400'::pcpoint);
\x01010000800000000000c05fc000000000008046400000000000005f40

📦 PC_EnvelopeAsBinary 📦

PC_EnvelopeAsBinary(p pcpatch) returns bytea

Returns the OGC "well-known binary" format for the 2D bounds of the patch. Super useful for doing 2D intersection tests with geometries. 🫶

SELECT PC_EnvelopeAsBinary(pa) FROM patches LIMIT 1;
\x0103000000010000000500000090c2f5285cbf5fc0e17a
14ae4781464090c2f5285cbf5fc0ec51b81e858b46400ad7
a3703dba5fc0ec51b81e858b46400ad7a3703dba5fc0e17a
14ae4781464090c2f5285cbf5fc0e17a14ae47814640
Deprecation tea ☕: PC_Envelope is an alias to PC_EnvelopeAsBinary. But PC_Envelope is deprecated and will be removed in a future version (2.0) of the extension. Use PC_EnvelopeAsBinary instead, bestie — the alias is not it. 😭

🦋 PC_BoundingDiagonalAsBinary 🦋

PC_BoundingDiagonalAsBinary(p pcpatch) returns bytea

Returns the OGC "well-known binary" format for the bounding diagonal of the patch. Diagonal queen energy. 👑

SELECT PC_BoundingDiagonalAsBinary( PC_Patch(ARRAY[ PC_MakePoint(1, ARRAY[0.,0.,0.,10.]), PC_MakePoint(1, ARRAY[1.,1.,1.,10.]), PC_MakePoint(1, ARRAY[10.,10.,10.,10.])]));
\x01020000a0e610000002000000000000000000000000000000000000000000000000000000000000000000244000000000000024400000000000002440

🌍 PostGIS 🌍

So the pointcloud_postgis extension adds functions that let you use PostgreSQL Pointcloud with PostGIS — converting PcPoint and PcPatch to Geometry and doing spatial filtering on point cloud data. 🤩

The pointcloud_postgis extension depends on both the postgis and pointcloud extensions, so they must be installed first — order matters, bestie:

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

🫶 PC_Intersects 🫶

PC_Intersects(p pcpatch, g geometry) returns boolean

PC_Intersects(g geometry, p pcpatch) returns boolean

Returns true if the bounds of the patch intersect the geometry. Both argument orders work — she's flexible like that. 💅

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

✂️ PC_Intersection ✂️

PC_Intersection(pcpatch, geometry) returns pcpatch

Returns a PcPatch that contains only the points that intersected the geometry. Like a little guest list — if you're not on it, you're not getting in. 🚪✨

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

🌈 Geometry 🌈

Geometry(pcpoint) returns geometry

pcpoint::geometry returns geometry

Casts PcPoint to the PostGIS geometry equivalent, placing the x/y/z/m of the PcPoint into the x/y/z/m of the PostGIS point. One-to-one, no funny business. 🧚

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

🎀 PC_EnvelopeGeometry 🎀

PC_EnvelopeGeometry(pcpatch) returns geometry

Returns the 2D bounds of the patch as a PostGIS Polygon 2D. Useful for performing 2D intersection tests with PostGIS geometries. 🫶

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

For example, this is how one may want to create an index:

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

⭐ PC_BoundingDiagonalGeometry ⭐

PC_BoundingDiagonalGeometry(pcpatch) returns geometry

Returns the bounding diagonal of a patch. Depending on whether the patch has Z and M dimensions, it's a LineString (2D), a LineString Z, a LineString M or a LineString ZM — so she adapts to the situation. 💅 This function is useful for creating an index on a patch column.

SELECT ST_AsText(PC_BoundingDiagonalGeometry(pa)) FROM patches;
                  st_astext
------------------------------------------------
LINESTRING Z (-126.99 45.01 1,-126.91 45.09 9)
LINESTRING Z (-126 46 100,-126 46 100)
LINESTRING Z (-126.2 45.8 80,-126.11 45.89 89)
LINESTRING Z (-126.4 45.6 60,-126.31 45.69 69)
LINESTRING Z (-126.3 45.7 70,-126.21 45.79 79)
LINESTRING Z (-126.8 45.2 20,-126.71 45.29 29)
LINESTRING Z (-126.5 45.5 50,-126.41 45.59 59)
LINESTRING Z (-126.6 45.4 40,-126.51 45.49 49)
LINESTRING Z (-126.9 45.1 10,-126.81 45.19 19)
LINESTRING Z (-126.7 45.3 30,-126.61 45.39 39)
LINESTRING Z (-126.1 45.9 90,-126.01 45.99 99)

For example, this is how one may want to create an index:

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

💅 UPDATE 💅

Okay bestie, so a fresh pgPointcloud version just dropped? You might wanna update the databases where the extension is already in use. First thing: compare the version you're currently running with the versions actually available on your system. Like a vibe check for your extension, no cap:

SELECT pc_version();
--> 1.1.1

SELECT version FROM pg_available_extension_versions WHERE name ='pointcloud';
--> 1.1.1
--> 1.2.1

Then you can update to the latest version. The page gives you these exact lines:

ALTER EXTENSION pointcloud;
ALTER EXTENSION pointcloud_postgis;

Or target a specific version, if that's more your main character energy:

ALTER EXTENSION pointcloud UPDATE TO '1.2.1';

SELECT pc_version();
--> 1.2.1
⚠️ Warning, bestie: The GHT compression has been removed in the 1.2.0 version. Unfortunately, you have to remove the compression on your tables before updating the extension from 1.1.x to a higher version. Some info is available in the Schema and compression tutorial: https://pgpointcloud.github.io/pointcloud/tutorials/compression.html#schema-and-compression

🎓 TUTORIALS 🎓

This chapter provides some basic tutorials on how to deploy and use pgPointcloud. Starter pack vibes, honestly.

🍓 Storing points 🍓

This tutorial is a basic introduction to pgPointcloud for storing points in a PostgreSQL database hosted on a Docker container. Iconic little setup.

🐳 Start Docker container 🐳

First we download the latest tag of the pgPointcloud Docker image:

$ docker pull pgpointcloud/pointcloud

This Docker image is based on the official PostgreSQL image, and the full documentation is available here: https://hub.docker.com/_/postgres.

For basic usage, we have to define two environment variables:

  • the PostgreSQL database: POSTGRES_DB
  • the PostgreSQL password: POSTGRES_PASSWORD

Then we can start a new container:

$ docker run --name pgpointcloud -e POSTGRES_DB=pointclouds -e POSTGRES_PASSWORD=mysecretpassword -d pgpointcloud/pointcloud

Extensions are automatically created in the new database named pointclouds:

$ docker exec -it pgpointcloud psql -U postgres -d pointclouds -c "\dx"
                                          List of installed extensions
        Name          | Version |   Schema   |                             Description
------------------------+---------+------------+---------------------------------------------------------------------
 fuzzystrmatch          | 1.1     | public     | determine similarities and distance between strings
 plpgsql                | 1.0     | pg_catalog | PL/pgSQL procedural language
 pointcloud             | 1.2.1   | public     | data type for lidar point clouds
 pointcloud_postgis     | 1.2.1   | public     | integration for pointcloud LIDAR data and PostGIS geometry data
 postgis                | 3.0.1   | public     | PostGIS geometry, geography, and raster spatial types and functions
 postgis_tiger_geocoder | 3.0.1   | tiger      | PostGIS tiger geocoder and reverse geocoder
 postgis_topology       | 3.0.1   | topology   | PostGIS topology spatial types and functions
(7 rows)

🔥 Run PDAL pipeline 🔥

For the needs of this tutorial, we can download sample data from the PDAL organization: https://github.com/PDAL.

$ wget https://github.com/PDAL/data/raw/master/liblas/LAS12_Sample_withRGB_Quick_Terrain_Modeler_fixed.laz -P /tmp

Thanks to the pdal info command, we can obtain some info on the dataset:

  • Number of points: 3811489
  • Spatial reference: EPSG:32616

To configure the JSON PDAL pipeline, we need to set up the connection parameter for the pgpointcloud writer. To do that, the Docker container IP address on which the PostgreSQL database is running is necessary:

$ docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' pgpointcloud
172.17.0.2

So the pipeline.json file looks like this:

{
  "pipeline":[
    {
      "type":"readers.las",
      "filename":"/tmp/LAS12_Sample_withRGB_Quick_Terrain_Modeler_fixed.laz"
    },
    {
      "type":"filters.chipper",
      "capacity":"400"
    },
    {
      "type":"writers.pgpointcloud",
      "connection":"host='172.17.0.2' dbname='pointclouds' user='postgres' password='mysecretpassword' port='5432'",
      "table":"airport",
      "compression":"dimensional",
      "srid":"32616"
    }
  ]
}

The PDAL pipeline can finally be executed with pdal pipeline pipeline.json, and an airport table is created. Slay.

🫶 Configure connection service file 🫶

To make access to the database hosted on the Docker container easier, we can configure the PostgreSQL connection service file:

[pgpointcloud]
host=172.17.0.2
port=5432
dbname=pointclouds
user=postgres
password=mysecretpassword

Then we can explore the content of the new airport table:

$ psql service=pgpointcloud
psql (12.3)
Type "help" for help.

pointclouds=# SELECT COUNT(*) FROM airport;
 count
-------
  9529
(1 row)

In this case, we have 9529 patchs containing 400 points (the size of the chipper filter), meaning about 3811600 points. So the last patch isn't fully filled.

🌸 Schema and compression 🌸

This tutorial is an introduction to investigating XML schemas and playing with compression of patches. Very demure, very mindful.

💜 Compression type 💜

The compression of a patch may be retrieved through its XML schema, but it's also stored in the patch itself. Of course, both need to be consistent, so updating an existing schema is hardly discouraged and may lead to errors. Bestie, be careful.

In the first case, the XML schema needs to be parsed with the xpath function to retrieve the pc:metadata tag of a specific patch:

WITH tmp AS (
    SELECT pc_pcid(pa)
    AS _pcid
    FROM airport
    LIMIT 1
)
SELECT unnest(
    xpath(
        '/pc:PointCloudSchema/pc:metadata/Metadata/text()',
        schema::xml,
        array[
            ['pc', 'http://pointcloud.org/schemas/PC/'],
            ['xsi', 'http://www.w3.org/2001/XMLSchema-instance']
        ]
    )
)
FROM tmp,pointcloud_formats
WHERE pcid=tmp._pcid;
--> dimensional

A much easier way to retrieve the compression type is to take a look at the JSON summary of the patch:

SELECT pc_summary(pa)::json->'compr' FROM airport LIMIT 1;
--> dimensional

🎀 Create a new schema 🎀

A schema is just an XML document and may be manually inserted into the pointcloud_formats table directly from a file. We can also duplicate an existing schema and tweak some parameters.

For example, we can create a new schema without compression, based on the schema pcid=1:

INSERT INTO pointcloud_formats (pcid, srid, schema)
SELECT 2, srid, regexp_replace(schema, 'dimensional', 'none', 'g')
FROM pointcloud_formats
WHERE pcid=1;

✨ Transform a patch ✨

Thanks to the pc_transform function, we can transform the underlying data of a patch to match a specific schema. So if we want to remove the dimensional compression from an existing patch, we can use the schema with pcid=2 previously created.

In this particular case, the transformed patch doesn't have compression anymore:

SELECT pc_summary(pc_transform(pa, 2))::json->'compr' FROM airport LIMIT 1;
--> none

So a new table of uncompressed patches may be easily created:

CREATE TABLE airport_uncompressed AS SELECT pc_transform(pa, 2) AS pa FROM airport;

SELECT pc_summary(pa)::json->'compr' FROM airport_uncompressed LIMIT 1;
-->  none

SELECT pc_astext(pc_patchavg(pa)) FROM airport LIMIT 1;
--> {"pcid":1,"pt":[65535,0,0,0,0,0,0,0,0,30744,25999,17189,728265,4.67644e+06,299.08]}

SELECT pc_astext(pc_patchavg(pa)) FROM airport_uncompressed LIMIT 1;
--> {"pcid":2,"pt":[65535,0,0,0,0,0,0,0,0,30744,25999,17189,728265,4.67644e+06,299.08]}

💻 DEVELOPMENT 💻

🧚 Development 🧚

Developer documentation with dependancies, build instructions, how to build a Docker image, update the documentation and run unit tests. It’s giving “I got you, bestie” energy ✨

📦 Requirements 📦

  • PostgreSQL and PostgreSQL development packages must be installed (pg_config and server headers). For Red Hat and Ubuntu, the package names are usually postgresql-dev or postgresql-devel.
  • LibXML2 development packages must be installed, usually libxml2-dev or libxml2-devel.
  • CUnit packages must be installed.
  • [Optional] the laz-perf library may be installed for LAZ compression support (built from source https://github.com/hobu/laz-perf).

🔨 Build/Install 🔨

After generating the configure script with autogen.sh, you can use ./configure --help to get a complete listing of configuration options. Slay, configure script, slay 💅

$ ./autogen.sh
$ ./configure
$ make
$ sudo make install
Note: You can use --with-pgconfig on the ./configure command line if you have multiple PostgreSQL installations on your system and want to target a specific one. For example:
$ ./configure --with-pgconfig=/usr/lib/postgresql/12/bin/pg_config
Note: If qsort_r is not available on your system, then you can use the embedded implementation instead thanks to the next directive:
$ ./configure CFLAGS="-DNESTED_QSORT=1"

🧪 Tests 🧪

✅ Unit tests ✅

$ make check

🐞 Regressions tests 🐞

pgPointcloud includes SQL tests to run against an existing installation. To run the SQL tests:

$ sudo make install
$ PGUSER=a_user PGPASSWORD=a_password PGHOST=localhost make installcheck

This command will create a database named contrib_regression and will execute the SQL scripts located in pgsql/sql in this database. Database said “new name, who dis?” and honestly valid 💜

🧊 Write a loading system 🧊

If you are writing your own loading system and want to write into Pointcloud types, create well-known binary inputs, in uncompressed format. If your schema indicates that your patch storage is compressed, Pointcloud will automatically compress your patch before storing it, so you can create patches in uncompressed WKB without worrying about the nuances of particular internal compression schemes. Bestie, let Pointcloud handle the compression glow-up ✨

The only issues to watch when creating WKB patches are: ensuring the data you write is sized according to the schema (use the specified dimension type); ensuring that the endianness of the data matches the declared endianness of the patch. Endianness matching is the vibe check you cannot skip 🔥

📚 Documentation 📚

Sphinx is used to build the documentation. For that, you have to install the next Python packages:

  • sphinx
  • sphinx_rtd_theme

Then:

$ cd doc && make html

The HTML documentation is available in doc/build/html.

Note: The documentation can be generated in another format like pdf, epub, ... You can use make to get a list of all available formats. PDF girlies, EPUB besties, make has you covered 🎀

🐳 Docker Image 🐳

A Dockerfile is provided in the docker directory and based on the official PostgreSQL docker image available DockerHub https://hub.docker.com/_/postgres. The image generated is based on PostgreSQL 14, PostGIS 3 and the laz-perf support is activated. Built, based, and laz-perf-activated — iconic behavior 🐳

$ docker build --rm -t pgpointcloud docker/

🔁 Continuous Integration 🔁

pgPointcloud tests are run with Github Actions https://github.com/pgpointcloud/pointcloud/actions on several Ubuntu versions and with various PostgreSQL/PostGIS releases:

  • PostgreSQL 12
    • PostGIS 2.5: 12_25_focal + [image]
    • PostGIS 3.3: 12_33_focal + [image]; 12_33_jammy + [image]
    • W/O PostGIS: 12_33_focal + [image]; 12_33_jammy + [image]
  • PostgreSQL 13
    • PostGIS 2.5: (empty in the original table)
    • PostGIS 3.3: 13_33_focal + [image]; 13_33_jammy + [image]
    • W/O PostGIS: 13_33_focal + [image]; 13_33_jammy + [image]
  • PostgreSQL 14
    • PostGIS 2.5: (empty in the original table)
    • PostGIS 3.3: 14_33_focal + [image]; 14_33_jammy + [image]
    • W/O PostGIS: 14_33_focal + [image]; 14_33_jammy + [image]
  • PostgreSQL 15
    • PostGIS 2.5: (empty in the original table)
    • PostGIS 3.3: 15_33_focal + [image]; 15_33_jammy + [image]
    • W/O PostGIS: 15_33_focal + [image]; 15_33_jammy + [image]
  • PostgreSQL 16
    • PostGIS 2.5: (empty in the original table)
    • PostGIS 3.3: 16_33_jammy + [image]
    • W/O PostGIS: 16_33_jammy + [image]

CI matrix said “I contain multitudes” and honestly? Same 🌈

🚀 Release 🚀

Steps for releasing a new version of Pointcloud:

  1. Add a new section to the NEWS file, listing all the changes associated with the new release.
  2. Change the version number in the README, Version.config and pgsql/expected/pointcloud.out files.
  3. Update the value of UPGRADABLE in pgsql/Makefile.in and pgsql_postgis/Makefile. This variable defines the versions from which a database can be upgraded to the new Pointcloud version.
  4. Create a PR with these changes.
  5. When the PR is merged create a tag for the new release and push it to GitHub:
$ git tag -a vx.y.z -m 'version x.y.z'
$ git push origin vx.y.z

Tag it, push it, release it — main character release energy 👑

🧠 Valgrind memcheck 🧠

For checking the memory management of pgPointcloud extension, Valgrind can be used with PostgreSQL in single-user mode.

But first, it’s necessary to compile the extension with debug symbols and without compiler optimizations:

$ ./configure CFLAGS="-O0 -g"
$ make
$ sudo make install

Debug symbols may also be installed for PostgreSQL and PostGIS. For example for Debian based distributions with PostgreSQL 13 and PostGIS 3:

$ sudo apt-get install postgresql-13-dbgsym postgresql-13-postgis-3-dbgsym

And finally:

$ echo "select pc_transform(patch, 1) from patchs limit 1" | \
  valgrind -s --track-origins=yes --leak-check=yes \
    --show-leak-kinds=all --read-var-info=yes --log-file=/tmp/valgrind.log \
    /usr/lib/postgresql/13/bin/postgres --single -D /var/lib/postgresql/13/main \
    -c config_file=/etc/postgresql/13/main/postgresql.conf \
    mydatabase

Then Valgrind’s report is available in /tmp/valgrind.log. Leak check bestie, no cap 🫶

🪄 GDB interactive mode 🪄

GDB may be attached to a running session for debugging purpose:

$ psql mydatabase
psql (14.5)
Type "help" for help.

mydatabase=# select pid from pg_stat_activity where usename = 'pblottiere' and state = 'active';
  pid
-------
 34699
(1 row)

$ sudo gdb -p 34699
GNU gdb (GDB) 12.1
(gdb)

Then you can execute a SQL request in the corresponding session and use GDB as usual (step by step, backtrace, ...). Step-by-step debugging is literally the group-chat replay we deserve 💅

💖 PROJECT 💖

🌐 Community 🌐

okay so pgPointcloud's community interacts through the Mailing List (http://lists.osgeo.org/mailman/listinfo/pgpointcloud) and GitHub (https://github.com/pgpointcloud/pointcloud). The Mailing List (http://lists.osgeo.org/mailman/listinfo/pgpointcloud) communication channel is for general questions and feedback. The GitHub (https://github.com/pgpointcloud/pointcloud) communication channel is for development activities, bug reports, and testing. fr fr 🫶

🎀 FAQ 🎀

What can be done with pgPointcloud?

  • pgPointcloud offers a way to efficiently store points in a postgres database. In fact, pgpPointCloud stores groups of points (pcPoints) called pcPatch — storing groups of point allows efficient compression of data. This patch have a bounding box which can be used by PostGIS spatial features to greatly accelerate queries. pgPointcloud allows then to efficiently query very big point cloud. Querying can be done using spatial criteria (which points are in this area), as well as point attributes criteria (which points have a value of this attribute between .. and ... ?).
  • The point cloud being in a server, they can be used for processing or visualization, or streamed.

Why use pgPointcloud to store Lidar points into postgreSQL and not in point cloud files?

The traditional way of storing point cloud is using several files containing each a part of the point cloud. This has some severe limitations:

  • Not efficient data query (to get a few points, you need to read the whole file)
  • No concurrency (only one user can modify points at a time/read points at a time)
  • Files tends to get duplicated a lot (each worker has it's own private version) no security of data ( file could be corrupted by a processing, hard to manage who access what)
  • Hard to use several different point cloud at the same time
  • Hard to use point cloud with other spatial data (vector, raster, images)

pgPointcloud solves all of this problem, at a very low cost: you have to use a DBMS.

Does pgPointcloud scale?

pgPointcloud is a young project, yet it has been proven to work fast (1ms query time) with a 5 billions points cloud.

Currently pgPointcloud is fast for:

  • Load data into DB
  • Automatically compress data
  • Query patches based on spatial or other attributes

It is slow for:

  • Data output (100k pts/sec)
  • In base conversion (no functions)

📜 License 📜

okay bestie legal text time, do not touch, here is the whole thing exactly:

Copyright (c) 2013 Natural Resources Canada All rights reserved.

Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions are
met:

• Redistributions of source code must retain the above copyright notice,
  this list of conditions and the following disclaimer.

• Redistributions in binary form must reproduce the above copyright
  notice, this list of conditions and the following disclaimer in the
  documentation and/or other materials provided with the distribution.

• Neither the name of the Natural Resources Canada, the Government of
  Canada nor the names of its contributors may be used to endorse or
  promote products derived from this software without specific prior
  written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS
IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A
PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED
TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR
PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

🦋 INDICES AND TABLES 🦋

  • Index <>
  • Search Page <>

✨ Author ✨

Author name not set

1980

okay that's the whole page, mwah 💋 — PGPOINTCLOUD(1), version 1.2.5, dated January 01, 1980. 🎀

📦 where this page comes from