Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Analytics
- :
- Re: Calculating distance between points using long...

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

yxchua

Contributor

2019-11-12
03:44 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Calculating distance between points using longitude latitude

Hi,

With the longitude latitude of about 300 shop locations, I would like to identify the shops with few or no shops nearby. Hence, I would need to find the minimum distance of each shop to all other shops.

Shops with high minimum distance to other shops will be those that I'm interested in - there is potential to open new shops nearby to increase sales.

Could you suggest the best way to go about doing this?

Thanks.

2,188 Views

3 Replies

2019-11-12
04:52 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I may suggest this approach

1) Check the first place of latitude which you want decide

2) Try to do Above function in Design where as Previous / Peek in script to do compare

3) Use longitude for the same Then see If there is any variation etc.

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

2,174 Views

Stoyan_Terziev

Partner - Creator III

2019-11-12
04:54 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi Yxchua,

You can do this:

First you calculate the distance between each shop and every other shop in the back-end like this:

```
Shops:
Load * INLINE [
ShopID,Lat,Lon
1, 42.482004, 23.177356
2, 42.756876, 23.159511
3, 42.740346, 23.274082
4, 42.691619, 23.317504
5, 42.703100, 23.375329
6, 42.662088, 23.350792
];
DistancePlaceholder:
LOAD
'' as ShopID_temp
, '' as Lat_temp
, '' as Lon_temp
, '' as ShopID_temp2
, '' as Lat_temp2
, '' as Lon_temp2
AutoGenerate 0;
LET vCountOfShops = NoOfRows('Shops');
For vShopCounter=0 to $(vCountOfShops)-1
Let vShopID=Peek('ShopID',$(vShopCounter),'Shops');
ShopDistance:
NoConcatenate
LOAD
ShopID as ShopID_temp
, Lat as Lat_temp
, Lon as Lon_temp
Resident Shops
WHERE
ShopID='$(vShopID)'
;
Outer Join (ShopDistance)
LOAD
ShopID as ShopID_temp2
, Lat as Lat_temp2
, Lon as Lon_temp2
Resident Shops
WHERE
ShopID<>'$(vShopID)'
;
Concatenate(DistancePlaceholder)
LOAD
*
Resident ShopDistance
;
DROP TABLE ShopDistance;
NEXT vShopCounter;
DistancePlaceholder_temp:
NoConcatenate
LOAD
ShopID_temp
, Lat_temp
, Lon_temp
, ShopID_temp2
, Lat_temp2
, Lon_temp2
, sqrt(pow(Lat_temp - Lat_temp2, 2)+pow(Lon_temp-Lon_temp2,2))*10000 as DistanceMeters
Resident DistancePlaceholder
;
DROP TABLE DistancePlaceholder;
```

Then you can create a table on the front end with dimension ShopID_temp and measure:

```
Min(Aggr(
Avg(DistanceMeters)
, ShopID_temp2
, ShopID_temp
))
```

Results:

But this distance is not perfectly accurate.

To make it accurate, you need to implement this formula: https://en.wikipedia.org/wiki/Haversine_formula

That's the "simple" answer so far.

The complex answer is you actually want to do a test model with clustering and see what number of shop clusters is there and see how you can optimize each cluster or focus on another one. But that's not a task for QV 😉

https://en.wikipedia.org/wiki/K-means_clustering

I hope that helps!

Kind regards,

S.T.

2,171 Views

richard_pearce6

Luminary Alumni

2020-11-13
04:34 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

If you have two lat longs this converts to KM

DataShort: Load * ,6371 * (2 * Atan2( sqrt((sin(((lat2-lat1) * Pi() /180) /2) * sin(((lat2-lat1) * Pi() /180) /2) +sin(((lon2-lon1) * Pi() /180) /2) * sin(((lon2-lon1) * Pi() /180) /2) * cos(((lat1) * Pi() /180) ) * cos(((lat2) * Pi() /180) ))) , sqrt(1-(sin(((lat2-lat1) * Pi() /180) /2) * sin(((lat2-lat1) * Pi() /180) /2) +sin(((lon2-lon1) * Pi() /180) /2) * sin(((lon2-lon1) * Pi() /180) /2) * cos(((lat1) * Pi() /180) ) * cos(((lat2) * Pi() /180) ))) )) AS Distance Resident Data;

1,984 Views

Community Browser