Crime Analysis

Automated database updates, address lookup, and an interactive GIS dashboard for crime reporting.

Why it matters

A reporting map needs current records and usable locations. This workflow keeps the map connected to database changes, looks up missing coordinates, and highlights records that still need review.

My contribution

I contributed to this project end to end, from automated SQL Server data updates and ArcGIS Online layers to dashboard design and implementation.

Data updates and address lookup

Python updates the online GIS layers from SQL Server, checking for missing or extra records while preserving valid duplicates. It fills missing coordinates using local address data, backup services when needed, and saved results for reuse.

Dashboard design and reporting

I designed and implemented the dashboard below using the updated ArcGIS Online layers.

Result

The workflow updates the dashboard layers, looks up missing coordinates, and checks for missing or extra records after each update. Valid duplicate records stay intact, and unresolved addresses remain visible for review.

Tools

Python
SQL Server
ArcGIS Online
ArcPy
pandas
SQLite
ArcGIS Dashboards

Update checks detect missing or extra records; they do not verify every field value or map location.

Project gallery

Explanatory portfolio panels based on saved outputs from actual Python functions using synthetic records and simulated services. These are not screenshots of a deployed application. No AI image generation was used. 3 images. Select an image to view it full size.

Portfolio panel explaining how database records pass through Python into hosted GIS layers, with location enrichment and update checks. — Explanatory portfolio panel showing the project's workflow. The supporting examples use actual saved outputs from a local demonstration with synthetic records and simulated services.

Portfolio panel showing a synthetic refresh preserving intended duplicates, adding a new entry, and removing obsolete and excess entries. — Explanatory panel based on actual local synchronization outputs. Synthetic records and a simulated hosted layer demonstrate the change decisions; no live layer was edited.

Portfolio panel explaining accepted, unresolved, and preserved location results from synthetic addresses and simulated locators. — Explanatory panel based on the project's geocoding and SQLite cache outputs. Addresses and locator responses are synthetic. The example demonstrates decisions and cache reuse, not real-world location accuracy.

Open original application (opens in a new tab or window)

Loading the application connects to the external provider. If it cannot load here, use the original application link above.

Technical details

Professional · Geospatial data engineering and software development

The dashboard needed updates from a SQL Server database without repeated manual exports. Records with missing coordinates also needed address lookup before they could appear on a map.

Check the source schema, map fields, and compare source counts with prior run state before updating hosted layers.
Use content hashes and occurrence counts to identify changes while preserving legitimate duplicate records.
Enrich missing coordinates through local-first geocoding, fallback services, cached results, configurable acceptance rules, and request limits.
Support manual coordinate corrections and explicit rejection overrides for address matches.
Prevent overlapping runs with an exclusive lock, retain persistent run state, and send operational alerts.
Keep rejected edits separate from uncertain network outcomes, then reconcile stored content hashes and record counts after updates. This does not independently verify every hosted attribute or geometry.
Design and implement the dashboard using the hosted GIS layers for reporting.

Record comparison

Same total. Different records.

Three records on each side can still hide a missing copy and an extra record.

In the database

3 records

Record AFirst copy
Record AA second, legitimate copy
Record BDifferent contents

In the online layer

3 records

Record AOnly one copy remains
Record BCount matches
Record CAn extra record appears

−1Missing copy of AExpected two. Found one.

+1Extra record CExpected none. Found one.

Check matching contents and the number of copies
Record	Database	Online	Finding
A	2	1	1 missing
B	1	1	Count matches
C	0	1	1 extra

Keep legitimate duplicates. Compare more than the total.

Synthetic example. A, B and C represent matching contents, not unique record IDs. The pipeline compares stored record fingerprints and counts; it does not independently confirm every field value or map location.

← Back to projects