geometry data type of snowflake

Question

I am trying to use the geometry data type and was wondering what the unit of the spatial functions will be. The documentation says as below. Is there a way I can pass a unit to the function. If not, how do i find out the unit

The measurement functions (e.g. ST_LENGTH) use the same units as the coordinate system

Thanks

Answer 1

I was surprised by the results, and the docs could be clearer.

With a geography, st_length() returns the distance in meters, as described in the docs. For example, for a line between SF and a point 10km to the east:

select st_length(
  to_geography('LINESTRING(-122.4194 37.7749,-122.3094 37.7749 )')
);
-- 9,668.032993573

However, that's not what you get when you have a geometry between the same points:

select st_length(
  to_geometry('LINESTRING(-122.4194 37.7749,-122.3094 37.7749 )')
);
-- 0.11

So where does the 0.11 come from? Vertica says:

"For GEOMETRY objects, the length is measured in Cartesian coordinate units. For GEOGRAPHY objects, the length is measured in meters."

Same on PostGIS:

For geometry types: returns the 2D Cartesian length of the geometry [...]

For geography types: [...] Units of length are in meters. [...]

https://postgis.net/docs/ST_Length.html

I'll notify our docs team so we make the necessary update on https://docs.snowflake.com/en/sql-reference/functions/st_length.html#returns.