Swapped Coordinates and the Honest Import

I opened the intake spreadsheet for a designated heritage site registry. It contained 22 records and their associated photos. Scrolling to the coordinate columns, I noticed an immediate anomaly. The Latitude column displayed values formatted as 106 degrees 45 minutes 23.1 seconds East. The Longitude column showed 6 degrees 23 minutes 36.4 seconds South. The columns were entirely swapped at the source.

My initial assumption was straightforward. I planned to implement a quick header swap in the parsing logic, followed by a standard bulk insert.

Uncovering Hidden Defects

A closer inspection revealed that a simple swap was insufficient. The dataset contained three additional defect classes. First, several longitude prefixes displayed 108 or 109 degrees, which were highly likely typographical errors for 106 degrees. Second, one record shared identical coordinates with its neighboring entry. Third, one record contained no coordinate data whatsoever.

This aligns with established data science principles. Plausible-but-wrong entries frequently remain undetected without explicit, context-aware validation [3]. Relying solely on format checks would have allowed these corrupted records to pass silently into the production database.

Building an Honest Importer

I redesigned the ingestion pipeline to handle these discrepancies explicitly. The parser now reads the source latitude column as longitude, and vice versa. Crucially, the system stores the original DMS (Degrees, Minutes, Seconds) strings verbatim in the entity attributes before any numeric conversion occurs. This practice preserves the raw input and prevents irreversible data loss [4].

During conversion, the system applies standard hemisphere designators and negative-west decimal conventions to validate the numeric output [1]. If a row fails validation or triggers a proximity match with an existing record, the importer halts the publish action. Instead, it routes the suspect row to a draft state with a geo_review flag attached. This ensures that standardization efforts do not silently overwrite ambiguous source material [2].

The Resulting Workflow

The revised importer processed the 22 records with clear outcomes. Sixteen records passed validation and were published. Six records were routed to draft due to missing data or suspicious coordinate values.

The pipeline is fully idempotent. It utilizes a slug index keyed directly by the source row number. Executing a second run against the same spreadsheet resulted in exactly 22 skips, confirming that no duplicate processing or state mutation occurred.

A final anomaly emerged during the media attachment phase. The manifest listed 23 images. However, only 22 were actual entity photos. The 23rd file was merely a screenshot of a summary table. The ingestion logic correctly identified the file type mismatch and ignored it, preventing a broken image link in the final registry.

Sources

1. NOAA NCAT — Latitude-Longitude-Height Service (DMS and decimal formats)
2. NOAA Office of National Marine Sanctuaries — Standardized Coordinates
3. Veridical Data Science, Chapter 4 — Data Cleaning
4. J-PAL — Data Cleaning and Management Guide