I was given a TIN representing a terrain as an OBJ file:
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:
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
orapt-get install meshio-tools
Then, simply from a bash prompt:
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: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?):
Which results in:
(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.