Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
yxchua
Contributor
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)
3 Replies
Anil_Babu_Samineni

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

image.png

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.

richard_pearce6
Luminary Alumni
Luminary Alumni

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;