atan2

Applies to VAST Cluster 5.5.

Functions › Numeric and math functions › Inverse trigonometry. Returns the angle, in radians from -pi to pi, between the positive x axis and the point (x, y). It takes the y coordinate first. Unlike atan(y / x), it tells the four quadrants apart and works when x is 0.

Syntax

atan2(y, x)

Returns: DOUBLE, from -pi to pi.

Set up the examples

The examples connect with the ADBC driver, as in VAST Query Engine (ADBC), and work in a schema called docs_sql_fn_numeric_atan2. Set QE_ENDPOINT, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY and VASTDB_BUCKET (SQL reference), and run the blocks in order in one Python session. Below each block is the output you should see.

This block connects, defines the run() and plan() helpers, and creates a table of seven points, one in each quadrant, one on an axis, one at the origin and one with no x coordinate:

import os
import re

import adbc_driver_manager
import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb

BUCKET = os.environ["VASTDB_BUCKET"]
SCHEMA = f'"{BUCKET}/docs_sql_fn_numeric_atan2"'

conn = dbapi.connect(
    driver=adbc_driver_vastdb.get_driver_path(),
    db_kwargs={
        "vast.db.endpoint": os.environ["QE_ENDPOINT"],
        "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"],
        "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"],
    },
    autocommit=True,
)

def server_message(e):
    """The server's message in a driver error, without the request ID or the echoed statement."""
    text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e))
    found = (re.search(r'message: "(.*?)", request_id', text, re.S)
             or re.search(r"description: (.*)\)", text, re.S))
    message = found.group(1) if found else text.strip().splitlines()[-1]
    message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S)
    message = re.split(r"\\n|\n", message)[0].replace('\\"', '"')
    return message.replace(BUCKET, "<bucket>")

def run(sql):
    """Run one statement. Print the rows it returns, or the error."""
    try:
        with conn.cursor() as cur:
            cur.execute(sql)
            rows = cur.fetchall() if cur.description else []
    except (adbc_driver_manager.Error, OSError) as e:
        print(f"{type(e).__name__}: {server_message(e)}")
    else:
        print(rows)

def plan(sql):
    """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert."""
    with conn.cursor() as cur:
        cur.execute("EXPLAIN " + sql)
        steps = cur.fetchall()[0][1]
    print("\n".join(line for line in steps.splitlines() if "Rows" not in line))

TABLE = f"{SCHEMA}.points"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, x DOUBLE, y DOUBLE)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 1.0, 1.0), (2, -1.0, 1.0), (3, -1.0, -1.0), (4, 1.0, -1.0), "
    "(5, 0.0, 2.0), (6, 0.0, 0.0), (7, NULL, 1.0)")

[]
[]
[(7,)]

What it does

atan2(y, x) is the angle, measured counterclockwise from the positive x axis, of the line from the origin to the point (x, y). It's positive above the x axis (y > 0), negative below it, and pi or -pi on the negative x axis. When x is positive it's the same as atan(y / x).

The result is a DOUBLE for every numeric argument type.

run("SELECT atan2(1, 1), atan2(1, -1), atan2(-1, -1), atan2(-1, 1)")
run("SELECT atan2(1, 0), atan2(-1, 0), atan2(0, -1)")
run("SELECT typeof(atan2(1::INTEGER, 1::INTEGER)), atan2(1, 1) = atan(1)")

[(0.7853981633974483, 2.356194490192345, -2.356194490192345, -0.7853981633974483)]
[(1.5707963267948966, -1.5707963267948966, 3.141592653589793)]
[('DOUBLE', True)]

NULLs and edge cases

A literal -0.0 is a DECIMAL, which has no negative zero; write -0.0::DOUBLE to get one.

The same cases, as the cluster returns them. They use the helpers from Set up the examples:

run("SELECT atan2(NULL, 1), atan2(1, NULL), atan2(0, 0)")
run("SELECT atan2(-0.0::DOUBLE, 0), atan2(0, -0.0::DOUBLE), atan2(-0.0::DOUBLE, -0.0::DOUBLE), atan2(-0.0::DOUBLE, -1)")
run("SELECT atan2('inf'::DOUBLE, 'inf'::DOUBLE), atan2(1, 'inf'::DOUBLE), atan2(1, '-inf'::DOUBLE)")
run("SELECT atan2('nan'::DOUBLE, 1), atan2(1, 'nan'::DOUBLE), atan2('12', 1)")
run("SELECT atan2('abc', 1)")
run("SELECT atan2(DATE '2026-10-16', 1)")

[(None, None, 0.0)]
[(-0.0, 3.141592653589793, -3.141592653589793, -3.141592653589793)]
[(0.7853981633974483, 0.0, 3.141592653589793)]
[(nan, nan, 1.4876550949064553)]
OSError: Could not convert string 'abc' to DOUBLE
InternalError: Binder Error: No function matches the given name and argument types 'atan2(DATE, INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

The angle of each point, in radians and in degrees. Note the order of the arguments: y first, then x:

run(f"SELECT id, x, y, atan2(y, x), degrees(atan2(y, x)) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 1.0, 1.0, 0.7853981633974483, 45.0), (2, -1.0, 1.0, 2.356194490192345, 135.0), (3, -1.0, -1.0, -2.356194490192345, -135.0), (4, 1.0, -1.0, -0.7853981633974483, -45.0), (5, 0.0, 2.0, 1.5707963267948966, 90.0), (6, 0.0, 0.0, 0.0, 0.0), (7, None, 1.0, None, None)]

Keep only the points more than 90 degrees counterclockwise from the positive x axis:

run(f"SELECT id, x, y FROM {TABLE} WHERE atan2(y, x) > pi() / 2 ORDER BY id LIMIT 10")

[(2, -1.0, 1.0)]

How it runs in VAST

The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (VAST_SCAN). VAST DataBase can evaluate a comparison of a column with a constant, but not a function of a column, so atan2() is always computed by the Query Engine:

• In the SELECT list, the scan reads both columns and the Query Engine computes atan2() for each row it returns.
• In WHERE, the scan sends both columns for every row, and a FILTER step in the Query Engine computes atan2() and keeps the rows that match. No rows are skipped in storage.

The plan of the WHERE example shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE atan2(y, x) > pi() / 2")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│      (atan2(y, x) > 1     │
│     .5707963267948966)    │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             y             │
│             x             │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Select a quadrant with the signs of the coordinates. An angle strictly between 0 and pi/2 is the same as x > 0 AND y > 0, and each of those is a comparison of a column with a constant, which is pushed down to VAST DataBase:

plan(f"SELECT id FROM {TABLE} WHERE x > 0 AND y > 0")
run(f"SELECT id, x, y FROM {TABLE} WHERE x > 0 AND y > 0 ORDER BY id LIMIT 10")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
[(1, 1.0, 1.0)]

• Store the angle if you filter on it often. Compute it when you write the rows and keep it in its own column: a condition on that column is pushed down, a condition on atan2() isn't.

Limitations and errors

• Two arguments, y first. atan2(1) fails with No function matches the given name and argument types 'atan2(INTEGER_LITERAL)'; the error lists the one form atan2 has, atan2(DOUBLE, DOUBLE) -> DOUBLE. Swapping the arguments gives a different angle and raises no error.
• Numbers only. A BLOB, a date, a boolean, a list or an interval fails with No function matches the given name and argument types. A string literal is converted to DOUBLE if it holds a number, and otherwise fails with Could not convert string 'abc' to DOUBLE.
• No degrees variant. atan2d fails with Scalar Function with name atan2d does not exist!. Use degrees(atan2(y, x)):

run("SELECT atan2d(1, 1)")
run("SELECT degrees(atan2(1, 1))")

InternalError: Catalog Error: Scalar Function with name atan2d does not exist!
[(45.0,)]

Similar functions

run("SELECT atan2(-1, -1), atan(-1 / -1), degrees(atan2(-1, -1))")

[(-2.356194490192345, 0.7853981633974483, -135.0)]

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]

See also

• Numeric and math functions: all the numeric functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.