Geospatial joins are now supported by Azure Data Explorer

Published 09-18-2020 01:13 PM 2,258 Views
Microsoft

This blog post covers a new geo function and the basics of geospatial joins.

 

geo_polygon_to_s2cells

geo_polygon_to_s2cells takes a polygon or multipolygon and a S2 cell level as input and creates an array of all token strings which cover it. Be aware that covering a large-area polygon with small-area cells can lead to a huge amount of covering cells. As a result, the query might return null.

 

KQL-Query:

 

let polygon = dynamic({"type":"Polygon","coordinates":[[[0,0],[0,50],[100,50],[0,0]]]});
print s2_cell_token_count = array_length(geo_polygon_to_s2cells(polygon, 5));

 

 Result:

s2_cell_token_count
286

 

Geospatial joins

This query pattern is oftentimes used in various mobility solutions (geospatial telemetry and static reference data), geospatial risk analysis and agriculture optimization using weather data. It is based on the three-dimensional S2 geometry and the functions geo_polygon_to_s2cells and geo_point_in_polygon. By use of this functionality a geospatial join consists of a coarse-grained join using the S2 cell coverage and the exact validation using the geo_point_in_polygon function.

 

The four main steps:

  1. Converting polygons to S2 cells of level k,
  2. Converting points to the same S2 cells level k,
  3. Joining on S2 cells,
  4. Filtering by geo_point_in_polygon().

 

The following picture explains the flow of the entire KQL query. First you need to choose the right S2 cell level. It should not be too big and not too small (it is not recommended to use more than 10.000 cells, 65535 are possible in theory). Second you are creating the S2 cell tokens for the static dataset. Next you are joining the polygons with the timeseries based on the tokens. This provides you with a dataset which might still has some false positives (S2 cell overlap) and that is why you need to end the statement with a check if the actual point is in the polygon.

geospatialJoin.png

 

This is a concrete example of a geospatial join based on the StormEvents and US_States table located on the help cluster. It calculates the damage in $ by state.

 

KQL-Query:

 

let join_level = 4; 
US_States 
| project State = features.properties.NAME, polygon = features.geometry 
| extend covering = geo_polygon_to_s2cells(polygon, join_level) 
| mv-expand covering to typeof(string) 
| join kind = inner hint.strategy = broadcast 
( 
  StormEvents 
  | project BeginLon, BeginLat , DamageProperty 
  | extend covering = geo_point_to_s2cell(BeginLon, BeginLat, join_level) 
) on covering 
| where geo_point_in_polygon(BeginLon, BeginLat, polygon) 
| summarize CountOfEvents=count(), DamageInDollar=sum(DamageProperty) by tostring(State) 
| top 3 by DamageInDollar desc

 

Result:

State CountOfEvents DamageInDollar

Kansas

2298

533352500

Texas

3889

488525500

Ohio

852

371630500

2 Comments
New Contributor

Thank you @cosh23 for this post!

Are there any other planned geo functions in the pipline?

Microsoft

Hi @elyusubov ,

 

we continously improving our geospatial capabilities. Recently we added support for multi-lines in some functions and converting polygon or multipolygon planar edges to geodesics by adding intermediate points using geo_polygon_densify(). Is there something particular that you are looking for? Please feel free to add an item to https://feedback.azure.com/forums/915733-azure-data-explorer. Thx

 

Cheers, henning.

%3CLINGO-SUB%20id%3D%22lingo-sub-1684048%22%20slang%3D%22en-US%22%3EGeospatial%20joins%20are%20now%20supported%20by%20Azure%20Data%20Explorer%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1684048%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EThis%20blog%20post%20covers%20a%20new%20geo%20function%20and%20the%20basics%20of%20geospatial%20joins.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--1265811304%22%20id%3D%22toc-hId--1265811304%22%20id%3D%22toc-hId--1265811304%22%20id%3D%22toc-hId--1265813165%22%3E%3CSPAN%3Egeo_polygon_to_s2cells%3C%2FSPAN%3E%3C%2FH2%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fgeo-polygon-to-s2cells-function%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Egeo_polygon_to_s2cells%3C%2FA%3E%20takes%20a%20polygon%20or%20multipolygon%20and%20a%20%3CA%20href%3D%22https%3A%2F%2Fs2geometry.io%2Fdevguide%2Fs2cell_hierarchy%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3ES2%20cell%3C%2FA%3E%20level%20as%20input%20and%20creates%20an%20array%20of%20all%20token%20strings%20which%20cover%20it.%20Be%20aware%20that%20covering%20a%20large-area%20polygon%20with%20small-area%20cells%20can%20lead%20to%20a%20huge%20amount%20of%20covering%20cells.%20As%20a%20result%2C%20the%20query%20might%20return%20null.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH4%20id%3D%22toc-hId-1922766107%22%20id%3D%22toc-hId-1922766107%22%20id%3D%22toc-hId-1922766107%22%20id%3D%22toc-hId-1922764246%22%3EKQL-Query%3A%3C%2FH4%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-json%22%3E%3CCODE%3Elet%20polygon%20%3D%20dynamic(%7B%22type%22%3A%22Polygon%22%2C%22coordinates%22%3A%5B%5B%5B0%2C0%5D%2C%5B0%2C50%5D%2C%5B100%2C50%5D%2C%5B0%2C0%5D%5D%5D%7D)%3B%0Aprint%20s2_cell_token_count%20%3D%20array_length(geo_polygon_to_s2cells(polygon%2C%205))%3B%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH4%20id%3D%22toc-hId-115311644%22%20id%3D%22toc-hId-115311644%22%20id%3D%22toc-hId-115311644%22%20id%3D%22toc-hId-115309783%22%3E%26nbsp%3BResult%3A%3C%2FH4%3E%0A%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%22100%25%22%20height%3D%2230px%22%3E%3CSTRONG%3Es2_cell_token_count%3C%2FSTRONG%3E%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%22100%25%22%20height%3D%2230px%22%3E286%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-1901759899%22%20id%3D%22toc-hId-1901759899%22%20id%3D%22toc-hId-1901759899%22%20id%3D%22toc-hId-1901758038%22%3EGeospatial%20joins%3C%2FH2%3E%0A%3CP%3EThis%20query%20pattern%20is%20oftentimes%20used%20in%20various%20mobility%20solutions%20(geospatial%20telemetry%20and%20static%20reference%20data)%2C%20geospatial%20risk%20analysis%20and%20agriculture%20optimization%20using%20weather%20data.%20It%20is%20based%20on%20the%20three-dimensional%20S2%20geometry%20and%20the%20functions%20geo_polygon_to_s2cells%20and%20geo_point_in_polygon.%20By%20use%20of%20this%20functionality%20a%20geospatial%20join%20consists%20of%20a%20coarse-grained%20join%20using%20the%20S2%20cell%20coverage%20and%20the%20exact%20validation%20using%20the%20geo_point_in_polygon%20function.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20four%20main%20steps%3A%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3EConverting%20polygons%20to%20S2%20cells%20of%20level%20k%2C%3C%2FLI%3E%0A%3CLI%3EConverting%20points%20to%20the%20same%20S2%20cells%20level%20k%2C%3C%2FLI%3E%0A%3CLI%3EJoining%20on%20S2%20cells%2C%3C%2FLI%3E%0A%3CLI%3EFiltering%20by%20geo_point_in_polygon().%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20following%20picture%20explains%20the%20flow%20of%20the%20entire%20KQL%20query.%20First%20you%20need%20to%20choose%20the%20right%20S2%20cell%20level.%20It%20should%20not%20be%20too%20big%20and%20not%20too%20small%20(it%20is%20not%20recommended%20to%20use%20more%20than%2010.000%20cells%2C%2065535%20are%20possible%20in%20theory).%20Second%20you%20are%20creating%20the%20S2%20cell%20tokens%20for%20the%20static%20dataset.%20Next%20you%20are%20joining%20the%20polygons%20with%20the%20timeseries%20based%20on%20the%20tokens.%20This%20provides%20you%20with%20a%20dataset%20which%20might%20still%20has%20some%20false%20positives%20(S2%20cell%20overlap)%20and%20that%20is%20why%20you%20need%20to%20end%20the%20statement%20with%20a%20check%20if%20the%20actual%20point%20is%20in%20the%20polygon.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22geospatialJoin.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F219564iF2808933B8CBB60D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22geospatialJoin.png%22%20alt%3D%22geospatialJoin.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20is%20a%20concrete%20example%20of%20a%20geospatial%20join%20based%20on%20the%20StormEvents%20and%20US_States%20table%20located%20on%20the%20%3CA%20href%3D%22https%3A%2F%2Fhelp.kusto.windows.net%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehelp%3C%2FA%3E%20cluster.%20It%20calculates%20the%20damage%20in%20%24%20by%20state.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH4%20id%3D%22toc-hId-795370014%22%20id%3D%22toc-hId-795370014%22%20id%3D%22toc-hId-795370014%22%20id%3D%22toc-hId-795368153%22%3EKQL-Query%3A%3C%2FH4%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-json%22%3E%3CCODE%3Elet%20join_level%20%3D%204%3B%20%0AUS_States%20%0A%7C%20project%20State%20%3D%20features.properties.NAME%2C%20polygon%20%3D%20features.geometry%20%0A%7C%20extend%20covering%20%3D%20geo_polygon_to_s2cells(polygon%2C%20join_level)%20%0A%7C%20mv-expand%20covering%20to%20typeof(string)%20%0A%7C%20join%20kind%20%3D%20inner%20hint.strategy%20%3D%20broadcast%20%0A(%20%0A%20%20StormEvents%20%0A%20%20%7C%20project%20BeginLon%2C%20BeginLat%20%2C%20DamageProperty%20%0A%20%20%7C%20extend%20covering%20%3D%20geo_point_to_s2cell(BeginLon%2C%20BeginLat%2C%20join_level)%20%0A)%20on%20covering%20%0A%7C%20where%20geo_point_in_polygon(BeginLon%2C%20BeginLat%2C%20polygon)%20%0A%7C%20summarize%20CountOfEvents%3Dcount()%2C%20DamageInDollar%3Dsum(DamageProperty)%20by%20tostring(State)%20%0A%7C%20top%203%20by%20DamageInDollar%20desc%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH4%20id%3D%22toc-hId--1012084449%22%20id%3D%22toc-hId--1012084449%22%20id%3D%22toc-hId--1012084449%22%20id%3D%22toc-hId--1012086310%22%3EResult%3A%3C%2FH4%3E%0A%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2233.333333333333336%25%22%3E%3CSTRONG%3EState%3C%2FSTRONG%3E%3C%2FTD%3E%0A%3CTD%20width%3D%2233.333333333333336%25%22%3E%3CSTRONG%3ECountOfEvents%3C%2FSTRONG%3E%3C%2FTD%3E%0A%3CTD%20width%3D%2233.333333333333336%25%22%3E%3CSTRONG%3EDamageInDollar%3C%2FSTRONG%3E%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2233.333333333333336%25%22%3E%0A%3CP%3EKansas%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%2233.333333333333336%25%22%3E%0A%3CP%3E2298%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%2233.333333333333336%25%22%3E%0A%3CP%3E533352500%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2233.333333333333336%25%22%3E%0A%3CP%3ETexas%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%2233.333333333333336%25%22%3E%0A%3CP%3E3889%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%2233.333333333333336%25%22%3E%0A%3CP%3E488525500%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2233.333333333333336%25%22%3E%0A%3CP%3EOhio%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%2233.333333333333336%25%22%3E%0A%3CP%3E852%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%2233.333333333333336%25%22%3E%0A%3CP%3E371630500%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1684048%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3ERecently%20we%20started%20to%20provide%20new%20geo%20functions%20which%20enables%20the%20user%20to%20implement%20geospatial%20joins.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1684048%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Eadx%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%20Data%20Explorer%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EKusto%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1727347%22%20slang%3D%22en-US%22%3ERe%3A%20Geospatial%20joins%20are%20now%20supported%20by%20Azure%20Data%20Explorer%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1727347%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F391642%22%20target%3D%22_blank%22%3E%40cosh23%3C%2FA%3E%26nbsp%3Bfor%20this%20post!%3C%2FP%3E%3CP%3EAre%20there%20any%20other%20planned%20geo%20functions%20in%20the%20pipline%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Version history
Last update:
‎Sep 18 2020 01:19 PM
Updated by: