Matching GPS coordinates

user_12220585 0
Viewed 152

I am looking for a tool to match GPS coordinates. Attached is a sheet with a list of GPS coordinates. https://docs.google.com/spreadsheets/d/1pCVlq7BEUBQyST0iRoPcgp_XUhUyOYjAuPQ3mfR5ehU/edit#gid=0

I tried array but I cannot make 1 cell subtract from a column and even if I do it is taking a very long method.

Do you have any suggestion of what I can use? I ideal situation I’m hoping for is that it checks the list of coordinates and highlight the coordinates that exist more than once, say within 300meters.

I use this formula to calculate distance between 2 points = 111*SQRT((X1-X2)^2+(Y1-Y2)^2) Each change in degree of GPS coordinate, corresponds to 111km on geographical scale approx.

google-apps-script google-sheets match gps lookup
1 Answers

Solution

You can check the coordinate points that are closer than a specific distance by creating, in another sheet, a matrix of distances between all the points and by using conditional formatting to highlight the pair of points that are closer than a specific distance.

Steps to achieve this:

  1. Create a new sheet in your Spreadsheet.
  2. In the first cell insert the following formula:

=ARRAYFORMULA(SQRT(POW(INDIRECT("Sheet1!$B"&(COLUMN(B2)))-Sheet1!$B2:$B340,2) + POW(INDIRECT("Sheet1!$C"&(COLUMN(B2)))-Sheet1!$C2:$C340,2)))

Formula explanation : This formula calculates the euclidean distance between two coordinate points. I have used ARRAY FORMULA to automatically calculate the distance between the first point with the rest of the coordinates of your GPS signal. Note that indirect is used so that you can propagate this formula to the rest of the columns. In this case I am testing with 340 coordinate points but you can increase this as you want.

  1. Select the cell where you wrote the formula in and drag it through the columns until you reach your total number of elements. (You can easily check this when the 0s diagonal reaches the last row of values).
  2. Select your whole range (matrix) and head over to Format -> Conditional Formatting and then in the UI in Less or equal than choose the distance you wish to put as the limit.

In this way you will have this distance matrix telling you which intersection points are closer than a specific distance. This is an example image of how it would look like (for distances smaller than 0.4):

enter image description here

Resources used :

ARRAYFORUMLA, POW, INDIRECT, Conditional Formatting

user_128357570
Related