Extension that adds support for manipulating point cloud data using SQL.
Maintainer(s):
ahuarte47
Installing and Loading
INSTALL pdal FROM community;
LOAD pdal;
Example
SELECT * FROM PDAL_Read('path/to/your/pointcloud.las');
---
┌───────────┬───────────┬────────┬───────┐
│ X │ Y │ Z │ │
│ double │ double │ double │ ... │
├───────────┼───────────┼────────┤───────┤
│ 637177.98 │ 849393.95 │ 411.19 │ ... │
│ 637177.30 │ 849396.95 │ 411.25 │ ... │
│ 637176.34 │ 849400.84 │ 411.01 │ ... │
│ 637175.45 │ 849404.62 │ 410.99 │ ... │
│ 637174.33 │ 849407.37 │ 411.38 │ ... │
└───────────┴───────────┴────────┴───────┘
WITH __input AS (
SELECT
X, Y, RasterValue
FROM
PDAL_Pipeline('./test/data/overlay-sample.tiff', './test/data/overlay-sample-pipeline.json')
)
SELECT
COUNT(*) AS c,
SUM(RasterValue) AS s
FROM
__input
;
---
┌───────┬──────────┐
│ c │ s │
│ int64 │ double │
├───────┼──────────┤
│ 57600 │ 576000.0 │
└───────┴──────────┘
About pdal
This is an extension for DuckDB for manipulating point cloud data using SQL.
You can use the extension to read point cloud data from various formats (e.g., LAS, LAZ) and perform transformations on them.
The extension is built on top of PDAL (Point Data Abstraction Library), a C++ library that enables users to read, write, and process point cloud data and, with this extension, load the data directly into DuckDB using SQL queries.
SELECT * FROM PDAL_Read('path/to/your/pointcloud.las');
┌───────────┬───────────┬────────┬───────┐
│ X │ Y │ Z │ │
│ double │ double │ double │ ... │
├───────────┼───────────┼────────┤───────┤
│ 637177.98 │ 849393.95 │ 411.19 │ ... │
│ 637177.30 │ 849396.95 │ 411.25 │ ... │
│ 637176.34 │ 849400.84 │ 411.01 │ ... │
│ 637175.45 │ 849404.62 │ 410.99 │ ... │
│ 637174.33 │ 849407.37 │ 411.38 │ ... │
└───────────┴───────────┴────────┴───────┘
PDAL supports data pipelines, you can perform complex transformations before loading data points into DuckDB.
For example, load a raster file, and using filters.overlay, extract attributes from a Geopackage:
WITH __input AS (
SELECT
X, Y, RasterValue
FROM
PDAL_Pipeline('./test/data/overlay-sample.tiff', './test/data/overlay-sample-pipeline.json')
)
SELECT
COUNT(*) AS c,
SUM(RasterValue) AS s
FROM
__input
;
┌───────┬──────────┐
│ c │ s │
│ int64 │ double │
├───────┼──────────┤
│ 57600 │ 576000.0 │
└───────┴──────────┘
Where the pipeline is:
{
"pipeline": [
{
"type": "filters.assign",
"value" : [ "RasterValue = 0.0" ]
},
{
"type": "filters.overlay",
"datasource": "./test/data/overlay-sample.gpkg",
"layer": "area",
"column": "user_data",
"dimension": "RasterValue"
}
]
}