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

Ideas
11

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 :

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