statistics

ADVANCED

This module contains functions to perform spatial statistics calculations.

P_VALUE

P_VALUE(z_score)

Description

This function computes the one tail p-value (upper-tail test) of a given z-score assuming the population follows a normal distribution where the mean is 0 and the standard deviation is 1. The z-score is a measure of how many standard deviations below or above the population mean a value is. It gives you an idea of how far from the mean a data point is. The p-value is the probability that a randomly sampled point has a value at least as extreme as the point whose z-score is being tested.

• z_score: FLOAT

Return type

FLOAT64

Example

SELECT CARTO.CARTO.P_VALUE(u) as p_value
    FROM LATERAL FLATTEN(input => ARRAY_CONSTRUCT(-2,-1,0,1,2)) AS u;

-- [ 0.9772499371127437, 0.8413447361676363, 0.49999999949999996, 0.15865526383236372, 0.02275006288725634]

GETIS_ORD_H3

GETIS_ORD_H3(input, size, kernel)

Description

This function computes the Getis-Ord Gi* statistic for each H3 index in the input array.

• input: ARRAY<STRUCT<index STRING, value FLOAT64>> input data with the indexes and values of the cells.

• size: INT64 size of the H3 kring (distance from the origin). This defines the area around each index cell that will be taken into account to compute its Gi* statistic.

• kernel: STRING kernel function to compute the spatial weights across the kring. Available functions are: uniform, triangular, quadratic, quartic and gaussian.

Return type

ARRAY<STRUCT<index STRING, gi FLOAT, p_value FLOAT>>

Example

SELECT CARTO.CARTO.GETIS_ORD_H3(
   ARRAY_CONSTRUCT(
        OBJECT_CONSTRUCT('index', '89394460323ffff', 'value', 51.0),
        OBJECT_CONSTRUCT('index', '89394460c37ffff', 'value', 28.0),
        OBJECT_CONSTRUCT('index', '89394460077ffff', 'value', 19.0)
   ),
    3, 'gaussian'
);
-- {"index": "89394460323ffff", "gi": 1.3606194139870573, "p_value": 0.13329689888387608}
-- {"index": "89394460c37ffff", "gi": -0.34633948719670526, "p_value": 0.6113291103317855}
-- {"index": "89394460077ffff", "gi": -1.0142799267903515, "p_value": 0.7962089998559484 }

GETIS_ORD_QUADBIN

GETIS_ORD_QUADBIN(input, size, kernel)

Description

This function computes the Getis-Ord Gi* statistic for each Quadbin index in the input array.

• input: ARRAY<STRUCT<index STRING, value FLOAT64>> input data with the indexes and values of the cells.

• size: INT64 size of the Quadbin k-ring (distance from the origin). This defines the area around each index cell that will be taken into account to compute its Gi* statistic.

• kernel: STRING kernel function to compute the spatial weights across the kring. Available functions are: uniform, triangular, quadratic, quartic and gaussian.

Return type

ARRAY<STRUCT<index STRING, gi FLOAT, p_value FLOAT>>

Example

SELECT CARTO.CARTO.GETIS_ORD_QUADBIN(
    ARRAY_CONSTRUCT(
        OBJECT_CONSTRUCT('index', 5266443791933898751, 'value', 51.0),
        OBJECT_CONSTRUCT('index', 5266443803500740607, 'value', 28.0),
        OBJECT_CONSTRUCT('index', 5266443790415822847, 'value', 19.0)
    ),
    3, 'gaussian'
);
-- {"index": 5266443791933898751, "gi": 1.360619413987058, "p_value": 0.086817058065399522}
-- {"index": 5266443803500740607, "gi": -0.3463394871967051, "p_value": 0.63545613599515272}
-- {"index": 5266443790415822847, "gi": -1.0142799267903515, "p_value": 0.84477538488255133}

MORANS_I_H3

MORANS_I_H3(input, size, decay)

Description

This function computes the Moran's I spatial autocorrelation from the input array of H3 indexes.

• input: ARRAY input data with the indexes and values of the cells.

• size: INT size of the H3 kring (distance from the origin). This defines the area around each index cell where the distance decay will be applied.

• decay: STRING decay function to compute the distance decay. Available functions are: uniform, inverse, inverse_square and exponential.

Return type

FLOAT

Example

SELECT CARTO.CARTO.MORANS_I_H3(
    ARRAY_CONSTRUCT(
        OBJECT_CONSTRUCT('index', '89394460323ffff', 'value', 51.0),
        OBJECT_CONSTRUCT('index', '89394460c37ffff', 'value', 28.0),
        OBJECT_CONSTRUCT('index', '89394460077ffff', 'value', 19.0)
    ),
    3, 'exponential'
);
-- 0.07219909881624618

MORANS_I_QUADBIN

MORANS_I_QUADBIN(input, size, decay)

Description

This function computes the Moran's I spatial autocorrelation from the input array of Quadbin indexes.

• input: ARRAY input data with the indexes and values of the cells.

• size: INT size of the Quadbin k-ring (distance from the origin). This defines the area around each index cell where the distance decay will be applied.

• decay: STRING decay function to compute the distance decay. Available functions are: uniform, inverse, inverse_square and exponential.

Return type

FLOAT

Example

SELECT CARTO.CARTO.MORANS_I_QUADBIN(
    ARRAY_CONSTRUCT(
        OBJECT_CONSTRUCT('index', 5266443791927869439, 'value', 51.0),
        OBJECT_CONSTRUCT('index', 5266443791928131583, 'value', 28.0),
        OBJECT_CONSTRUCT('index', 5266443791928918015, 'value', 19.0)
    ),
    3, 'exponential'
);
-- -0.29665713826808621