# Geo fields should be stored in wkt format (Well Known Text)

**URL:** <https://forum.getodk.org/t/geo-fields-should-be-stored-in-wkt-format-well-known-text/7048>\
**Category:** Ideas\
**Tags:** odk-aggregate\
**Created:** [June 27, 2017, 3:12pm UTC](https://forum.getodk.org/t/geo-fields-should-be-stored-in-wkt-format-well-known-text/7048 "2017-06-27T15:12:25Z")\
**Posts on this page:** 1\
**Showing post:** 11

<div class="post-metadata">

**Author:** ![mathieubossaert](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/mathieubossaert/32/6131_2.png) [@mathieubossaert](https://forum.getodk.org/u/mathieubossaert)\
**Post date:** [July 24, 2019, 11:42am UTC](https://forum.getodk.org/t/geo-fields-should-be-stored-in-wkt-format-well-known-text/7048/11 "2019-07-24T11:42:54Z")

</div>

Thanks @Phil_Burapha for you interest, but I can't see and reproduce the problem you mention, but maybe I misunderstood the problem. The tree examples below are working fine...

for a geotrace :

> WITH geom\_from\_odk AS  
> (  
> SELECT trim(COALESCE(NULLIF(trim(concat(null,' ',null),';'),' '),  
> '43.608362555356365 3.892917466396426 0.0 0.0;43.60864838739175 3.8930497886624664 0.0 0.0;43.608906859254205 3.8928904418296213 0.0 0.0;43.60870681929299 3.892721462561326 0.0 0.0;',  
> null),';') as geom\_odk  
> )  
> SELECT geom\_odk, unnest(string\_to\_array(btrim(geom\_odk::text, ';'::text), ';'::text)) AS unnest,  
> array\_length(string\_to\_array(btrim(geom\_odk::text, ';'::text), ';'::text), 1) AS nb\_points,  
> generate\_series(1, array\_length(string\_to\_array(btrim(geom\_odk::text, ';'::text), ';'::text), 1)) AS ordre,  
> split\_part(unnest(string\_to\_array(btrim(geom\_odk::text, ';'::text), ';'::text)), ' '::text, 1)::numeric AS LAT,  
> split\_part(unnest(string\_to\_array(btrim(geom\_odk::text, ';'::text), ';'::text)), ' '::text, 2)::numeric AS long  
> FROM geom\_from\_odk

for a geopoint :

> WITH geom\_from\_odk AS  
> (  
> SELECT trim(COALESCE(NULLIF(trim(concat(3.892917466396426,' ',43.608362555356365),';'),' '),  
> null,  
> null),';') as geom\_odk  
> )  
> SELECT geom\_odk, unnest(string\_to\_array(btrim(geom\_odk::text, ';'::text), ';'::text)) AS unnest,  
> array\_length(string\_to\_array(btrim(geom\_odk::text, ';'::text), ';'::text), 1) AS nb\_points,  
> generate\_series(1, array\_length(string\_to\_array(btrim(geom\_odk::text, ';'::text), ';'::text), 1)) AS ordre,  
> split\_part(unnest(string\_to\_array(btrim(geom\_odk::text, ';'::text), ';'::text)), ' '::text, 1)::numeric AS LAT,  
> split\_part(unnest(string\_to\_array(btrim(geom\_odk::text, ';'::text), ';'::text)), ' '::text, 2)::numeric AS long  
> FROM geom\_from\_odk

for a geoshape :

> WITH geom\_from\_odk AS  
> (  
> SELECT trim(COALESCE(NULLIF(trim(concat(null,' ',null),';'),' '),  
> null,  
> '43.608647467555784 3.8931233982374636 0.0 0.0;43.60826940402103 3.8932827624893207 0.0 0.0;43.60789392761811 3.893790823480458 0.0 0.0;43.60862157286206 3.8943042359775006 0.0 0.0;43.60907213881663 3.8937161287686024 0.0 0.0;43.608647467555784 3.8931233982374636 0.0 0.0;'),';') as geom\_odk  
> )  
> SELECT geom\_odk, unnest(string\_to\_array(btrim(geom\_odk::text, ';'::text), ';'::text)) AS unnest,  
> array\_length(string\_to\_array(btrim(geom\_odk::text, ';'::text), ';'::text), 1) AS nb\_points,  
> generate\_series(1, array\_length(string\_to\_array(btrim(geom\_odk::text, ';'::text), ';'::text), 1)) AS ordre,  
> split\_part(unnest(string\_to\_array(btrim(geom\_odk::text, ';'::text), ';'::text)), ' '::text, 1)::numeric AS LAT,  
> split\_part(unnest(string\_to\_array(btrim(geom\_odk::text, ';'::text), ';'::text)), ' '::text, 2)::numeric AS long  
> FROM geom\_from\_odk

As an alternative, following @Xiphware and @detrygeoffroy I will soon play to generate a WKT column withi the form as they did with kml and geojson :

- [ODK geoshape/geotrace/geopoint to KML](https://forum.getodk.org/t/odk-geoshape-geotrace-geopoint-to-kml/18985)
- [ODK geoshape/geotrace/geopoint to GeoJSON - #3 by Oliver\_Burdekin](https://forum.getodk.org/t/odk-geoshape-geotrace-geopoint-to-geojson/21046/3)

It will be more simple to do because we only need to concatenate edges coordinates.

---

_[View the full topic](https://forum.getodk.org/t/geo-fields-should-be-stored-in-wkt-format-well-known-text/7048)._