IGN and Oslandia have been collaborating on a research project named LI3DS. LI3DS 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 LI3DS project with a future post.
This blog post is about
fdw-li3ds, a library we’ve been working as part of LI3DS.
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 LI3DS and other projects. Contributions are also very welcome, obviously 🙂
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
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;
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
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.
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!