# GIS without GIS (Using Spreadsheets)

No, I haven’t gone mad, though some might disagree. I was inspired to write this after talking to a colleague who previously worked for a bank they were tasked with getting proximities to properties using Excel alone. This got me thinking over how much we crank up the GIS, when sometimes it is so much easier to use your brain, or at least Excel.

### 1. Calculate Distance

Using a formula from the 6th century doesn’t immediately come across as a great choice, but it is a stroke of genius. Pythagoras knew his stuff, and adapting the theorem we learned verbatim at school a little, we can quickly and easily extract distances between coordinates in a planar system. I have used this a few times on spreadsheets to calculate distances between huge swathes of points:

##### D=SQUARE ROOT OF ((X1–X2)²+(Y1-Y2)²)
How is this applicable to Exel?

Consider two sets of coordinates x1,y1 & x2,y2. If we first process the first part of the equation ((X1–X2)²+(Y1-Y2)²) below, we get -121103. Excel, of course, won’t square root a negative number so we have to put in a conversion. IF the number is negative (see below, using IF and ABS), we can then square root the positive number, which provides the answer 347.9986. (This is a metric coordinate system using metres.) Therefore the distance is 348m

 x1 y1 x2 y2 Apply formula but don’t root Change if negative Answer 612109.00 5639930.00 612108.00 5640278.00 -121103 121103 347.9986 ((A2-C2)^2-(B2-D2)^2) IF(E2<0,ABS(E2),E2)

Although it might be easier to use the measure tool if you have the GIS open, if you are faced with a ream of coordinates, this method might be a lifesaver.

### 2. Change from Degrees, Minutes, Seconds to Decimal Degrees

This one is close to my heart, though  I often see it being some mammoth task undertaken by someone as they use some coordinate converter or conversion tool. This can be done quickly and easily in your favourite spreadsheet.

 A B C D E F Latitude Longitude Latitude                 (for conversion) Longitude                     (for conversion) Latitude Longitude (decimal degree) (decimal degree) 42° 43′ 5″ N 71° 12′ 37″ W 42D 43′ 5″ N 71D 12′ 37″ W 42.71805556 -71.21027778 42° 36′ 7.880″ N 71° 10′ 17.400″ W 42D 36′ 7.880″ N 71D 10′ 17.400″ W 42.60218889 -71.1715 42° 26′ 31.65″ N 71° 6′ 16.16″ W 42D 26′ 31.65″ N 71D 6′ 16.16″ W 42.442125 -71.10448889 42° 26′ 31.65″ N 71° 6′ 16.16″ W 42D 26′ 31.65″ N 71D 6′ 16.16″ W 42.442125 -71.10448889

There are [again] a couple of steps required to do this, but if you have a spreadsheet already set up, you can just add your values and extract your numbers.

First task using the method here is to convert the coordinates in columns A&B so that instead of having the degrees symbol, it uses the letter “D.” this should be put into columns C&D.

Then I use this equation to convert C4 to the result in E4:  =SUBSTITUTE(SUBSTITUTE(LEFT(C4,LEN(C4)-3),”D “,”:”),”‘ “,”:”)*IF(RIGHT(D4,1)=”S”,-24,24)

To convert D4 into the result in F4 I use this equation:  =SUBSTITUTE(SUBSTITUTE(LEFT(C4,LEN(C4)-3),”D “,”:”),”‘ “,”:”)*IF(RIGHT(D4,1)=”S”,-24,24)

As you can see above, this works fine, and as long as you put it in correctly you can just use “Fill” to populate the other coordinates easily. Best thing is I know EXACTLY what this is doing to my coordinates!

To add another freebing here – if you are working with offshore data, you will frequently find data supplied in Degrees, Decimal Minutes (DDM) to convert this to Decimal Degrees use the following method:

 A B C D Latitude DDM Longitude DDM Latitude Longitude (decimal degree) (decimal degree) 42° 43.5′ N 71° 12.37′ W 42.725 -71.205 47° 41.5′ N 71° 12.37′ W 47.692 -71.205 21° 25.5′ N 71° 12.37′ W 21.425 -71.205

To convert A4 (42° 43.5′ N) to decimal degrees, put this in C4: =IF(RIGHT(A4,1)=”S”,ROUND(LEFT(A4,2)+MID(A4,4,6)/60, 3)*-1, ROUND(LEFT(A4,2)+MID(A4,4,6)/60, 3))

To convert B4 (71° 12.37′ W) to decimal degrees, put this in D4: =IF(RIGHT(B4,1)=”W”,ROUND(LEFT(B4,2)+MID(B4,4,6)/60, 3)*-1, ROUND(LEFT(B4,2)+MID(B4,4,6)/60, 3))

You will find that this will also convert west and east coordinates into negative values.

### 3. Calculate LiDAR RMSE (Root Mean Square Error)

1. In cell A2, type “observed” as a title. In B2, type “predicted value”. In C2, type “difference”.

2. If you have 10 observations, place observed elevation values in A3 to A12. Place predicted values in B3 to B12.

3. In column C3, subtract observed value and predicted value: =A3-B3. Repeat for all rows below where predicted and observed values exist.

4. In cell D3, use the following formula to calculate RMSE: =SQRT(SUMSQ(C3:C12)/COUNTA(C3:C12))

Cell D3 is the root mean square error value

 A B C D Observed Predicted Difference RMSE 12.1 17.6 -5.5 13.02463051 12.1 15 -2.9 12.1 13 -0.9 12.1 11 1.1 12.1 52 -39.9 12.1 7 5.1 12.1 12 0.1 12.1 11.7 0.4 12.1 14.8 -2.7 12.1 17.6 -5.5

### Will I be a Convert?

It is true, some of this might be quicker for a GIS pro to be done in the GIS, though I personally have a few gripes with GIS now in that it has become a huge “black box”. How well do you know exactly what calculations the software is performing? I recently got questioned on what the calculation ArcGIS used for the visibility analysis as it has Earth curvature and air coefficient as options. The point here is that it is all ASSUMED. Although the above might not draw any maps for you, it might help towards understanding HOW it works.

Maybe the next blog should be on the “Great Circle” equation, which calculates in latitude/longitude based on earth curvature (better for long distances) ….

d=2*asin(sqrt((sin((lat1-lat2)/2))^2 + cos(lat1)*cos(lat2)*(sin((lon1-lon2)/2))^2))

Nick D