ODK code only sometime converts to accurate polygons on maps, but not always?

Hello ODK Forum,
Attached is some data recently collected through ODK forms and stored on ONA. We collected data from 56 sites in Mali, including recording their perimeters with GPS. The “Map” functionality of ONA shows the location of these sites, except for 14 of the sites – where the location is unclear.

Please see the excel attachment called: ODK_Forum_Mali_Data_GPS_Question
Please see the worksheet called: “group_survey_group_gps_geopoint”.
In Column L called “_parent_index” (highlighted in yellow) please filter on: 10, 16, 27, 26, 29, 33, 36, 37, 40, 42, 44, 46, 50 and 55.
These are the 14 sites for which the mapping has not worked.
You can see that their corresponding longitude, latitude and altitude (etc.) coordinates appear to have got a bit mixed up.

Please see the worksheet called: “Details_Confirmed”.
Here I have listed the details of the different tablets involved in collecting the data.

Would you be able to advise on:

  • Why did 14/56 sites not record the GPS perimeter successfully?

The following is the response which I received from ONA to this question:

“Hello David,
the two function that are not working well are one for latitude and longitude if(${index}=0,${latstr},substring-after(${latstr}, ';')) and selected-at(${geoshape},${index}*3+1)
I am not sure why it is working fine to most records and it fails in some records by pulling wrong data from geoshape polygon points.
I recommend you ask the question on ODK Forum and attach the form by highlighting the two functions since sometimes it pulls altitude in stead of latitude.
We are not very sure why this happens but what we can confirm from our end is that the data from data collectors are coming in in the correct format.
A workaround for now is exporting data from Ona and identify those sites that have incorrect data and clean it manually in Excel.

I have highlighted the two formula in red in the worksheet called “ref_survey” in cells S73 and S74.

Thank you in advance for your interest, time and support.
Very best wishes,
David Baines
ODK_Forum_Mali_Data_GPS_Question.xlsx (269.2 KB)

OK this is not a solution, but an explanation. Looking at the geometry definition, I observed that the "not working" ones do have a space character following the semicolon.

[edit] removed the link to an old error wich may be confusing [/edit]

Hello Mathieu,
Thank you for your message (and link). Indeed you are correct, the "not working" ones do display a space character after the semicolon. Although this does sometimes correspond to shorter length strings, it is not exclusive i.e. there are some longer length strings that also are "not working" and also display a space character after the semicolon.
Either way, is there code which we could include into the ODK form which will always 'remove' space characters after a semicolon?
Thank you and very best wishes,

yes you are right :wink:

I just post a new version of the sample geoshp2wkt xlsform here :

You should just have to adapt it it in your form. Please let me know if it is ok for you.

I tried it with both ";" and "; " separated geoshapes and it works fine :wink:

Good Morning Mathieu,
Thank you so much. I will update our ODK forms accordingly. We will test and let you know how they work. Thank you again.

1 Like

Mali_Land_Use_Survey_Sept21_prepared_GPS_v4.xlsx (424.1 KB)

Hello Mathieu,
I trust you are well.
Our Programme Manager recently clarified to me that the column which he uses to upload data from the Excel onto Google Maps/QGIS is on worksheet "Data_v2" and its column "BB" titled "group_survey/group_gps/polygon/wkt". I had previously thought it was column "AW" titled "group_survey/group_gps/geoshape" which he was using.
May I check with you, that the new code you recently shared with us will also provide 'clean' data in Column "BB", allowing him to automate the uploading of that data? (My previous understanding was that your code was removing the space which sometimes occurs after the semi-colon in column "AW").
The data in column "BB" was mixed up, and I needed to clean it manually in worksheet "group_gps_geopoint_v2" before copying it across into worksheet "Data_v2" column "BB".
Thank you Mathieu,

Hi David,

the values in the BB column are the calculated fields of the form, so yes, if you apply the changes I made in the showcase's form in the geopoints and polygon section of your survey, calculated values would be ok... I tested it by replacing the "geo" question by a text input in the showcase form. If you try it with each wrong geoshape from the AW column, you should get a well formatted wkt.