Longitude and Latitude Calculations advice

Reshaw

Board Regular
Joined
Mar 2, 2011
Messages
53
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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.
 
Upvote 0
maybe Batch geocoding and then just measure or judge by eye, you can't a afford a centre thats Buckingham Palace
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Random points in a 4 x 4 degree area:

[Table="width:, class:grid"][tr][td] [/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][td]
G​
[/td][td]
H​
[/td][td]
I​
[/td][td]
J​
[/td][td]
K​
[/td][/tr]
[tr][td]
4​
[/td][td]
Midpoint
[/td][td]
53.434​
[/td][td]
-0.985​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]B4: =SUMPRODUCT(B8:B21, $D8:$D21)/SUM($D8:$D21)[/td][/tr]

[tr][td]
5​
[/td][td]
Cosine
[/td][td]
0.5957​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]C4: =SUMPRODUCT(C8:C21, $D8:$D21)/SUM($D8:$D21)[/td][/tr]

[tr][td]
6​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]B5: =COS(RADIANS(B4))[/td][/tr]

[tr][td]
7​
[/td][td][/td][td]
Lat
[/td][td]
Lon
[/td][td]
Wgt
[/td][td]
Lon'
[/td][td][/td][td]
Euc Dist [km]
[/td][td]
GC Dist [km]
[/td][td]
% Error
[/td][td][/td][td][/td][/tr]

[tr][td]
8​
[/td][td][/td][td]
52.870​
[/td][td]
0.730​
[/td][td]
1​
[/td][td]
0.037​
[/td][td][/td][td]
129.7​
[/td][td]
130.4​
[/td][td]
0.6%​
[/td][td][/td][td]E8: =(C8-$C$4)*$B$5 + $C$4[/td][/tr]

[tr][td]
9​
[/td][td][/td][td]
52.710​
[/td][td]
0.632​
[/td][td]
4​
[/td][td]
-0.022​
[/td][td][/td][td]
133.9​
[/td][td]
134.7​
[/td][td]
0.6%​
[/td][td][/td][td]F8: =111.12 * SQRT((B8-$B$4)^2 + (E8-$C$4)^2)[/td][/tr]

[tr][td]
10​
[/td][td][/td][td]
54.941​
[/td][td]
-0.313​
[/td][td]
5​
[/td][td]
-0.585​
[/td][td][/td][td]
173.2​
[/td][td]
173.1​
[/td][td]
-0.1%​
[/td][td][/td][td]G8: =6371 * CentralAngle(B8, C8, $B$4, $C$4)[/td][/tr]

[tr][td]
11​
[/td][td][/td][td]
52.924​
[/td][td]
-1.585​
[/td][td]
1​
[/td][td]
-1.342​
[/td][td][/td][td]
69.3​
[/td][td]
69.4​
[/td][td]
0.3%​
[/td][td][/td][td]H8: =1-F8/G8[/td][/tr]

[tr][td]
12​
[/td][td][/td][td]
52.064​
[/td][td]
-1.784​
[/td][td]
2​
[/td][td]
-1.461​
[/td][td][/td][td]
161.2​
[/td][td]
161.6​
[/td][td]
0.2%​
[/td][td][/td][td][/td][/tr]

[tr][td]
13​
[/td][td][/td][td]
51.276​
[/td][td]
-1.303​
[/td][td]
1​
[/td][td]
-1.174​
[/td][td][/td][td]
240.8​
[/td][td]
241.0​
[/td][td]
0.1%​
[/td][td][/td][td][/td][/tr]

[tr][td]
14​
[/td][td][/td][td]
52.786​
[/td][td]
-2.091​
[/td][td]
4​
[/td][td]
-1.644​
[/td][td][/td][td]
102.7​
[/td][td]
103.2​
[/td][td]
0.5%​
[/td][td][/td][td][/td][/tr]

[tr][td]
15​
[/td][td][/td][td]
52.895​
[/td][td]
-0.874​
[/td][td]
3​
[/td][td]
-0.919​
[/td][td][/td][td]
60.4​
[/td][td]
60.4​
[/td][td]
0.1%​
[/td][td][/td][td][/td][/tr]

[tr][td]
16​
[/td][td][/td][td]
54.337​
[/td][td]
-2.779​
[/td][td]
3​
[/td][td]
-2.054​
[/td][td][/td][td]
155.5​
[/td][td]
154.6​
[/td][td]
-0.6%​
[/td][td][/td][td][/td][/tr]

[tr][td]
17​
[/td][td][/td][td]
54.942​
[/td][td]
0.479​
[/td][td]
3​
[/td][td]
-0.113​
[/td][td][/td][td]
193.5​
[/td][td]
192.8​
[/td][td]
-0.4%​
[/td][td][/td][td][/td][/tr]

[tr][td]
18​
[/td][td][/td][td]
52.879​
[/td][td]
-0.797​
[/td][td]
4​
[/td][td]
-0.873​
[/td][td][/td][td]
63.0​
[/td][td]
63.0​
[/td][td]
0.1%​
[/td][td][/td][td][/td][/tr]

[tr][td]
19​
[/td][td][/td][td]
54.161​
[/td][td]
-1.324​
[/td][td]
3​
[/td][td]
-1.187​
[/td][td][/td][td]
83.8​
[/td][td]
83.8​
[/td][td]
0.0%​
[/td][td][/td][td][/td][/tr]

[tr][td]
20​
[/td][td][/td][td]
52.545​
[/td][td]
-1.864​
[/td][td]
3​
[/td][td]
-1.509​
[/td][td][/td][td]
114.7​
[/td][td]
115.1​
[/td][td]
0.3%​
[/td][td][/td][td][/td][/tr]

[tr][td]
21​
[/td][td][/td][td]
54.464​
[/td][td]
-2.019​
[/td][td]
1​
[/td][td]
-1.601​
[/td][td][/td][td]
133.3​
[/td][td]
133.0​
[/td][td]
-0.3%​
[/td][td][/td][td][/td][/tr]
[/table]
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top