Kusto Detective Agency Season 1 (Challenge 4)#

Part 1#

This is the first challenge where we are split to multiple parts.

#Primes
#| sort by Numbers desc
#| where Numbers < 50000000
#| project Numbers, previous = prev(Numbers)
#| project Numbers, previous, total = Numbers + previous + 1
#| join kind=inner Primes on $left.total == $right.Numbers
#| sort by total

Part 2#

We’re running a bit off grid now, taking instructions from an unknown person. Surely theres some rules about this…. Anyways, this is all we’re getting (along with some data to import):

Well done, my friend. It’s time to meet. Let’s go for a virtual sTREEt tour… Across the Big Apple city, there is a special place with Turkish Hazelnut and four Schubert Chokecherries within 66-meters radius area. Go ‘out’ and look for me there, near the smallest American Linden tree (within the same area). Find me and the bottom line: my key message to you.

Cheers, El Puente.

Investigating the Data#

As is tradition, lets grab the top few rows and take a look what we’re working with.
KDA_S1_C4_trees.png

So, it’s quite a diverse dataset, with plenty of data that we dont really need. At the end of the day, all of our requirements revolve around 3 species. We can find these in the ‘spc_common’ column. Lets get rid of everything else

nyc_trees
| where spc_common in (“‘Schubert’ chokecherry”, “Turkish hazelnut”, “American linden”)

Finding the Radius Cell & Tree#

Shock, theres still a lot, but we can get started now. The count of trees inside a radius area is a strange one. I was thinking how I could do a ‘moving’ mask and filter through that, but I felt it was excessive and likely not the right path. Going through the hints, they refer to a H3 cell of size 10 being roughly that size. After a bit of research on the topic, I had so many ‘what if’s’ with the static cells; what if the count overlaps? Do the instructions include dead trees? Is it four ‘cherries’ or more etc etc…
Getting out of my own head, it’s suggested by the challenge, just give it a go. There is a function that converts lat/long to a H3 cell. Lets add this as a column and get rid of a lot of extras..

nyc_trees
| where spc_common in (“‘Schubert’ chokecherry”, “Turkish hazelnut”, “American linden”)
| project tree_id, spc_common, tree_dbh, latitude, longitude, cell = geo_point_to_h3cell(longitude, latitude, 10)
| project tree_id, spc_common, cell, tree_dbh

KDA_S1_C4_cells.png

Next step, we need to find the cell that meets our criteria. Simply, we need the data for each cell, which means we need to summarise
This is where we introduce the ‘countif’. Simply, it counts the rows where the conditions are met, by the cell (at the end). We are creating two columns here.
Lastly, we’re adding the ‘where’ to only give us cells with the correct count of trees.

nyc_trees
| where spc_common in (“‘Schubert’ chokecherry”, “Turkish hazelnut”, “American linden”)
| project tree_id, spc_common, tree_dbh, latitude, longitude, cell = geo_point_to_h3cell(longitude, latitude, 10)
| project tree_id, spc_common, cell, tree_dbh
| summarize SC=countif(spc_common==“‘Schubert’ chokecherry”), TH=countif(spc_common==”Turkish hazelnut”) by cell
| where SC==4 and TH>0

KDA_S1_C4_sumarizedCells.png

Awesome, our ‘66m radius’ cell is 8a2a100dec9ffff. Thats only our first step here however. We dont want the cell, we want the smallest “American Linden” in the cell. Jumping up our query we can just ask for this though. Add the Latitude and Longitude back in too.
KDA_S1_C4_smallest.png

Finding the Message#

Final submission is the last line: wytaPUJM!PS:2,7,17,29,42,49,58,59,63

#Working out the correct cell
#
#nyc_trees 
#| where spc_common in ("'Schubert' chokecherry", "Turkish hazelnut", "American linden") 
#| project tree_id, spc_common, tree_dbh, latitude, longitude, cell = geo_point_to_h3cell(longitude, latitude, 1, 0)
#| project tree_id, spc_common, cell, tree_id
#| summarize SC=countif(spc_common=="'Schubert' chokecherry"), TH=countif(spc_common=="Turkish hazelnut") by cell
#| where SC==4 and TH>0
#
#Finding the tree
#nyc_trees
#| where spc_common in ("'Schubert' chokecherry", "Turkish hazelnut", "American linden") 
#| project tree_id, spc_common, tree_dbh, latitude, longitude, cell = geo_point_to_h3cell(longitude, latitude, 1, 0)
#| where cell == "8a2a100dec9ff
#VirtualTourLink(40.71222313,-73.96452201)
#
#Decoding the message
#
#let code=@'20INznpGzmkmK2NlZ0JILtO4OoYhOoYUB0OrOoTl5mJ3KgXrB0[8LTSSXUYhzUY8vmkyKUYevUYrDgYNK07yaf7soC3kKgMlOtHkLt[kZEclBtkyOoYwvtJGK2YevUY[v65iLtkeLEOhvtNlBtpizoY[v65yLdOkLEOhvtNlDn5lB07lOtJIDmllzmJ4vf7soCpiLdYIK0[eK27soleqO6keDpYp2CeH5d\F\fN6aQT6aQL[aQcUaQc[aQ57aQ5[aQDG';
#
#let message = "ASHES to ASHES";

#print Decrypt(code, message)