cancel
Showing results for
Did you mean:
Contributor

## 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.

Labels (3)

• ### longitude

3 Replies

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
Partner - Creator III

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:
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:
'' 	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
ShopID 	as ShopID_temp
,	Lat		as Lat_temp
,	Lon		as Lon_temp
Resident Shops
WHERE
ShopID='\$(vShopID)'
;

Outer Join (ShopDistance)
ShopID 	as ShopID_temp2
,	Lat		as Lat_temp2
,	Lon		as Lon_temp2
Resident Shops
WHERE
ShopID<>'\$(vShopID)'
;

Concatenate(DistancePlaceholder)
*
Resident ShopDistance
;

DROP TABLE ShopDistance;

NEXT vShopCounter;

DistancePlaceholder_temp:
NoConcatenate
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.

Luminary Alumni

If you have two lat longs this converts to KM

```DataShort: