statistics

ADVANCED
This module contains functions to perform spatial statistics calculations.
P_VALUE
P_VALUE(z_score)
Description
This function computes the p-value (two-tails 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
FLOAT
Example
SELECT CARTO.CARTO.P_VALUE(value) as p_value
FROM LATERAL FLATTEN(input => ARRAY_CONSTRUCT(-2,-1,0,1,2));
-- 0.04550012577451279,
-- 0.31731052766472745,
-- 0.999999999,
-- 0.31731052766472745,
-- 0.04550012577451279
GETIS_ORD_H3
GETIS_ORD_H3(input, output_table, index_col, value_col, size, kernel)
Description
This procedure computes the Getis-Ord Gi* statistic for each row in the input table.
input: STRING the query to the data used to compute the coefficient. A qualified table name can be given as well: <project-id>.<dataset-id>.<table-name>.
output_table: STRING qualified name of the output table: <project-id>.<dataset-id>.<table-name>.
index_col: STRING name of the column with the H3 indexes.
value_col: STRING name of the column with the values for each H3 cell.
size: INT 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.
Output
The results are stored in the table named <output_table>, which contains the following columns:
index: STRING
gi: FLOAT computed Gi* value.
p_value: FLOAT computed P value.
Example
CALL CARTO.CARTO.GETIS_ORD_H3(
    'myproject.mydataset.h3table',
    'myproject.mydataset.outputtable',
    'h3',
    'value'
    3,
    'gaussian'
);
GETIS_ORD_QUADBIN
GETIS_ORD_QUADBIN(input, output_table, index_col, value_col, size, kernel)
Description
This procedure computes the Getis-Ord Gi* statistic for each row in the input table.
input: STRING the query to the data used to compute the coefficient. A qualified table name can be given as well: <project-id>.<dataset-id>.<table-name>.
output_table: STRING qualified name of the output table: <project-id>.<dataset-id>.<table-name>.
index_col: STRING name of the column with the Quadbin indexes.
value_col: STRING name of the column with the values for each Quadbin cell.
size: INT size of the Quadbin 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.
Output
The results are stored in the table named <output_table>, which contains the following columns:
index: BIGINT
gi: FLOAT computed Gi* value.
p_value: FLOAT computed P value.
Example
CALL CARTO.CARTO.GETIS_ORD_QUADBIN(
    'myproject.mydataset.quadbintable',
    'myproject.mydataset.outputtable',
    'quadbin',
    'value'
    3,
    'gaussian'
);
MORANS_I_H3
MORANS_I_H3(input, output_table, index_col, value_col, size, decay)
Description
This procedure computes the Moran's I spatial autocorrelation from the input table with H3 indexes.
input: STRING the query to the data used to compute the coefficient. A qualified table name can be given as well: <project-id>.<dataset-id>.<table-name>.
output_table: STRING qualified name of the output table: <project-id>.<dataset-id>.<table-name>.
index_col: STRING name of the column with the H3 indexes.
value_col: STRING name of the column with the values for each H3 cell.
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.
Output
The results are stored in the table named <output_table>, which contains the following column:
morans_i: FLOAT Moran's I spatial autocorrelation.
Example
CALL CARTO.CARTO.MORANS_I_H3(
    'myproject.mydataset.h3table',
    'myproject.mydataset.outputtable',
    'h3',
    'value'
    5,
    'uniform'
);
MORANS_I_QUADBIN
MORANS_I_QUADBIN(input, output_table, index_col, value_col, size, decay)
Description
This procedure computes the Moran's I spatial autocorrelation from the input table with Quadbin indexes.
input: STRING the query to the data used to compute the coefficient. A qualified table name can be given as well: <project-id>.<dataset-id>.<table-name>.
output_table: STRING qualified name of the output table: <project-id>.<dataset-id>.<table-name>.
index_col: STRING name of the column with the Quadbin indexes.
value_col: STRING name of the column with the values for each Quadbin cell.
size: INT size of the Quadbin 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.
Output
The results are stored in the table named <output_table>, which contains the following column:
morans_i: FLOAT Moran's I spatial autocorrelation.
Example
CALL CARTO.CARTO.MORANS_I_QUADBIN(
    'myproject.mydataset.quadbintable',
    'myproject.mydataset.outputtable',
    'quadbin',
    'value'
    5,
    'uniform'
);
tiler
Last modified 19d ago