Coordinates in excel

hola, I am using odkcollect con google drive and googlesheet but,I don't like that latitude and longitude coordinates appear in the same cell. I would like them to appear in different cells to facilitate export to GIS.

Take a look at this thread - specifically the geopoint2kml stuff - for how to extract the latitude and longitude (into separate results) from a geopoint.

Hola Daniel! It's no problem if ODK will provide the coordinates in the same cell. You can split or separate the longitude and latitude from each other using excel. And it's a very easy step. Using MS Excel, you can split the data using the "text-to-columns" button under the "Data" tab. Just use the "semi-colon" as the delimiter. I have an exported csv file from ODK collect which I splitted the longlat cell into two cells. Here is an attached image of a table with splitted long/lat.

3 Likes

I have a similar problem, posted on another question Google spreadheets get image filename instead of URL shared link (so it probably hasn't been picked up). In my case I cannot easily merge data that was pulled through Briefcase with that collated via Google sheets. So there appears to be internal inconsistency within ODK. The great thing about data from Briefcase, which is in csv format, is that you can load that into (for example) QGIS as a delimited text layer and select which columns represent lat and long... No can do with Google sheets.

So these answers address the problem but actually would it not be better for ODK to do this 'natively'? The more processing steps there are, the harder it is to manage data, especially when working with an ongoing data collection process. And then the more chance of duplicating or missing data points if it has to be run through a script or formula...

So apologies for my naivety, but if it can be done through Briefcase, can it be done when sending data to Google sheets?

Hi,
To populate the longitude and latitude in two separate columns use the type "calculate" in the survey form.
type name label calculation
calculate latitude Lat selected-at(string(${gps}),0)
calculate longitude long selected-at(string(${gps}),1)
Pl see the attached excel sheet. The lat / long columns can be seen in the output saved file.
Lat_Long_Extract.xls (32.5 KB)

1 Like