How can I cast a geography type into a string using Snowflake?

I'm surprised this isn't possible. Is there an alternative?

SELECT st_point(37.5, 45.5)::string;
-- SQL compilation error: invalid type [CAST(ST_POINT(TO_DOUBLE(37.5), TO_DOUBLE(45.5)) AS VARCHAR(16777216))] for parameter 'TO_VARCHAR'

The reason why I want to do this is that I'm using st_collect, which returns a multipoint object. I would like to convert a multipoint into a linestring, but apparently st_makeline only allows 2 points and I have many.

A simple fix is to replace the string, but it doesn't seem to be allowed.

Solution

Does ST_ASTEXT or ST_ASWKT meet your needs?

SELECT 
    st_point(37.5, 45.5) as x,
    SYSTEM$TYPEOF(x),
    ST_ASTEXT(x),
    ST_ASWKT(x),
    ST_ASEWKT(x);

X	SYSTEM$TYPEOF(X)	ST_ASTEXT(X)	ST_ASWKT(X)	ST_ASEWKT(X)
{"coordinates": [3.750000000000000e+01, 4.550000000000000e+01], "type": "Point"}	VARIANT[LOB]	POINT(37.5 45.5)	POINT(37.5 45.5)	SRID=4326;POINT(37.5 45.5)

A longer answer is that, when new data types are added, they are often added by Snowflake in a MVP way, thus they work in the core way they are needed/intented, and 100% every other way/type can be lagging or just never supported. At some level this can be frustrating, that one style of "doing things does not work for all things", but the inverse, is there are more useful things, if you can see how to use them as intended.

The reason above I asked for the "typeof" is "if it is VARIANT variant function should be happy, but GET is not happy, and shows:

SELECT st_point(37.5, 45.5) as x, SYSTEM$TYPEOF(x), get(x, 'type');

001044 (42P13): SQL compilation error: error line 1 at position 52

Invalid argument types for function 'GET': (GEOGRAPHY, VARCHAR(4))

so it says it's a variant, but know's it's a GROGRAPHY.. lets cast:

SELECT st_point(37.5, 45.5) as x, SYSTEM$TYPEOF(x), get(x::variant, 'type');

Invalid type [CAST(X AS VARIANT)] for parameter 'TO_VARIANT'

So here is a gap in the type support. If this is critical to your business use, I would open a support ticket, and given it most likey "not a bug" just a gap in support, it will not be change quickly, thus it makes the most sense in both cases, to find another way to achieve you goals.