How Pointcloud_in_db works? - Remi-C/Pointcloud_in_db GitHub Wiki

##How does it works (short)##

Abstract :

  • Converting binary point cloud file into ASCII CSV :

By default the ply files containing a binary representation of points are converted to an ASCII "csv" representation of the points (the separator is a white-space, not a comma)

  • Loading Point data into temporary table inside DB :

This ASCII points with all their attributes are then send to a psql process which fill a temporary table in database with incoming data : one row per point, one column per attributes

  • Creating points and grouping close points into patch :

We create points from temporary table and group it (with a spatial criteria : by cubic meter) to form patches. Patches are a new type defined by the PointCloud extension This PCPATCH are written in the patch table where the totality of the point-cloud will be stored. The point must respect a user-defined schema (see table pointcloud_formats).

  • Working with the points

When the patch table has been populated, we create indexes to allow fast query on patches. When querying, we first find candidate patches then decompose candidate patches into points, which can be used for visualization/processing.

  • Visualizing points

The patches and points in the database can then be accessed in 2D (with Quantum GIS) or in 3D with a WebGl enabled Browser.

##How does it works (detailed)##

  • How does it works precisely
    • Sending points to database
    • Getting points into temporary tables
    • Grouping temporary points into patch
    • Using the patch table
    • Visualizing the patches/points in 2D/3D

@TODO : insert here the schema

Sending points to database

|binary points| ---> |ASCII points| ---> | to database| Note : all the data is streamed, there is no temporary files used

This steps are performed using a modified version of RPly and a linux fifo, creating an efficient stream Note : this process is suboptimal, it would be better to directly load binary points into Postgres (using pg_bulkoad for instance)

  • converting from binary ".ply" to plain ascii ".csv"
    • We use a modified version of the RPly program : it has been modified to not output the ply header and to increase the number of digits it outputs (as the float precision limit is system-depend).

Getting points into temporary tables

|input ".csv" stream | ---> |psql COPY process| ---> | temporary table with one column per attribute|

  • getting points into temporary tables
    • The input is a stream of ASCII point values separated by space and end of line. The order of the input values and of the temporary table column must exactly match.
    • We use a psql process hosting a SQL COPY statement reading from STDIN and writing into the temporary table inside the database.

Grouping temporary points into patch

|temporary table | ---> |points | ---> | groups of points| ---> | patches| ---> | patch table |

  • grouping temporary points into patch Note : This is currently the most time consuming part of the process, and could without a doubt be greatly improved.
    • The idea is to regroup points based on how points will be used by the final users.
    • For this we use the group by function of sql to form the groups.
      • We tried spatial grouping (0.125 cubic, meter, 1 cubic meter), and time grouping (1millisecond, 100 millisecond, based on time of acquisition).
    • the points in a group are then merged into a patch.
    • Choosing how point are grouped is very important as points will be accessed/filtered based on patches.

Using the patch table

points retrieval: |patch table | ---> |filtering on patch| ---> | patch candidates| ---> | extraction of points from patch candidate| ---> | filtering on points| ---> | Point processing/ Visualization / editing ...|

  • using the patch table
    • creating indexes
      • We create indexes based on what we want to do with data
    • querying for points :
      • a query for points is in 2 parts. The first part is to find the patches that might contains the points we are interested in. The second part is to extract points from this patches and use it

Visualizing the patches/points in 2D/3D

2D with QGIS : |filtered points from Postgres| ---> |Attributes we want| ---> |unique id| ---> |import as QGIS layer| 3D streaming with webgl Browser : |filtered points from Postgres| ---> |Attributes we want| ---> |node.js| ---socket---> |WebGL| ---> |Display on Screen|

|patch table | ---> |filtering on patch| ---> | patch candidates| ---> | extraction of points from patch candidate| ---> | filtering on points| ---> | Point processing/ Visualization / editing ...|

  • Visualizing the patches/points in 2D/3D
    • patch and points in 2D with qgis
      • @TODO
    • points in 3D with a WebGL browser
      • @TODO