SQL Server to ArcGIS Online

A modernized SQL-to-GIS workflow with spatial data checks, reviewable SQL plans, and separate ArcGIS Online and Server publishing tools.

My contribution

I built the original 2018 tool to prepare SQL Server spatial tables, schedule refreshes, and publish ArcGIS Server services. I modernized it around explicit data rules and reviewable steps, adding SQL-to-GeoJSON export, private ArcGIS Online publication, and desktop and browser workspaces.

Result: An implemented desktop and command-line publishing workflow, with a browser workspace for planning. Recorded local validation: 167 Python tests and 11 JavaScript tests passed.

Tools

Python
SQL Server / Spatial SQL
Flask / JavaScript
Tkinter
GeoJSON / Shapely
ArcGIS Online REST API
ArcPy / ArcGIS Pro / Server

Real application screenshots use fictional demonstration data. Live SQL Server and ArcGIS integrations remain unverified; Online publication produces a snapshot, not continuous synchronization.

Project gallery

Focused application views using fictional demonstration data. The Online screen shows an offline review, not a completed upload. 5 images. Select an image to view it full size.

Focused light Studio view of the schematic map with twelve fictional assets, publishing steps, and linked inventory controls. — Focused Studio map and linked inventory at the same zoom as the other browser captures. Fictional sample data.

Focused light Studio overview showing sample metrics, a schematic map, and publishing steps for twelve fictional assets. — Light Studio overview at a fixed viewing scale: sample metrics, schematic map, and publishing workflow.

Focused light publishing guide showing contract definition, snapshot export, offline destination review, and private ArcGIS Online publication steps. — Publishing steps and supporting guidance at the shared browser zoom. This is a focused viewport, not a full-page capture.

Light native Tkinter window showing a fictional data contract, generated SQL preview, and GeoJSON export controls; no SQL was executed. — Native light desktop contract editor and SQL preview at a fixed 1280x800 window size; no SQL execution.

Light native Tkinter Online review of twelve fictional GeoJSON records, with a blank token field and no upload or ArcGIS Online connection. — Native light desktop review at the same window size and scale; twelve fictional records, blank token, and no upload.

Review the data, then publish

SQL records need valid feature IDs and geometry before they can become GIS layers. This workflow defines those rules, prepares SQL plans, and exports GeoJSON for private ArcGIS Online publication. ArcGIS Pro/Server has a separate staging and upload path. The desktop and command-line tools implement publication; the browser prepares plans and explains the steps.

Studio and Atlas offer two light browser layouts with linked map and table selection, search, contract editing, and SQL previews. A light Tkinter desktop workspace brings export, publication review, and confirmation together.

Keep data changes and publication explicit

Stable source IDs preserve feature identity. Data contracts expose schema and geometry requirements before processing, while transactional refresh plans check the source and inserted counts before committing changes. Separate review, staging, and upload steps make the intended destination and action visible before publication.

Technical details

Original tool: 2018 · Modernized desktop and browser workflows

Turning SQL records into GIS layers requires consistent feature IDs, valid geometry, and a controlled path from data preparation to publication.

The original desktop and Flask interfaces coordinated spatial table preparation, indexing, scheduled refresh, and MXD-based ArcGIS Server publication. The modernization separates configuration, SQL planning, database execution, and publication into a shared Python core with dedicated adapters.
A data contract defines allowed fields and types, a stable source feature ID, and geometry requirements. SQL identifiers and types are validated before plan generation. Refresh checks managed-table ownership and schema, validates a source snapshot, and replaces rows within a transaction while preserving the table and indexes. Inserted-row counts are checked before commit. This is a full refresh, not incremental change tracking.
SQL-to-GeoJSON export reads the source directly and validates supported WGS84 geometry and properties. The SQL source key is retained as an attribute because ArcGIS assigns its own hosted object IDs.
The implemented ArcGIS Online adapter reviews the GeoJSON and destination account, checks service-name availability, uploads a new private hosted layer, polls the publishing job, and checks private ownership. It creates a snapshot rather than continuous synchronization. Tests simulate the external responses; a live upload has not been verified.
The separate ArcGIS Pro/Server path stages a new service definition from a prepared project, checks map sources, and verifies the definition and connection again before upload. It targets standalone ArcGIS Server map services and does not intentionally replace an existing service.
The native Tkinter interface supports contracts, SQL previews, SQL export, Online review and publication, and Server staging and upload. Background workers keep network tasks separate from interface interaction; publication requires confirmation of the reviewed destination. Tokens stay in memory and out of saved contracts.
The Flask workspace links a schematic map and searchable, filterable asset table. Contract import, editing, and export feed SQL previews and downloads. The browser prepares plans and publishing guidance; it has no database execution or publication endpoint. Map filters explore the fictional sample and do not filter the SQL source.
Studio and Atlas browser designs default to light mode, including their map and code editors, with optional remembered dark themes. The native desktop workspace also uses a light design. Screenshots show actual interfaces with fictional demonstration data; the Online view is an offline review with empty credentials.
Recorded local validation includes 167 passing Python tests and 11 passing JavaScript tests. SQL guards are inspected as generated text, and external integrations use mocked adapters. A separate light-theme review reran 16 web tests and 12 desktop tests, along with the JavaScript suite; these are review subsets, not additional tests to add to the baseline. Browser layouts were checked at 390 and 320 pixels, with native desktop captures and layout checks also recorded.
Live SQL Server, ArcGIS Online, and ArcGIS Pro/Server integration remain unverified. The evidence does not establish production deployment, adoption, time savings, or performance gains.

← Back to projects