SQL Server and GIS Data Integration

Reconstructed SQL and GIS workflows for address quality, spatial document indexing, and reporting, demonstrated with synthetic data.

My contribution

I reconstructed recurring workflows from my working SQL archive as documented examples with synthetic data. I made address and parcel matching, document-to-map relationships, and reporting rules explicit, with checks for missing data, duplicate identities, and failed refreshes.

Result: 48 original scripts accounted for. Recorded local validation: all 17 SQL modules and four SQL test suites passed twice; 29 Python tests passed.

Tools

SQL Server / T-SQL
Spatial SQL
Python
JSON
ArcGIS REST
PROJ / pyproj

Synthetic reconstruction and recorded local testing, not production or live-service validation. The reconstruction and tests were developed with AI assistance.

Project gallery

Portfolio presentation views based on synthetic data and recorded local validation. These are not screenshots of a deployed application. 5 images. Select an image to view it full size.

Portfolio overview of address quality, spatial document indexing, work-order reporting, and data integration, with recorded counts of 48 original files, 17 SQL modules, and 29 Python tests. — Project overview: reconstructed workflows using synthetic data.

SQL query and recorded diagnostics for seven synthetic addresses: three matched, one ambiguous, and three unmatched; uncertain parcel matches remain visible for review. — Address quality: synthetic parcel-match results and SQL.

Three synthetic map grids, each with two documents, alongside SQL for six unique document/grid pairs and a selected JSON summary. — Spatial indexing: synthetic grids, document counts, and JSON.

Public-view SQL requiring explicit approval, with 11 internal and six public synthetic work-order entities and checks excluding private comments. — Public work-order rules: SQL and locally verified synthetic assertions.

Recorded local validation of 17 SQL modules and four SQL test suites executed twice, plus 29 passing Python tests; live services and hosted CI are outside the evidence. — Validation evidence: recorded local SQL and Python checks.

Connecting records to locations

Address normalization makes inconsistent records comparable while keeping uncertain parcel matches visible for review. Spatial document indexing links plans to map grids, preserves different documents with the same name, and flags invalid references.

Reporting and reliable refreshes

Work-order reports retain the source of each asset location. Separate public views include only explicitly approved records and omit private comments. JSON ingestion checks incoming data before replacement, and failed refreshes preserve the last valid cache. Coordinate transformation requires a known source reference system.

Technical details

Working-archive reconstruction · Recorded local validation: October 6, 2026

Business records need consistent addresses, traceable links to spatial assets, and clear rules for reporting and public access.

Account for all 48 original script filenames through reconstructed examples or explicit retirements. The original working archive remains private; this collection does not reproduce its credentials or operational records.
Normalize address units, fractional numbers, Unicode, and suffix references while preserving source identity. Report ambiguous or missing parcel matches instead of silently choosing a candidate.
Convert multivalue document metadata into document/grid relationships. Preserve distinct documents with the same name, identify invalid grid references, and produce map-ready JSON summaries.
Reconcile work orders with GIS assets through parent-service and source-layer identifiers. Select duplicates deterministically, retain geometry provenance, and report why geometry is missing.
Separate internal and public reporting views. The synthetic public-data rules require explicit opt-in and exclude private comments; they do not establish the original organization's policy.
Stage validated JSON in SQL Server, retrieve complete ArcGIS ID batches in the reconstructed adapter, and preserve the last valid cache when replacement fails. Adapter tests use synthetic responses rather than live APIs.
Transform coordinates through PROJ with an explicit source coordinate reference system. The synthetic coordinate tests do not establish the CRS or accuracy of any historical dataset.
Recorded local validation on October 6, 2026: 17 SQL modules and four SQL test suites passed in sequence twice against the same disposable SQL Server 2022 Developer database. An existing unmarked database was refused before demo objects were created. The isolated container was removed afterward.
Recorded Python validation: 29 tests passed on Windows with Python 3.11.6, pyproj 3.7.2, and PROJ 9.5.1, with no failures, errors, or skips. Checks used synthetic HTTP responses and temporary files without live API requests.
Source hashes tie the recorded checks to the reviewed implementation. These results establish synthetic functional behavior, not historical source correctness, production performance, adoption, deployment, live-API compatibility, or passing hosted CI.

← Back to projects