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

Ideas
14

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 (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;
1 Like