Exporting 3D Models to Spatial Databases for Heritage Archives #

A photogrammetric model that ends its life as a loose .obj on a project drive is not archived — it is merely stored, unqueryable, disconnected from the excavation record, and one disk failure from loss. The point of exporting 3D models into a spatial database is to make the geometry first-class: a wall mesh you can spatially join to its context sheet, a point cloud you can clip to a trench polygon, a ruin you can retrieve by location and date rather than by remembering a filename. This section, part of the Photogrammetry & 3D Site Mapping Pipelines workflow, takes the mesh and point-cloud products of mesh generation & optimization for ruins and the georeferenced rasters from orthomosaic generation and tiling and lands them in PostGIS with real coordinates and durable metadata. The database schema they join is designed in PostGIS schema design for excavation units; the two guides here — loading textured meshes into PostGIS 3D and storing point clouds as pgPointCloud patches — cover the two data models in detail.

Mesh formats and the handoff #

Photogrammetry packages export a small family of mesh formats, and each carries different baggage into the database. OBJ is the archival lingua franca: plain-text vertices and faces with a companion .mtl and texture image, easy to parse but coordinate-naive. PLY stores per-vertex colour or normals compactly and suits coloured meshes without a texture atlas. glTF (and its binary .glb) is the delivery format for web viewers, bundling geometry, textures, and a scene graph, but it is a transport container, not an archive master. The practical rule: keep OBJ or PLY as the preservation master and derive glTF for display. When loading into PostGIS you extract the raw triangle geometry from OBJ or PLY; the texture stays a linked asset referenced by metadata rather than living inside the geometry column.

PostGIS 3D geometry: POLYHEDRALSURFACE Z #

PostGIS represents a closed or open mesh as a POLYHEDRALSURFACE Z — a collection of planar polygon faces each carrying X, Y, and Z ordinates. Unlike a 2D POLYGON, every vertex holds true elevation, so a wall recorded at its real height stores and returns that height. The 3D analytic functions live in the SFCGAL extension: ST_3DArea measures surface area across all faces, ST_3DIntersects tests whether two solids meet, ST_3DDistance returns the true separation between 3D geometries, and ST_Volume (on a closed solid) gives enclosed volume. These turn a mesh from a picture into something you can measure and relate. The textured-mesh loading guide walks the OBJ-to-WKT conversion and the validity checks that must pass first.

Point clouds: pgPointCloud #

A dense photogrammetric or lidar cloud has millions to billions of points, and storing one row per point is untenable. pgPointCloud solves this by grouping nearby points into PcPatch objects — compressed blocks of a few hundred to a few thousand points sharing a schema. A pointcloud_formats table registers the per-point dimensions (X, Y, Z, intensity, classification, RGB) as an XML schema, and every patch references it. PDAL’s writers.pgpointcloud streams a LAS/LAZ file straight into patch rows, and the PC_* functions (PC_PatchAvg, PC_Intersects, PC_Explode, PC_FilterEquals) query them without unpacking everything. The point-cloud storage guide gives the full PDAL pipeline and schema registration.

Coordinate anchoring, LOD, and metadata #

Three cross-cutting concerns govern every load. Coordinate anchoring: photogrammetry meshes frequently sit in a local project frame with a large offset removed for numerical precision, so you must re-apply that offset and stamp the correct SRID (EPSG:27700; # substitute your site's EPSG) or the model floats near the origin, kilometres from the site. Level of detail (LOD): store a decimated mesh for interactive query and keep the full-density master separately, so a spatial join does not drag a hundred-million-triangle solid through memory. Metadata: capture the survey date, method, accuracy, operator, and monument reference alongside the geometry, because a 3D record with no lineage cannot be cited or trusted.

Prerequisites #

Assumptions: a PostGIS database exists with CREATE EXTENSION postgis;, CREATE EXTENSION postgis_sfcgal;, and CREATE EXTENSION pointcloud; (plus pointcloud_postgis for the bridge functions). Meshes and clouds are already georeferenced to a known datum, and you hold the local-to-world offset from the photogrammetry project.

Implementation #

The field-to-archive order runs input triage, conversion, anchoring, insertion, then validation.

1. Enable the extensions once per database:

-- PostgreSQL 16 / PostGIS 3.4
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS postgis_sfcgal;
CREATE EXTENSION IF NOT EXISTS pointcloud;
CREATE EXTENSION IF NOT EXISTS pointcloud_postgis;

2. Define the mesh archive table with a typed geometry column and metadata:

-- PostgreSQL 16 / PostGIS 3.4
CREATE TABLE heritage_mesh (
    id           bigserial PRIMARY KEY,
    monument_ref text NOT NULL,
    survey_date  date NOT NULL,
    method       text NOT NULL,          -- e.g. 'SfM photogrammetry'
    lod          text NOT NULL,          -- 'master' | 'display'
    texture_uri  text,                   -- linked atlas, not inline
    geom         geometry(PolyhedralSurfaceZ, 27700)  -- substitute your site's EPSG
);

3. Convert and anchor a mesh — extract triangles, re-apply the offset, emit WKT. This is detailed in the textured-mesh loading guide:

# requirements.txt
# trimesh==4.2.0
# psycopg==3.1.18

# trimesh==4.2.0, psycopg==3.1.18
import trimesh

def obj_to_wkt(path, offset):
    """OBJ -> POLYHEDRALSURFACE Z WKT with the local offset re-applied."""
    mesh = trimesh.load(path, process=False)
    ox, oy, oz = offset  # the datum offset removed at export time
    faces = []
    for tri in mesh.faces:
        ring = [mesh.vertices[i] + (ox, oy, oz) for i in tri]
        ring.append(ring[0])  # close the ring
        coords = ",".join(f"{x} {y} {z}" for x, y, z in ring)
        faces.append(f"(({coords}))")
    return "POLYHEDRALSURFACE Z(" + ",".join(faces) + ")"

4. Insert point clouds via PDAL — covered fully in the pgPointCloud guide; the writer chunks LAS into patches and streams them in.

5. Validate before promotion (next section).

CRS & Geometry Validation #

Every mesh must pass ST_IsValid(geom) before it earns a place in the archive; SFCGAL is strict, and a POLYHEDRALSURFACE Z with an unclosed ring or a degenerate face fails. Check the SRID with ST_SRID(geom) equals your target EPSG — a 0 means the geometry was inserted without a coordinate system and every later 3D join will silently mismatch. Confirm the bounding box lands on-site with ST_3DExtent(geom); a Z range of thousands of metres, or an XY box centred near zero, is the tell-tale of a missing coordinate anchor. For clouds, verify PC_Get(PC_PatchMin(pa), 'Z') sits in a sane elevation band for the region.

Automated QA/QC #

Gate each load on: (1) ST_IsValid true for meshes; (2) SRID equal to the mandated EPSG for both meshes and patches; (3) ST_3DExtent / patch bounds intersecting the known site polygon; and (4) non-null monument_ref, survey_date, and method. Route failures to a staging schema keyed by the failing check, and append an audit row recording source file, SHA-256 hash, point/triangle count, SRID, and validation outcome, so any archived 3D record traces back to its raw survey.

Integration with adjacent workflows #

The stored geometry feeds the wider archive directly. Mesh and cloud extents can be spatially joined to excavation contexts modelled under PostGIS schema design for excavation units, tying a 3D ruin to its stratigraphic record. The orthomosaic generation and tiling rasters provide the draped base a viewer renders the mesh over, and the cleaned meshes arrive from mesh generation & optimization for ruins already decimated to sane triangle counts.

Troubleshooting #

• Polyhedral surface is invalid : Ring X is not closed from ST_IsValid — a face ring did not repeat its first vertex; ensure the WKT builder appends the opening coordinate.
• function st_3darea(geometry) does not exist — the SFCGAL extension is missing; run CREATE EXTENSION postgis_sfcgal;.
• Component geometry has an invalid dimension / geometry loads as 2D — the WKT declared POLYHEDRALSURFACE without the Z flag, dropping elevations. Emit POLYHEDRALSURFACE Z(...) explicitly.
• ERROR: pcpatch schema ... does not exist — the pointcloud_formats row is missing or its pcid mismatches the writer; register the schema before loading.
• Operation on mixed SRID geometries on a join — one input has SRID 0. Set it with ST_SetSRID and re-anchor, do not just relabel.
• Model near the origin — the local shift was not re-applied; add the offset in the converter and reload.

Compliance notes #

Attach ISO 19115 lineage to every 3D record: the acquisition method, survey date, absolute and relative accuracy, sensor/platform, and the CRS as an EPSG code. MIDAS Heritage requires a monument identifier and an event reference — store both as columns, not just in prose. For FAIR reuse, mint a persistent identifier for each archived model, keep the texture and master mesh under stable URIs referenced from texture_uri, and record the licence, so an interoperable 3D record is genuinely findable and reusable by another research team.

Related #

• Mesh Generation & Optimization for Ruins — the mesh cleanup and decimation feeding this export.
• Orthomosaic Generation and Tiling — the draped raster base paired with 3D records.
• PostGIS Schema Design for Excavation Units — the excavation schema these 3D records join against.
• Loading Textured Meshes into PostGIS 3D — OBJ-to-WKT conversion and 3D validity checks.
• Storing Point Clouds as PostGIS pgPointCloud Patches — PDAL patch loading and PC_* queries.

Part of Photogrammetry & 3D Site Mapping Pipelines.