xmetricFunctions

Tableau function Spatial

Tableau PARSE_WKT LineString to SQL

PARSE_WKT([Route]) turns text such as LINESTRING (106.7009 10.7769, 105.7469 10.0452) into a line through each vertex in order. Each vertex is longitude then latitude, and a line can have more than two. In SQL it is DuckDB ST_GeomFromText(Route), Snowflake TO_GEOGRAPHY(Route) or BigQuery ST_GEOGFROMTEXT(Route). The runnable SQL builds the same GeoJSON the engine returns with json_object, because this page's DuckDB has no spatial extension. With the extension loaded, the DuckDB call is shown in the FAQ.

Updated · Checked on the xmetric engine

What is the syntax?

PARSE_WKT(string)
string
LINESTRING Well-Known Text: vertices separated by commas, each written longitude then latitude.

Returns a spatial line read from LINESTRING Well-Known Text. Null when the string is null.

What does it return, and what is the SQL?

Each example runs on the same 4 sample rows twice: the Tableau formula on the xmetric engine, and the SQL in DuckDB.

What path does each route follow?

Tableau

PARSE_WKT([Route])

SQL (DuckDB)

SELECT
  CASE
    WHEN Route IS NULL THEN NULL
    ELSE json_object('type', 'LineString', 'coordinates', list_transform(string_split(regexp_extract(Route, '(?i)LINESTRING\s*\((.*)\)', 1), ','), p - > list_transform(regexp_split_to_array(trim(p), '\s+'), n - > CAST (n AS DOUBLE))))
    END AS result
FROM input
ORDER BY id

Da Nang's route has three vertices; the others have two. Row 2 has no spaces after the commas and still parses.

RouteResult
LINESTRING (106.7009 10.7769, 105.7469 10.0452)[object Object]
LINESTRING(105.8542 21.0285,106.6881 20.8449)[object Object]
LINESTRING (108.2022 16.0544, 108.0 16.2, 107.5909 16.4637)[object Object]
null

The Tableau formula and the SQL return the same 4 rows.

Where does a straight translation go wrong?

Common mistake

Why does a pattern with a space reject a valid line?

LINESTRING(x y,x y) is as valid as LINESTRING (x y, x y), and Tableau reads it. A check that requires the space after LINESTRING marks Hanoi's route as unreadable.

Tableau

ISNULL(PARSE_WKT([Route]))

Tempting SQL

SELECT
  Route IS NULL OR NOT regexp_matches(Route, '^LINESTRING \(') AS result
FROM input
ORDER BY id

SQL that matches

SELECT
  Route IS NULL OR NOT regexp_matches(Route, '(?i)^\s*LINESTRING\s*\(') AS result
FROM input
ORDER BY id
RouteTableauTempting SQLSQL that matches
LINESTRING (106.7009 10.7769, 105.7469 10.0452)falsefalsefalse
LINESTRING(105.8542 21.0285,106.6881 20.8449)falsetruefalse
LINESTRING (108.2022 16.0544, 108.0 16.2, 107.5909 16.4637)falsefalsefalse
truetruetrue

The SQL that matches returns the Tableau result on every row; the marked cells are where the tempting SQL differs.

How do I write it in Snowflake or BigQuery?

The first example, written for each warehouse from its documentation. These are not run here; DuckDB is.

Snowflake

SELECT TO_GEOGRAPHY(Route) AS result
FROM input
ORDER BY id

Snowflake documentation

BigQuery

SELECT ST_GEOGFROMTEXT(Route) AS result
FROM input
ORDER BY id

BigQuery documentation

What does your own PARSE_WKT formula return?

Edit the formula and run it on the 4 sample rows. Fields: [id], [Depot], [Lat], [Lon], [DestLat], [DestLon], [Wkt], [Route].

Runs in your browser on the xmetric engine. Nothing is uploaded.

Which functions go with it?

What do people ask about PARSE_WKT?

Is WKT latitude first?

No. WKT writes x then y, which is longitude then latitude: POINT (106.7009 10.7769) is Saigon. That is the opposite of MAKEPOINT's argument order.

Is a LINESTRING from WKT the same as MAKELINE?

For two vertices, yes. WKT can carry any number of vertices in order, while MAKELINE joins exactly two points.

Where is this documented?