Forum Discussion
Sergei2435
Jun 17, 2022Brass Contributor
SignIn Office Location
Hi,
I am trying to develop a query which determines how many users sign in from each office around the country. For the location of each office, I have the latitude and longitude.
The following was my initial approach:
1. Create a table with the office name, latitude and longitude
2. Generate geohash value
3. Generate geohash value for sign-in geocoordinates in the SignIn table
4. Join two tables on geohash value
let Office = datatable(Office:string, longitude:real, latitude:real)
[
"Durham", double(-78.90213775634764),35.99591827392578,
"LondonStrand", double(-0.016599999740719796),51.58330154418945,
"NYNEW", double(-0.7399736916),40.75118126
]
| extend geohash = geo_point_to_geohash(longitude, latitude);
//Office
let SignIn=SigninLogs
| extend latitude_ = tostring(parse_json(tostring(LocationDetails.geoCoordinates)).latitude)
| extend longitude_ = tostring(parse_json(tostring(LocationDetails.geoCoordinates)).longitude)
| extend geohash = geo_point_to_geohash(latitude_, longitude__);
Office
| join kind=inner SignIn on geohash
[
"Durham", double(-78.90213775634764),35.99591827392578,
"LondonStrand", double(-0.016599999740719796),51.58330154418945,
"NYNEW", double(-0.7399736916),40.75118126
]
| extend geohash = geo_point_to_geohash(longitude, latitude);
//Office
let SignIn=SigninLogs
| extend latitude_ = tostring(parse_json(tostring(LocationDetails.geoCoordinates)).latitude)
| extend longitude_ = tostring(parse_json(tostring(LocationDetails.geoCoordinates)).longitude)
| extend geohash = geo_point_to_geohash(latitude_, longitude__);
Office
| join kind=inner SignIn on geohash
When I try to get geohash from SignIn logs, I receive an error.
I'm a bit lost now after hitting a brick wall for so long.
Any suggestions would be greatly appreciated. Possibly another approach?
Any suggestions would be greatly appreciated. Possibly another approach?
Many Thanks
- Clive_WatsonBronze Contributor
let SignIn= SigninLogs | extend latitude_ = todouble(parse_json(tostring(LocationDetails.geoCoordinates)).latitude) | extend longitude_ = todouble(parse_json(tostring(LocationDetails.geoCoordinates)).longitude) | extend geohash = geo_point_to_geohash(latitude_, longitude_) ;
- Sergei2435Brass ContributorThank you very much, Clive! I have no idea how I missed changing the "tostring" to "todouble". I guess one of those days....
- Clive_WatsonBronze ContributorYou also had a double underscore in this line, so you probably missed it because of that other error:
latitude_, longitude__)
Glad it worked!