SQL Server and GIS Data Integration
Synthetic reconstruction of SQL data-preparation workflows for address and parcel matching, spatial document indexing, and controlled reporting refreshes.
Why it matters
A reporting layer is only useful when its records can be traced to the right locations. These reconstructed workflows expose ambiguous matches, preserve document identity, and make refresh and public-access rules explicit.
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
The reconstructed workflows produce normalized addresses, document-to-grid relationships, and map-ready reporting data while keeping ambiguous matches, missing geometry, and failed refreshes visible. Failed cache replacements retain the last valid data.
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
Redesigned portfolio figures use synthetic data and recorded local validation. Parcel geometry is invented for explanation; document grids use synthetic seed geometry. These are not operational maps or screenshots of a deployed application. 5 images. Select an image to view it full size.





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.
Validation
48 original scripts accounted for. Recorded local validation: all 17 SQL modules and four SQL test suites passed twice; 29 Python tests passed. SQL checks used the same disposable SQL Server 2022 Developer database. Python checks used Windows, Python 3.11.6, pyproj 3.7.2, PROJ 9.5.1, synthetic HTTP responses, and temporary files; no live API requests were made.
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.