Help: Split GPS coordinates

Users
1

Hi all,

I am wondering if anyone could explain to me how to split coordinates into latitude and longitude captured through a commcare app: These are examples I copied from data exported into excel

-13.1058758 39.8739394 375.73 18.76
-13.1075339 39.8723163 313.52 6.43
-13.1037908 39.9784823 313.41 4.8
-13.1040679 39.9783811 319.53 4.8
-13.1074439 39.8722719 360.4 6.43
-13.1074439 39.8722719 360.4 6.43
-13.1058189 39.8741424 392.55 9.64
-13.1058972 39.874191 373.09 4.8
-13.107771 39.8723125 375.98 8.57
-13.1058996 39.8739973 367.98 8.57
-13.1041881 39.9784299 324.56 15.0
-13.1039981 39.9786807 325.49 9.0
-13.1084256 39.8720012 345.51 8.84
-13.1059195 39.8737811 352.55 7.07

My aim is to build a map of locations from these coordinates

Thanks

2

Hi,

Geopoints from CommCare are a space separated list of

latitude longitude altitude accuracy

You can split by the space character and then join the first two with a comma into a lat/lon format which should be compatible with most systems

3

Hi @Clayton_Sims,

I can not understand this part very well:

My aim is to split the coordinates into columns in excel.
Thanks

4

If you are manipulating the data in excel, you can use the text to columns feature to extract the 4 elements into 4 columns, then use the CONCATENATE function to bring them back together with a comma.

5

To parse from a cell in this case H10:
Latitude =LEFT(H10,FIND(" ",H10,1)-1)
Longitude =MID(H10,FIND(" ",H10,1)+1,FIND(" ",H10,FIND(" ",H10,1)+1)-FIND(" ",H10,1))
If using a table simply replace the cell reference A1 with a field name like TableName[form.GPS].

6

Hi @Clayton_Sims,

I want to build the map on google maps from the coordinates. I have split the coordinates using text to columns method and combined the latitude and longitude through the concatenate function. Is any difference from keeping LAT and LONG in separate columns and keeping them in one column when it comes to map building?

Thanks

7

In most contexts, you will find that a comma separated lat/lon pair is the most compatible with various tools and formats.

For example, the Google Maps web app pattern for looking at a place is:

https://google.com/maps/@,<zoom_level>z

IE:

https://google.com/maps/@31.6333744,-7.9553251,13z

8

Hi @Clayton_Sims,

Thank, you, for your help. I managed to build the map.

9

Awesome :grinning:! Do you mind sharing how you're setting up the map in particular? Always interested to hear how people are using their data downstream, and other folks in the forum may be interested in the example.

10

Hi @Clayton_Sims,

I followed your instructions to separate latitude, longitude, elevation and accuracy. After I eliminated elevation and accuracy columns. I combined lat. and long. columns into one and included another column with names of locations as shown below.
image

Having the csv excel file ready, I went to Google Maps and followed some steps:

  1. On your computer, I opened Google My Maps.
  2. I clicked on create a map.
  3. In the map legend, I clicked Add layer.
  4. I gave the new layer a name.
  5. Under the new layer, I clicked Import.
  6. I Chose or uploaded the csv excel file containing your info
  7. I selected the columns from the file to put placemarks on the map ( I selected the LAT_LONG column)
  8. I Picked a column to use as the title for the placemarks, here I selected the locations column person.
  9. I had the info added on the map. (To have names of locations appear on the map, I defined labels (names in the column column)

The map I got as result:

image
image822×493 106 KB

Thanks