statistics
ADVANCED
This module contains functions to perform spatial statistics calculations.
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(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(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(input, output_table, index_col, value_col, size, decay)
Description
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(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