Work Order Mapping and GIS Integration

A SQL workflow that connects Cityworks work orders to GIS assets, bringing maintenance activity and its spatial context into a shared reporting structure.

My contribution

I built the SQL workflow to combine work-order details, reconcile asset references, and associate records with authoritative geometry. I separated point and line reporting layers, then configured asset-specific symbols and structured popups so the map retained both the maintenance context and the asset relationships.

Result: A shared reporting structure for viewing work-order activity alongside the point and line assets involved, with diagnostic queries to examine record counts and geometry coverage.

Tools

SQL Server
T-SQL
Cityworks
ArcGIS Pro
SQL Server spatial types

Reconstructed demonstrations use entirely synthetic records and geography. The figures illustrate the workflow, not production performance or complete geometry coverage.

Project gallery

Reconstructed demonstrations using entirely synthetic records and geography. These presentation views are not captures of the operational application or native ArcGIS interface. 3 images. Select an image to view it full size.

Illustrative map of invented point and line assets with a legend for hydrants, traffic signals, water points, water lines, sewer lines, and street segments. — Reconstructed map demonstration showing synthetic point and line work orders organized by asset type.

Synthetic work-order map, selected record panel, and reporting table. The highlighted record describes an invented fire hydrant inspection. — Reconstructed work-order detail view with invented asset references, completion status, comments, and a custom field.

Workflow diagram linking source records and GIS assets to matching, staging, transactional refresh, and point and line map layers. — Illustrative project workflow showing work-order records and GIS asset geometry feeding SQL processing and ArcGIS layers.

Connect the work to the assets

A work order may reference several assets, each with its own GIS geometry. I brought together the work-order attributes, comments, and selected custom fields, reconciled the asset identifiers, and matched the records to authoritative geometry. Coordinate-based fallback logic adds another route to locating a record.

I staged the results before replacing the reporting cache within a database transaction. Separate point and line layers in ArcGIS Pro use asset-specific symbols and structured popups to show the work alongside the assets involved.

A shared view with explicit checks

The result is a shared reporting structure for examining maintenance activity in its spatial context. Diagnostic queries distinguish work-order counts from asset records and examine geometry coverage, making those differences visible when reviewing the output.

Technical details

SQL integration · Point and line asset reporting

Maintenance records describe the work, while GIS layers describe the assets involved. Differences in identifiers and geometry make those records difficult to review together.

Work-order attributes, comments, and selected custom fields feed a shared reporting structure. Asset references are reconciled before matching records to authoritative GIS geometry; coordinate-based fallback logic provides another location path when an asset match is unavailable.
The workflow stages the combined results before replacing a reporting cache within a database transaction. This separates data preparation from the cache replacement step.
Separate ArcGIS Pro point and line layers represent the different asset geometries. Asset-specific symbols distinguish feature types, while structured popups present the related work-order details.
Diagnostic queries distinguish unique work-order counts from asset records and examine geometry coverage. Keeping these measures separate avoids treating a work order associated with several assets as several distinct work orders.
The portfolio images are reconstructed demonstrations using entirely synthetic records and geography. They illustrate the reporting design and workflow; they are not operational application screenshots or native ArcGIS interface captures.

← Back to projects