Yellowfin 7.3+: JSON connectivity has arrived

For our recent Yellowfin 7.3+ launch, we showcased the JSON connector directly accessing a real-time GeoJSON feed of earthquakes around the world from the USGS Earthquake Hazards Program, and plotting them onto a map.

Today, we’ll discuss a different method which is connecting to a JSON API endpoint. Since crime is a hot topic in the US right now, we looked at a few JSON API feeds and came across the Chicago Data Portal which houses data collection in various categories, one of which is crime.

We decided to connect to the 2017 Chicago Crime data set via the Socrata Open Data API (SODA) which provides additional abilities to filter, query, and aggregate as needed. To provide a more focused story, we narrowed it down to burglaries.

 

JSON data via REST API

Using the JSON Connector in Yellowfin 7.3+, we generated a token, provided the details below, and validated the JSON end point:

 

End Point URL: https://data.cityofchicago.org/resource/d62x-nvdr.json
Param List: $limit=10000&primary_type=BURGLARY

This then opens the JDesigner which allows Yellowfin to scan the JSON schema to extract the keys/fields for analysis. At this stage, you can also reformat the chosen fields into the desired data types for easy reporting which includes:

i) Strings to integers

ii) Strings to a chosen date format (even Epoch time)

iii) Coordinate geometry conversions

iv) Automatically combining latitude and longitude values into a POINT field

…and much more!

 

Visualizing JSON data

After selecting the fields and converting a few data types, we analyzed the burglaries data set and came up with a dashboard below:

 

 

This API provided a rich dataset that reflects reported incidents of crime that occurred in the City of Chicago from 2017 to present (minus the most recent 7 days). Some of the quick insights gleaned from this data set were:

i) Forcible entries make up over 60% of all burglary crime.

ii) Burglaries are generally decreasing over time, but both Forcible Entry and Unlawful Entry crimes spiked in April.

iii) If  you’re looking to relocate to Chicago, the boundary area between the 4th and 5th District might be the place to go to avoid burglaries.

note: insights were recorded at time of writing. As this is a real-time JSON API feed, this might change over time.

 

Discovering the Chicago Alternative Policing Strategy (CAPS)

While looking at crime hotspots, we were quite interested in the concept of a “police beat”. A beat is the smallest police geographic area. Each beat has a dedicated police beat car, with 3 to 5 beats making up a police sector, and 3 sectors making up a police district. Find out more about CAPS here.

According to the portal, the Chicago Police Department has 22 police districts:

 

 

This sparked a discussion amongst ourselves where taking the number of incidents and arrests into account, was it better to live in a district where police presence (in this case, number of beats resulting in dedicated police cars and allocated police officers) was higher for a chosen geographic area?

 

 

As you can see, using the data set above we mapped the police beat areas and discovered a few interesting points:

i) There seemed to be a data quality issue as the most current data was duplicating sector and beat values. Maybe better data governance is required?

ii) There appeared to be 0 police presence at Norridge and Harwood Heights (we discovered that this is because these areas form an enclave and aren’t part of Chicago despite being surrounded by the city) 

iii) And speaking of the lack of police presence, avoid Mt. Greenwood Cemetery at all costs as ghosts aren’t your only worry!

 

 

With the data quality issue throwing a wrench in our discovery, the question still remained: which is the safest Chicago district in terms of burglaries? We visualized the reported incidents vs arrests by district and came up with the chart below:

 

 

In regards to total burglaries, the 8th and 20th District seemed to be the most dangerous and safest respectively.

However, the most worrying insight was that the amount of arrests for burglaries in each district averaged to only around 6, with only a maximum of 10 arrests in the 24th District. This could suggest that either the police force are prioritizing other crime incidents besides burglaries, or they might just be slightly tapped out in resources.

Perhaps this is where CAPS comes in, with the goal of encouraging community members and the police force to work together and prevent crime. We would obviously need more data to investigate but perhaps the decreasing trend in burglaries for 2017 could be connected to the effectiveness of CAPS?

Either way, we were pleasantly surprised that the community members in each district could actually attend local beat meetings and have a Q&A with their officers. If you’re in Chicago and would like to do so, select a district in the chart above and click through to attend the next beat meeting!

 

See what’s new in Yellowfin

To learn more about the Yellowfin 7.3+ beta, visit our What’s New page and continue the conversation at our Yellowfin Community