Links

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'
);
Last modified 19d ago