[pgpointcloud] How to create a PcPoint table in PostGIS?

Wed Jun 28 02:46:48 PDT 2023

Hello Neil and Regina 

For your application, I would use the " foreign key to a separate table of plane equations" approach you proposed : 
- create a new schema with an added plane id column to the current pcpatch schema 
- use the PC_SetPCId function (or PC_Transform if you changed existing columns) with this new schema on the patches to get a default assignment (default = unassigned = outlier point) 
- iterate over the patches to assign a new plane id to outlier points that are inliers of the new RANSAC extracted plane 

For the plane assignment modification in the last step, a possibility is to do something like : 
- PC_Explode the patch 
- PC_Get each point to get the float8[] array of values 
- modify the plane id value in the array if it is an inlier of the new plane 
- PC_MakePoint to create the modified point 
- PC_Patch to reaggregate the points into a patch 

Best, 
Mathieu 

PS: maybe we should consider implementing functions like the following to simplify point and patch modifications : 
- PC_Set(pa pcpatch, dimname text, n int4, val float8 ) returns pcpatch 
- PC_Set(pt pcpoint, dimname text, val float8 ) returns pcpoint 

De: "Neil Underhill" <neil.underhill at gmail.com> 
À: "Regina Obe" <lr at pcorp.us> 
Cc: pgpointcloud at lists.osgeo.org 
Envoyé: Mercredi 28 Juin 2023 10:34:55 
Objet: Re: [pgpointcloud] How to create a PcPoint table in PostGIS? 

Hello all, 
A follow on question to the below... is it possible to add an additional column to data stored as a pgPointcloud ? 
Reason I ask is that I am using the RANSAC algorithm to detect roof shapes (i.e. which Cartesian equation of a plane fits the most points). 
Currently the algorithm will only find the plane equation which fits the biggest number of points in a point cloud. However there is usually more than one roof in a point cloud, so I would like to add a column to indicate that a point already belongs to a plane so the algorithm can exclude, re-reun and find the next biggest biggest plane, etc. etc., until a minimum threshold in area is reached. 
I am thinking the new column should be either a binary flag, or a foreign key to a separate table of plane equations. 
If the above is not possible, then I would assume that I need to dump out the pgPointcloud into a standard PostGIS point geometry table, where I can add additional column? 

Thanks, 
Neil 

On Tue, 28 Mar 2023 at 21:08, Regina Obe < [ mailto:lr at pcorp.us | lr at pcorp.us ] > wrote: 

You can use the PC_Explode function to access individual points. It converts a patch into individual PcPoints. 

[ https://pgpointcloud.github.io/pointcloud/functions/patchs.html#pc-explode | https://pgpointcloud.github.io/pointcloud/functions/patchs.html#pc-explode ] 

You can use the PC_PointN to access individual points by index number in the patch. 

[ https://pgpointcloud.github.io/pointcloud/functions/patchs.html#pc-pointn | https://pgpointcloud.github.io/pointcloud/functions/patchs.html#pc-pointn ] 

So for example to pick at random 3 points, you can do something like (NOT TESTED) 

SELECT PC_PointN(pa, index) AS pcp 

FROM patches AS p, PC_NumPoints( [ http://p.pa/ | p.pa ] ) AS np 

CROSS JOIN LATERAL (SELECT index FROM generate_series(1, np) AS index ORDER BY random() LIMIT 3) AS pi(index); 

Hope that helps, 

Regina 

From: pgpointcloud [mailto: [ mailto:pgpointcloud-bounces at lists.osgeo.org | pgpointcloud-bounces at lists.osgeo.org ] ] On Behalf Of Neil Underhill 
Sent: Tuesday, March 28, 2023 12:47 PM 
To: [ mailto:pgpointcloud at lists.osgeo.org | pgpointcloud at lists.osgeo.org ] 
Subject: [pgpointcloud] How to create a PcPoint table in PostGIS? 

I have a .laz file with about 51m points in it (225 MB in size), and want to run a RANSAC algorithm to detect roof planes. 

I would like to add the LIDAR data to a PostGIS database so I can interact with other geospatial layers (e.g. use a mask to filter the points to consider) and also access individual points to input into the algorithm. 

I have been following the information here ( [ https://pgpointcloud.github.io/pointcloud/concepts/objects.html | https://pgpointcloud.github.io/pointcloud/concepts/objects.html ] ) about the different Point Cloud Objects. 

My question is, if I store the data as a PcPatch, will I be able to access the individual points (e.g. select three points at random, that may or may not be in the same patch) ? 

If not, it seems I will need to store as PcPoint. If it's the latter, I am not clear how I create a database with PcPoint objects, as the example in the Quickstart ( [ https://pgpointcloud.github.io/pointcloud/quickstart.html | https://pgpointcloud.github.io/pointcloud/quickstart.html ] ) only creastes a PcPatch table. 

Thanks for any advice, 

Neil 

_______________________________________________ 
pgpointcloud mailing list 
pgpointcloud at lists.osgeo.org 
https://lists.osgeo.org/mailman/listinfo/pgpointcloud 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pgpointcloud/attachments/20230628/680e42ee/attachment-0001.htm>