PostGIS TIN Format – Saving TIN from Wavefront OBJ File

meshobjplypostgistin

I was given a TIN representing a terrain as an OBJ file:

The obj terrain

I need to store it into a PostGIS database as the PostGIS internal TIN format.

How could I do that?

If needed I can have access to the original raster DEM as a GeoTIFF file.

For convenience, I've tried to load the OBJ file into QGIS but no luck; this format is not recognized as a valid mesh or vector format:

Invalid Data Source: /data/TIN/dem.obj is not a valid or recognized data source.

Apparently, QGIS only seems to rely on those mesh file formats: https://github.com/lutraconsulting/MDAL#supported-formats

So I converted the OBJ to the Stanford PLY file format, and I succeeded to load it:

The PLY mesh in QGIS

But then, how could I save it to the PostGIS database?

Note: at the really end, the machine I will have to execute this process is a headless Linux server (Ubuntu 20.04), so it would ideally have to be run only at the command line on such environment.

Best Answer

As a DIY solution, after diving into the galaxy of 3D related stuff, I found a nice Python (>= 3.7) MIT licensed package called meshio (Schlömer, Nico. (2022). meshio: Tools for mesh files (v5.3.4). Zenodo. https://doi.org/10.5281/zenodo.6346837) (github) which can translate between some 3D formats. Upon those, luckily WKT. This package can be installed on Ubuntu either with:

  • pip install meshio or
  • apt-get install meshio-tools

Then, simply from a bash prompt:

$ meshio --version
meshio 5.3.4 [Python 3.10.4]
Copyright (c) 2015-2021 Nico Schlömer et al.

$ meshio convert input.obj output.wkt

After what, one can load this WKT file to PostGIS from a psql CLI.
But this is the tricky part which I cannot yet fully automate because of the \set statement:

\set content `cat /path/to/output.wkt`
UPDATE table SET wkt_geom = :'content' WHERE id=1;

Or by simply copy/pasting the raw content of the file in a prepared query (beware, this can be laggy with large content...).

Finally, one can visualize the result, e.g. in pgAdmin, using such query (inspired by: Converting TIN to Multipolygon in PostGIS?):

SELECT
  ST_Force2d(ST_Transform(ST_Collect(geom), 4326)) AS geom 
FROM (
  SELECT
    ST_MakePolygon(
      ST_ExteriorRing(
        (ST_Dump(wkt_geom)).geom
      )
    ) AS geom
  FROM table
  WHERE id=1
) foo;

Which results in:

Result in pgAdmin

(If you have an FME license, you can probably do the same using an OBJ reader or a Triangulator up to the PostGIS writer, but I didn't try).

⚠️ Drawback:

This goes well when playing with small data sets (up to a few triangles) but for large data sets it's definitely not optimal and you may run into critical issues, see: Storing a large 3D TIN into a PostGIS database Therefore, you'd better split your input data into sub chunks.

Related Question