CODEWITHSUNDEEP
snowflake-cloud-data-platform
John
John

Reputation: 515

Add Snowflake calculated geography column

I've got a block of data with lat/longs and I'm trying to add a point to a Snowflake table from this data.

First I tried to accomplish it when I created the table with:

create or replace table geo (
 best_lat double,
 best_lon double,
 geography geography as (ST_POINT(best_lon, best_lat)));

This errored out with SQL compilation error: error line 4 at position 2 Data type of virtual column does not match the data type of its expression for column 'GEOGRAPHY'. Expected data type 'GEOGRAPHY', found 'VARIANT'

Then I tried to add the column with:

alter table geo
add column geom geography as (select st_makepoint(best_lon, best_lat) from geo)

This errored out with SQL compilation error: Invalid virtual column expression [(SELECT ST_MAKEPOINT(GEO.BEST_LON, GEO.BEST_LAT) AS "ST_MAKEPOINT(BEST_LON, BEST_LAT)" FROM GEO AS GEO)]

Clearly I'm doing something wrong here. Can anyone provide some insight?

Upvotes: 2

Views: 220

Answers (1)

NickW
NickW

Reputation: 9798

Snowflake doesn’t really support calculated columns, what it does do is allow you to set a default value for a column and this default can be a simple SQL statement. The syntax is documented here

Because this isn’t a pure calculated column, you can still insert values directly into the column, which will override the defined default value.

Upvotes: 0

Related Questions