Pointclouds in PostgreSQL with Foreign Data Wrappers

IGN and Oslandia have been collaborating on a research project named LI³DS. LI³DS stands for “Large Input 3D System”. The project involves acquiring data on the field, such as images and point clouds, and providing tools for storing, processing and analyzing the data. Everything developed as part of the project is opensource, and available on GitHub: https://github.com/LI3DS. We will provide more information about the LI³DS project with a future post.

LI³DS logo

This blog post is about fdw-li3ds, a library we’ve been working as part of LI³DS. fdw stands for Foreign Data Wrapper (FDW), which you may know if you’re a PostgreSQL user. PostgreSQL’s FDWs provide a way to access remote data and interact with that data through SQL as if it was stored in local tables. fdw-li3ds provides FDWs for pointcloud data. At time of this writing fdw-li3ds supports three file formats: SBET, EchoPulse and Rosbag. Other pointcloud file formats will be supported in the future, based on our needs for LI³DS and other projects. Contributions are also very welcome, obviously 🙂

Using fdw-li3ds this is how you create a “foreign table” linked to a SBET file:

CREATE SERVER sbetserver FOREIGN DATA WRAPPER multicorn
OPTIONS (
    wrapper 'fdwli3ds.Sbet'
);

CREATE FOREIGN TABLE sbet_schema (
    schema text
)
SERVER sbetserver
OPTIONS (
    metadata 'true'
);

INSERT INTO pointcloud_formats (pcid, srid, schema)
SELECT 2, 4326, schema FROM sbet_schema;

CREATE FOREIGN TABLE sbet (
    points pcpatch(2)
)
SERVER sbetserver
OPTIONS (
    sources 'data/sbet/sbet.bin'
    , patch_size '100'
    , pcid '2'
);

Let’s review this step by step:

-- Create server
CREATE SERVER sbetserver FOREIGN DATA WRAPPER multicorn
OPTIONS (
    wrapper 'fdwli3ds.Sbet'
);

Before creating a “foreign table” we need to create a “foreign server”. Here we create a server named sbetserver based on the “multicorn” FDW and the fdwli3ds.Sbet wrapper.

Multicorn is a PostgreSQL extension that makes it possible to define FDWs in Python (one of Oslandia’s favorite languages, among many others…). wrapper 'fdwli3ds.Sbet' in the options specifies that we want to use the fdwli3ds.Sbet Multicorn wrapper, which is the wrapper fdw-li3ds provides for reading SBET files. If fdw-li3ds supported LAS then fdwli3ds.Las would be used here.

-- Create metadata foreign table
CREATE FOREIGN TABLE sbet_schema (
    schema text
)
SERVER sbetserver
OPTIONS (
    metadata 'true'
);

This query creates a “foreign table” named sbet_schema which relies on the “sbetserver” server we created previously. metadata 'true' specifies that this foreign table contains the SBET metadata (as opposed to the SBET data).

-- insert SBET schema into pointcloud_formats
INSERT INTO pointcloud_formats (pcid, srid, schema)
SELECT 2, 4326, schema FROM sbet_schema;

This reads the SBET file’s schema (metadata) from the sbet_schema foreign table created previously, and insert that schema into the PostgreSQL Pointcloud extension’s pointcloud_formats table. Having a schema is required for creating, and working with, Pointcloud columns.

-- create foreign table linked to SBET file
CREATE FOREIGN TABLE sbet (
    points pcpatch(2)
)
SERVER sbetserver
OPTIONS (
    sources '/data/sbet/sbet.bin'
    , patch_size '100'
    , pcid '2'
);

The last query finishes up the process by creating the actual foreign table bound to the SBET file, /data/sbet/sbet.bin You can now query that table in the same way you’d query any other Postgres table. For example:

SELECT points FROM sbet;

The points column of the sbet table is of type pcpatch, which is one of data types defined by the PostgreSQL Pointcloud extension. QGIS knowing about the pcpatch data type, visualizing the content of the sbet table in QGIS is straightforward.

Also, for better query performance, a materialized view of the sbet table can be created, with, for example, an index on the pcpatch column:

CREATE MATERIALIZED VIEW sbet_view AS select points, row_number() over () as id from sbet;
CREATE UNIQUE INDEX ON sbet_view (id);
CREATE INDEX ON sbet_view USING GIST(PC_EnvelopeGeometry(points))

You can now go ahead and create a QGIS layer based on sbet_view. The experience should be much better than relying on the foreign table.

Example of a SBET file displayed in QGIS

As a quick conclusion we think that using Foreign Data Wrappers referencing pointcloud files provides for an interesting approach. By keeping the pointcloud data into files data duplication can be avoided. And using fdw-li3ds you can still provide an SQL access to that data, making it possible to query and analyze the data through SQL. With the PostgreSQL and PostGIS arsenal at your disposal!

Feel free to contact us if you have questions, or if you want to know more about what we’re doing with point clouds!