Longitude and Latitude Calculations advice

[Reshaw]

Good afternoon everyone

I have used gmaps functions to fetch a list of longitude and latitudes for a (very long) list of delivery points for one of my customers.

I am attempting to calculate the centre of gravity for the distribution (not to be confused with the centre of mass of a physical object, this is a logistics term). So I did a simple mean average of the long's and the lat's and ploted these average values into a Google maps via a Hyperlink command. this works well and seems to make a kind of sense. For example I can filter out Scottish destinations and the CofG moves southwards as you'd expect.

I read online somewhere that given the earth is not flat and here in the UK we are quite a ways above the equator, the error could be relatively large.

If I assume the earth is spherical then I should perform some kind of cosine calculation to make it more accurate...

but of course the earth is not spherical either...

So my question is, How inaccurate is the simple mean average calculation I have done?

I am trying to find a suitable transport hub on a motorway near the CofG so a couple of miles out shouldn't be a massive problem. But if I have a potential error of many 10's of miles that would be more of a problem.

Any advice, thoughts or feedback is welcome.

Regards
Rob

 

[shg]

For driving distances between the arctic circles, I think you could scale x (longitude) by the cosine of the midpoint latitude (like a UTM projection), and errors due to spherical earth would be dwarfed by errors due to road networks not being point to point.

 

[mole999]

maybe Batch geocoding and then just measure or judge by eye, you can't a afford a centre thats Buckingham Palace

 

[Reshaw]

For driving distances between the arctic circles, I think you could scale x (longitude) by the cosine of the midpoint latitude (like a UTM projection), and errors due to spherical earth would be dwarfed by errors due to road networks not being point to point.

The gmaps functions use google maps to provide a distance by road from point to point, so i can be fairly confident the road distances are reasonably accurate.

The long lat calculations provide a centre (accuracy to be confirmed) from which i can pick a suitable location and then recalculate the road distances for each delivery point.

What i would like to know is how far off the true centre point i will be with this method

Thanks for your input, it is appreciated

 

[Reshaw]

There are approx 14000 movements to map out, 4000 of them unique locations. Gut feeling is usually a way off with this sort of thing.

 

[shg]

If I were going to do this with Excel, I'd do the UTM approach to calculate the frequency-weighted center, and pick a half-dozen nice industrial parks within an x-mile radius. Then use whatever API to get driving distances, and calulate the frequency-weighted average for each possibility.

Seems like ESRI's ArcGIS should have support for exactly this problem.

 

[Reshaw]

If I were going to do this with Excel, I'd do the UTM approach to calculate the frequency-weighted center, and pick a half-dozen nice industrial parks within an x-mile radius. Then use whatever API to get driving distances, and calulate the frequency-weighted average for each possibility.

Seems like ESRI's ArcGIS should have support for exactly this problem.

Ok so what would the utm approach be precisely? I mean what would the formula look like?

Thanks in advance
Rob

 

[shg]

Random points in a 4 x 4 degree area:

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
4​	Midpoint​	53.434​	-0.985​								B4: =SUMPRODUCT(B8:B21, $D8:$D21)/SUM($D8:$D21)
5​	Cosine​	0.5957​									C4: =SUMPRODUCT(C8:C21, $D8:$D21)/SUM($D8:$D21)
6​											B5: =COS(RADIANS(B4))
7​		Lat​	Lon​	Wgt​	Lon'​		Euc Dist [km]​	GC Dist [km]​	% Error​
8​		52.870​	0.730​	1​	0.037​		129.7​	130.4​	0.6%​		E8: =(C8-$C$4)*$B$5 + $C$4
9​		52.710​	0.632​	4​	-0.022​		133.9​	134.7​	0.6%​		F8: =111.12 * SQRT((B8-$B$4)^2 + (E8-$C$4)^2)
10​		54.941​	-0.313​	5​	-0.585​		173.2​	173.1​	-0.1%​		G8: =6371 * CentralAngle(B8, C8, $B$4, $C$4)
11​		52.924​	-1.585​	1​	-1.342​		69.3​	69.4​	0.3%​		H8: =1-F8/G8
12​		52.064​	-1.784​	2​	-1.461​		161.2​	161.6​	0.2%​
13​		51.276​	-1.303​	1​	-1.174​		240.8​	241.0​	0.1%​
14​		52.786​	-2.091​	4​	-1.644​		102.7​	103.2​	0.5%​
15​		52.895​	-0.874​	3​	-0.919​		60.4​	60.4​	0.1%​
16​		54.337​	-2.779​	3​	-2.054​		155.5​	154.6​	-0.6%​
17​		54.942​	0.479​	3​	-0.113​		193.5​	192.8​	-0.4%​
18​		52.879​	-0.797​	4​	-0.873​		63.0​	63.0​	0.1%​
19​		54.161​	-1.324​	3​	-1.187​		83.8​	83.8​	0.0%​
20​		52.545​	-1.864​	3​	-1.509​		114.7​	115.1​	0.3%​
21​		54.464​	-2.019​	1​	-1.601​		133.3​	133.0​	-0.3%​