Heritage GIS

Loading Textured Meshes into PostGIS 3D

You have a cleaned photogrammetric mesh of a collapsing medieval undercroft — an OBJ with a texture atlas — and you need it in PostGIS so it can be spatially joined to its context record and measured with the 3D functions, not just admired in a viewer. The task is a controlled type conversion: read the triangle geometry, re-anchor it to the site datum, serialise it to a POLYHEDRALSURFACE Z well-known text, and insert it with the correct SRID so ST_IsValid and ST_3DArea return trustworthy answers. This guide, part of exporting 3D models to spatial databases, gives the full Python and SQL, including the closure and dimension pitfalls that make an otherwise-fine mesh fail validation.

OBJ to PostGIS POLYHEDRALSURFACE Z conversion An OBJ mesh parsed to triangles, anchored to the site datum, serialised to WKT, and inserted into a PostGIS 3D column. Mesh to spatial column, one closed ring per face Parse OBJ verts + faces Anchor + close offset, ring loop WKT Z POLYHEDRALSURFACE INSERT SRID 27700

Context & When to Use

Load a mesh into PostGIS 3D when you need to query or relate it, not merely display it — spatial joins to excavation contexts, 3D area and volume measurement, or intersection tests against a trench solid. If the only requirement is a web fly-through, keep the mesh as glTF on a file store and skip the database. The main trade-off is triangle count. A POLYHEDRALSURFACE Z stores every face as an explicit polygon ring, so a raw hundred-million-triangle master will bloat the table and slow SFCGAL to a crawl. Load a decimated LOD (typically well under a million faces) for query, and reference the full-density master by URI. The second trade-off is validity strictness: SFCGAL rejects unclosed rings and zero-area faces outright, so meshes with holes or degenerate triangles must be repaired before conversion, not after insertion.

Implementation

The converter reads the OBJ with trimesh, re-applies the datum offset removed at export, builds one closed ring per triangle, and inserts through psycopg with the SRID stamped in SQL. Every step comments the archaeological reasoning.

# requirements.txt
# trimesh==4.2.0
# numpy==1.26.4
# psycopg==3.1.18
# trimesh==4.2.0, numpy==1.26.4, psycopg==3.1.18
import numpy as np
import trimesh
import psycopg

SRID = 27700  # British National Grid; substitute your site's EPSG


def load_mesh_wkt(obj_path: str, offset: tuple[float, float, float]) -> str:
    """Read an OBJ and return POLYHEDRALSURFACE Z WKT anchored to the datum.

    offset is the local shift the photogrammetry exporter subtracted for
    float precision; re-applying it places the ruin on its true coordinates.
    """
    mesh = trimesh.load(obj_path, process=False)  # keep original vertex order
    if mesh.faces.shape[1] != 3:
        raise ValueError("expected a triangulated mesh; triangulate first")

    verts = mesh.vertices + np.asarray(offset, dtype="f8")
    face_wkt = []
    for a, b, c in mesh.faces:
        ring = [verts[a], verts[b], verts[c], verts[a]]  # close: repeat first
        coords = ",".join(f"{x:.4f} {y:.4f} {z:.4f}" for x, y, z in ring)
        face_wkt.append(f"(({coords}))")
    return "POLYHEDRALSURFACE Z(" + ",".join(face_wkt) + ")"


def insert_mesh(dsn: str, wkt: str, monument_ref: str, survey_date: str) -> int:
    """Insert the mesh with its SRID and metadata; return the new row id."""
    sql = """
        INSERT INTO heritage_mesh (monument_ref, survey_date, method, lod, geom)
        VALUES (%s, %s, 'SfM photogrammetry', 'display',
                ST_GeomFromText(%s, %s))
        RETURNING id;
    """
    with psycopg.connect(dsn) as conn, conn.cursor() as cur:
        cur.execute(sql, (monument_ref, survey_date, wkt, SRID))
        return cur.fetchone()[0]


if __name__ == "__main__":
    wkt = load_mesh_wkt("undercroft_lod.obj", offset=(352000.0, 174000.0, 0.0))
    new_id = insert_mesh("dbname=heritage", wkt, "MON-01423", "2026-06-18")
    print(f"inserted mesh id={new_id}")

Two details do the load-bearing work. Repeating the first vertex (verts[a] at the end of each ring) is what makes the ring closed — omit it and every face is invalid. Building the geometry with ST_GeomFromText(%s, %s) stamps the SRID at insert time, so the row is anchored the moment it lands rather than relabelled later.

Verification

Immediately after loading, prove validity, dimensionality, SRID, and a plausible surface area:

-- PostgreSQL 16 / PostGIS 3.4 (SFCGAL)
SELECT id,
       ST_IsValid(geom)            AS valid,
       ST_NDims(geom)              AS ndims,
       ST_SRID(geom)               AS srid,
       round(ST_3DArea(geom)::numeric, 2) AS area_m2
FROM heritage_mesh
WHERE id = :new_id;

Expected output for a well-formed undercroft record:

 id | valid | ndims | srid  |  area_m2
----+-------+-------+-------+-----------
  7 | t     |     3 | 27700 |   248.61

Assert all four: valid = t, ndims = 3 (a 2 means the Z flag was dropped), srid = 27700, and an area_m2 in a sane range for the structure. A validity failure should block promotion — run ST_IsValidReason(geom) to see exactly which face failed. Confirm placement with SELECT ST_3DExtent(geom) FROM heritage_mesh WHERE id = :new_id; and check the box straddles the site’s known easting/northing, not the origin.

Field note. A mesh can pass ST_IsValid and still be geometrically upside-down if the photogrammetry export used a Z-up-versus-Y-up convention different from your database. SFCGAL only checks topology, not orientation. After the first load from a new software pipeline, measure one known vertical — a wall height you recorded by hand — with ST_ZMax(geom) - ST_ZMin(geom) and confirm it matches before trusting the batch.

Common Errors & Fixes

  • Polyhedral surface is invalid : Ring 0 is not closed — the ring did not repeat its first vertex. Ensure the converter appends the opening coordinate to every face, as the ring list does above.
  • Geometry has Z dimension but is not tagged as such or a silently 2D result (ST_NDims returns 2) — the WKT string said POLYHEDRALSURFACE(...) without the Z keyword. Emit POLYHEDRALSURFACE Z(...) so PostGIS keeps the elevations.
  • lwgeom_sfcgal.c: ... SFCGAL is not enabled or function st_3darea(geometry) does not exist — the SFCGAL extension is not loaded. Run CREATE EXTENSION postgis_sfcgal; in the target database, then re-run the verification query.

Part of Photogrammetry & 3D Site Mapping Pipelines.