Heritage GIS

Storing Point Clouds as PostGIS pgPointCloud Patches

A dense photogrammetric cloud of an excavated Bronze Age roundhouse can run to eighty million points, and inserting one row per point would produce a table no index can save. pgPointCloud stores the same cloud as a few tens of thousands of compressed PcPatch blocks, each holding a spatially coherent group of points under a shared dimensional schema, so you keep every point yet query at patch granularity. This guide, part of exporting 3D models to spatial databases, gives the full PDAL pipeline that streams a LAZ file into patches, the pointcloud_formats schema registration that makes it possible, and the PC_* queries that get points back out.

LAZ to pgPointCloud patches via PDAL A LAZ file passes through a PDAL chipper into fixed-size patches written to a PostGIS pointcloud column with a registered schema. Millions of points become thousands of patches LAZ input 80M points PDAL chipper 400-pt blocks PcPatch compressed PostGIS pcid schema

Context & When to Use

Store a cloud as pgPointCloud when you want the raw points to remain queryable inside the database — clipping to a context polygon, extracting a classified ground surface, or computing per-patch statistics — rather than as an opaque file. If you only ever consume the cloud whole in a desktop tool, a LAZ file on disk is simpler and the database buys you nothing. Patch size is the tuning knob: PDAL’s chipper groups points into patches of a target count (commonly 400). Small patches give finer spatial pruning and faster clips but more rows and index overhead; large patches compress better and reduce row count but read more slack on a tight query. For excavation-scale clouds, 400 to 600 points per patch is a sound default. Note that a mesh and a cloud answer different questions — use loading textured meshes into PostGIS 3D when you need a measurable surface, and pgPointCloud when you need the sampled points themselves.

Implementation

First register a point schema so the database knows each patch’s dimensions. The pcid this row receives ties every patch to its layout.

-- PostgreSQL 16 / PostGIS 3.4 / pgPointCloud 1.2.5
CREATE TABLE roundhouse_cloud (
    id  serial PRIMARY KEY,
    pa  pcpatch(1)          -- pcid 1, defined below
);

INSERT INTO pointcloud_formats (pcid, srid, schema) VALUES (1, 27700, $XML$
<?xml version="1.0" encoding="UTF-8"?>
<pc:PointCloudSchema xmlns:pc="http://pointcloud.org/schemas/PC/1.1">
  <pc:dimension><pc:position>1</pc:position><pc:size>4</pc:size>
    <pc:name>X</pc:name><pc:interpretation>int32_t</pc:interpretation>
    <pc:scale>0.001</pc:scale><pc:offset>352000</pc:offset></pc:dimension>
  <pc:dimension><pc:position>2</pc:position><pc:size>4</pc:size>
    <pc:name>Y</pc:name><pc:interpretation>int32_t</pc:interpretation>
    <pc:scale>0.001</pc:scale><pc:offset>174000</pc:offset></pc:dimension>
  <pc:dimension><pc:position>3</pc:position><pc:size>4</pc:size>
    <pc:name>Z</pc:name><pc:interpretation>int32_t</pc:interpretation>
    <pc:scale>0.001</pc:scale><pc:offset>0</pc:offset></pc:dimension>
  <pc:dimension><pc:position>4</pc:position><pc:size>2</pc:size>
    <pc:name>Intensity</pc:name><pc:interpretation>uint16_t</pc:interpretation></pc:dimension>
  <pc:dimension><pc:position>5</pc:position><pc:size>1</pc:size>
    <pc:name>Classification</pc:name><pc:interpretation>uint8_t</pc:interpretation></pc:dimension>
  <pc:metadata><Metadata name="compression">dimensional</Metadata></pc:metadata>
</pc:PointCloudSchema>
$XML$);
-- srid 27700 = British National Grid; substitute your site's EPSG

The scale and offset on X and Y store coordinates as scaled integers around the site origin, which is how pgPointCloud keeps precision without floats. Now stream the LAZ in with a PDAL pipeline whose writers.pgpointcloud matches this pcid:

# requirements.txt / conda
# pdal==2.6.3
# libpdal-plugin-pgpointcloud present
{
  "pipeline": [
    { "type": "readers.las", "filename": "roundhouse.laz" },
    { "type": "filters.reprojection",
      "in_srs": "EPSG:32630", "out_srs": "EPSG:27700" },
    { "type": "filters.chipper", "capacity": 400 },
    { "type": "writers.pgpointcloud",
      "connection": "host=localhost dbname=heritage user=gis",
      "table": "roundhouse_cloud",
      "column": "pa",
      "compression": "dimensional",
      "srid": "27700",
      "pcid": 1,
      "scale_x": 0.001, "offset_x": 352000,
      "scale_y": 0.001, "offset_y": 174000 }
  ]
}

Run it with pdal pipeline load_roundhouse.json. The filters.chipper is what turns a flat point stream into spatially compact patches; filters.reprojection re-anchors a UTM-captured cloud to the site datum before it lands, so the stored patches share the CRS of every other archive layer. Add a spatial index once loaded:

-- PostgreSQL 16 / PostGIS 3.4
CREATE INDEX roundhouse_cloud_gix
  ON roundhouse_cloud USING GIST (Geometry(pa));

Verification

Confirm the load produced patches, the point total survived, and the CRS is right:

-- PostgreSQL 16 / PostGIS 3.4 / pgPointCloud 1.2.5
SELECT count(*)                       AS patches,
       sum(PC_NumPoints(pa))          AS points,
       PC_Get(PC_PatchMin(pa), 'Z')   AS min_z_sample,
       ST_SRID(Geometry(pa))          AS srid
FROM roundhouse_cloud
GROUP BY Geometry(pa)
LIMIT 1;

Better, check the whole-table totals and bounds:

-- PostgreSQL 16 / PostGIS 3.4
SELECT count(*) AS patches, sum(PC_NumPoints(pa)) AS total_points
FROM roundhouse_cloud;

Expected shape of the result for the roundhouse:

 patches | total_points
---------+--------------
  201480 |    80591772

Assert total_points matches the source LAZ header count (pdal info roundhouse.laz --metadata reports it), patches ≈ total_points / capacity, and the SRID equals your target EPSG. To pull real points from a trench, intersect patches with a context polygon and explode:

-- PostgreSQL 16 / PostGIS 3.4
SELECT PC_Get(pt, 'Z') AS z
FROM roundhouse_cloud,
     LATERAL PC_Explode(PC_Intersection(pa, ST_GeomFromText('POLYGON((...))', 27700))) AS pt
LIMIT 5;
Field note. The offset_x and offset_y in the writer must match the offsets in the registered pointcloud_formats schema exactly. If they drift — say the schema says 352000 but the writer says 0 — the load succeeds without error and the points store at a coordinate hundreds of kilometres off, because the scaled integers are decoded against the wrong origin. Copy the numbers from one place to both.

Common Errors & Fixes

  • ERROR: Unable to find pcid X in pointcloud_formats — the writer’s pcid has no matching row, or you loaded before registering the schema. Insert the pointcloud_formats row first, then re-run the pipeline.
  • writers.pgpointcloud: Requested dimension 'Classification' not found in schema — the LAZ lacks a dimension the registered schema demands, or a name mismatch (Classification vs Class). Align the schema dimensions to the actual LAS dimensions from pdal info --schema.
  • filters.chipper: capacity producing one giant patch / out-of-memory — the chipper was omitted or set absurdly high, so PDAL tried to buffer the whole cloud. Keep "capacity": 400 in the pipeline so points are chunked as they stream.

Part of Photogrammetry & 3D Site Mapping Pipelines.