What is your use case for this? Find and replace is a pretty quick processing step in spreadsheet or text editor software; what's the purpose of doing it within the form itself?
Because I want to see if I can change the geotrace coordinate format to kml format to show polygons directly in a Google Fusion map.
What you probably want here is the XPath 2.0 translate() function, which ODK does not currently support in javaRosa AFAIK.
However, it appears Enketo does support the translate() XPath function [perhaps @martijnr can explain?], so you can in fact do this conversion if you run your form thru Enketo! For example
translate("345;3456;23456", ";", ",")
returns
345,3456,23456
You can certainly submit a feature request to add translate() to ODK for consideration, and provide some good usecases for why its worthwhile. This will, of course, be even more likely to gain approval if you offer to implement it... [hint: it'll go in https://github.com/opendatakit/javarosa/blob/master/src/org/javarosa/xpath/expr/XPathFuncExpr.java] 
3 Likes
Hi Gareth,
This is exactely what I discovered. The translate function does not work. Good to have this confirmed. I suppose I can speed things up if we would hire a consultant to program such a change in ODK...? Or hire Martijn to implement it in ODK. I don't have it clear yet how these things work in open source (to request changes, either by the community or paid consultant), but will figure it out. Any advise on the options is welcomed. Thanks for your feedback!
Uh oh... It's accidental. Enketo accidentally supports all XPath 1.0 functions and axes (translate() is XPath 1.0). There wouldn't be a way to disable that support either (and the popularity of ODK Validate ensures there isn't a compatibility problem as forms using unsupported features won't pass).
So far, if there is a demand to add a native XPath 1.0 function to the ODK Spec, that has always been approved quickly. It's the easiest kind of spec proposal addition (axes is a different story).
Before throwing money at it, first off I would want to actually confirm that (just) implementing translate() is all that is required to solve your problem - namely converting ODK geopoint/geotrace/geoshape strings to their respective KML coordinate/LineString/LinearRing. All translate() can do is simple single character substitutions; it cant reorder things, drop elements, substitute substrings, etc. If its sufficient then awesome, we can look at supporting it in javaRosa (which as @martijnr says should be relatively uncontentious. More so 'cause its zero work for him! 
).
Serendipitously, looks like you can use Enketo to confirm that you can perform the necessary conversion! [naughty Enketo for supporting all XPath1 functions! But we can forgive you this one time. 
]
If it is then great! If your need is urgent, and you can fund the development work required, then I might suggest (@yanokwa?) contacting Nafundi (no affiliation), and maybe they can fast-track a custom build for you. Otherwise, you could post a feature request to the appropriate Forum, with suitable justification, and perhaps offer to pay for the necessary code changes, which when complete and tested will be pulled into the source via a suitable PR (Pull Request) containing all the necessary new code, and the function will appear in a subsequent release. If you are interested in the process, perhaps look at this current feature request which I have in flight, to similarly add a new XPath function to ODK.
This is what I hope to do with translate():
Column 1: Geotrace coordinates in original format
43.001847 4.9032711 0.0 40.188;43.001847 4.9032711 0.0 40.188;43.001847 4.9032711 0.0 40.188;43.001847 4.9032711 0.0 40.188;43.0018446 4.9032637 0.0
Column 2: Add kml labels with concat():
concat("Polygon><outerBoundaryIs<LinearRing<coordinates", ${column 1},"</coordinates</LinearRing</outerBoundaryIs</Polygon")
Column 3: use translate() to Change " " (blanc) to "," and change ";" to " " (blank);
translate("column 2"," ",","} and translate("column 2",";"," "}
Result column 3:
Polygon><outerBoundaryIs<LinearRing<coordinates43.001847,4.9032711,0.0,40.188 43.001847,4.9032711,0.0,40.188 43.001847,4.9032711,0.0,40.188 43.001847,4.9032711,0.0,40.188 43.001847,4.9032711,0.0,40.188</coordinates</LinearRing</outerBoundaryIs</Polygon
I think this should work. No reordering, no dropping of elements, just converting a set of blanc spaces to comma's and some ; to blanc spaces. It is a workaround to do this calculation in ODK collect in order to show them in Google Fusion (better would it be if you can just publish polygons to Google Fusion), but it will be a helpfull workaround I think. The only thing is that geotrace plots 2 extra columns after the coordinates (height and accuracy) while kml only has 1 (height). But with testing, this does not seem to be a problem if they are separated by a comma.
Now the only option is downloading with the new Briefcase version (with export to GeoJSON), which is great work and a great tool by the way. However, I would like to have all my data in Google Fusion, including polygons, on the fly. This could be a great work around.
The help and feedback of you both is very much appreciated. I don't know how long it will take to be incorporated and being downloadable in a new ODK version (I supose modifications need to be made in XLSforms and in Aggregate and in ODK Collect?).
Very good! I was optimistic that it could be done; be thankful a geopoint and coordinate both use <lat> followed by <long>, or we'd have been screwed! . But I wasnt 100% sure, what with a geopoint have four elements vs a KML coordinate's three...
And you should be able to accomplish both character substitutions with a single translate(); eg
translate(${foo}, "; ", " ,")
which will perform the following substitutions:
';' --> ' '
' ' --> ','
Also, watch for missing '>', '<' in your concat(...).
And you can combine everything into a single calculation if you like. Try this:
concat("<Polygon><outerBoundaryIs><LinearRing><coordinates>", translate(${column_1}, "; ", " ,"),"</coordinates></LinearRing></outerBoundaryIs></Polygon>")
The new translate() function would need to be added to javaRosa, which would be picked up by Collect in its next release, and likewise picked up by Validate (which is the only change needed for Aggregate to accommodate). I dont believe any change would be needed to XLSForm (actually, pyxform) because it basically just exports whatever XPath expression you type in when translating XLSForm to XML.
It seems I made an error with testing... Like you mention, kml uses long/lat format while geotrace uses lat/lon... I thought I had it working, but another test did not work... That is a shame... Means that the translate will not work. I will do some other checks, but in case I don't find a solution, we have to skipp translate as a workaround. Apologies for your time and confusion and thanks for the feedback! At least, people haven't start coding while its not working...
Np. Best to discover translate() won’t work now, before throwing $$ at it, eh.
Sound like it might require either a custom ODK Collect widget (or appearance?) that spits out KML encoded geo-data, or a custom XPath function to specifically convert a geopoint/geotrace/geoshape to its KML equivalent; ie swapping lat<—>long, and dropping geo’s 4th accuracy value. Probably a single (new) XPath function would suffice to handle all 3 geo* types.
Thoughts @ggalmazor? (our resident GIS ‘afficionado’... )
1 Like
Just wanted to say that I've read this, but won't have time to elaborate until next Monday
Agree. What would be ideal is the same as geopoints are now shown: they can be easily visualized in Aggregate and in Googe Fusion. Would be perfect if you could do the same with shapes. At present, you have to either export shapes from Aggregate (export to kml) or download them with Briefcase as GeoJSON. I like the export to Google Fusion because the posibility to (edit and) analyse and visualize your data almost automatically. I hope Ggalmazor has a solution.
In case you haven't already, you might be interested in this related thread; specifically it describes a script that might be useful to your purposes.
Although arguably a custom ODK Collect (and Aggregate) - with geopoint/geotrace/geoshape widgets which spit out compatible KML instead - might be the most user-friendly solution to your users, its highly unlikely to make it into the main code, so you'd end up having to maintain a custom fork going forward. And you'd have to re-write all @ggalmazor's GeoJSON export code. Similarly, a custom geo-to-kml() XPath extension function is probably too specific to justify adding to main; it would require all ODK clients having to add it to their XPath library, eg Enketo uses a different lib than Collect (so again you may end up having to maintain a custom fork). But perhaps if there are other standard XPath functions (eg tokenize()?) that could be used (in conjunction with translate()] to do the translation, these might be more amenable to add in support for.
Something along these lines may be your best bet; that is extending current export formats to include an export to Google Fusion format. This keeps the (ostensibly redundant) additional KML format out of the core Collect/Aggregate data flow.
So the solution it is quiet complex... I have to stick to the Briefcase to GeoJSON solution then and import all kml polygons manually in Google Fusion. Do you know if the other data storage platforms that are compatibel with ODK Collect (Ona, Kobo Toolbox etc.) are capable to visualize polygons and make a connection with Google Fusion?
Google just announced that they are retiring Fusion Tables on December 3, 2019.
Another thing you could try is to stream all the submissions from Aggregate to another server, and do the geo-to-KML conversion there. I did something along these lines to visualize geo-referenced XForm submissions directly in Google Earth, which obviously involved having to convert geopoints to KML coordinates, but you could just as easily extract and translate geotrace & geoshapes as well. See this thread for more details.
Failing all that, there's certainly no harm in floating the idea of a new geo-to-KML (or geo-to-JSON?) XPath function in Features [especially when you can offer some funding]. Certainly this long-lat format used (solely) by KML seems to be a well-known pain-point...
Interesting. I will check these options. In the meantime we just got a message from Google that Google Fusion will go off-line by 03 December 2019... So something to consider and to look for alternatives...
Sorry for answering sooooo late to this!!
I don't think you're going to be able to pull this one off without turning to an external tool that lets you process data, which defeats the purpose, I think.
The best thing you could do without involving any external tools is to export a KML with Aggregate and open it with Google Earth, but I understand that it's less convenient than what you wanted to do with Google Fusion Tables.
I'm sorry I can't provide any help!
FYI @Eddy_Rellum, I've been working on a test form that'll convert geoshapes/geotraces to their KML equivalent... Its not very efficient to attempt to do this internally within the form itself (it would be far more efficient to have a dedicated XPath function to do the conversion) but it'll get the job done in a pinch. It does, however, require the new substring-after() function, which the likes of Enketo already supports but javaRose/ODK Collect wont until a subsequent release; hence why I dont want to post this for general consumption just yet. But if you'd like to play around with it using Enketo I can email you a copy.
Hi Gareth,
Would be great to try. Thanks in advance. In the meantime, I am doing an inventory on what alternative options there are for visualising polygons in a "Google Fusion like" alternative. I am working for a an organization with numerous reforestation projects that submit polygons to us. Now, all needs to be processed by hand... This is not manageable in the long term and a waste of time. I estimate that more people would welcome a flexible central platform where aggregate data can be processed, (eddited) and visualized and other desktop information could be integrated. I saw the requirements inventory of Yaw about an alternative for Google Fusion. I know there is finance available, the question is how to put development into action.