What is it?

pgPointcloud is a PostgreSQL extension that allows storing point clouds in a PostgreSQL database. This article presents its various features, trade-offs, and main use cases.

The Problem

The need to create pgPointcloud arose from the emergence of very large point cloud acquisitions, potentially containing hundreds of millions or even billions of points. How do you store these points and their attributes while taking advantage of the power of PostgreSQL and PostGIS?

Naively storing in POINT format is not recommended. This would mean having one row per point in the table, which will not scale for geographic indexes when the number of points exceeds several tens of millions.

Moreover, the PostGIS spatial index is poorly suited to points. It is based on a hierarchy of bounding boxes. While this hierarchy is still useful for points, the bounding box of a point is… the point itself! So we end up duplicating the table storage in the index.

We therefore prefer solutions that group points into geographically coherent sets to maintain a reasonable index size.

`MULTIPOINT`

One possible solution is of course to use the MULTIPOINT geographic type, grouping points according to a geographic partitioning. This solution allows the spatial index to scale, but has two limitations:

This format is not compressed
It does not easily allow storing metadata linked to each point.

For the latter, one can use a MultiPointM and abuse the M dimension to store a metadata identifier that can be used in a JOIN condition, for example.

pgPointcloud

pgPointcloud offers another way to store points by grouping them into “patches”. Compared to MULTIPOINT, pgPointcloud allows two additional things:

compression of a point patch;
definition of a specific point format.

pgPointcloud can thus be compared to multipoint, while offering a different trade-off in terms of storage and data format.

Compression

pgPointcloud supports configuration of a compression method. The three possible ones are:

no compression;
LAZ compression;
dimensional compression. It groups data by dimension (X, Y, Z) since they are more likely to be similar, then uses various compression strategies.

The official pgPointcloud site explains these methods in more detail. These compressions allow compression ratios between 3:1 and 5:1.

Format Declaration

pgPointcloud allows declaring a point format. Mainly useful for declaring additional attributes beyond the standard X, Y, and Z. This makes it possible to embed intensity, classification, return number, etc., in each point without requiring joining to an external table. The format declaration is done using an XML schema.

Advantages

Storing points in a database with PostGIS brings two main benefits:

the ability to have transactional updates and modifications, which ensures safety and better consistency guarantees;
the ability to cross-reference with other spatial tables using spatial indexes.

Disadvantages

This type of storage may not be suitable for all use cases. Storing a large number of points in a database inevitably increases its weight, which can complicate maintenance and operational readiness (backup, restore, migration, etc.).

Compared to other storage types like MULTIPOINT, compression will necessarily slow down read operations.

Finally, pgPointcloud, while allowing efficient spatial querying, cannot be used alone for visualization, as it does not implement a level-of-detail concept. These levels must be implemented separately, for example, with an additional table. You can then use pg_tileserv and an SQL function that queries the table containing the levels of detail for overview views, and the reference table for more detailed views.

A pgpointcloud table connected to pg_tileserv and visualized in QGIS

Use Cases

pgPointcloud may be chosen if:

Transactional storage is a strict requirement
Cross-referencing with spatial data is frequent and necessary
Storage optimization is preferred over fast querying
The point format is specific

Here’s a small diagram summarizing the key decision points.

pgpointcloud decision diagram

This diagram summarizes the criteria, but the actual choice will of course depend on the context of use. Still, pgPointcloud can be considered for use cases such as a data repository (where integrity is very important) or as a step enabling complex spatial computations.

Practical Info

Installation

pgPointcloud is available through the official apt repository of debian and ubuntu (for example postgresql-16-pointcloud for ubuntu 24.10). More versions and support for more linux distributions are available through the postgresql package repository (pgdg) corresponding to a specific distro.

For Windows, pgPointcloud is included in the official PostGIS bundle.

Loading and Extracting Data

To load and extract data, PDAL remains a top choice, as it has both a pgPointcloud reader and a writer.

The full power of SQL is also at your disposal, using pgPointcloud functions and PostGIS functions.

For example, here’s a query extracting points in MVT format over a given extent, including Z as a property:

WITH mvtgeom AS (
  SELECT
    pc_get(points, 'z') AS z,
    st_asmvtgeom(st_transform(points::geometry, 3857), bounds.geom) point
  FROM points,
    -- pc_explode decompresses and unwraps the patch of points to access each individual points
    LATERAL pc_explode(sonde.patch) v(points)
  WHERE
    pc_intersects(patch, polygon)
)
SELECT st_asmvt(mvtgeom) FROM mvtgeom;

Conclusion

pgPointcloud offers an interesting solution for storing and processing point clouds. Leveraging SQL power, PostgreSQL’s reliability, and PostGIS’s features, it provides a compelling trade-off when building a repository or performing spatial joins.

If you’re interested in this technology, feel free to contact us to evaluate whether it fits your use case.

To go further:

pgPointcloud: A Different Trade-off for Storing Point Clouds