Question

Pointclouds from postgis / PostgreSQL

  • 13 February 2015
  • 1 reply
  • 13 views
J
Badge

Hi All,

 

Does anyone have any experience getting a pointcloud out of PostGIS?

 

We're using the PostgreSQL extension "Pointcloud" - https://github.com/pgpointcloud/pointcloud

 

 

Currently the data is stored in pcpatch data types and we extract it with pc_explode(column)::geometry. This returns to FME all of the individual points which if desired we can then recombine into a pointcloud.

 

 

But is there a way to get a pointcloud out *as* a pointcloud? I've tried PC_AsText, but the JSON it returns isn't GeoJSON so I can't just feed it straight into a GeometryReplacer. And there's no PC_AsBinary for the pcpatch datatype.

 

 

Anyone have any thoughts or experiences with this?

 

Thanks,

Jonathan

1 reply

R
Badge +5

Hello Johnatan,

 

the anwer is a bit late ^^, but maybe it will be useful for another user.

To read pgpointclouds in FME as a pointclouds, you can transform it to a multipoint format in SQL :

  • Use a SQL EXECUTOR with the option : Combine attributes = result attribute only
  • Then you can use a SQL like this one : 
SELECT exp.geom 
 FROM  public.sonde,    
 lateral
 (    
	 select st_Collect(points::geometry) AS geom
	 FROM public.pc_explode((patch)) points 
	 GROUP BY PC_Get(points, 'surfac_id')  ) exp										
	 LIMIT 5000

Then the result will be a multipoint,much faster to use (even the SQL request is much faster).

One difference is that multipoint gather 4 dimsensions (x, y, z, M), and pgpointclouds as much as needed. Here is another version of the sql to collect the FK information gathered for each point in pgpointclouds format (the additional attribute has to be declared in the GROUP BY)

SELECT  surfac_id, exp.geom 
 FROM  public.sonde,    
 lateral
 (    
	 select 
	 PC_Get(points, 'surfac_id') surfac_id,
	 st_Collect(points::geometry) AS geom	 
	 FROM public.pc_explode((patch)) points 
	 GROUP BY PC_Get(points, 'surfac_id')  ) exp										
	 LIMIT 50

 

Ronan

 

Reply


Community Stats

28,731
Posts
109,530
Replies
36,565
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.