PORTFOLIO

Project Description:

With the Colorado State Team Awareness Kit (COTAK) program gaining significant adoption in 2024 and member agencies increasingly seeking to host their own situational awareness dashboards, a clear need emerged to expose Computer-Aided Dispatch (CAD) incidents and Automatic Vehicle Location (AVL) data within a geospatial environment. At Jeffcom911, CAD data is maintained in a SQL Server database that does not provide a native API for integration with ArcGIS Enterprise or ArcGIS Online. In parallel, real-time AVL data is broadcast through a NATS message broker rather than a traditional REST service. To meet operational demand, a custom geospatial integration workflow was designed to bridge these systems.

The CAD component of this workflow implements a near–real-time synchronization pipeline between the Jeffcom911 CAD SQL database and an ArcGIS feature service. The script authenticates to the ArcGIS portal using stored credentials via keyring, enabling secure, non-interactive access suitable for unattended execution. In consideration of CJIS requirements, all personally identifiable information and CAD narrative content are excluded from the GIS representation. Instead, records include a reference link that allows authenticated users on the CAD network to retrieve additional incident details as needed.

Operational CAD data is queried from SQL Server using pyodbc and ingested into Pandas and GeoPandas data frames for spatial processing and attribute management. Latitude and longitude values are converted into point geometries, spatially referenced, and projected to Web Mercator to align with enterprise GIS standards. Timestamp fields are normalized to UTC, with explicit handling of NaN values and placeholder dates to prevent invalid edits and ensure temporal consistency.

The script performs full lifecycle synchronization against the target feature layer, including updates to existing incidents, insertion of newly created records, and removal of stale features that no longer exist in the source system. Data validation, schema enforcement, and NaN handling are applied prior to all edit operations, which are processed in batches to support high-frequency execution. This process runs once per minute as a Windows service using NSSM (Non-Sucking Service Manager), providing resilience and continuous operation.

In addition to CAD synchronization, a complementary real-time AVL integration consumes live vehicle status and location updates from a NATS messaging server using an asynchronous Python workflow. AVL messages are parsed as they are received and evaluated against operational criteria, such as unit status and unit type. Active units are written to an ArcGIS feature layer as point features, while units returning to an available status are automatically removed to ensure the map reflects only operationally relevant resources.

The AVL workflow performs conditional adds, updates, and deletes against the feature layer, allowing vehicle locations to update continuously without requiring scheduled polling. This event-driven architecture supports low-latency updates and scales efficiently during high-activity incidents.

Together, these workflows provide a unified, near–real-time geospatial representation of incidents and responding resources. The resulting feature services support internal situational awareness dashboards and directly feed a COTAK channel, enabling live operational visibility for command staff and field personnel during emergency response.