# LiDAR and pointcloud extension pt 5

Now for the crazy stuff: The objective is to allow us to do vertical and horizontal summaries of our data. To do this, we’ll take chipped LiDAR input and further chip it vertically by classifying it.

First a classifier for height that we’ll use to do vertical splits on our point cloud chips:

```CREATE OR REPLACE FUNCTION height_classify(numeric)
RETURNS integer AS
\$BODY\$

SELECT
CASE WHEN \$1 < 10 THEN 1
WHEN \$1 >= 10 AND \$1 < 20 THEN 2
WHEN \$1 >= 20 AND \$1 < 30 THEN 3
WHEN \$1 >= 30 AND \$1 < 40 THEN 4
WHEN \$1 >= 40 AND \$1 < 50 THEN 5
WHEN \$1 >= 50 AND \$1 < 60 THEN 6
WHEN \$1 >= 60 AND \$1 < 70 THEN 7
WHEN \$1 >= 70 AND \$1 < 80 THEN 8
WHEN \$1 >= 80 AND \$1 < 90 THEN 9
WHEN \$1 >= 90 AND \$1 < 100 THEN 10
WHEN \$1 >= 100 AND \$1 < 110 THEN 11
WHEN \$1 >= 110 AND \$1 < 120 THEN 12
WHEN \$1 >= 120 AND \$1 < 130 THEN 13
WHEN \$1 >= 130 AND \$1 < 140 THEN 14
WHEN \$1 >= 140 AND \$1 < 150 THEN 15
WHEN \$1 >= 150 AND \$1 < 160 THEN 16
WHEN \$1 >= 160 AND \$1 < 170 THEN 17
WHEN \$1 >= 170 AND \$1 < 180 THEN 18
ELSE 0
END
FROM test;

\$BODY\$
LANGUAGE sql VOLATILE
COST 100;

```

And now, let’s pull apart our point cloud, calculate heights from approximate ground, then put it all back together in chips grouped by height classes and aggregates of our original chips. Yes, I will explain more later. And don’t do this at home– I’m not even sure if it makes sense yet… :

```/*
First we explode the patches into points,
and along the way grab the minimum value
for the patch, and the id
*/
WITH lraw AS (
SELECT PC_Explode(pa) AS ex,  PC_PatchMin(pa, 'Z') AS min, id
FROM test1
),
/*
Calculate our height value ( Z - min )
*/
heights AS (
SELECT PC_Get(ex, 'X') || ',' || PC_Get(ex, 'Y') || ',' -- grab X and Y
|| PC_Get(ex, 'Z') - min                -- calculate height
|| ',' ||
-- And return our remaining dimensions
PC_Get(ex, 'Intensity') || ',' || PC_Get(ex, 'ReturnNumber') || ',' || PC_Get(ex, 'NumberOfReturns') || ',' ||
PC_Get(ex, 'ScanDirectionFlag') || ',' || PC_Get(ex, 'EdgeOfFlightLine') || ',' ||
PC_Get(ex, 'Classification') || ',' || PC_Get(ex, 'ScanAngleRank') || ',' || PC_Get(ex, 'UserData') || ',' ||
PC_Get(ex, 'PointSourceId') || ',' || PC_Get(ex, 'Time') || ',' || PC_Get(ex, 'PointID') || ',' || PC_Get(ex, 'BlockID')
AS xyheight, PC_Get(ex, 'Z') - min AS height, id FROM lraw
),
-- Now we can turn this aggregated text into an array and then a set of points
heightcloud AS (
SELECT PC_MakePoint(1, string_to_array(xyheight, ',')::float8[]) pt, height_classify(height) heightclass, id FROM heights
)
-- Finally, we bin the points back into patches grouped by our heigh class and original ids.
SELECT PC_Patch(pt), id / 20 AS id, heightclass FROM heightcloud GROUP BY id / 20, heightclass;
```

The resultant output should allow us to query our database by height above ground and patch. Now we can generate vertical summaries of our point cloud. Clever or dumb? It’s too early to tell.

This site uses Akismet to reduce spam. Learn how your comment data is processed.