geoTraining

#connect cluster('demo12.westus.kusto.windows.net').database('Datasets')

https://demo12.westus.kusto.windows.net

// 1. Count of events,  ~165M
nyc_taxi
| count


// 2. Show random pickups
nyc_taxi
| where pickup_longitude  != 0 and pickup_latitude  != 0
| project pickup_longitude, pickup_latitude
| take 300
| render scatterchart with (kind = map)


// 3. Show nearby point pickups
nyc_taxi
| where pickup_longitude  != 0 and pickup_latitude  != 0
| project pickup_longitude, pickup_latitude
| where geo_point_in_circle(pickup_longitude, pickup_latitude,-73.9850, 40.7535, 10)
| take 500
| render scatterchart with (kind = map)


// 4. Show nearby line pickups
nyc_taxi
| where pickup_longitude  != 0 and pickup_latitude  != 0
| project pickup_longitude, pickup_latitude
| where geo_distance_point_to_line(pickup_longitude, pickup_latitude, dynamic({"type":"LineString","coordinates":[[-73.9706039428711,40.76162593648898],[-73.97601127624512,40.75466940037548],[-73.98510932922363,40.75850533742767],[-73.9892292022705,40.752783858657295]]})) < 0.1 
| take 100
| render scatterchart with (kind = map)



// 5. Show geofencing
nyc_taxi
| where pickup_longitude  != 0 and pickup_latitude  != 0
| project pickup_longitude, pickup_latitude
| where geo_point_in_polygon(pickup_longitude, pickup_latitude, dynamic({"type":"Polygon","coordinates":[[[-73.99515151977538,40.74361546275168],[-73.98236274719238,40.743485405490695],[-73.97541046142577,40.75414925627699],[-73.98399353027344,40.74985791219436],[-73.98656845092773,40.76045572900912],[-73.99103164672852,40.750248045828585],[-74.00107383728026,40.75622980825467],[-73.99515151977538,40.74361546275168]]]}))
| take 1000
| render scatterchart with (kind = map)



// 6. Show clustering
let manhattan = dynamic({"type":"Polygon","coordinates":[[[-73.92597198486328,40.87821814104651],[-73.94691467285156,40.85069618625578],[-73.94691467285156,40.841865966890786],[-74.01008605957031,40.7519385984599],[-74.01866912841797,40.704586878965245],[-74.01214599609375,40.699901911003046],[-73.99772644042969,40.70875101828792],[-73.97747039794922,40.71083299030839],[-73.97026062011719,40.7290474687069],[-73.97506713867186,40.734510840309376],[-73.970947265625,40.74543623770158],[-73.94210815429688,40.77586181063573],[-73.9434814453125,40.78080140115127],[-73.92974853515625,40.79691751000055],[-73.93077850341797,40.804454347291006],[-73.93489837646484,40.80965166748853],[-73.93524169921875,40.837190668541105],[-73.92288208007812,40.85770758108904],[-73.9101791381836,40.871728144624974],[-73.92597198486328,40.87821814104651]],[[-73.95824432373047,40.80071852197889],[-73.98206233978271,40.76815921628347],[-73.97309303283691,40.76422632379533],[-73.94914627075195,40.796949998204596],[-73.95824432373047,40.80071852197889]]]});
nyc_taxi
| where pickup_longitude  != 0 and pickup_latitude  != 0
| project pickup_longitude, pickup_latitude
| where geo_point_in_polygon(pickup_longitude, pickup_latitude, manhattan)
| summarize by geo_point_to_s2cell(pickup_longitude, pickup_latitude, 15)
| project geo_s2cell_to_central_point(Column1)
| render scatterchart with (kind = map)



// Charts
let southCoast = dynamic({"type":"LineString","coordinates":[[-97.18505859374999,25.997549919572112],[-97.58056640625,26.96124577052697],[-97.119140625,27.955591004642553],[-94.04296874999999,29.726222319395504],[-92.98828125,29.82158272057499],[-89.18701171875,29.11377539511439],[-89.384765625,30.315987718557867],[-87.5830078125,30.221101852485987],[-86.484375,30.4297295750316],[-85.1220703125,29.6880527498568],[-84.00146484374999,30.14512718337613],[-82.6611328125,28.806173508854776],[-82.81494140625,28.033197847676377],[-82.177734375,26.52956523826758],[-80.9912109375,25.20494115356912]]});
StormEvents
| project BeginLon, BeginLat, EventType
| where geo_distance_point_to_line(BeginLon, BeginLat, southCoast) < 5000
| render scatterchart with (kind=map) 


#connect cluster('help.kusto.windows.net').database('Samples')

let california = dynamic({"type":"Polygon","coordinates":[[[-123.233256,42.006186],[-122.378853,42.011663],[-121.037003,41.995232],[-120.001861,41.995232],[-119.996384,40.264519],[-120.001861,38.999346],[-118.71478,38.101128],[-117.498899,37.21934],[-116.540435,36.501861],[-115.85034,35.970598],[-114.634459,35.00118],[-114.634459,34.87521],[-114.470151,34.710902],[-114.333228,34.448009],[-114.136058,34.305608],[-114.256551,34.174162],[-114.415382,34.108438],[-114.535874,33.933176],[-114.497536,33.697668],[-114.524921,33.54979],[-114.727567,33.40739],[-114.661844,33.034958],[-114.524921,33.029481],[-114.470151,32.843265],[-114.524921,32.755634],[-114.72209,32.717295],[-116.04751,32.624187],[-117.126467,32.536556],[-117.24696,32.668003],[-117.252437,32.876127],[-117.329114,33.122589],[-117.471515,33.297851],[-117.7837,33.538836],[-118.183517,33.763391],[-118.260194,33.703145],[-118.413548,33.741483],[-118.391641,33.840068],[-118.566903,34.042715],[-118.802411,33.998899],[-119.218659,34.146777],[-119.278905,34.26727],[-119.558229,34.415147],[-119.875891,34.40967],[-120.138784,34.475393],[-120.472878,34.448009],[-120.64814,34.579455],[-120.609801,34.858779],[-120.670048,34.902595],[-120.631709,35.099764],[-120.894602,35.247642],[-120.905556,35.450289],[-121.004141,35.461243],[-121.168449,35.636505],[-121.283465,35.674843],[-121.332757,35.784382],[-121.716143,36.195153],[-121.896882,36.315645],[-121.935221,36.638785],[-121.858544,36.6114],[-121.787344,36.803093],[-121.929744,36.978355],[-122.105006,36.956447],[-122.335038,37.115279],[-122.417192,37.241248],[-122.400761,37.361741],[-122.515777,37.520572],[-122.515777,37.783465],[-122.329561,37.783465],[-122.406238,38.15042],[-122.488392,38.112082],[-122.504823,37.931343],[-122.701993,37.893004],[-122.937501,38.029928],[-122.97584,38.265436],[-123.129194,38.451652],[-123.331841,38.566668],[-123.44138,38.698114],[-123.737134,38.95553],[-123.687842,39.032208],[-123.824765,39.366301],[-123.764519,39.552517],[-123.85215,39.831841],[-124.109566,40.105688],[-124.361506,40.259042],[-124.410798,40.439781],[-124.158859,40.877937],[-124.109566,41.025814],[-124.158859,41.14083],[-124.065751,41.442061],[-124.147905,41.715908],[-124.257444,41.781632],[-124.213628,42.000709],[-123.233256,42.006186]]]});
StormEvents
| project BeginLon, BeginLat, EventType
| where geo_point_in_polygon(BeginLon, BeginLat, california)
| summarize count() by EventType, hash = geo_point_to_s2cell(BeginLon, BeginLat, 7)
| project geo_s2cell_to_central_point(hash), EventType, count_
| render piechart with (kind=map)


StormEvents
| extend s2 = geo_point_to_s2cell(BeginLon, BeginLat, 5)
| summarize damage = sum(DamageProperty) by s2, EventType
| extend centralPoint=geo_s2cell_to_central_point(s2)
| project centralPoint, damage, EventType
| render piechart with (kind = map )