Calculating distance between points using longitude latitude

[yxchua]

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.

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

[Stoyan_Terziev]

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.

[richard_pearce6]

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;