Fix: InferClass substring matching + expand data extraction

[tygwan]

Full archive with figures, audit script, and reproducible data:
https://github.com/tygwan/first-ontology-project/tree/main/docs/findings/2026-04-12-M1-piping-misclassification

Related downstream project: first-ontology-project — a BIM ontology/knowledge graph pipeline built on top of DXTnavis outputs. The full audit was performed during that project's Phase 1 verification.

---

Summary

Two categories of changes to Services/RefinedXlsxExporter.cs:

1. Bug fix: InferClass uses case-insensitive substring matching which produces 997 false-positive Piping classifications on the 2026-04-07 snapshot (~34% inflation of the Piping class). Root cause: Piping keyword "tee" matches "steel", and "pipe" matches "Pipe Rack" / "Pipe Trench" folder names in the system path.

2. Data extraction enhancement: Expand the XLSX output so downstream consumers (knowledge graph / ontology pipelines / Palantir Foundry) can build on top of DXTnavis without joining multiple raw exports.

Part 1 — Bug fix: InferClass substring matching

Current behavior

InferClass (lines 298-375) performs Tier 3 classification using .Contains() which is substring matching without word boundaries:

string combined = (sysPath + " " + displayName).ToLowerInvariant();
foreach (var key in objData.Keys)
{
    if (key.StartsWith("__")) continue;
    combined += " " + key.ToLowerInvariant();
}

if (combined.Contains("pipe") || combined.Contains("valve") ||
    combined.Contains("flange") || combined.Contains("elbow") ||
    combined.Contains("tee") || combined.Contains("reducer") ||
    combined.Contains("nozzle") || combined.Contains("coupling"))
    return "Piping";

Problem

Intended keyword	Actually matches	Impact
"tee" (Tee pipe fitting)	"steel" (via s-TEE-l)	10 steel structural members → Piping
"pipe" (Pipe fitting)	"Pipe Rack" folder (structural support)	698 objects → Piping
"pipe" (Pipe fitting)	"Pipe Trench" folder (civil work)	60 objects → Piping
"pipe" (Pipe fitting)	"Pipeline" folder (grouping node)	12 objects → Piping

Concrete example

Object: MemberSystem-1-0151
System Path: For Review.nwd > Electrical Device > Steel > MemberSystem-1-0151
Navisworks properties: 항목|유형, 항목|이름, 항목|소스 파일, ... (8 Item metadata only)
SP3D properties: none
Current XLSX Class: Piping   ← BUG
Correct Class: Structure (steel member)

Because the system path contains "Steel", the tee keyword (intended for pipe Tee fittings) hits via substring match s-TEE-l. Piping is evaluated first in Tier 3, so Structure (which would match on the "steel" keyword) never gets a chance.

Quantitative impact (2026-04-07 snapshot, 12,009 objects)

Piping subset	Count	% of Piping
HIGH confidence (has pipeline + commodity/spec/NPD metadata)	2,926	72.9%
LOW confidence (has some metadata but no pipeline)	91	2.3%
LIKELY BUG (no pipeline, no piping metadata)	997	24.8%
Total labeled Piping	4,014	100%

Cross-reference: the Structure class (5,926 objects) has zero sp3d_pipeline or sp3d_eqp_type_0 set, so contamination is unidirectional (Piping absorbs misclassified Structure/Electrical, not the other way).

Breakdown of 997 LIKELY_BUG Piping by root cause:

• Pipe Rack folder → 698
• Pipe Trench folder → 60
• Pipeline folder → 12
• steel substring → tee → 10
• Other complex/nested paths → ~217

Proposed fix — word boundary regex

using System.Text.RegularExpressions;

private static readonly Regex PipingKeywordRegex = new Regex(
    @"\b(pipe|valve|flange|elbow|tee|reducer|nozzle|coupling)\b",
    RegexOptions.IgnoreCase | RegexOptions.Compiled);

private static readonly Regex EquipmentKeywordRegex = new Regex(
    @"\b(equipment|vessel|pump|tank|compressor|exchanger|heater|reactor)\b",
    RegexOptions.IgnoreCase | RegexOptions.Compiled);

private static readonly Regex StructureKeywordRegex = new Regex(
    @"\b(struct|structural|structure|steel|beam|column|brace|foundation|slab|plate|grating|handrail|ladder|stair)\b",
    RegexOptions.IgnoreCase | RegexOptions.Compiled);

private static readonly Regex ElectricalKeywordRegex = new Regex(
    @"\b(electrical|cable|conduit|tray)\b",
    RegexOptions.IgnoreCase | RegexOptions.Compiled);

private static readonly Regex HvacKeywordRegex = new Regex(
    @"\b(hvac|duct|ventilat)\b",
    RegexOptions.IgnoreCase | RegexOptions.Compiled);

private static readonly Regex InstrumentationKeywordRegex = new Regex(
    @"\binstrument\b",
    RegexOptions.IgnoreCase | RegexOptions.Compiled);

Then in InferClass Tier 3:

if (PipingKeywordRegex.IsMatch(combined))          return "Piping";
if (EquipmentKeywordRegex.IsMatch(combined))       return "Equipment";
if (StructureKeywordRegex.IsMatch(combined))       return "Structure";
if (ElectricalKeywordRegex.IsMatch(combined))      return "Electrical";
if (HvacKeywordRegex.IsMatch(combined))            return "HVAC";
if (InstrumentationKeywordRegex.IsMatch(combined)) return "Instrumentation";
return "Other";

Expected outcome

Class	Before (buggy)	After (fixed)	Delta
Piping	4,014	~2,926	-1,088
Structure	5,926	~6,900	+974
Electrical	449	~510	+61
HVAC	72	~72	0
Equipment	851	~851	0
Other	697	~750	+53

Tests to add

[Test] public void Tee_keyword_should_not_match_steel() {
    var result = InferClass(emptyProps, "Electrical Device > Steel > Member", "Member-1");
    Assert.That(result, Is.EqualTo("Structure"));
}

[Test] public void Pipe_keyword_should_not_match_pipe_rack() {
    var result = InferClass(emptyProps, "> A1 > U12 > Civil > Pipe Rack", "Beam-1");
    Assert.That(result, Is.EqualTo("Structure"));
}

[Test] public void Pipe_keyword_should_still_match_real_pipe() {
    var props = new Dictionary<string,string> { { "SmartPlant 3D|Pipeline", "P-001" } };
    var result = InferClass(props, "> Piping > P-001", "Pipe-1-0042");
    Assert.That(result, Is.EqualTo("Piping"));
}

---

Part 2 — Data extraction wishlist

Downstream consumers (BIM ontology / knowledge graph / Foundry) need the following to build on top of DXTnavis without having to join multiple raw exports.

MUST — blocking downstream Phase 2+

M2a. Preserve ParentId in XLSX output

Current: BuildDynamicColumns (line 204-229) excludes any key starting with __, dropping __ParentId. The XLSX has no way to reconstruct the parent-child hierarchy.

Impact: Downstream consumers must join XLSX with AllProperties.csv or validation.csv to recover ParentId, which is fragile (validation.csv only has 294/12,009 populated rows).

Fix: Add ParentId to the MetaColumns array.

M2b. Preserve Level as an integer column

Current: Level is written as a string via objData["__Level"] = record.Level.ToString().

Fix: Write Level as an int directly: ws.Cell(r, c).Value = record.Level.

M2c. Emit a dedicated Hierarchy sheet

Desired content: ObjectId(GUID), ParentId, Level — 3 columns.

Rationale: Even with ParentId preserved in the pivot sheet, a dedicated hierarchy sheet makes it trivial to build HasParent relationship datasets.

SHOULD — significant quality-of-life for downstream

S2a. Recover dropped SP3D columns

Missing from XLSX but present in AllProperties_*.csv:

• SmartPlant 3D|Flow Direction — critical for piping flow graph analysis
• SmartPlant 3D|Cut length (lowercase l — possibly a typo)
• 객체이름 (Korean display name alternative)

S2b. Resolve multi-category Pipeline name collision

Current: FindKey(columns, "Pipeline") selects the first column whose PropertyName is "Pipeline", typically SmartPlant 3D|Pipeline. If other categories have a "Pipeline" property, those are silently ignored.

Impact: 10+ pipelines are lost for downstream summary consumers (147 in XLSX Pipeline_Summary vs 157 in SQLite backend).

S2c. Filter placeholder "Pipelines" label from Pipeline field

Current: 153 objects at Level 7 have SmartPlant 3D|Pipeline = "Pipelines" (the literal folder name) rather than a real pipeline identifier.

S2d. Type-strict columns

Current: All values written as strings. Numeric values (weights, lengths, coordinates, counts) become text cells.

Fix: Emit numeric fields as numbers. Decide on SI vs imperial policy per field.

NICE-TO-HAVE

• N2a. Parquet output option (--format parquet flag) — Foundry and Spark tools ingest Parquet natively
• N2b. Emit relationship sheets — AdjacentTo, HasMaterial, HasSpecification, BelongsToPipeline, SupportedBy
• N2c. Canonical reference tables — Materials.csv, Specifications.csv, Pipelines.csv
• N2d. Change tracking between exports — ChangeLog.csv with added/removed/modified diff
• N2e. Embedded metadata sheet — export timestamp, version, source file hash, parameters

---

Effort estimate

Task	Effort
Part 1 InferClass fix + unit tests	0.5 day
Regression test with snapshot fixture	0.5 day
M2a/b/c hierarchy preservation	0.5 day
S2a-d schema improvements	1.5 day
Total MUST+SHOULD	~3 days
Total including NICE-TO-HAVE	~9 days

Downstream validation

The first-ontology-project repository has 192 tests exercising the XLSX classification logic via a Python port. After applying the C# fix, update the Python port keywords to use regex word boundaries as well, then re-run:

.venv/bin/python -m pytest tests/test_ingest/test_xlsx_classifier.py

A 100% match after both sides are fixed is the acceptance criterion.

---

Evidence files

Full archive in the downstream project:
https://github.com/tygwan/first-ontology-project/tree/main/docs/findings/2026-04-12-M1-piping-misclassification

Contains:

• README.md — 5-section finding report
• audit.py — reproducible diagnosis script
• make_figures.py — matplotlib visualization generator
• data/ — 5 CSV evidence files (confidence breakdown, substring causes, etc.)
• figures/ — 4 PNG charts (confidence distribution, substring causes, misclassified prefixes, class distribution)
• dxtnavis-pr-draft.md — full PR draft (this issue body is a condensed version)

[tygwan]

Verification report — word-boundary fix alone is insufficient

Implemented Part 1 in #3 against the 2026-04-12 snapshot of the same model (12,009 objects). One important finding for anyone implementing this fix or porting it downstream:

The proposed \b...\b fix produces zero change

I first applied the exact pattern from this issue:

@"\b(pipe|valve|flange|elbow|tee|reducer|nozzle|coupling)\b"

After rebuild + re-export, class distribution was bit-identical to the buggy baseline:

Class	Buggy .Contains()	After \b...\b fix	Δ
Piping	3,903	3,903	0
Structure	4,448	4,448	0
Other	1,758	1,758	0
Electrical	1,008	1,008	0
HVAC	122	122	0
Equipment	770	770	0

Initially I suspected DLL caching, but binary inspection of the deployed DLL confirmed the new regex literals (pipe|valve, valve|flange, reducer, structural, ventilat) were all present in the #US heap. The code was running — the regex just wasn't filtering out what we expected.

Root cause

Pipe Rack and Pipe Trench defeat \b...\b because the words are whitespace-separated:

combined string: "...refining area > refining pipe rack ..."
                                              ^^^^

In Pipe Rack:

• \b before pipe: ✓ (space → word transition)
• \b after pipe: ✓ (word → space transition)
• \bpipe\b matches pipe in Pipe Rack

The issue's stated expectation (Piping 4,014 → ~2,926) was an estimate, not an empirically verified result against this fix.

Inspection of one buggy object on this model

DisplayName: Refining Pipe Rack
SysPath:     For Review.nwd > TRAINING > Refining Area > Refining Pipe Rack
Properties:  항목|내부 유형 = RefiningPipeRack
             항목|유형 = Refining Pipe Rack
             항목|이름 = Refining Pipe Rack
             (5 more 항목|... metadata only)

Tier 1 (explicit Class): no match (no ClassDisplayName/Class property).
Tier 2 (property keys): no match (no key contains pipeline/piperun/piping/equipment).
Tier 3 (regex on combined): \bpipe\b hits pipe in Pipe Rack → returns Piping. ❌

The actual fix (PR #3)

@"\b(pipe(?!\s+(rack|trench|support|way|bridge|shoe))|valve|flange|elbow|tee|reducer|nozzle|coupling)\b"

Negative lookahead (?!\s+(rack|trench|support|way|bridge|shoe)) runs after matching pipe and rejects when one of the structural-modifier nouns follows. The other 5 class regexes (Equipment / Structure / Electrical / HVAC / Instrumentation) use plain \b...\b because the audit showed no false-positive evidence for them.

Verification results — same 12,009-object model

Class	Before	After (fixed)	Δ
Piping	3,903	3,062	-841
Structure	4,448	4,840	+392
Other	1,758	2,159	+401
Electrical	1,008	1,053	+45
HVAC	122	125	+3
Equipment	770	770	0
Total	12,009	12,009	0

Regression check: of the 2,773 objects with a non-empty SmartPlant 3D|Pipeline property, 2,773 remain Piping — zero leakage.

Pipe Rack tracking (898 objects):

	Before	After
Piping	890	109
Structure	0	392
Other	0	341
Electrical	0	45
HVAC	0	3
Equipment	8	8

The 109 residual Piping under Pipe Rack are legitimate fittings inside the rack — confirmed by spot-check:

Refining Pipe Rack > PR01-PipingSystems > PR01-PipingSys-ChillWater > PR01-BFW-2001
  > Refining Pipe Rack-6-M-0001-1C0031
  > 90 Degree Direction Change-3114        ← real elbow
  > Pipe                                    ← real pipe
  > Concentric Size Change-1001             ← real reducer

Pipe Trench (60 objects): 60 Piping → 0 Piping, all moved to Other. ✓

Note on the 153 Pipeline folder objects (§S2c)

These remain Piping in the fixed export, but they classify via Tier 2 (the key SmartPlant 3D|Pipeline exists), not Tier 3 — so the regex change doesn't affect them. Spot-check shows they ARE real fittings (90 Degree Direction Change, Concentric Size Change); the issue is that their SmartPlant 3D|Pipeline value is the placeholder string "Pipelines". This is the §S2c concern in the original issue body and is not a classification bug. Tracking separately.

Implication for downstream Python port

The acceptance criterion in the issue says C# ↔ Python should match 100% across the 192 test_xlsx_classifier.py cases. The Python port must mirror the same negative-lookahead pattern, not plain \b...\b. If the Python tests were written expecting plain word-boundary behavior, they will need to be updated alongside the port. Recommend coordinating that change in first-ontology-project after #3 merges.

What's NOT in PR #3 (out of scope)

• Part 2 (data extraction expansion: ParentId, Level int, Hierarchy sheet, dropped SP3D columns, numeric types) — follow-up PRs
• §S2c "Pipelines" placeholder filtering — separate concern, doesn't affect classification
• Unit tests — project has no test infrastructure yet; recommend bundling with §S2c fix
• Downstream Python port sync — separate repo

🤖 Generated with Claude Code