Recently at my work place, there came an question from my manager requesting “Can we calculate distance based on longitude and latitude? ” ~ My immediate answer was,
“It’s an great question. I have never done it & would like to research and let him know about it.”
At the same time, I know that – based on your “CURRENT LOCATION” (i.e.) If LOCATION SERVICES are enabled for MicroStrategy Mobile app, then we can calculate distance easily using geo prompts feature in MicroStrategy(MSTR/MSI). I shared with him my older blog post, where this is feasible in MicroStrategy Mobile
URL : Geo Location filtering in MicroStrategy Mobile
I started researching about how to calculate the distance between two longitude and latitude coordinates and was able to achieve it. So this blog post is to share that experience to other technology developers/analysts to make advantage of it.
I tried various mathematical formula’s that are being used by other python or coders online. Finally, I was able to use below logic within MicroStrategy to achieve it (Thanks to the author of this post + person who answered this below logic!!)
URL : Stack Overflow
Now, let’s come to MicroStrategy Dossier & data part to make this work within the tool/technology
Data Analysis
Go ahead and gather an use case – Here I have taken 2 EXPRESS Store address to capture distance between 2 stores.
Example : If user want’s to see the nearest store in the located region – it would help.
Key to note : Your data should hold source and target longitude and latitude (or) Perform Attribute Role/Table Alias concepts to achieve in schematic approach or any BI tool you go with
MicroStrategy Dossier
- Import the data into MicroStrategy Dossier
2. Convert attribute of Source & Target Longitude / Latitude to Measures/Metrics
3. Create New Metrics based on logic provided earlier from Stack Overflow
- Metric 01 : Base >> (0.5-(Cos(((TLat-SLat)*0.0174532925199433))/2))+(((Cos((SLat*0.0174532925199433))*Cos((TLat*0.0174532925199433)))*(1-Cos(((TLong-SLong)*0.0174532925199433))))/2)
- Metric 02 : Distance (Km) >> 12742*Asin(Sqrt(Base))
- Metric 03 : Distance (Miles) >> [Distance(Km)]*0.621371
4. BOOM > You will now get the distance between two stores here based on new metrics created
Hope it helps for someone out there who is trying to achieve this kind of business requests!
Key – keep in mind your data curation part is key and if you model them right, then you can achieve anything and everything within any BI tool pretty much!
Wishing everyone an Happy New Year 2021!!