NYC 311 Service OpenData – ELK (Elastic, Kibana, Logstash)
Programming Language Used : Logstash Config, CURL
Software Used : Google Cloud Platform (GCP), Elasticsearch, Kibana, Logstash
Github Link : https://github.com/sidthree6/NYC_311_calls_ELK
Description :
I have gathered data from Newyork open data website where it contains huge data for 311 service requests from 2010 to present. Using Google Cloud Platform (GCP) and ELK stack I have attempted to analyzed data. Before analysing I had to create a template mapping in elasticsearch using curl and then write logstash config file to ingest huge data into elastic.
In the end using Kibana we are visualizing data using Kibana dashboard inferface and trying to answer 4 analytical question.
- Create a table showing the top 10 cities with the highest calls alongside the count of top 10 complaint calls (by Descriptor) in each city.
- Create a pie chart showing the top 5 cities with the highest calls alongside the top five calls (Descriptor) in each city
- Create a tag cloud representing the top 20 call descriptors.
- Create a coordinated map of all the major call descriptors in each city
Creating Mapping in Elastic using CURL.
curl -XPUT "http://localhost:9200/_template/nyccalls" -H 'Content-Type: application/json' -d'
{
"order": 1,
"index_patterns": ["nyccalls*"],
"mappings":
{
"properties":
{
"Location":
{
"type": "geo_point"
},
"Created Date":
{
"type": "date",
"format": ["yyyy-MM-dd"T"HH:mm:ss.SSS"Z""]
}
}
}
}'
Creating config file to ingest data into elastic
input {
file {
path => "/home/sidd/rows.csv"
start_position => "beginning"
sincedb_path => "/dev/null"
}
}
filter {
if[message] =~ /^#/ {
drop{}
}
csv {
separator =>","
columns => ["Unique Key","Created Date","Closed Date","Agency","Agency Name","Complaint Type","Descriptor","Location Type","Incident Zip","Incident Address","Street Name","Cross Street 1","Cross Street 2","Intersection Street 1","Intersection Street 2","Address Type","City","Landmark","Facility Type","Status","Due Date","Resolution Description","Resolution Action Updated Date","Community Board","BBL","Borough","X Coordinate (State Plane)","Y Coordinate (State Plane)","Open Data Channel Type","Park Facility Name","Park Borough","Vehicle Type","Taxi Company Borough","Taxi Pick Up Location","Bridge Highway Name","Bridge Highway Direction","Road Ramp","Bridge Highway Segment","Latitude","Longitude","Location"]
}
date {
match => ["Created Date", "MM/dd/yyyy hh:mm:ss a"]
target => "Created Date"
}
mutate {convert => ["X Coordinate (State Plane)","integer"]}
mutate {convert => ["Y Coordinate (State Plane)","integer"]}
mutate {convert => ["Latitude","float"]}
mutate {convert => ["Longitude","float"]}
mutate {replace => { "Location" => "%{Latitude},%{Longitude}" }}
if [Location] == "%{Latitude},%{Longitude}" {
mutate { remove_field => "Location" }
}
}
output {
elasticsearch {
hosts => "localhost"
index => "nyccalls"
}
#stdout {}
}Top 10 Cities with Top 10 Complaint Calls
All cities complaint call radial diagram
Top 20 Complaint Calls
Geo Location of Calls
Designed by Sidd @ 2022