How do I convert a MultiPoint into a LineString in Snowflake?

Question

I have a geography column called multipoint with values that look like this:

{
  "coordinates": [
    [
      -8.734592000000001e+01,
      4.146811000000000e+01
    ],
    [
      -8.734592000000001e+01,
      4.146811000000000e+01
    ],
    [
      -8.734592000000001e+01,
      4.146811000000000e+01
    ],
    [
      -8.734592000000001e+01,
      4.146811000000000e+01
    ],
    [
      -8.734592000000001e+01,
      4.146811000000000e+01
    ]
  ],
  "type": "MultiPoint"
}

How do I convert this to a LineString so I can get the length using [ST_LENGTH](https://docs.snowflake.com/en/sql-reference/functions/st_length)? Or alternatively, what's a way to get the length of the line represented by this point cloud?

I tried ST_MAKELINE, but that takes exactly two geography arguments. I have only a single geography object per row.

Answer 1

These are 2 alternative hacks:

1. Replace "MultiPoint" with "LineString" in the geometry string, and then apply ST_LENGTH().

Or:

2. Use the same "MultiPoint" twice to ST_MAKELINE(), then extract the first element, then use the first element to ST_MAKELINE() with the original "MultiPoint". Then you can get ST_LENGTH().

Somehow the second alternative runs way slower on my tests. In the meantime I'm asking the Snowflake team to look at this case, and hopefully support arrays as an input to ST_MAKELINE().

Code:

with data as (
select $$
{
  "coordinates": [
    [
      1.734592000000001e+01,
      1.146811000000000e+01
    ],
    [
      -8.734592000000001e+01,
      4.146811000000000e+01
    ],
    [
      -8.734592000000001e+01,
      4.146811000000000e+01
    ],
    [
      -8.734592000000001e+01,
      4.146811000000000e+01
    ],
    [
      0,
      0
    ]
  ],
  "type": "MultiPoint"
}
$$ x) 

select replace(x, 'MultiPoint', 'LineString') x2
    , try_to_geography(x2) line
    , st_length(line)
from data;

select try_to_geography(x) g, st_makeline(g, g) g2
    , st_makeline(st_pointn(g2, 1), g) y, st_length(y) 
from data;