statistics
ADVANCED
This module contains functions to perform spatial statistics calculations.
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 FLOAT64, p_value FLOAT64>>
Example
carto-un
carto-un-eu
manual
SELECT `carto-un`.carto.GETIS_ORD_H3(
[
STRUCT('89394460323ffff', 51.0),
STRUCT('89394460c37ffff', 28.0),
STRUCT('89394460077ffff', 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 }
SELECT `carto-un-eu`.carto.GETIS_ORD_H3(
[
STRUCT('89394460323ffff', 51.0),
STRUCT('89394460c37ffff', 28.0),
STRUCT('89394460077ffff', 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 }
SELECT carto.GETIS_ORD_H3(
[
STRUCT('89394460323ffff', 51.0),
STRUCT('89394460c37ffff', 28.0),
STRUCT('89394460077ffff', 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 }
carto-un
carto-un-eu
manual
SELECT `carto-un`.carto.GETIS_ORD_H3(input_data, 3, 'gaussian')
FROM (
SELECT ARRAY_AGG(STRUCT(index, value)) AS input_data
FROM mytable
)
SELECT `carto-un-eu`.carto.GETIS_ORD_H3(input_data, 3, 'gaussian')
FROM (
SELECT ARRAY_AGG(STRUCT(index, value)) AS input_data
FROM mytable
)
SELECT carto.GETIS_ORD_H3(input_data, 3, 'gaussian')
FROM (
SELECT ARRAY_AGG(STRUCT(index, value)) AS input_data
FROM mytable
)
Additional examples
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 FLOAT64, p_value FLOAT64>>
Example
carto-un
carto-un-eu
manual
SELECT `carto-un`.carto.GETIS_ORD_QUADBIN(
[
STRUCT(5266443791933898751, 51.0),
STRUCT(5266443803500740607, 28.0),
STRUCT(5266443790415822847, 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}
SELECT `carto-un-eu`.carto.GETIS_ORD_QUADBIN(
[
STRUCT(5266443791933898751, 51.0),
STRUCT(5266443803500740607, 28.0),
STRUCT(5266443790415822847, 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}
SELECT carto.GETIS_ORD_QUADBIN(
[
STRUCT(5266443791933898751, 51.0),
STRUCT(5266443803500740607, 28.0),
STRUCT(5266443790415822847, 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}
carto-un
carto-un-eu
manual
SELECT `carto-un`.carto.GETIS_ORD_QUADBIN(input_data, 3, 'gaussian')
FROM (
SELECT ARRAY_AGG(STRUCT(index, value)) AS input_data
FROM mytable
)
SELECT `carto-un-eu`.carto.GETIS_ORD_QUADBIN(input_data, 3, 'gaussian')
FROM (
SELECT ARRAY_AGG(STRUCT(index, value)) AS input_data
FROM mytable
)
SELECT carto.GETIS_ORD_QUADBIN(input_data, 3, 'gaussian')
FROM (
SELECT ARRAY_AGG(STRUCT(index, value)) AS input_data
FROM mytable
)
GFUN(points)
Description
points
:ARRAY<GEOGRAPHY>
input data points.
Return type
ARRAY<STRUCT<distance FLOAT64, gfun_G FLOAT64, gfun_ev FLOAT64>>
where:
distance
: the nearest neighbors distances.gfun_G
: the empirical G evaluated for each distance in the support.gfun_ev
: the theoretical Poisson G evaluated for each distance in the support.
Example
carto-un
carto-un-eu
manual
SELECT *
FROM UNNEST((
SELECT `carto-un`.carto.GFUN(myarray)
FROM (
SELECT ARRAY_AGG(position_geom) myarray
FROM `bigquery-public-data.catalonian_mobile_coverage.mobile_data_2015_2017`
WHERE date = '2017-12-31'
)
))
ORDER BY distance
--{
-- "distance": "38.599968853183",
-- "gfun_G": "0.4319167389418907",
-- "gfun_ev": "4.037383876246414E-4"
--},
--{
-- "distance": "77.199937706366",
-- "gfun_G": "0.5771899392888118",
-- "gfun_ev": "0.0016139757856029613"
--},
--{
-- "distance": "115.799906559549",
-- "gfun_G": "0.6522116218560278",
-- "gfun_ev": "0.003627782844736638"
--},
-- ...
SELECT *
FROM UNNEST((
SELECT `carto-un-eu`.carto.GFUN(myarray)
FROM (
SELECT ARRAY_AGG(position_geom) myarray
FROM `bigquery-public-data.catalonian_mobile_coverage.mobile_data_2015_2017`
WHERE date = '2017-12-31'
)
))
ORDER BY distance
--{
-- "distance": "38.599968853183",
-- "gfun_G": "0.4319167389418907",
-- "gfun_ev": "4.037383876246414E-4"
--},
--{
-- "distance": "77.199937706366",
-- "gfun_G": "0.5771899392888118",
-- "gfun_ev": "0.0016139757856029613"
--},
--{
-- "distance": "115.799906559549",
-- "gfun_G": "0.6522116218560278",
-- "gfun_ev": "0.003627782844736638"
--},
-- ...
SELECT *
FROM UNNEST((
SELECT carto.GFUN(myarray)
FROM (
SELECT ARRAY_AGG(position_geom) myarray
FROM `bigquery-public-data.catalonian_mobile_coverage.mobile_data_2015_2017`
WHERE date = '2017-12-31'
)
))
ORDER BY distance
--{
-- "distance": "38.599968853183",
-- "gfun_G": "0.4319167389418907",
-- "gfun_ev": "4.037383876246414E-4"
--},
--{
-- "distance": "77.199937706366",
-- "gfun_G": "0.5771899392888118",
-- "gfun_ev": "0.0016139757856029613"
--},
--{
-- "distance": "115.799906559549",
-- "gfun_G": "0.6522116218560278",
-- "gfun_ev": "0.003627782844736638"
--},
-- ...
GWR_GRID(input_table, features_columns, label_column, cell_column, cell_type, kring_distance, kernel_function, fit_intercept, output_table)
Description
Geographically weighted regression (GWR) models local relationships between spatially varying predictors and an outcome of interest using a local least squares regression.
This procedures performs a local least squares regression for every input cell. In each regression, the data of each cell and that of the neighboring cells, defined by the
kring_distance
parameter, will be taken into account. The data of the neighboring cells will be assigned a lower weight the further they are from the origin cell, following the function specified in the kernel_function
.input_table
:STRING
name of the source dataset. It should be a quoted qualified table with project and dataset:<project-id>.<dataset-id>.<table-name>
.features_columns
:ARRAY<STRING>
array of column names frominput_table
to be used as features in the GWR.label_column
:STRING
name of the target variable column.cell_column
:STRING
name of the column containing the cell ids.cell_type
:STRING
spatial index type as 'h3', 'quadbin'.kring_distance
:INT64
distance of the neighboring cells whose data will be included in the local regression of each cell.kernel_function
:STRING
kernel function to compute the spatial weights across the kring. Available functions are: 'uniform', 'triangular', 'quadratic', 'quartic' and 'gaussian'.fit_intercept
:BOOL
whether to calculate the interception of the model or to force it to zero if, for example, the input data is already supposed to be centered. If NULL,fit_intercept
will be considered asTRUE
.output_table
:STRING
name of the output table. It should be a quoted qualified table with project and dataset:<project-id>.<dataset-id>.<table-name>
. The process will fail if the target table already exists. If NULL, the result will be returned directly by the query and not persisted.
Output
The output table will contain a column with the cell id, a column for each feature column containing its corresponding coefficient estimate and one extra column for intercept if
fit_intercept
is TRUE
.Examples
carto-un
carto-un-eu
manual
CALL `carto-un`.carto.GWR_GRID(
'cartobq.docs.airbnb_berlin_h3_qk_qb',
['bedrooms', 'bathrooms'], -- [ beds feature, bathrooms feature ]
'price', -- price (target variable)
'h3_z6', 'h3', 3, 'gaussian', TRUE,
'<project-id>.<dataset-id>.<table-name>'
);
CALL `carto-un-eu`.carto.GWR_GRID(
'cartobq.docs.airbnb_berlin_h3_qk_qb',
['bedrooms', 'bathrooms'], -- [ beds feature, bathrooms feature ]
'price', -- price (target variable)
'h3_z6', 'h3', 3, 'gaussian', TRUE,
'<project-id>.<dataset-id>.<table-name>'
);
CALL carto.GWR_GRID(
'cartobq.docs.airbnb_berlin_h3_qk_qb',
['bedrooms', 'bathrooms'], -- [ beds feature, bathrooms feature ]
'price', -- price (target variable)
'h3_z6', 'h3', 3, 'gaussian', TRUE,
'<project-id>.<dataset-id>.<table-name>'
);
carto-un
carto-un-eu
manual
CALL `carto-un`.carto.GWR_GRID(
'cartobq.docs.airbnb_berlin_h3_qk_qb',
['bedrooms', 'bathrooms'], -- [ beds feature, bathrooms feature ]
'price', -- price (target variable)
'qb_z12', 'quadbin', 3, 'gaussian', TRUE,
'<project-id>.<dataset-id>.<table-name>'
);
CALL `carto-un-eu`.carto.GWR_GRID(
'cartobq.docs.airbnb_berlin_h3_qk_qb',
['bedrooms', 'bathrooms'], -- [ beds feature, bathrooms feature ]
'price', -- price (target variable)
'qb_z12', 'quadbin', 3, 'gaussian', TRUE,
'<project-id>.<dataset-id>.<table-name>'
);
CALL carto.GWR_GRID(
'cartobq.docs.airbnb_berlin_h3_qk_qb',
['bedrooms', 'bathrooms'], -- [ beds feature, bathrooms feature ]
'price', -- price (target variable)
'qb_z12', 'quadbin', 3, 'gaussian', TRUE,
'<project-id>.<dataset-id>.<table-name>'
);
Additional examples
IDW(inputsample, origin, maxdistance, n_neighbors, p)
Description
This function performs Inverse Distance Weighted interpolation. More information on the method can be found here. The method uses the values of the input samples to interpolate the values for the derived locations. The user can select the number of neighbors to be selected for the interpolation, the maximum distance between points and neighbors and the factor
p
for the weights.inputsample
:ARRAY<STRUCT<point GEOGRAPHY, value FLOAT64>>
Input array with the sample points and their values.origin
:ARRAY<GEOGRAPHY>
Input array with the points whose values will be interpolated.maxdistance
:FLOAT64
Maximum distance between point for interpolation and sampling points.n_neighbors
:INT64
Maximum number of sampling points to be considered for the interpolation.p
:FLOAT64
Power of distance.
Return type
ARRAY<STRUCT<point GEOGRAPHY, value FLOAT64>>
Example
carto-un
carto-un-eu
manual
SELECT
point, value
FROM
UNNEST(`carto-un`.carto.IDW(
ARRAY(SELECT AS STRUCT point, value FROM `cartobq.docs.kriging_sample_points`),
ARRAY(SELECT point FROM `cartobq.docs.kriging_interp_points`),
1.0E5,
20,
2)) WITH OFFSET pos
ORDER BY pos
-- TODO
SELECT
point, value
FROM
UNNEST(`carto-un-eu`.carto.IDW(
ARRAY(SELECT AS STRUCT point, value FROM `cartobq.docs.kriging_sample_points`),
ARRAY(SELECT point FROM `cartobq.docs.kriging_interp_points`),
1.0E5,
20,
2)) WITH OFFSET pos
ORDER BY pos
-- TODO
SELECT
point, value
FROM
UNNEST(carto.IDW(
ARRAY(SELECT AS STRUCT point, value FROM `cartobq.docs.kriging_sample_points`),
ARRAY(SELECT point FROM `cartobq.docs.kriging_interp_points`),
1.0E5,
20,
2)) WITH OFFSET pos
ORDER BY pos
-- TODO
KNN(points, k)
Description
points
:ARRAY<STRUCT<geoid STRING, geo GEOGRAPHY>>
input data with unique id and geography.k
:INT64
number of nearest neighbors (positive, typically small).
Return type
ARRAY<STRUCT<geo GEOGRAPHY, geo_knn GEOGRAPHY, geoid STRING, geoid_knn STRING, distance FLOAT64, knn INT64>>
where:
geo
: the geometry of the considered point.geo_knn
: the k-nearest neighbor point.geoid
: the unique identifier of the considered point.geoid_knn
: the unique identifier of the k-nearest neighbor.distance
: the k-nearest neighbor distance to the considered point.knn
: the k-order (knn)
Example
carto-un
carto-un-eu
manual
SELECT *
FROM UNNEST((
SELECT `carto-un`.carto.KNN(myarray, 10)
FROM (
SELECT ARRAY_AGG(STRUCT(format('%08x', uid),position_geom)) myarray
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY hour) AS uid, position_geom
FROM `bigquery-public-data.catalonian_mobile_coverage.mobile_data_2015_2017`
WHERE date = '2017-12-31'
)
)
))
ORDER BY geoid
--{
-- "geo": "POINT(2.82263 41.97118)",
-- "geo_knn": "POINT(2.8225 41.97117)",
-- "geoid": "00000001",
-- "geoid_knn": "00000624",
-- "distance": "10.804663098937658",
-- "knn": "1"
--},
--{
-- "geo": "POINT(2.82263 41.97118)",
-- "geo_knn": "POINT(2.823 41.9712)",
-- "geoid": "00000001",
-- "geoid_knn": "00000666",
-- "distance": "30.66917920746894",
-- "knn": "2"
--},
--{
-- "geo": "POINT(2.82263 41.97118)",
-- "geo_knn": "POINT(2.82298 41.9713)",
-- "geoid": "00000001",
-- "geoid_knn": "00000618",
-- "distance": "31.863463704968353",
-- "knn": "3"
--},
-- ...
SELECT *
FROM UNNEST((
SELECT `carto-un-eu`.carto.KNN(myarray, 10)
FROM (
SELECT ARRAY_AGG(STRUCT(format('%08x', uid),position_geom)) myarray
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY hour) AS uid, position_geom
FROM `bigquery-public-data.catalonian_mobile_coverage.mobile_data_2015_2017`
WHERE date = '2017-12-31'
)
)
))
ORDER BY geoid
--{
-- "geo": "POINT(2.82263 41.97118)",
-- "geo_knn": "POINT(2.8225 41.97117)",
-- "geoid": "00000001",
-- "geoid_knn": "00000624",
-- "distance": "10.804663098937658",
-- "knn": "1"
--},
--{
-- "geo": "POINT(2.82263 41.97118)",
-- "geo_knn": "POINT(2.823 41.9712)",
-- "geoid": "00000001",
-- "geoid_knn": "00000666",
-- "distance": "30.66917920746894",
-- "knn": "2"
--},
--{
-- "geo": "POINT(2.82263 41.97118)",
-- "geo_knn": "POINT(2.82298 41.9713)",
-- "geoid": "00000001",
-- "geoid_knn": "00000618",
-- "distance": "31.863463704968353",
-- "knn": "3"
--},
-- ...
SELECT *
FROM UNNEST((
SELECT carto.KNN(myarray, 10)
FROM (
SELECT ARRAY_AGG(STRUCT(format('%08x', uid),position_geom)) myarray
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY hour) AS uid, position_geom
FROM `bigquery-public-data.catalonian_mobile_coverage.mobile_data_2015_2017`
WHERE date = '2017-12-31'
)
)
))
ORDER BY geoid
--{
-- "geo": "POINT(2.82263 41.97118)",
-- "geo_knn": "POINT(2.8225 41.97117)",
-- "geoid": "00000001",
-- "geoid_knn": "00000624",
-- "distance": "10.804663098937658",
-- "knn": "1"
--},
--{
-- "geo": "POINT(2.82263 41.97118)",
-- "geo_knn": "POINT(2.823 41.9712)",
-- "geoid": "00000001",
-- "geoid_knn": "00000666",
-- "distance": "30.66917920746894",
-- "knn": "2"
--},
--{
-- "geo": "POINT(2.82263 41.97118)",
-- "geo_knn": "POINT(2.82298 41.9713)",
-- "geoid": "00000001",
-- "geoid_knn": "00000618",
-- "distance": "31.863463704968353",
-- "knn": "3"
--},
-- ...
LOCAL_MORANS_I_H3(input, size, decay, permutations)
Description
This function computes the local Moran's I spatial autocorrelation from the input array of H3 indexes. It outputs the H3
index
, local Moran's I spatial autocorrelation value
, simulated p value psim
, Conditional randomization null - expectation EIc
, Conditional randomization null - variance VIc
, Total randomization null - expectation EI
, Total randomization null - variance VI
, and the quad HH=1, LL=2, LH=3, HL=4.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 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.permutations
:INT64
number of permutations for the estimation of p-value.
Return type
ARRAY<STRUCT<index STRING, value FLOAT64, psim FLOAT64, EIc FLOAT64, VIc FLOAT64, EI FLOAT64, VI FLOAT64, quad INT64>>
Example
carto-un
carto-un-eu
manual
SELECT `carto-un`.carto.LOCAL_MORANS_I_H3(
[
STRUCT('89394460323ffff', 51.0),
STRUCT('8939446033bffff', 28.0),
STRUCT('8939446032bffff', 19.0)
],
3, 'exponential', 100
);
-- {
-- "index": "8939446032bffff",
-- "value": "-0.342921256629947",
-- "psim": "0.0099009900990099011",
-- "EIc": "-1.0287637698898404",
-- "VIc": "-1.0",
-- "EI": "0.0",
-- "VI": "-0.64721503525401447",
-- "quad": "3"
-- },
-- ...
SELECT `carto-un-eu`.carto.LOCAL_MORANS_I_H3(
[
STRUCT('89394460323ffff', 51.0),
STRUCT('8939446033bffff', 28.0),
STRUCT('8939446032bffff', 19.0)
],
3, 'exponential', 100
);
-- {
-- "index": "8939446032bffff",
-- "value": "-0.342921256629947",
-- "psim": "0.0099009900990099011",
-- "EIc": "-1.0287637698898404",
-- "VIc": "-1.0",
-- "EI": "0.0",
-- "VI": "-0.64721503525401447",
-- "quad": "3"
-- },
-- ...
SELECT carto.LOCAL_MORANS_I_H3(
[
STRUCT('89394460323ffff', 51.0),
STRUCT('8939446033bffff', 28.0),
STRUCT('8939446032bffff', 19.0)
],
3, 'exponential', 100
);
-- {
-- "index": "8939446032bffff",
-- "value": "-0.342921256629947",
-- "psim": "0.0099009900990099011",
-- "EIc": "-1.0287637698898404",
-- "VIc": "-1.0",
-- "EI": "0.0",
-- "VI": "-0.64721503525401447",
-- "quad": "3"
-- },
-- ...
carto-un
carto-un-eu
manual
SELECT `carto-un`.carto.LOCAL_MORANS_I_H3(
ARRAY(SELECT AS STRUCT index, value FROM mytable),
3, 'exponential', 100
)
SELECT `carto-un-eu`.carto.LOCAL_MORANS_I_H3(
ARRAY(SELECT AS STRUCT index, value FROM mytable),
3, 'exponential', 100
)
SELECT carto.LOCAL_MORANS_I_H3(
ARRAY(SELECT AS STRUCT index, value FROM mytable),
3, 'exponential', 100
)
LOCAL_MORANS_I_QUADBIN(input, size, decay)
Description
This function computes the local Moran's I spatial autocorrelation from the input array of Quadbin indexes. It outputs the Quadbin
index
, local Moran's I spatial autocorrelation value
, simulated p value psim
, Conditional randomization null - expectation EIc
, Conditional randomization null - variance VIc
, Total randomization null - expectation EI
, Total randomization null - variance VI
, and the quad HH=1, LL=2, LH=3, HL=4.input
:ARRAY<STRUCT<index INT64, 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 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.permutations
:INT64
number of permutations for the estimation of p-value.
Return type
ARRAY<STRUCT<index INT64, value FLOAT64, psim FLOAT64, EIc FLOAT64, VIc FLOAT64, EI FLOAT64, VI FLOAT64, quad INT64>>
Example
carto-un
carto-un-eu
manual
SELECT `carto-un`.carto.LOCAL_MORANS_I_QUADBIN(
[
STRUCT(5266443791927869439, 51.0),
STRUCT(5266443791928131583, 28.0),
STRUCT(5266443791928918015, 19.0)
],
3, 'exponential', 100
);
-- {
-- "index": "5266443791928918015",
-- "value": "-0.076228184845253524",
-- "psim": "0.0099009900990099011",
-- "EIc": "-0.70361240532717062",
-- "VIc": "-0.68393972058572117",
-- "EI": "0.29943435718277039",
-- "VI": "0.19089112237884748",
-- "quad": "3"
-- },
-- ...
SELECT `carto-un-eu`.carto.LOCAL_MORANS_I_QUADBIN(
[
STRUCT(5266443791927869439, 51.0),
STRUCT(5266443791928131583, 28.0),
STRUCT(5266443791928918015, 19.0)
],
3, 'exponential', 100
);
-- {
-- "index": "5266443791928918015",
-- "value": "-0.076228184845253524",
-- "psim": "0.0099009900990099011",
-- "EIc": "-0.70361240532717062",
-- "VIc": "-0.68393972058572117",
-- "EI": "0.29943435718277039",
-- "VI": "0.19089112237884748",
-- "quad": "3"
-- },
-- ...
SELECT carto.LOCAL_MORANS_I_QUADBIN(
[
STRUCT(5266443791927869439, 51.0),
STRUCT(5266443791928131583, 28.0),
STRUCT(5266443791928918015, 19.0)
],
3, 'exponential', 100
);
-- {
-- "index": "5266443791928918015",
-- "value": "-0.076228184845253524",
-- "psim": "0.0099009900990099011",
-- "EIc": "-0.70361240532717062",
-- "VIc": "-0.68393972058572117",
-- "EI": "0.29943435718277039",
-- "VI": "0.19089112237884748",
-- "quad": "3"
-- },
-- ...
carto-un
carto-un-eu
manual
SELECT `carto-un`.carto.LOCAL_MORANS_I_QUADBIN(
ARRAY(SELECT AS STRUCT index, value FROM mytable),
3, 'exponential', 100
);
SELECT `carto-un-eu`.carto.LOCAL_MORANS_I_QUADBIN(
ARRAY(SELECT AS STRUCT index, value FROM mytable),
3, 'exponential', 100
);
SELECT carto.LOCAL_MORANS_I_QUADBIN(
ARRAY(SELECT AS STRUCT index, value FROM mytable),
3, 'exponential', 100
);
LOF(points, k)
Description
points
:ARRAY<STRUCT<geoid STRING, geo GEOGRAPHY>>
input data points with unique id and geography.k
:INT64
number of nearest neighbors (positive, typically small).
Return type
ARRAY<STRUCT<geo GEOGRAPHY, geoid GEOGRAPHY, lof FLOAT64>>
where:geo
: the geometry of the considered point.geoid
: the unique identifier of the considered point.lof
: the Local Outlier Factor score.
Example
carto-un
carto-un-eu
manual
SELECT *
FROM UNNEST((
SELECT `carto-un`.carto.LOF(myarray, 10)
FROM (
SELECT ARRAY_AGG(STRUCT(format('%08x', uid),position_geom)) myarray
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY hour) AS uid, position_geom
FROM `bigquery-public-data.catalonian_mobile_coverage.mobile_data_2015_2017`
WHERE date = '2017-12-31'
)
)
))
ORDER BY geoid
-- {"geo": POINT(2.82263 41.97118), "geoid": "00000001", "lof": 1.3217599116891428}
-- {"geo": POINT(2.35705 41.49786), "geoid": "00000002", "lof": 1.235551000737416}
-- {"geo": POINT(2.13967 41.3838), "geoid": "00000003", "lof": 1.1305674032876687}
-- ...
SELECT *
FROM UNNEST((
SELECT `carto-un-eu`.carto.LOF(myarray, 10)
FROM (
SELECT ARRAY_AGG(STRUCT(format('%08x', uid),position_geom)) myarray
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY hour) AS uid, position_geom
FROM `