repository·master·Indexed 19 days ago
https://github.com/pgpointcloud/pointcloudA PostgreSQL extension for the efficient storage and management of point cloud (LIDAR) data. It provides support for PcPoint and PcPatch objects, schema documents compatible with the PDAL library, and various compression methods including Dimensional (Run-length, Significant bits removal, and Deflate/zlib) and LAZ. The extension includes a Point Binary format for compact data representation and a portable sort_r function for parameterized array sorting.
pgPointcloud is designed for efficient storage and querying of large-scale point cloud data within a PostgreSQL database.
Key features include:
pcPoints within structures known as pcPatch. This grouping allows for high-efficiency data compression.pcPatch includes a bounding box. This allows PostGIS spatial features to use these boxes to significantly accelerate queries.The sort_r function provides a portable, reentrant alternative to the standard qsort().
Standard qsort() does not allow passing custom parameters to the comparison operator without using global variables, which makes it unsuitable for multithreaded code. While qsort_r and qsort_s exist, they are not portable across different systems (GNU, BSD, and Windows all use different argument signatures). sort_r solves this by providing a consistent, portable interface for sorting arrays with parameterized comparison functions.
Patch binary formats are used to transmit groups of points. They include additional header information to specify compression methods and the number of points included. There are three main types of patch formats: Uncompressed, Dimensional, and LAZ.
All patch formats share a common header structure for endianness and pcid.
The extension provides two system-level objects to manage and inspect point cloud data:
pointcloud_formats table: Stores all registered pcid entries and their corresponding schema documents.pointcloud_columns view: A system view that lists all columns in your database containing point cloud objects (pcpoint or pcpatch).You can query pointcloud_columns to verify that your tables and columns have been correctly recognized by the extension.
SELECT * FROM pointcloud_columns;Run-length compression (type 1) reduces data by storing pairs of (repeat count, value). This is effective for dimensions with many repeating values.
Structure:
byte: number of times the word repeatsword: the value of the word being repeatedNote: The size of the word is determined by the schema.
byte: number of times the word repeats
word: value of the word being repeated
.... repeated for the number of runsDimensional compression optimizes storage by transforming the patch representation from a list of $N$ points (each with $M$ dimensions) into a list of $M$ dimensions (each containing $N$ values).
This transformation allows the system to apply different compression schemes to each dimension based on its specific data characteristics:
For LIDAR data organized into patches of points sampling similar areas, the dimensional scheme typically achieves compression efficiencies between 3:1 and 5:1.
// Original representation: List of N points containing M dimensions
{
"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]
]
}
// Dimensional representation: List of M dimensions containing N values
{
"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]
]
}A PcPatch is a collection of PcPoint objects stored together to optimize database efficiency. Because storing billions of individual point records is resource-intensive, LIDAR data is instead represented as a collection of PcPatch records (typically in the tens of millions).
To ensure performance, patches should ideally contain points that are spatially near each other.
When exported to a human-readable JSON format using PC_AsText(pcpatch), a PcPatch consists of:
pcid: A foreign key reference to the pointcloud_formats table.pts: A nested array where each inner array is a point's dimensions (the pt array of a PcPoint).{
"pcid" : 1,
"pts" : [
[0.02, 0.03, 0.05, 6],
[0.02, 0.03, 0.05, 8]
]
}A PcPoint is the fundamental unit of a point cloud. It represents a single point in space with a variable number of dimensions. At a minimum, it contains X and Y coordinates.
When exported to a human-readable JSON format using PC_AsText(pcpoint), a PcPoint consists of:
pcid: A foreign key reference to the pointcloud_formats table, which defines the meaning of each dimension.pt: An array of doubles representing the point's dimensions (e.g., coordinates, intensity, etc.).Note that while underlying storage might use different types, the extracted data is represented as doubles after scaling and offsetting.
{
"pcid" : 1,
"pt" : [0.01, 0.02, 0.03, 4]
}PostgreSQL Pointcloud uses a "schema document" to handle the variability of LIDAR data (e.g., different dimensions like X, Y, Z, Intensity, RGB, or return times). Because different sensors store data using different types (e.g., int32_t vs double) and different scaling/offsets, the schema document defines how to interpret the raw bytes stored in the database.
Key concepts:
interpretation), a size, and optional scale and offset values to convert stored integers into actual physical values.pointcloud_formats table. Instead of repeating the schema for every object, point cloud objects store a pcid (pointcloud identifier), which acts as a foreign key to the pointcloud_formats table, similar to how srid works in PostGIS.Deflate compression (type 3) uses the zlib algorithm for general-purpose compression of a dimension. The data area is a raw zlib buffer.
Implementation Details:
inflate() function.dimension word size * number of points in the patch.