# 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:** 14

<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:** [August 2, 2019, 9:07am UTC](https://forum.getodk.org/t/geo-fields-should-be-stored-in-wkt-format-well-known-text/7048/14 "2019-08-02T09:07:45Z")

</div>

Hi to all,

thanks to @Phil_Burapha I think I found a curious comportment in Aggregate : It seems that when geoshape is a short string, edges are separated by a semicolon and a space "; " and when the geoshape is a long string, the delimiter is only a semicolon.  
here are some examples, created the same days with the same form.  
[geoshp\_spaces.txt](https://forum.getodk.org/uploads/short-url/1c5yDpXevTpgXdCFtVahZFnyzzX.txt) (3.4 KB)

So I adapted the function to consider a possible space after the semicolon.

```
CREATE OR REPLACE FUNCTION odk.geom_from_odk_to_postgis(LONG numeric, LAT numeric, GEOTRACE TEXT, GEOSHAPE TEXT)
RETURNS geometry(GEOMETRY,4326) AS
$$
WITH geom_from_odk AS 
(
        SELECT regexp_split_to_array(btrim(COALESCE(NULLIF(trim(concat(LAT,' ',LONG),';'),' '),
        GEOTRACE,
        GEOSHAPE),';'),';\s*') as geom_odk
),
node_array AS (
        SELECT geom_odk, unnest(geom_odk) AS unnest,
        array_length(geom_odk,1) AS nb_points,
        generate_series(1, array_length(geom_odk, 1)) AS ordre,
        split_part(unnest(geom_odk), ' '::text, 1)::numeric AS LAT,
        split_part(unnest(geom_odk), ' '::text, 2)::numeric AS long
        FROM geom_from_odk
)--, geom AS (
SELECT 
CASE
WHEN node_array.nb_points = 1 THEN st_union(st_setsrid(st_makepoint(node_array.long::double precision, node_array.lat::double precision), 4326))
WHEN st_isclosed(st_makeline(array_agg(st_setsrid(st_makepoint(node_array.long::double precision, node_array.lat::double precision), 4326) ORDER BY node_array.ordre))) IS FALSE THEN st_makeline(array_agg(st_setsrid(st_makepoint(node_array.long::double precision, node_array.lat::double precision), 4326) ORDER BY node_array.ordre))
WHEN st_isclosed(st_makeline(array_agg(st_setsrid(st_makepoint(node_array.long::double precision, node_array.lat::double precision), 4326) ORDER BY node_array.ordre))) AND node_array.nb_points > 3 THEN st_makepolygon(st_makeline(array_agg(st_setsrid(st_makepoint(node_array.long::double precision, node_array.lat::double precision), 4326) ORDER BY node_array.ordre)))
-- ELSE the polygon is invalid because made of 3 points, 1st and 3rd are the same so we create a linestring
ELSE st_makeline(array_agg(st_setsrid(st_makepoint(node_array.long::double precision, node_array.lat::double precision), 4326) ORDER BY node_array.ordre))
END AS geom
FROM node_array
GROUP BY node_array.nb_points;
$$
  LANGUAGE sql VOLATILE
  COST 100;

```

---

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