pgPointcloud

https://pgpointcloud.github.io/pointcloud/
Admin
pgPointcloud is an open source PostgreSQL extension for storing and querying LIDAR point cloud data...
Tokens:23,500
Snippets:262
Trust Score:9.7
Update:5 months ago
Show doc for...
Tokens:
Raw
### Install PostGIS Extensions

Source: https://pgpointcloud.github.io/pointcloud/functions/postgis.html

Install the necessary PostGIS and pointcloud extensions before using PostGIS functions with point cloud data.

```sql
CREATE EXTENSION postgis;
CREATE EXTENSION pointcloud;
CREATE EXTENSION pointcloud_postgis;

```

--------------------------------

### List Installed Extensions

Source: https://pgpointcloud.github.io/pointcloud/tutorials/storing.html

Verify that the 'pointcloud' and 'pointcloud_postgis' extensions are installed in the PostgreSQL database. This command is run inside the Docker container.

```bash
$ docker exec -it pgpointcloud psql -U postgres -d pointclouds -c "\dx"

```

--------------------------------

### Install PostgreSQL and PostGIS Debug Symbols

Source: https://pgpointcloud.github.io/pointcloud/development.html

Install debug symbols for PostgreSQL and PostGIS, which may be necessary for comprehensive debugging with tools like Valgrind.

```bash
$ sudo apt-get install postgresql-13-dbgsym postgresql-13-postgis-3-dbgsym

```

--------------------------------

### Run Regression Tests

Source: https://pgpointcloud.github.io/pointcloud/development.html

Execute SQL regression tests against an existing Pointcloud installation. This requires `make install` to be run first and sets environment variables for database connection.

```bash
$ sudo make install
$ PGUSER=a_user PGPASSWORD=a_password PGHOST=localhost make installcheck

```

--------------------------------

### Configure Pointcloud with LAZ Support

Source: https://pgpointcloud.github.io/pointcloud/development.html

Enable LAZ compression support in Pointcloud by specifying the installation path of the laz-perf library during configuration.

```bash
$ ./configure --with-lazperf=/usr/local

```

--------------------------------

### Check pgPointcloud Version

Source: https://pgpointcloud.github.io/pointcloud/update.html

Use `pc_version()` to check the currently installed version and query `pg_available_extension_versions` to see available versions.

```sql
SELECT pc_version();
--> 1.1.1

SELECT version FROM pg_available_extension_versions WHERE name ='pointcloud';
--> 1.1.1
--> 1.2.1
```

--------------------------------

### Build and Install laz-perf Library

Source: https://pgpointcloud.github.io/pointcloud/development.html

Instructions for building the laz-perf library from source, required for LAZ compression support in Pointcloud. Ensure you checkout the correct version.

```bash
$ git clone https://github.com/hobuinc/laz-perf
$ cd laz-perf
$ git checkout 1.5.0
$ cmake .
$ make
$ make install

```

--------------------------------

### Get PcPatch Memory Size

Source: https://pgpointcloud.github.io/pointcloud/functions/patchs.html

Use PC_MemSize to retrieve the memory footprint of a PcPatch in bytes. This example calculates the size of a patch containing a single point.

```sql
SELECT PC_MemSize(PC_Patch(PC_MakePoint(1, ARRAY[-127, 45, 124.0, 4.0])));

161
```

--------------------------------

### Run pgPointcloud Docker Container

Source: https://pgpointcloud.github.io/pointcloud/tutorials/storing.html

Start a Docker container for pgPointcloud, specifying the database name and password. Extensions like 'pointcloud' and 'pointcloud_postgis' are automatically created.

```bash
$ docker run --name pgpointcloud -e POSTGRES_DB=pointclouds -e POSTGRES_PASSWORD=mysecretpassword -d pgpointcloud/pointcloud

```

--------------------------------

### Get Full System Version

Source: https://pgpointcloud.github.io/pointcloud/functions/utils.html

Returns a composite version string of Point Cloud system components. Useful for debugging or verifying runtime compatibility.

```sql
SELECT PC_Full_Version();

POINTCLOUD="1.2.5 2346cc2" PGSQL="170" LIBXML2="2.14.3 LAZPERF enabled=false

```

--------------------------------

### Configure Pointcloud with Specific PostgreSQL

Source: https://pgpointcloud.github.io/pointcloud/development.html

Use this option when multiple PostgreSQL installations exist to target a specific pg_config executable during the configure step.

```bash
$ ./configure --with-pgconfig=/usr/lib/postgresql/12/bin/pg_config

```

--------------------------------

### Create GIST Index on Bounding Diagonal

Source: https://pgpointcloud.github.io/pointcloud/functions/postgis.html

Example of creating a GIST index on the bounding diagonal geometry of a patch column for efficient spatial queries.

```sql
CREATE INDEX ON patches USING GIST(PC_BoundingDiagonalGeometry(patch) gist_geometry_ops_nd);

```

--------------------------------

### Get PostgreSQL Version

Source: https://pgpointcloud.github.io/pointcloud/functions/utils.html

Returns the PostgreSQL version number. This is important for ensuring compatibility with the database server.

```sql
SELECT PC_PGSQL_Version();

170

```

--------------------------------

### Create GIST Index on Envelope Geometry

Source: https://pgpointcloud.github.io/pointcloud/functions/postgis.html

Example of creating a GIST index on the envelope geometry of a patch column for efficient spatial queries.

```sql
CREATE INDEX ON patches USING GIST(PC_EnvelopeGeometry(patch));

```

--------------------------------

### Get Extension Version

Source: https://pgpointcloud.github.io/pointcloud/functions/utils.html

Returns the main extension version number. This is the primary identifier for the Point Cloud extension version.

```sql
SELECT PC_Version();

1.2.5

```

--------------------------------

### Get Script Version

Source: https://pgpointcloud.github.io/pointcloud/functions/utils.html

Returns the script version number. This may refer to associated scripting components or utilities.

```sql
SELECT PC_Script_Version();

1.2.5

```

--------------------------------

### Get libxml2 Version

Source: https://pgpointcloud.github.io/pointcloud/functions/utils.html

Returns the libxml2 version number. This is useful for checking compatibility with the underlying XML parsing library.

```sql
SELECT PC_LibXML2_Version();

2.14.3

```

--------------------------------

### Get Average Patch Data (Compressed)

Source: https://pgpointcloud.github.io/pointcloud/tutorials/compression.html

Retrieve the average patch data from the original 'airport' table using 'pc_astext' and 'pc_patchavg'.

```sql
SELECT pc_astext(pc_patchavg(pa)) FROM airport LIMIT 1;
```

--------------------------------

### Get Minimum Dimension Values as PcPoint

Source: https://pgpointcloud.github.io/pointcloud/functions/patchs.html

Returns a PcPoint containing the minimum values for each dimension within the patch.

```sql
SELECT PC_PatchMin(pa)
FROM patches WHERE id = 7;

{"pcid":1,"pt":[-126.5,45.5,50,5]}
```

--------------------------------

### PDAL Pipeline: LAS to PostgreSQL Pointcloud

Source: https://pgpointcloud.github.io/pointcloud/quickstart.html

This pipeline reads a LAS file, chips the data into patches, and writes it to a PostgreSQL Pointcloud database. Ensure PDAL and pgPointcloud are installed.

```json
{
  "pipeline":[
    {
      "type":"readers.las",
      "filename":"/home/lidar/st-helens-small.las",
      "spatialreference":"EPSG:26910"
    },
    {
      "type":"filters.chipper",
      "capacity":400
    },
    {
      "type":"writers.pgpointcloud",
      "connection":"host='localhost' dbname='pc' user='lidar' password='lidar' port='5432'",
      "table":"sthsm",
      "compression":"dimensional",
      "srid":"26910"
    }
  ]
}

```

--------------------------------

### Get Average Patch Data (Uncompressed)

Source: https://pgpointcloud.github.io/pointcloud/tutorials/compression.html

Retrieve the average patch data from the 'airport_uncompressed' table using 'pc_astext' and 'pc_patchavg' to compare with the compressed version.

```sql
SELECT pc_astext(pc_patchavg(pa)) FROM airport_uncompressed LIMIT 1;
```

--------------------------------

### Convert PcPatch to JSON Text

Source: https://pgpointcloud.github.io/pointcloud/functions/patchs.html

Use PC_AsText to get a JSON representation of a PcPatch. Useful for inspecting patch data.

```sql
SELECT PC_AsText(pa) FROM patches LIMIT 1;

{"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],
 [-126.93,45.07,7,0],[-126.92,45.08,8,0],[-126.91,45.09,9,0]
]}
```

--------------------------------

### Get Library Version

Source: https://pgpointcloud.github.io/pointcloud/functions/utils.html

Returns the library version number of the Point Cloud extension. This helps in identifying the specific version of the core library in use.

```sql
SELECT PC_Lib_Version();

1.2.5 2346cc2

```

--------------------------------

### Get PostGIS Version

Source: https://pgpointcloud.github.io/pointcloud/functions/utils.html

Returns the PostGIS extension version number. This is useful for checking compatibility with the PostGIS extension.

```sql
SELECT PC_PostGIS_Version();

1.2.5

```

--------------------------------

### Get Memory Size of PcPoint

Source: https://pgpointcloud.github.io/pointcloud/functions/points.html

Use PC_MemSize to determine the memory footprint of a pcpoint. This can be useful for performance analysis.

```sql
SELECT PC_MemSize(PC_MakePoint(1, ARRAY[-127, 45, 124.0, 4.0]));
```

--------------------------------

### Aggregate Points into PcPatch

Source: https://pgpointcloud.github.io/pointcloud/functions/patchs.html

Use PC_Patch as an aggregate function to collect a set of pcpoint records into a single PcPatch. This example inserts newly created patches into a table.

```sql
INSERT INTO patches (pa)
SELECT PC_Patch(pt) FROM points GROUP BY id/10;
```

--------------------------------

### Build Documentation

Source: https://pgpointcloud.github.io/pointcloud/development.html

Compile the HTML documentation using Sphinx. Navigate to the doc directory and run `make html`.

```bash
$ cd doc && make html

```

--------------------------------

### Build Pointcloud from Sources

Source: https://pgpointcloud.github.io/pointcloud/development.html

Standard build process for compiling Pointcloud from its source code. Ensure all dependencies are met before running.

```bash
$ ./autogen.sh
$ ./configure
$ make
$ sudo make install

```

--------------------------------

### Configure PostgreSQL Connection Service File

Source: https://pgpointcloud.github.io/pointcloud/tutorials/storing.html

Set up a PostgreSQL connection service file to simplify connecting to the database. This allows using a service name instead of a full connection string.

```ini
[pgpointcloud]
host=172.17.0.2
port=5432
dbname=pointclouds
user=postgres
password=mysecretpassword

```

--------------------------------

### Run Unit Tests

Source: https://pgpointcloud.github.io/pointcloud/development.html

Execute the unit tests for Pointcloud using the `make check` command.

```bash
$ make check

```

--------------------------------

### PC_Range

Source: https://pgpointcloud.github.io/pointcloud/functions/patchs.html

Extracts a sub-patch containing a specified number of points, starting from a given point index.

```APIDOC
## PC_Range(p pcpatch, start int4, n int4) returns pcpatch

Returns a patch containing n points. These points are selected from the start-th point with 1-based indexing.
```

--------------------------------

### Build Docker Image

Source: https://pgpointcloud.github.io/pointcloud/development.html

Build the Pointcloud Docker image using the provided Dockerfile. This command tags the image as `pgpointcloud`.

```bash
$ docker build --rm -t pgpointcloud docker/

```

--------------------------------

### Get Maximum Dimension Values as PcPoint

Source: https://pgpointcloud.github.io/pointcloud/functions/patchs.html

Returns a PcPoint containing the maximum values for each dimension within the patch.

```sql
SELECT PC_PatchMax(pa)
FROM patches WHERE id = 7;

{"pcid":1,"pt":[-126.41,45.59,59,5]}
```

--------------------------------

### Get Average Dimension Values as PcPoint

Source: https://pgpointcloud.github.io/pointcloud/functions/patchs.html

Returns a PcPoint containing the average values for each dimension within the patch.

```sql
SELECT PC_AsText(PC_PatchAvg(pa))
FROM patches WHERE id = 7;

{"pcid":1,"pt":[-126.46,45.54,54.5,5]}
```

--------------------------------

### PC_AsBinary

Source: https://pgpointcloud.github.io/pointcloud/functions/wkb.html

Returns the OGC Well-Known Binary (WKB) format for a given point.

```APIDOC
## PC_AsBinary

### Description
Returns the OGC "well-known binary" format for the point.

### Signature
PC_AsBinary(p pcpoint) returns bytea

### Example
```sql
SELECT PC_AsBinary('010100000064CEFFFF94110000703000000400'::pcpoint);
-- \x01010000800000000000c05fc000000000008046400000000000005f40
```
```

--------------------------------

### Download Sample Point Cloud Data

Source: https://pgpointcloud.github.io/pointcloud/tutorials/storing.html

Download a sample LAS file for use in the PDAL pipeline. The file is saved to the /tmp directory.

```bash
$ wget https://github.com/PDAL/data/blob/main/liblas/LAS12_Sample_withRGB_Quick_Terrain_Modeler_fixed.laz -P /tmp

```

--------------------------------

### PC_Full_Version

Source: https://pgpointcloud.github.io/pointcloud/functions/utils.html

Returns a composite version string summarizing pointcloud system components, including library, SQL, libxml2, PostgreSQL interface versions, and LAZperf support flag. Useful for debugging or verifying runtime compatibility.

```APIDOC
## PC_Full_Version

### Description
Returns a composite version string summarizing pointcloud system components. Includes library, SQL, libxml2, PostgreSQL interface versions, and LAZperf support flag. Useful for debugging or verifying runtime compatibility of the pointcloud extension.

### Method
SQL Function Call

### Endpoint
N/A

### Parameters
None

### Request Example
```sql
SELECT PC_Full_Version();
```

### Response
#### Success Response (text)
- Returns a string containing version information.

#### Response Example
```
POINTCLOUD="1.2.5 2346cc2" PGSQL="170" LIBXML2="2.14.3 LAZPERF enabled=false
```
```

--------------------------------

### Create Git Tag for Release

Source: https://pgpointcloud.github.io/pointcloud/development.html

Create and push a Git tag for a new Pointcloud release. Replace `vx.y.z` with the actual version number.

```bash
$ git tag -a vx.y.z -m 'version x.y.z'
$ git push origin vx.y.z

```

--------------------------------

### Check PcPatch Intersection

Source: https://pgpointcloud.github.io/pointcloud/functions/patchs.html

Use PC_Intersects to determine if the bounding boxes of two PcPatches overlap. This example checks if a patch intersects with itself.

```sql
-- Patch should intersect itself
SELECT PC_Intersects(
  '01010000000000000001000000C8CEFFFFF8110000102700000A00'::pcpatch,
  '01010000000000000001000000C8CEFFFFF8110000102700000A00'::pcpatch);

t
```

--------------------------------

### Convert Point to WKB

Source: https://pgpointcloud.github.io/pointcloud/functions/wkb.html

Returns the OGC Well-Known Binary (WKB) format for a given point. Use this to represent point data in a standardized binary format.

```sql
SELECT PC_AsBinary('010100000064CEFFFF94110000703000000400'::pcpoint);
```

```text
\x01010000800000000000c05fc000000000008046400000000000005f40
```

--------------------------------

### Construct PcPoint from Schema and Values

Source: https://pgpointcloud.github.io/pointcloud/functions/points.html

Use PC_MakePoint to create a new pcpoint. Provide a valid pcid schema number and an array of doubles matching the schema.

```sql
SELECT PC_MakePoint(1, ARRAY[-127, 45, 124.0, 4.0]);
```

--------------------------------

### Get Patch Envelope Geometry

Source: https://pgpointcloud.github.io/pointcloud/functions/postgis.html

Returns the 2D bounds of a patch as a PostGIS Polygon. Useful for 2D intersection tests with other PostGIS geometries.

```sql
SELECT ST_AsText(PC_EnvelopeGeometry(pa)) FROM patches LIMIT 1;

```

--------------------------------

### Verify Compression Type in New Table

Source: https://pgpointcloud.github.io/pointcloud/tutorials/compression.html

Confirm that patches in the newly created 'airport_uncompressed' table have no compression by checking their JSON summary.

```sql
SELECT pc_summary(pa)::json->'compr' FROM airport_uncompressed LIMIT 1;
```

--------------------------------

### Convert PcPoint to JSON Text

Source: https://pgpointcloud.github.io/pointcloud/functions/points.html

Use PC_AsText to get a JSON representation of a pcpoint's data. This is useful for inspecting point attributes.

```sql
SELECT PC_AsText('010100000064CEFFFF94110000703000000400'::pcpoint);
```

--------------------------------

### Configure Pointcloud with Embedded Qsort

Source: https://pgpointcloud.github.io/pointcloud/development.html

Enable this directive if your system lacks the `qsort_r` function, utilizing Pointcloud's embedded implementation instead.

```bash
$ ./configure CFLAGS="-DNESTED_QSORT=1"

```

--------------------------------

### Get PcId of PcPatch

Source: https://pgpointcloud.github.io/pointcloud/functions/patchs.html

Use PC_PCId to retrieve the schema identifier (pcid) for the points within a PcPatch. This indicates the dimensionality and data structure of the points.

```sql
SELECT PC_PCId(pa) FROM patches LIMIT 1;

1
```

--------------------------------

### Create Pointcloud Tables

Source: https://pgpointcloud.github.io/pointcloud/concepts/tables.html

Use these SQL statements to create tables for storing point cloud data. Ensure the pcid exists in your schema.

```sql
-- This example requires the schema entry from the previous
-- section to be loaded so that pcid==1 exists.

-- A table of points
CREATE TABLE points (
    id SERIAL PRIMARY KEY,
    pt PCPOINT(1)
);

-- A table of patches
CREATE TABLE patches (
    id SERIAL PRIMARY KEY,
    pa PCPATCH(1)
);
```

--------------------------------

### PC_BoundingDiagonalAsBinary

Source: https://pgpointcloud.github.io/pointcloud/functions/wkb.html

Returns the OGC Well-Known Binary (WKB) format for the bounding diagonal of a point cloud patch.

```APIDOC
## PC_BoundingDiagonalAsBinary

### Description
Returns the OGC "well-known binary" format for the bounding diagonal of the patch.

### Signature
PC_BoundingDiagonalAsBinary(p pcpatch) returns bytea

### Example
```sql
SELECT PC_BoundingDiagonalAsBinary( PC_Patch(ARRAY[ PC_MakePoint(1, ARRAY[0.,0.,0.,10.]), PC_MakePoint(1, ARRAY[1.,1.,1.,10.]), PC_MakePoint(1, ARRAY[10.,10.,10.,10.])]));
-- \x01020000a0e610000002000000000000000000000000000000000000000000000000000000000000000000244000000000000024400000000000002440
```
```

--------------------------------

### Get Docker Container IP Address

Source: https://pgpointcloud.github.io/pointcloud/tutorials/storing.html

Retrieve the IP address of the running pgPointcloud Docker container. This is needed to configure the PDAL pipeline connection.

```bash
$ docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' pgpointcloud

```

--------------------------------

### Create Table of Uncompressed Patches

Source: https://pgpointcloud.github.io/pointcloud/tutorials/compression.html

Create a new table 'airport_uncompressed' by transforming all patches from the 'airport' table to use the uncompressed schema (pcid=2).

```sql
CREATE TABLE airport_uncompressed AS SELECT pc_transform(pa, 2) AS pa FROM airport;
```

--------------------------------

### Convert Bounding Diagonal to WKB

Source: https://pgpointcloud.github.io/pointcloud/functions/wkb.html

Returns the OGC Well-Known Binary (WKB) format for the bounding diagonal of a patch. This is useful for spatial indexing and queries.

```sql
SELECT PC_BoundingDiagonalAsBinary( PC_Patch(ARRAY[ PC_MakePoint(1, ARRAY[0.,0.,0.,10.]), PC_MakePoint(1, ARRAY[1.,1.,1.,10.]), PC_MakePoint(1, ARRAY[10.,10.,10.,10.])]));
```

```text
\x01020000a0e610000002000000000000000000000000000000000000000000000000000000000000000000244000000000000024400000000000002440
```

--------------------------------

### PC_EnvelopeAsBinary

Source: https://pgpointcloud.github.io/pointcloud/functions/wkb.html

Returns the OGC Well-Known Binary (WKB) format for the 2D bounds of a point cloud patch.

```APIDOC
## PC_EnvelopeAsBinary

### Description
Returns the OGC "well-known binary" format for the 2D bounds of the patch. Useful for performing 2D intersection tests with geometries.

### Signature
PC_EnvelopeAsBinary(p pcpatch) returns bytea

### Example
```sql
SELECT PC_EnvelopeAsBinary(pa) FROM patches LIMIT 1;
-- \x0103000000010000000500000090c2f5285cbf5fc0e17a
-- 14ae4781464090c2f5285cbf5fc0ec51b81e858b46400ad7
-- a3703dba5fc0ec51b81e858b46400ad7a3703dba5fc0e17a
-- 90c2f5285cbf5fc0e17a14ae47814640
```

`PC_Envelope` is an alias to `PC_EnvelopeAsBinary`. But `PC_Envelope` is deprecated and will be removed in a future version (2.0) of the extension. `PC_EnvelopeAsBinary` is to be used instead.
```

--------------------------------

### Get Patch Bounding Diagonal Geometry

Source: https://pgpointcloud.github.io/pointcloud/functions/postgis.html

Returns the bounding diagonal of a patch as a LineString geometry. This is useful for creating spatial indexes on patch data.

```sql
SELECT ST_AsText(PC_BoundingDiagonalGeometry(pa)) FROM patches;

```

--------------------------------

### Retrieve PCID Schema Number from PcPoint

Source: https://pgpointcloud.github.io/pointcloud/functions/points.html

Use PC_PCId to get the pcid schema number associated with a point. This function is available from version 1.1.0.

```sql
SELECT PC_PCId('010100000064CEFFFF94110000703000000400'::pcpoint);
```

--------------------------------

### Get Number of Dimensions for a Schema

Source: https://pgpointcloud.github.io/pointcloud/functions/schema.html

Use PC_SchemaGetNDims to retrieve the number of dimensions associated with a given schema ID. Ensure the schema ID exists.

```sql
SELECT PC_SchemaGetNDims(1);

18

```

--------------------------------

### Insert Multiple Points using PC_MakePoint

Source: https://pgpointcloud.github.io/pointcloud/functions/points.html

This snippet demonstrates inserting multiple points into a 'points' table using PC_MakePoint within a data generation loop. It constructs points with varying coordinates and intensity.

```sql
INSERT INTO points (pt)
SELECT PC_MakePoint(1, ARRAY[x,y,z,intensity])
FROM (
  SELECT
  -127+a/100.0 AS x,
    45+a/100.0 AS y,
         1.0*a AS z,
          a/10 AS intensity
  FROM generate_series(1,100) AS a
) AS values;
```

--------------------------------

### Clone Source Repository

Source: https://pgpointcloud.github.io/pointcloud/install.html

Retrieve the development version of the source code from the GitHub repository using Git.

```bash
$ git clone https://github.com/pgpointcloud/pointcloud/

```

--------------------------------

### Compile pgPointcloud with Debug Symbols

Source: https://pgpointcloud.github.io/pointcloud/development.html

Compile the extension with debug symbols and without optimizations for effective memory management checks with Valgrind.

```bash
$ ./configure CFLAGS="-O0 -g"
$ make
$ sudo make install

```

--------------------------------

### Convert Patch Envelope to WKB

Source: https://pgpointcloud.github.io/pointcloud/functions/wkb.html

Returns the OGC Well-Known Binary (WKB) format for the 2D bounds of a patch. This is particularly useful for performing 2D intersection tests with geometries.

```sql
SELECT PC_EnvelopeAsBinary(pa) FROM patches LIMIT 1;
```

```text
\x0103000000010000000500000090c2f5285cbf5fc0e17a
14ae4781464090c2f5285cbf5fc0ec51b81e858b46400ad7
a3703dba5fc0ec51b81e858b46400ad7a3703dba5fc0e17a
90c2f5285cbf5fc0e17a14ae47814640
```

--------------------------------

### Get Number of Points in PcPatch

Source: https://pgpointcloud.github.io/pointcloud/functions/patchs.html

Use PC_NumPoints to count the total number of points contained within a PcPatch. This is useful for quick data size estimations.

```sql
SELECT PC_NumPoints(pa) FROM patches LIMIT 1;

9
```

--------------------------------

### Run Valgrind with PostgreSQL Single-User Mode

Source: https://pgpointcloud.github.io/pointcloud/development.html

Execute a SQL query in single-user mode with Valgrind to check for memory leaks and track origin of variables. The report will be saved to /tmp/valgrind.log.

```bash
$ echo "select pc_transform(patch, 1) from patchs limit 1" | \
  valgrind -s --track-origins=yes --leak-check=yes \
    --show-leak-kinds=all --read-var-info=yes --log-file=/tmp/valgrind.log \
    /usr/lib/postgresql/13/bin/postgres --single -D /var/lib/postgresql/13/main \
    -c config_file=/etc/postgresql/13/main/postgresql.conf \
    mydatabase

```

--------------------------------

### WKB Functions

Source: https://pgpointcloud.github.io/pointcloud/functions/index.html

Functions for Well-Known Binary (WKB) representation of Pointcloud data.

```APIDOC
## PC_AsBinary

### Description
Converts a Pointcloud to its WKB representation.

### Method
N/A (Function Call)

### Endpoint
N/A

### Parameters
None explicitly documented.

### Request Example
N/A

### Response
N/A

## PC_BoundingDiagonalAsBinary

### Description
Gets the bounding diagonal of a Pointcloud as WKB.

### Method
N/A (Function Call)

### Endpoint
N/A

### Parameters
None explicitly documented.

### Request Example
N/A

### Response
N/A

## PC_EnvelopeAsBinary

### Description
Gets the envelope of a Pointcloud as WKB.

### Method
N/A (Function Call)

### Endpoint
N/A

### Parameters
None explicitly documented.

### Request Example
N/A

### Response
N/A
```

--------------------------------

### PDAL Pipeline Configuration

Source: https://pgpointcloud.github.io/pointcloud/tutorials/storing.html

Configure a PDAL pipeline to read a LAS file and write it to the PostgreSQL database using the pgpointcloud writer. Ensure the connection string uses the correct Docker container IP address.

```json
{
  "pipeline":[
    {
      "type":"readers.las",
      "filename":"/tmp/LAS12_Sample_withRGB_Quick_Terrain_Modeler_fixed.laz"
    },
    {
      "type":"filters.chipper",
      "capacity":"400"
    },
    {
      "type":"writers.pgpointcloud",
      "connection":"host='172.17.0.2' dbname='pointclouds' user='postgres' password='mysecretpassword' port='5432'",
      "table":"airport",
      "compression":"dimensional",
      "srid":"32616"
    }
  ]
}

```

--------------------------------

### Retrieve Compression Type from JSON Summary

Source: https://pgpointcloud.github.io/pointcloud/tutorials/compression.html

Easily get the compression type by accessing the 'compr' field in the JSON summary of a patch. This is a more straightforward method than parsing the XML schema.

```sql
SELECT pc_summary(pa)::json->'compr' FROM airport LIMIT 1;
```

--------------------------------

### Check LAZperf Compression Support

Source: https://pgpointcloud.github.io/pointcloud/functions/utils.html

Returns true if the Point Cloud extension includes LAZperf compression support. Use this to determine if LAZperf is available for compression operations.

```sql
SELECT PC_Lazperf_Enabled();

t

```

--------------------------------

### Execute PDAL Pipeline

Source: https://pgpointcloud.github.io/pointcloud/quickstart.html

Use the 'pdal pipeline' command to execute a JSON pipeline file for processing LIDAR data.

```bash
$ pdal pipeline --input pipelinefile.json

```

--------------------------------

### PC_MakePoint

Source: https://pgpointcloud.github.io/pointcloud/functions/points.html

Constructs a new point from a schema ID and dimension values.

```APIDOC
## PC_MakePoint

### Description
Given a valid pcid schema number and an array of doubles that matches the schema, construct a new pcpoint.

### Method
SQL Function

### Parameters
#### Path Parameters
- **pcid** (integer) - Required - The point cloud schema ID.
- **vals** (float8[]) - Required - An array of double-precision floating-point numbers representing the dimension values.

### Response
#### Success Response (pcpoint)
- Returns a `pcpoint` data type.

### Request Example
```sql
SELECT PC_MakePoint(1, ARRAY[-127, 45, 124.0, 4.0]);
```

### Response Example
```sql
010100000064CEFFFF94110000703000000400
```

### Usage Example (INSERT)
```sql
INSERT INTO points (pt)
SELECT PC_MakePoint(1, ARRAY[x,y,z,intensity])
FROM (
  SELECT
  -127+a/100.0 AS x,
    45+a/100.0 AS y,
         1.0*a AS z,
          a/10 AS intensity
  FROM generate_series(1,100) AS a
) AS values;
```
```

--------------------------------

### PC_MakePatch

Source: https://pgpointcloud.github.io/pointcloud/functions/patchs.html

Constructs a new PcPatch from a given PCID and an array of double-precision floating-point values.

```APIDOC
## PC_MakePatch

### Description
Given a valid pcid schema number and an array of doubles that matches the schema, construct a new pcpatch. Array size must be a multiple of the number of dimensions.

### Signature
PC_MakePatch(pcid integer, vals float8[]) returns pcpatch

### Example
```sql
SELECT PC_AsText(PC_MakePatch(1, ARRAY[-126.99,45.01,1,0, -126.98,45.02,2,0, -126.97,45.03,3,0]));
```

### Response Example
```json
{
  "pcid":1,
  "pts":[
 [-126.99,45.01,1,0],[-126.98,45.02,2,0],[-126.97,45.03,3,0]
]
}
```
```

--------------------------------

### PC_Summary

Source: https://pgpointcloud.github.io/pointcloud/functions/patchs.html

Generates a JSON-formatted summary of the data within a pointcloud patch, including schema information, compression details, and dimension statistics.

```APIDOC
## PC_Summary(p pcpatch) returns text (from 1.1.0)

Returns a JSON formatted summary of the data in that point.

### Example
```sql
SELECT PC_Summary(pa) FROM patches LIMIT 1;
```
```

--------------------------------

### PC_Version

Source: https://pgpointcloud.github.io/pointcloud/functions/utils.html

Returns the main extension version number for Pointcloud.

```APIDOC
## PC_Version

### Description
Return the extension version number.

### Method
SQL Function Call

### Endpoint
N/A

### Parameters
None

### Request Example
```sql
SELECT PC_Version();
```

### Response
#### Success Response (text)
- Returns the extension version string.

#### Response Example
```
1.2.5
```
```

--------------------------------

### PcPatch Functions

Source: https://pgpointcloud.github.io/pointcloud/functions/index.html

Functions for manipulating Pointcloud patches.

```APIDOC
## PC_AsText

### Description
Converts a Pointcloud patch to its text representation.

### Method
N/A (Function Call)

### Endpoint
N/A

### Parameters
None explicitly documented.

### Request Example
N/A

### Response
N/A

## PC_Compress

### Description
Compresses a Pointcloud patch.

### Method
N/A (Function Call)

### Endpoint
N/A

### Parameters
None explicitly documented.

### Request Example
N/A

### Response
N/A

## PC_Explode

### Description
Explodes a Pointcloud patch into its constituent points.

### Method
N/A (Function Call)

### Endpoint
N/A

### Parameters
None explicitly documented.

### Request Example
N/A

### Response
N/A

## PC_FilterBetween

### Description
Filters points within a Pointcloud patch based on a range.

### Method
N/A (Function Call)

### Endpoint
N/A

### Parameters
None explicitly documented.

### Request Example
N/A

### Response
N/A

## PC_FilterEquals

### Description
Filters points in a Pointcloud patch for equality.

### Method
N/A (Function Call)

### Endpoint
N/A

### Parameters
None explicitly documented.

### Request Example
N/A

### Response
N/A

## PC_FilterGreaterThan

### Description
Filters points in a Pointcloud patch greater than a value.

### Method
N/A (Function Call)

### Endpoint
N/A

### Parameters
None explicitly documented.

### Request Example
N/A

### Response
N/A

## PC_FilterLessThan

### Description
Filters points in a Pointcloud patch less than a value.

### Method
N/A (Function Call)

### Endpoint
N/A

### Parameters
None explicitly documented.

### Request Example
N/A

### Response
N/A

## PC_Intersects

### Description
Checks if a Pointcloud patch intersects with another geometry.

### Method
N/A (Function Call)

### Endpoint
N/A

### Parameters
None explicitly documented.

### Request Example
N/A

### Response
N/A

## PC_IsSorted

### Description
Checks if a Pointcloud patch is sorted.

### Method
N/A (Function Call)

### Endpoint
N/A

### Parameters
None explicitly documented.

### Request Example
N/A

### Response
N/A

## PC_MakePatch

### Description
Creates a Pointcloud patch.

### Method
N/A (Function Call)

### Endpoint
N/A

### Parameters
None explicitly documented.

### Request Example
N/A

### Response
N/A

## PC_MemSize

### Description
Returns the memory size of a Pointcloud patch.

### Method
N/A (Function Call)

### Endpoint
N/A

### Parameters
None explicitly documented.

### Request Example
N/A

### Response
N/A

## PC_NumPoints

### Description
Returns the number of points in a Pointcloud patch.

### Method
N/A (Function Call)

### Endpoint
N/A

### Parameters
None explicitly documented.

### Request Example
N/A

### Response
N/A

## PC_PCId

### Description
Retrieves the Pointcloud ID associated with a patch.

### Method
N/A (Function Call)

### Endpoint
N/A

### Parameters
None explicitly documented.

### Request Example
N/A

### Response
N/A

## PC_Patch

### Description
Retrieves a Pointcloud patch.

### Method
N/A (Function Call)

### Endpoint
N/A

### Parameters
None explicitly documented.

### Request Example
N/A

### Response
N/A

## PC_PatchAvg

### Description
Calculates the average of points in a Pointcloud patch.

### Method
N/A (Function Call)

### Endpoint
N/A

### Parameters
None explicitly documented.

### Request Example
N/A

### Response
N/A

## PC_PatchMax

### Description
Finds the maximum point in a Pointcloud patch.

### Method
N/A (Function Call)

### Endpoint
N/A

### Parameters
None explicitly documented.

### Request Example
N/A

### Response
N/A

## PC_PatchMin

### Description
Finds the minimum point in a Pointcloud patch.

### Method
N/A (Function Call)

### Endpoint
N/A

### Parameters
None explicitly documented.

### Request Example
N/A

### Response
N/A

## PC_PointN

### Description
Retrieves the Nth point from a Pointcloud patch.

### Method
N/A (Function Call)

### Endpoint
N/A

### Parameters
None explicitly documented.

### Request Example
N/A

### Response
N/A

## PC_Range

### Description
Calculates the range of a Pointcloud patch.

### Method
N/A (Function Call)

### Endpoint
N/A

### Parameters
None explicitly documented.

### Request Example
N/A

### Response
N/A

## PC_SetPCId

### Description
Sets the Pointcloud ID for a patch.

### Method
N/A (Function Call)

### Endpoint
N/A

### Parameters
None explicitly documented.

### Request Example
N/A

### Response
N/A

## PC_Sort

### Description
Sorts a Pointcloud patch.

### Method
N/A (Function Call)

### Endpoint
N/A

### Parameters
None explicitly documented.

### Request Example
N/A

### Response
N/A

## PC_Summary

### Description
Provides a summary of a Pointcloud patch.

### Method
N/A (Function Call)

### Endpoint
N/A

### Parameters
None explicitly documented.

### Request Example
N/A

### Response
N/A

## PC_Transform

### Description
Transforms a Pointcloud patch.

### Method
N/A (Function Call)

### Endpoint
N/A

### Parameters
None explicitly documented.

### Request Example
N/A

### Response
N/A

## PC_Uncompress

### Description
Uncompresses a Pointcloud patch.

### Method
N/A (Function Call)

### Endpoint
N/A

### Parameters
None explicitly documented.

### Request Example
N/A

### Response
N/A

## PC_Union

### Description
Computes the union of Pointcloud patches.

### Method
N/A (Function Call)

### Endpoint
N/A

### Parameters
None explicitly documented.

### Request Example
N/A

### Response
N/A
```

--------------------------------

### PC_LibXML2_Version

Source: https://pgpointcloud.github.io/pointcloud/functions/utils.html

Returns the libxml2 version number used by the pointcloud extension.

```APIDOC
## PC_LibXML2_Version

### Description
Return the libxml2 version number.

### Method
SQL Function Call

### Endpoint
N/A

### Parameters
None

### Request Example
```sql
SELECT PC_LibXML2_Version();
```

### Response
#### Success Response (text)
- Returns the libxml2 version string.

#### Response Example
```
2.14.3
```
```

--------------------------------

### PC_Get

Source: https://pgpointcloud.github.io/pointcloud/functions/points.html

Retrieves point dimension values.

```APIDOC
## PC_Get

### Description
Retrieves values of all dimensions in an array or the numeric value of a specific named dimension.

### Method
SQL Function

### Parameters
#### Path Parameters
- **pt** (pcpoint) - Required - The point from which to retrieve values.
- **dimname** (text) - Optional - The name of the dimension to retrieve. If not provided, all dimension values are returned.

### Response
#### Success Response (float8[] or numeric)
- Returns an array of float8 if `dimname` is not specified.
- Returns a numeric value if `dimname` is specified.

### Request Example (all dimensions)
```sql
SELECT PC_Get('010100000064CEFFFF94110000703000000400'::pcpoint);
```

### Response Example (all dimensions)
```json
{-127,45,124,4}
```

### Request Example (specific dimension)
```sql
SELECT PC_Get('010100000064CEFFFF94110000703000000400'::pcpoint, 'Intensity');
```

### Response Example (specific dimension)
```json
4
```
```

--------------------------------

### Render PcPatch as Text

Source: https://pgpointcloud.github.io/pointcloud/concepts/objects.html

Use PC_AsText(pcpatch) to render a PcPatch in a human-readable JSON format. This is useful for representing groups of points stored efficiently in the database.

```json
{
    "pcid" : 1,
     "pts" : [
              [0.02, 0.03, 0.05, 6],
              [0.02, 0.03, 0.05, 8]
             ]
}

```

--------------------------------

### PDAL Pipeline: PostgreSQL Pointcloud to CSV

Source: https://pgpointcloud.github.io/pointcloud/quickstart.html

This pipeline reads data from a PostgreSQL Pointcloud table and writes it to a CSV text file. Data is read directly without needing to re-chip.

```json
{
  "pipeline":[
    {
      "type":"readers.pgpointcloud",
      "connection":"host='localhost' dbname='pc' user='lidar' password='lidar' port='5432'",
      "table":"sthsm",
      "column":"pa",
      "spatialreference":"EPSG:26910"
    },
    {
      "type":"writers.text",
      "filename":"/home/lidar/st-helens-small-out.txt"
    }
  ]
}

```

--------------------------------

### Create New Schema Without Compression

Source: https://pgpointcloud.github.io/pointcloud/tutorials/compression.html

Insert a new schema into the 'pointcloud_formats' table by duplicating an existing schema (pcid=1) and modifying the compression type to 'none'.

```sql
INSERT INTO pointcloud_formats (pcid, srid, schema)
SELECT 2, srid, regexp_replace(schema, dimensional, none, g)
FROM pointcloud_formats
WHERE pcid=1;
```

--------------------------------

### PC_AsText

Source: https://pgpointcloud.github.io/pointcloud/functions/patchs.html

Returns a JSON representation of the data contained within a PcPatch.

```APIDOC
## PC_AsText

### Description
Returns a JSON version of the data in that patch.

### Signature
PC_AsText(p pcpatch) returns text

### Example
```sql
SELECT PC_AsText(pa) FROM patches LIMIT 1;
```

### Response Example
```json
{
  "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],
 [-126.93,45.07,7,0],[-126.92,45.08,8,0],[-126.91,45.09,9,0]
]
}
```
```

--------------------------------

### Create PcPatch from Array

Source: https://pgpointcloud.github.io/pointcloud/functions/patchs.html

Use PC_MakePatch to construct a PcPatch from a pcid and an array of double-precision floating-point values. The array must be a multiple of the number of dimensions defined by the pcid.

```sql
SELECT PC_AsText(PC_MakePatch(1, ARRAY[-126.99,45.01,1,0, -126.98,45.02,2,0, -126.97,45.03,3,0]));

{"pcid":1,"pts":[
 [-126.99,45.01,1,0],[-126.98,45.02,2,0],[-126.97,45.03,3,0]
]}
```

--------------------------------

### PC_Lib_Version

Source: https://pgpointcloud.github.io/pointcloud/functions/utils.html

Returns the version number of the pointcloud library.

```APIDOC
## PC_Lib_Version

### Description
Return the library version number.

### Method
SQL Function Call

### Endpoint
N/A

### Parameters
None

### Request Example
```sql
SELECT PC_Lib_Version();
```

### Response
#### Success Response (text)
- Returns the library version string.

#### Response Example
```
1.2.5 2346cc2
```
```

--------------------------------

### Generate JSON Summary of Patch Data

Source: https://pgpointcloud.github.io/pointcloud/functions/patchs.html

Returns a JSON formatted summary of the data contained within a point cloud patch. Requires Point Cloud library version 1.1.0 or later.

```sql
SELECT PC_Summary(pa) FROM patches LIMIT 1;

{"pcid":1, "npts":9, "srid":4326, "compr":"dimensional","dims":[{"pos":0,"name":"X","size":4,"type":"int32_t","compr":"sigbits","stats":{"min":-126.99,"max":-126.91,"avg":-126.95}},{"pos":1,"name":"Y","size":4,"type":"int32_t","compr":"sigbits","stats":{"min":45.01,"max":45.09,"avg":45.05}},{"pos":2,"name":"Z","size":4,"type":"int32_t","compr":"sigbits","stats":{"min":1,"max":9,"avg":5}},{"pos":3,"name":"Intensity","size":2,"type":"uint16_t","compr":"rle","stats":{"min":0,"max":0,"avg":0}}]}
```

--------------------------------

### PC_Compress

Source: https://pgpointcloud.github.io/pointcloud/functions/patchs.html

Compresses a patch using a specified compression scheme and configuration.

```APIDOC
## PC_Compress

### Description
Compress a patch with a manually specified scheme. The compression_config semantic depends on the global compression scheme. Allowed global compression schemes are:
  * auto: determined by pcid
  * laz: no compression config supported
  * dimensional: configuration is a comma-separated list of per-dimension compressions from this list
>     * auto: determined automatically from values stats
>     * zlib: deflate compression
>     * sigbits: significant bits removal
>     * rle: run-length encoding

### Signature
PC_Compress(p pcpatch, global_compression_scheme text, compression_config text) returns pcpatch
```

--------------------------------

### Insert a 4-Dimensional Schema Document

Source: https://pgpointcloud.github.io/pointcloud/concepts/schemas.html

This SQL statement inserts a sample XML schema document into the 'pointcloud_formats' table. This schema defines four dimensions: X, Y, Z coordinates, and Intensity, along with their properties like size, description, interpretation, and scale.

```sql
INSERT INTO pointcloud_formats (pcid, srid, schema) VALUES (1, 4326,
'<\?xml version="1.0" encoding="UTF-8"?>
<pc:PointCloudSchema xmlns:pc="http://pointcloud.org/schemas/PC/1.1"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <pc:dimension>
    <pc:position>1</pc:position>
    <pc:size>4</pc:size>
    <pc:description>X coordinate as a long integer. You must use the
                    scale and offset information of the header to
                    determine the double value.</pc:description>
    <pc:name>X</pc:name>
    <pc:interpretation>int32_t</pc:interpretation>
    <pc:scale>0.01</pc:scale>
  </pc:dimension>
  <pc:dimension>
    <pc:position>2</pc:position>
    <pc:size>4</pc:size>
    <pc:description>Y coordinate as a long integer. You must use the
                    scale and offset information of the header to
                    determine the double value.</pc:description>
    <pc:name>Y</pc:name>
    <pc:interpretation>int32_t</pc:interpretation>
    <pc:scale>0.01</pc:scale>
  </pc:dimension>
  <pc:dimension>
    <pc:position>3</pc:position>
    <pc:size>4</pc:size>
    <pc:description>Z coordinate as a long integer. You must use the
                    scale and offset information of the header to
                    determine the double value.</pc:description>
    <pc:name>Z</pc:name>
    <pc:interpretation>int32_t</pc:interpretation>
    <pc:scale>0.01</pc:scale>
  </pc:dimension>
  <pc:dimension>
    <pc:position>4</pc:position>
    <pc:size>2</pc:size>
    <pc:description>The intensity value is the integer representation
                    of the pulse return magnitude. This value is optional
                    and system specific. However, it should always be
                    included if available.</pc:description>
    <pc:name>Intensity</pc:name>
    <pc:interpretation>uint16_t</pc:interpretation>
    <pc:scale>1</pc:scale>
  </pc:dimension>
  <pc:metadata>
    <Metadata name="compression">dimensional</Metadata>
  </pc:metadata>
</pc:PointCloudSchema>');

```

--------------------------------

### PC_BoundingDiagonalGeometry(pcpatch)

Source: https://pgpointcloud.github.io/pointcloud/functions/postgis.html

Returns the bounding diagonal of a patch as a LineString, useful for indexing.

```APIDOC
## PC_BoundingDiagonalGeometry(pcpatch)

### Description
Returns the bounding diagonal of a patch. This is a LineString (2D), a LineString Z or a LineString M or a LineString ZM, based on the existence of the Z and M dimensions in the patch. This function is useful for creating an index on a patch column.

### Method
Function Call

### Endpoint
N/A (Function Signature)

### Parameters
#### Path Parameters
N/A

#### Query Parameters
N/A

#### Request Body
N/A

### Request Example
```sql
SELECT ST_AsText(PC_BoundingDiagonalGeometry(pa)) FROM patches;
```

### Response
#### Success Response (200)
- **geometry** (geometry) - A LineString representing the bounding diagonal of the patch.

#### Response Example
```
LINESTRING Z (-126.99 45.01 1,-126.91 45.09 9)
```

### Indexing Example
```sql
CREATE INDEX ON patches USING GIST(PC_BoundingDiagonalGeometry(patch) gist_geometry_ops_nd);
```
```

--------------------------------

### Count Points in Airport Table

Source: https://pgpointcloud.github.io/pointcloud/tutorials/storing.html

Connect to the PostgreSQL database using the configured service file and count the number of points in the 'airport' table. This verifies the data ingestion from the PDAL pipeline.

```bash
$ psql service=pgpointcloud
psql (12.3)
Type "help" for help.

pointclouds=# SELECT COUNT(*) FROM airport;
 count
-------
  9529
(1 row)

```

--------------------------------

### Declare Compression Method in Metadata

Source: https://pgpointcloud.github.io/pointcloud/concepts/compressions.html

Specify the preferred compression method for a schema within the `<pc:metadata>` block. If no compression is declared, 'none' is assumed.

```xml
<pc:metadata>
  <Metadata name="compression">dimensional</Metadata>
</pc:metadata>

```
Tokens:
Raw