1. Getting the number of homicides and thefts in Guerrero over a period of time. Filters:
- crime_category => [homicide, theft]
- incident_date => [start_date, end_date]
- location => [guerrero]
Aggregations:
X Axis => incident_date
X Segment => crime_category
Y Axis => None
Y Operation => COUNT <default>
{
"crimes" : [ "homicide" , "theft" ] ,
"start-date" : "2018-01-01" ,
"end-date" : "2018-12-31" ,
"locs" : [ "e6f22e88-9bd2-4fdc-b802-25b726bb48e3" ] ,
"x-axis" : "incident_date" ,
"x-segment" : "crime_category" ,
'y-axis' : 'crime_category' ,
'y-operation' : 'COUNT' ,
} gb = new GraphicBuilder (owner )
qb .set_filter ('crime_category' , ['homicide' , 'theft' ])
gq .set_date_filter ('incident_date' , ['2018-01-01' , '2018-12-31' ])
gq .set_filter ('state' , 'e6f22e88-9bd2-4fdc-b802-25b726bb48e3' )
# gq.set_filters({
# 'crime_category': ['homicide', 'theft'],
# 'incident_date': ['2018-01-01', '2018-12-31'],
# 'state': 'e6f22e88-9bd2-4fdc-b802-25b726bb48e3'
# })
gq .x_axis ('incident_date' )
gq .x_segment ('crime_category' )
results = gq .execute (){
'data' : [
{ 'x' : '2018-01-01 00:00:00' , 'y0' : 2 , 'y1' : 5 } ,
{ 'x' : '2018-02-01 00:00:00' } ,
{ 'x' : '2018-03-01 00:00:00' } ,
{ 'x' : '2018-04-01 00:00:00' , 'y0' : 4 , 'y1' : 8 } ,
{ 'x' : '2018-05-01 00:00:00' } ,
{ 'x' : '2018-06-01 00:00:00' , 'y0' : 10 , 'y1' : 22 }
] ,
'meta' : {
'segments' : {
'y0' : {
name : 'homicide'
} ,
'y1' : {
name : 'theft'
}
}
}
} {
"size" : 0 ,
"query" : {
"bool" : {
"must" : [
{ "terms" : { "crime_category" : [ "homicide" , "theft" ] } } ,
{ "terms" : { "location.state_id" : [ "e6f22e88-9bd2-4fdc-b802-25b726bb48e3" ] } } ,
{
"range" : {
"incident_date" : {
"gte" : "2018-01-01 00:00:00" ,
"lte" : "2018-12-31 00:00:00"
}
}
}
]
}
} ,
"aggs" : {
"by_main_grouping" : {
"date_histogram" : {
"field" : "incident_date" ,
"interval" : "month"
} ,
"aggs" : {
"by_segment" : {
"terms" : {
"field" : "crime_category" ,
"include" : [ "homicide" , "theft" ] ,
"min_doc_count" : 0
} ,
"aggs" : {
"by_operation" : {
"cardinality" : {
"field" : "_id"
}
}
}
}
}
}
}
} "aggregations" : {
"by_main_grouping" : {
"buckets" : [
{
"key_as_string" : "2018-01-01 00:00:00" ,
"key" : 1514764800000 ,
"doc_count" : 1 ,
"by_segment" : {
"doc_count_error_upper_bound" : 0 ,
"sum_other_doc_count" : 0 ,
"buckets" : [
{
"key" : "theft" ,
"doc_count" : 1 ,
"by_operation" : {
"value" : 1
}
} ,
{
"key" : "homicide" ,
"doc_count" : 0 ,
"by_operation" : {
"value" : 0
}
}
]
}
} ,
{
"key_as_string" : "2018-02-01 00:00:00" ,
"key" : 1517443200000 ,
"doc_count" : 0 ,
"by_segment" : {
"doc_count_error_upper_bound" : 0 ,
"sum_other_doc_count" : 0 ,
"buckets" : [ ]
}
} ,
{
"key_as_string" : "2018-03-01 00:00:00" ,
"key" : 1519862400000 ,
"doc_count" : 0 ,
"by_segment" : {
"doc_count_error_upper_bound" : 0 ,
"sum_other_doc_count" : 0 ,
"buckets" : [ ]
}
} ,
{
"key_as_string" : "2018-04-01 00:00:00" ,
"key" : 1522540800000 ,
"doc_count" : 0 ,
"by_segment" : {
"doc_count_error_upper_bound" : 0 ,
"sum_other_doc_count" : 0 ,
"buckets" : [ ]
}
} ,
{
"key_as_string" : "2018-05-01 00:00:00" ,
"key" : 1525132800000 ,
"doc_count" : 0 ,
"by_segment" : {
"doc_count_error_upper_bound" : 0 ,
"sum_other_doc_count" : 0 ,
"buckets" : [ ]
}
} ,
{
"key_as_string" : "2018-06-01 00:00:00" ,
"key" : 1527811200000 ,
"doc_count" : 2 ,
"by_segment" : {
"doc_count_error_upper_bound" : 0 ,
"sum_other_doc_count" : 0 ,
"buckets" : [
{
"key" : "homicide" ,
"doc_count" : 1 ,
"by_operation" : {
"value" : 1
}
} ,
{
"key" : "theft" ,
"doc_count" : 1 ,
"by_operation" : {
"value" : 1
}
}
]
}
}
]
}
} 2. Getting the loss amount of each employee over a period of time. Filters:
- incident_date => [start_date, end_date]
Aggregations:
X Axis => incident_date
X Segment => employee
Y Axis => loss_amount
Y Operation => SUM
[
{ xAxis : '2018-01-01' , segment1 : 10000 , segment2 : 5000 } ,
{ xAxis : '2018-01-02' , segment1 : 9000 , segment2 : 6000 } ,
{ xAxis : '2018-01-03' , segment1 : 6600 , segment2 : 4250 } ,
{ xAxis : '2018-01-04' , segment1 : 8500 , segment2 : 1300 }
] {
"size" : 0 ,
"query" : {
"bool" : {
"must" : [
{
"range" : {
"incident_date" : {
"gte" : "2018-01-01 00:00:00" ,
"lte" : "2018-12-31 00:00:00"
}
}
}
]
}
} ,
"aggs" : {
"by_main_grouping" : {
"date_histogram" : {
"field" : "incident_date" ,
"interval" : "month"
} ,
"aggs" : {
"by_segment" : {
"terms" : {
"field" : "source.name"
} ,
"aggs" : {
"by_operation" : {
"sum" : {
"field" : "loss_amount.min"
}
}
}
}
}
}
}
} "aggregations" : {
"by_main_grouping" : {
"buckets" : [
{
"key_as_string" : "2018-01-01 00:00:00" ,
"key" : 1514764800000 ,
"doc_count" : 13 ,
"by_segment" : {
"doc_count_error_upper_bound" : 0 ,
"sum_other_doc_count" : 0 ,
"buckets" : [
{
"key" : "Carlos Soria Velazquez" ,
"doc_count" : 13 ,
"by_operation" : {
"value" : 13000
}
}
]
}
} ,
{
"key_as_string" : "2018-02-01 00:00:00" ,
"key" : 1517443200000 ,
"doc_count" : 12 ,
"by_segment" : {
"doc_count_error_upper_bound" : 0 ,
"sum_other_doc_count" : 0 ,
"buckets" : [
{
"key" : "Carlos Soria Velazquez" ,
"doc_count" : 12 ,
"by_operation" : {
"value" : 12000
}
}
]
}
} ,
{
"key_as_string" : "2018-03-01 00:00:00" ,
"key" : 1519862400000 ,
"doc_count" : 19 ,
"by_segment" : {
"doc_count_error_upper_bound" : 0 ,
"sum_other_doc_count" : 0 ,
"buckets" : [
{
"key" : "Carlos Soria Velazquez" ,
"doc_count" : 19 ,
"by_operation" : {
"value" : 19000
}
}
]
}
} ,
{
"key_as_string" : "2018-04-01 00:00:00" ,
"key" : 1522540800000 ,
"doc_count" : 4 ,
"by_segment" : {
"doc_count_error_upper_bound" : 0 ,
"sum_other_doc_count" : 0 ,
"buckets" : [
{
"key" : "Carlos Soria Velazquez" ,
"doc_count" : 4 ,
"by_operation" : {
"value" : 4000
}
}
]
}
} ,
{
"key_as_string" : "2018-05-01 00:00:00" ,
"key" : 1525132800000 ,
"doc_count" : 13 ,
"by_segment" : {
"doc_count_error_upper_bound" : 0 ,
"sum_other_doc_count" : 0 ,
"buckets" : [
{
"key" : "Carlos Soria Velazquez" ,
"doc_count" : 13 ,
"by_operation" : {
"value" : 13000
}
}
]
}
} ,
{
"key_as_string" : "2018-06-01 00:00:00" ,
"key" : 1527811200000 ,
"doc_count" : 34 ,
"by_segment" : {
"doc_count_error_upper_bound" : 0 ,
"sum_other_doc_count" : 0 ,
"buckets" : [
{
"key" : "Carlos Soria Velazquez" ,
"doc_count" : 34 ,
"by_operation" : {
"value" : 34000
}
}
]
}
} ,
{
"key_as_string" : "2018-07-01 00:00:00" ,
"key" : 1530403200000 ,
"doc_count" : 2 ,
"by_segment" : {
"doc_count_error_upper_bound" : 0 ,
"sum_other_doc_count" : 0 ,
"buckets" : [
{
"key" : "Carlos Soria Velazquez" ,
"doc_count" : 2 ,
"by_operation" : {
"value" : 2000
}
}
]
}
}
]
}
} 3. Getting the loss amount of each employee ever. Aggregations:
X Axis => employee
Y Axis => loss_amount
Y Operation => SUM
[
{ xAxis : 'Carlos Soria' , segment1 : 150400 } ,
{ xAxis : 'Iván Munguía' , segment1 : 45000 } ,
{ xAxis : 'Luis Zepeda' , segment1 : 13000 }
] {
"size" : 0 ,
"query" : { "match_all" : { } } ,
"aggs" : {
"by_main_grouping" : {
"terms" : {
"field" : "source.name"
} ,
"aggs" : {
"by_operation" : {
"sum" : {
"field" : "loss_amount.min"
}
}
}
}
}
} "aggregations" : {
"by_main_grouping" : {
"doc_count_error_upper_bound" : 0 ,
"sum_other_doc_count" : 0 ,
"buckets" : [
{
"key" : "Carlos Soria Velazquez" ,
"doc_count" : 527 ,
"by_operation" : {
"value" : 11168000
}
}
]
}
} 4. Getting the ocurrence of all crimes in Tamaulipas Filters:
- location.state_id => [tamaulipas]
Aggregations:
X Axis => crime_category
Y Axis => None
Y Operation => COUNT <default>
[
{ name : 'Homicide' , value : 400 } ,
{ name : 'Robbery' , value : 300 } ,
{ name : 'Theft' , value : 300 } ,
{ name : 'Larceny' , value : 200 }
] {
"size" : 0 ,
"query" : {
"bool" : {
"must" : [
{ "terms" : { "location.state_id" : [ "7718335d-5317-4d7b-a370-00fe01ce9c72" ] } }
]
}
} ,
"aggs" : {
"by_main_grouping" : {
"terms" : {
"field" : "crime_category"
} ,
"aggs" : {
"by_operation" : {
"cardinality" : {
"field" : "_id"
}
}
}
}
}
} "aggregations" : {
"by_main_grouping" : {
"doc_count_error_upper_bound" : 0 ,
"sum_other_doc_count" : 0 ,
"buckets" : [
{
"key" : "theft" ,
"doc_count" : 5 ,
"by_operation" : {
"value" : 5
}
} ,
{
"key" : "non_violent_theft" ,
"doc_count" : 3 ,
"by_operation" : {
"value" : 3
}
} ,
{
"key" : "corruption" ,
"doc_count" : 2 ,
"by_operation" : {
"value" : 2
}
} ,
{
"key" : "homicide" ,
"doc_count" : 1 ,
"by_operation" : {
"value" : 1
}
} ,
{
"key" : "kidnapping" ,
"doc_count" : 1 ,
"by_operation" : {
"value" : 1
}
} ,
{
"key" : "protest" ,
"doc_count" : 1 ,
"by_operation" : {
"value" : 1
}
}
]
}
} 5. Getting how much we are lossing for each crime in the State of Mexico. Filters:
- location.state_id => [state of mexico]
Aggregations:
X Axis => crime_category
Y Axis => loss_amount
Y Operation => SUM
{
"size" : 0 ,
"query" : {
"bool" : {
"must" : [
{ "terms" : { "location.state_id" : [ "ace8ba64-905f-454d-bfdf-51d276691db4" ] } }
]
}
} ,
"aggs" : {
"by_main_grouping" : {
"terms" : {
"field" : "crime_category" ,
"min_doc_count" : 0
} ,
"aggs" : {
"by_operation" : {
"sum" : {
"field" : "loss_amount.min"
}
}
}
}
}
} "aggregations" : {
"by_main_grouping" : {
"doc_count_error_upper_bound" : 0 ,
"sum_other_doc_count" : 0 ,
"buckets" : [
{
"key" : "theft" ,
"doc_count" : 11 ,
"by_operation" : {
"value" : 11000
}
} ,
{
"key" : "homicide" ,
"doc_count" : 8 ,
"by_operation" : {
"value" : 240000
}
} ,
{
"key" : "kidnapping" ,
"doc_count" : 2 ,
"by_operation" : {
"value" : 180000
}
} ,
{
"key" : "non_violent_theft" ,
"doc_count" : 2 ,
"by_operation" : {
"value" : 2000
}
} ,
{
"key" : "robbery" ,
"doc_count" : 2 ,
"by_operation" : {
"value" : 180000
}
} ,
{
"key" : "car_theft" ,
"doc_count" : 0 ,
"by_operation" : {
"value" : 0
}
} ,
{
"key" : "corruption" ,
"doc_count" : 0 ,
"by_operation" : {
"value" : 0
}
} ,
{
"key" : "curfew" ,
"doc_count" : 0 ,
"by_operation" : {
"value" : 0
}
} ,
{
"key" : "extortion" ,
"doc_count" : 0 ,
"by_operation" : {
"value" : 0
}
} ,
{
"key" : "gun_fight" ,
"doc_count" : 0 ,
"by_operation" : {
"value" : 0
}
}
]
}
} 6. Getting the loss amount of an employee on thefts over a period of time. Filters:
- employee => [name]
- crime_category => [theft]
Aggregations:
X Axis => incident_date
X Segment => employee
Y Axis => loss_amount
Y Operation => SUM
{
"size" : 0 ,
"query" : {
"bool" : {
"must" : [
{ "terms" : { "source.name" : [ "Carlos Soria Velazquez" ] } } ,
{ "terms" : { "crime_category" : [ "theft" ] } } ,
{
"range" : {
"incident_date" : {
"gte" : "2018-01-01 00:00:00" ,
"lte" : "2018-12-31 00:00:00"
}
}
}
]
}
} ,
"aggs" : {
"by_main_grouping" : {
"date_histogram" : {
"field" : "incident_date" ,
"interval" : "month"
} ,
"aggs" : {
"by_segment" : {
"terms" : {
"field" : "source.name"
} ,
"aggs" : {
"by_operation" : {
"sum" : {
"field" : "loss_amount.min"
}
}
}
}
}
}
}
} "aggregations" : {
"by_main_grouping" : {
"buckets" : [
{
"key_as_string" : "2018-01-01 00:00:00" ,
"key" : 1514764800000 ,
"doc_count" : 4 ,
"by_segment" : {
"doc_count_error_upper_bound" : 0 ,
"sum_other_doc_count" : 0 ,
"buckets" : [
{
"key" : "Carlos Soria Velazquez" ,
"doc_count" : 4 ,
"by_operation" : {
"value" : 4000
}
}
]
}
} ,
{
"key_as_string" : "2018-02-01 00:00:00" ,
"key" : 1517443200000 ,
"doc_count" : 5 ,
"by_segment" : {
"doc_count_error_upper_bound" : 0 ,
"sum_other_doc_count" : 0 ,
"buckets" : [
{
"key" : "Carlos Soria Velazquez" ,
"doc_count" : 5 ,
"by_operation" : {
"value" : 5000
}
}
]
}
} ,
{
"key_as_string" : "2018-03-01 00:00:00" ,
"key" : 1519862400000 ,
"doc_count" : 15 ,
"by_segment" : {
"doc_count_error_upper_bound" : 0 ,
"sum_other_doc_count" : 0 ,
"buckets" : [
{
"key" : "Carlos Soria Velazquez" ,
"doc_count" : 15 ,
"by_operation" : {
"value" : 15000
}
}
]
}
} ,
{
"key_as_string" : "2018-04-01 00:00:00" ,
"key" : 1522540800000 ,
"doc_count" : 1 ,
"by_segment" : {
"doc_count_error_upper_bound" : 0 ,
"sum_other_doc_count" : 0 ,
"buckets" : [
{
"key" : "Carlos Soria Velazquez" ,
"doc_count" : 1 ,
"by_operation" : {
"value" : 1000
}
}
]
}
} ,
{
"key_as_string" : "2018-05-01 00:00:00" ,
"key" : 1525132800000 ,
"doc_count" : 9 ,
"by_segment" : {
"doc_count_error_upper_bound" : 0 ,
"sum_other_doc_count" : 0 ,
"buckets" : [
{
"key" : "Carlos Soria Velazquez" ,
"doc_count" : 9 ,
"by_operation" : {
"value" : 9000
}
}
]
}
} ,
{
"key_as_string" : "2018-06-01 00:00:00" ,
"key" : 1527811200000 ,
"doc_count" : 9 ,
"by_segment" : {
"doc_count_error_upper_bound" : 0 ,
"sum_other_doc_count" : 0 ,
"buckets" : [
{
"key" : "Carlos Soria Velazquez" ,
"doc_count" : 9 ,
"by_operation" : {
"value" : 9000
}
}
]
}
} ,
{
"key_as_string" : "2018-07-01 00:00:00" ,
"key" : 1530403200000 ,
"doc_count" : 1 ,
"by_segment" : {
"doc_count_error_upper_bound" : 0 ,
"sum_other_doc_count" : 0 ,
"buckets" : [
{
"key" : "Carlos Soria Velazquez" ,
"doc_count" : 1 ,
"by_operation" : {
"value" : 1000
}
}
]
}
}
]
}
} 7. Getting how much the Guerrero municipality has cost us. Filters:
- location.state_id => [guerrero]
Aggregations:
X Axis => location
Y Axis => loss_amount
Y Operation => SUM
{
"size" : 0 ,
"query" : {
"bool" : {
"must" : [
{ "terms" : { "location.state_id" : [ "e6f22e88-9bd2-4fdc-b802-25b726bb48e3" ] } }
]
}
} ,
"aggs" : {
"by_main_grouping" : {
"terms" : {
"field" : "location.state_id"
} ,
"aggs" : {
"by_operation" : {
"sum" : {
"field" : "loss_amount.min"
}
}
}
}
}
} "aggregations" : {
"by_main_grouping" : {
"doc_count_error_upper_bound" : 0 ,
"sum_other_doc_count" : 0 ,
"buckets" : [
{
"key" : "e6f22e88-9bd2-4fdc-b802-25b726bb48e3" ,
"doc_count" : 13 ,
"by_operation" : {
"value" : 13000
}
}
]
}
} 8. Getting how many homicides have happened in Tamaulipas, Guerrero and Sonora. Filters:
- location.state_id => [tamaulipas, guerrero, sonora]
Aggregations:
X Axis => location
Y Axis => crime_category
Y Operation => COUNT
{
"size" : 0 ,
"query" : {
"bool" : {
"must" : [
{ "terms" : { "location.state_id" : [ "7718335d-5317-4d7b-a370-00fe01ce9c72" , "e6f22e88-9bd2-4fdc-b802-25b726bb48e3" , "0ccb82d4-be38-4bd9-9393-4970d8759502" ] } }
]
}
} ,
"aggs" : {
"by_main_grouping" : {
"terms" : {
"field" : "location.state_id"
} ,
"aggs" : {
"by_operation" : {
"cardinality" : {
"field" : "_id"
}
}
}
}
}
} "aggregations" : {
"by_main_grouping" : {
"doc_count_error_upper_bound" : 0 ,
"sum_other_doc_count" : 0 ,
"buckets" : [
{
"key" : "0ccb82d4-be38-4bd9-9393-4970d8759502" ,
"doc_count" : 38 ,
"by_operation" : {
"value" : 38
}
} ,
{
"key" : "7718335d-5317-4d7b-a370-00fe01ce9c72" ,
"doc_count" : 13 ,
"by_operation" : {
"value" : 13
}
} ,
{
"key" : "e6f22e88-9bd2-4fdc-b802-25b726bb48e3" ,
"doc_count" : 13 ,
"by_operation" : {
"value" : 13
}
}
]
}
}
