Authors: Sahithi K.S & Unika
About Us:
We are a crime analytics company that maps crimes and carries out analysis of crime-related data. We help organizations expand their businesses and help them make better business decisions by providing insights and statistics from crime-analyzed data.
We used various tools and technologies to simplify data, process it and give a fully refined version of the data to our clients that can help them to analyze safety measures.
Why Crime Analytics?
There have been various types of crimes like robbery, rape, vandalism, theft, etc. Due to the increasing crimes, few companies which deliver items or provide cab services, like FedEx, Uber, etc., want to make sure that safety is their priority. There have been huge losses in these services due to the crimes.
We give precise insights about crimes through our analyzed data which helps companies to secure their services and provide a safe outcome.
Challenges:
Delivery boys are robbed, and all the items are stolen.
Females are being raped
The passengers in cabs are not safe and are unsure about the safe route
Delivery of large goods is robbed
Thefts and Vandalism in insecure areas.
Problem Statement
Present Scope:
Logistics companies face challenges in finding safer locations to set up their warehouses and pick-up points and safer time durations to transport their packages from one place to another. E.g., FedEx
Future Scope:
Real estate advertising platforms would want to attract more businesses by increasing customers' visits and revisit their platforms by listing properties like new homes, resale homes, rentals, plots, and co-living spaces in safe and secure areas.
E.g,: Zillow.com, Realtor.com
Cab service providers face challenges in improving security for their cab drivers, customers, and their vehicles. E.g., Uber
How can we help?
Logistic firms can use our insights and information based on crime patterns to set up warehouses in safer places along with pick-up points for the logistic partners.
Real estate advertising platforms can use crime-related analytics to improve the sales/rentals of properties listed on their platforms.
Using our crime-based analytics, cab aggregators can implement safer routes for navigation for their customers.
Architecture
Data Sources:
In this project, we collected data from the data.lacity.org government website in a variety of ways, like API data, RDS, and AWS S3 of structured and semi-structured data.
Data Ingestion:
It is the process of transferring data from one or more sources to a destination. For this, we have used Airbyte, which connects rds and API and places data in raw data storage. The geolocation data is used on the Visualizations dashboards to locate the locations in maps. The data in the s3 bucket is loaded into Snowflake using Snowpipe. Click here.
FLOW CHART FOR INGESTION:
Snowflake Data Cloud:
This is the most important part of the architecture, and it contains mainly four steps:
RAW
CONFORMED
CONSUMPTION
GOVERNANCE
Raw:
We are maintaining the landing zone for both structured and semi-structured data under the raw layer, which is transformed in Snowflake itself; refer to structured data, click here. Refer for semi-structured data, click here.
Conformed:
The processing of data is performed here. In this layer, we have used Snowflake features like streams and tasks, stored procedures where streams allow developers to place a query and extract information in a table and define changes to a table in rows as well as between two-time intervals and a task defines a recurring schedule click here, and stored procedures allow you to write procedural code that executes SQL. In a stored procedure, you can use programmatic constructs to perform branching and looping. Click here.
Consumption:
In order to protect the functionality of a view, we have used the secure views concept and also the Secure Data Sharing because in secure data sharing, no actual data is copied or transferred between accounts. All sharing is accomplished through Snowflake’s unique services layer and metadata store. Click here; we have also used zero-copy cloning which does not copy the data and only creates a reference to the original Data. We also implemented column-level masking, which lets you assign the MASKED attribute to columns so that unprivileged users cannot view the data click here.
Governance:
In the governance zone, we have implemented RBAC Role-based access control, which is a popular mechanism to enforce authorization in applications. While using RBAC, an application developer defines roles rather than authorizing individual users or groups click here; Snowflake supports MFA to provide increased login security for users connecting to Snowflake click here; Snowflake supports using key pair authentication for enhanced authentication security as an alternative to basic authentication (i.e., username and password), Instead of requiring a user's password, it is possible to confirm the client's identity by using asymmetric cryptography algorithms, with public and private keys Click here and for monitoring, we implemented data cataloging, it refers to an organized record of data assets that use Metadata to facilitate Data Management in an organization. Click here.
CI/CD Pipeline:
We used DBT for continuous ingestion and continuous deployment click here.
Flow chart:
Metriql:
Metriql makes metrics globally accessible to every other tool in the data stack. Rather than each tool defining its aggregations, the metrics layer is a centralized clearinghouse for how all metrics are calculated.
In our project, we used two ways of connecting metriql:
Visualizations:
We have used Tableau for creating dashboards click here and Snowsight for monitoring Snowflake resources.
Slack Notifications:
We have implemented slack notifications for the ingestion and deployment part to notify whether the jobs ran successfully or not.
Impact:
After going through our analyzed data and visualizations, logistic companies like FedEx were in a better position to make decisions for setting up warehouses and for delivering goods and services to the customer.
Cab aggregator platforms like uber were able to use much safer routes for the passengers using the insights provided by our data.
With the help of our data, governing bodies like the city council and law enforcement organizations could analyze the crime patterns in a much more efficient way and make better decisions, because of which the crime rates in the city have reduced.