Extension that adds support for manipulating point cloud data using SQL.
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"
}
]
}
The pipeline can be provided either as a JSON file or as an inline JSON string. If the second parameter value starts with "[" and ends with "]", it represents an inline JSON, otherwise it is a file path:
SELECT
COUNT(*)
FROM
PDAL_pipeline('./test/data/autzen_trim.las',
'[
{
"type": "filters.tail",
"count": 10
}
]'
)
;
┌──────────────┐
│ count_star() │
│ int64 │
├──────────────┤
│ 10 │
└──────────────┘
The PDAL_PipelineTable function runs a PDAL pipeline on an input table. It is supposed that the input table
contains columns compatible with PDAL point clouds.
The pipeline can be provided either as a JSON file or as an inline JSON string as well. If the second parameter value starts with "[" and ends with "]", it represents an inline JSON, otherwise it is a file path:
SELECT
*
FROM
PDAL_PipelineTable((SELECT X,Y,Z FROM ...), '[ {"type": "filters.tail", "count": 10} ]')
;
Added Functions
| function_name | function_type | description | comment | examples | |——————–|—————|————-|———|—————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————| | PDAL_Drivers | table | | NULL | [ SELECT name, description FROM PDAL_Drivers();
┌─────────────────────────────┬─────────────────────────────────────────────────────────────────────────────────┐
│ name │ description │
│ varchar │ varchar │
├─────────────────────────────┼─────────────────────────────────────────────────────────────────────────────────┤
│ filters.approximatecoplanar │ Estimates the planarity of a neighborhood of points using eigenvalues. │
│ filters.assign │ Assign values for a dimension range to a specified value. │
│ filters.chipper │ Organize points into spatially contiguous, squarish, and non-overlapping chips. │
│ filters.cluster │ Extract and label clusters using Euclidean distance. │
│ · │ · │
│ · │ · │
│ · │ · │
│ readers.slpk │ SLPK Reader │
│ readers.smrmsg │ SBET smrmsg Reader │
│ readers.stac │ STAC Reader │
│ readers.terrasolid │ TerraSolid Reader │
│ writers.copc │ COPC Writer │
│ writers.gdal │ Write a point cloud as a GDAL raster. │
│ writers.las │ ASPRS LAS 1.0 - 1.4 writer │
│ writers.text │ Text Writer │
├─────────────────────────────┴─────────────────────────────────────────────────────────────────────────────────┤
│ 119 rows 2 columns │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
] | | PDAL_Info | table | | NULL | [
SELECT * FROM PDAL_Info('./test/data/autzen_trim.laz');
] | | PDAL_Pipeline | table | | NULL | [
SELECT * FROM PDAL_Pipeline('path/to/your/filename.las', 'path/to/your/pipeline.json');
SELECT * FROM PDAL_Pipeline('path/to/your/filename.las', '[ {"type": "filters.tail", "count": 100} ]');
] | | PDAL_PipelineTable | table | | NULL | [
SELECT * FROM PDAL_PipelineTable((SELECT * FROM PDAL_Read('path/to/your/filename.las')), 'path/to/your/pipeline.json');
SELECT * FROM PDAL_PipelineTable((SELECT * FROM PDAL_Read('path/to/your/filename.las')), '[ {"type": "filters.tail", "count": 100} ]');
] | | PDAL_Read | table | | NULL | [
SELECT * FROM PDAL_Read('path/to/your/filename.las') LIMIT 10;
┌───────────┬───────────┬────────┐
│ 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 │
└───────────┴───────────┴────────┘
SELECT * FROM PDAL_Read('path/to/your/filename.las', options => MAP {'start': 10});
Optional Options parameter can be used to pass reader-specific options as key-value pairs.
For example, for the LAS/LAZ reader, the options are documented at https://pdal.io/en/stable/stages/readers.las.html#options
] |