Release notes
Module statistics
New
- Set of procedures to compute Area of Applicability of a model built with BigQuery ML:
- ​AREA_OF_APPLICABILITY: This procedure computes the Area of Applicability (AOA) of a Bigquery ML model. It generates a metric which tells the user where the results from a Machine Learning (ML) model can be trusted when the predictions are extrapolated outside the training space (i.e. where the estimated cross-validation performance holds).
- ​ENV_BLOCKING: This procedure derives cross validation (CV) folds based on environmental blocking.
Fix
- We have added a new mandatory parameter called correlation_var when scoring_method = FIRST_PC in the CREATE_SPATIAL_COMPOSITE_UNSUPERVISED​
- We have improved the management of temporary tables in all procedures to compute spatial composites (scores)
Module raster (beta)
Improvement
- Store ‘nodata’ value in metadata
- Add resolution level to metadata
- Fix bands metadata aggregation
- We have released experimental rendering capabilities for the raster data in deck.gl: https://deck.gl/docs/api-reference/carto/carto-layer#raster-data-experimental​
Module statistics
New
- We have released a new set of procedures within the statistics module to enable users to create spatial scores (also known as composite indicators or indexes) derived from a combination of different features. We have included 3 different procedures:
- ​CREATE_SPATIAL_COMPOSITE_SUPERVISED: to compute a spatial composite score as the residuals of a regression model which is used to detect areas of under- and over-prediction.
- ​CREATE_SPATIAL_COMPOSITE_UNSUPERVISED: to perform an aggregation of individual variables, scaled and weighted accordingly, into a spatial composite score.
- ​CRONBACH_ALPHA_COEFFICIENT: to measure the internal consistency of the variables used to derive the spatial composite score.
Module lds
Change
- We have updated the LDS module to be aligned with the new usage quota model, working as an annual budget and combining consumption across all supported LDS services.
Fix
- ​GEOCODE_TABLE now supports null values in the input address column, ignoring them when running the geocoding process to provide results only for the informed addresses.
Module raster (beta)
Improvement
Note
The raster module of the Analytics Toolbox for BigQuery v1.1.0 will work only with raster-loader v0.3.0, which pre-computes and stores metadata in the raster table and homogenizes the generic/quadbin raster table format. So, if you update your Analytics Toolbox version to the most recent v1.1.0, please ensure you are using the raster-loader v0.3.0.
- We have made the RASTER_GETMETADATA faster by retrieving metadata from the table and avoiding creating again the original table.
- We have improved the RASTER_ST_GETVALUE by removing pixel_geog column and cluster results by quadbin in the quadbin raster format.
- We have enhanced the RASTER_ST_GETVALUE_FROM_TABLE outcome by preserving the input columns, renaming centroid_quadbin_block and centroid_quadbin_pixel to block_quadbin and quadbin, clustering the results by quadbin in quadbin raster and removing few non-informative columns (e.g. geog and myoffset).
Module cpg
New
- We have released a new set of functions to enable users to perform the merchant universe matching analysis. It includes 2 different functions, UNIVERSE_MATCHING which performs a fuzzy match between two different POI datasets based on location and name similarity (i.e. one dataset should include current merchants and the other one the complete universe of all potential merchants), and UNIVERSE_MATCHING_REPORT that generates report-like tables summarizing market penetration based on the results of the UNIVERSE_MATCHING analysis.
Fix
Module lds
Improvement
- From this version, we support line break (\n) and other escaped sequences in addresses in GEOCODE_TABLE.
Raster module (beta)
New
Note that Analytics Toolbox for BigQuery v1.0.0 will work only with raster-loader v0.2.0, to use the latest version of rater-loader please ensure you also have access to the latest version of the Analytics Toolbox
- New module to operate with raster data in BigQuery. This module includes the procedures RASTER_ST_GETVALUE and RASTER_ST_GETVALUE_FROM_TABLE to obtain the values of the raster in one or multiple geographies, respectively. It also includes the RASTER_GETMETADATA procedure to obtain metadata associated with the raster file.
Routing module (beta)
Improvement
- New enhanced version of the routing module with procedures ROUTING_MATRIX to calculate origin-destination matrices and ROUTING_ISOLINES to compute isolines around a set of locations, both supporting multiple transportation modes (car, bike, and walk). These new functions run on top of CARTO’s road network (derived from OSM segments) that is available as a public subscription in the Data Observatory. Please note that these improvements imply breaking changes with the previous version of the routing module.
Module retail
Fix
Module statistics
New
Module tiler
Improvement
- Update default zoom values: geo, quadbin, h3 (12, 12, 6).
- Fix spatial index tiler with empty properties.
Module lds
Improvement
- We have added the possibility to configure more options as parameters when executing the functions to CREATE_ISOLINES. These new options, which depend on the LDS service provider, allow the user to configure more transportation modes such as truck or bike, the possibility of specifying departure or arrival times allowing the creation of reverse isolines, and other options like different routing modes. Please note that these improvements imply breaking changes with the previous version of the CREATE_ISOLINES functions.
- Added new confidence/relevance metadata to the results of the geocoding function GEOCODE_TABLE. Please note that this improvement has implied breaking changes with the previous versions of the GEOCODE_TABLE and GEOCODE_REVERSE_TABLE functions.
Module cpg
New
- New FIND_SIMILAR_LOCATIONS function to the CPG module, that allows users to identify which locations (e.g. merchants, stores) are more similar to a chosen location (e.g. top performant) based on the characteristics of their surrounding areas (or trade areas).
Module retail
Improvement
- Update in BUILD_CANNIBALIZATION_DATA and CANNIBALIZATION_OVERLAP to support buffer, kring and isoline methods. Please note that these improvements have implied some breaking changes in the aforementioned functions invalidating their previous version.
Module data
Fix
Module quadbin
New
- Quadbin/quadkey conversion functions have been added to ease the conversion from quadbin to quadkey and from quadkey to quadbin indexes.
Module retail
Fix
__KRING_DECAY_GRID
was aggregating the value of the moving window pivoting index instead of neighbour indexes.
Module h3
Improvement
- Add linestrings and points support to function H3_POLYFILL.
Module retail
New
- Add
FIND_TWIN_AREAS_WEIGHTED
procedure.
All modules
New
- Create release workflows
Module s2
New
Module transformations
Fix
- Fix ST_BUFFER crashing with geographies close to the poles.
Module tiler
Improvement
- Partition tables in spatial index tilesets.
Module cpg
New
- Add
CUSTOMER_SEGMENTATION_ANALYSIS_DATA
procedure. - Add
GENERATE_TRADE_AREAS
procedure. - Add
RUN_CUSTOMER_SEGMENTATION
procedure.
Module lds
Fix
- CREATE_ISOLINES output is stored in the column
geom
instead of__iso_geom
.
Improvement
- CREATE_ISOLINES output is clustered by geometry.
Module processing
Fix
- Prevent error in ST_VORONOIPOLYGONS, ST_VORONOILINES, ST_VORONOIPOLYGONS, ST_DELAUNAYLINES when points where too close together by rounding input coordinates to 5 decimal places.
Module clustering
New
- Move ST_CLUSTERKMEANS function to core.
Module random
New
- Move ST_GENERATEPOINTS function to core.
Module lds
Improvement
- Rename
LDS_API_URL
toAPI_BASE_URL
.
Module retail
Improvement
LINEAR_REG
model is now supported.
Module tiler
Improvement
- Fix bug in
__BUILD_REVENUE_MODEL_DATA_GRID
: Output table now containscompetitor_count
.
Module tiler
Improvement
- Set
version
field in tilers metadata compliant with AT version.
Module tiler
Improvement
- Hotfix: set
generate_feature_id
to false by default due a bug in the generation.
Module data
Improvement
- Optimize DATAOBS_ENRICH_GRID, DATAOBS_ENRICH_GRID_RAW when the enrich data is a grid compatible with the input.
- Remove support for s2, geohas and quadbinh in all enrich procedures.
- Optimize all enrichment result tables by default by clusterizing by the geography or spatial index column.
Module s2
New
- Add S2_CENTER function.
Module retail
Improvement
- Return shap_values in PREDICT_REVENUE_AVERAGE.
Module tiler
New
- Add
generate_feature_id
parameter inCREATE_SIMPLE_TILESET
.
Module retail
Improvement
- Index parameter admits any type in PREDICT_REVENUE_AVERAGE.
- Index from revenue_model_data is not casted in PREDICT_REVENUE_AVERAGE to reduce query data processing.
Module tiler
Fix
- Fixed bug when parsing zoom columns in geography tilers.
Module tiler
Improvement
- Unify
extra_metadata
intometadata
in tiler metadata.
Module h3
Fix
- Apply make_valid in H3_BOUNDARY.
Module tiler
Fix
- Fixed bug in metadata
CREATE_SPATIAL_INDEX_TILESET
.
Module retail
Improvement
- Check fixed model options in BUILD_REVENUE_MODEL procedure.
Module retail
New
- Add BUILD_CANNIBALIZATION_DATA procedure.
- Add CANNIBALIZATION_OVERLAP procedure.
Module lds
New
- Add GEOCODE_TABLE procedure.
- Add LDS_QUOTA_INFO procedure.
- Add GEOCODE_REVERSE_TABLE procedure.
- Add CREATE_ISOLINES procedure.
Module statistics
Improvement
- Added HH, LL, LH, HL detection in LOCAL_MORANS_I functions.
Module tiler
Fix
- Fixed bug when passing multiline input to
CREATE_SPATIAL_INDEX_TILESET
.
Module data
New
- Add quadbin support to module.
Module retail
New
- Add quadbin support
Module tiler
New
- Support of quadgrid as alias in
CREATE_POINT_AGGREGATION_TILESET
.
Improvement
- Use core functions to convert H3 from int to string.
Module h3
Fix
- Correctly handle large polygons in H3_POLYFILL.
- Fixed wrong uppercase for quadbin and h3 tile ids
Module statistics
New
- Add SMOOTHING_MRF_QUADBIN procedure.
- Add LOCAL_MORANS_I_QUADBIN function.
- Add MORANS_I_QUADBIN function.
- Add GETIS_ORD_QUADBIN function.
- Quadbin support in GWR_GRID function.
Module tiler
Fix
- Fixed tablename quoting in BigQuery tiler
Module retail
Fix
- Fix quadkey support in FIND_WHITESPACE_AREAS
Module retail
Fix
- Fixing bugs when not providing store variables to BUILD_REVENUE_MODEL_DATA
Module quadbin
New
- Add QUADBIN_BBOX function.
- Add QUADBIN_BOUNDARY function.
- Add QUADBIN_CENTER function.
- Add QUADBIN_FROMGEOGPOINT function.
- Add QUADBIN_FROMLONGLAT function.
- Add QUADBIN_FROMZXY function.
- Add QUADBIN_ISVALID function.
- Add QUADBIN_KRING function.
- Add QUADBIN_KRING_DISTANCES function.
- Add QUADBIN_POLYFILL function.
- Add QUADBIN_RESOLUTION function.
- Add QUADBIN_SIBLING function.
- Add QUADBIN_TOCHILDREN function.
- Add QUADBIN_TOPARENT function.
- Add QUADBIN_TOZXY function.
Module data
Fix
- Fix BQ inplace enrichment with uppercase column names.
Module tiler
Improvement
- Support H3 integer in
CREATE_SPATIAL_INDEX_TILESET
.
Module data
Fix
- Fix order of
__KRING_DECAY
parameters.
Module tiler
Fix
- Fix global stats in
CREATE_SPATIAL_INDEX_TILESET
.
Improvement
- Check input resolution limits in
CREATE_SPATIAL_INDEX_TILESET
.
Module data
Fix
- Report missing subscription geographies in DATAOBS_ENRICH_ procedures.
Module data
Fix
- Fix stability issues in GRIDIFY_ENRICH procedure.
Module retail
New
- Add store features and competitors stores features into BUILD_REVENUE_MODEL_DATA procedure.
Fix
- Fixing bugs calling other routines
Module tiler
New
- Add spatial index tileset generator
CREATE_SPATIAL_INDEX_TILESET
.
Module tiler
Fix
- Bug fixing when the geometry early collapse
Module data
New
- Add GRIDIFY_ENRICH procedure.
Module retail
New
- Add FIND_TWIN_AREAS procedure.
Module data
New
- Allow using tables as input, not just queries.
- Add in place enrichment.
- Allow using tables as input.
- Accept NULL as output.
Module retail
Fix
- Fix BUILD_REVENUE_MODEL_DATA k-ring buffer computation.
Module transformations
Improvement
- ST_CONCAVEHULL now allows arrays with one/two points as input.
Module retail
New
- Add COMMERCIAL_HOTSPOTS procedure.
Module statistics
New
- Add SMOOTHING_MRF_H3 procedure.
- Add SMOOTHING_MRF_QUADKEY procedure.
Module statistics
New
- Add VARIOGRAM function.
- Add ORDINARY_KRIGING function.
- Add ORDINARY_KRIGING_TABLE procedure.
Module h3
New
- Add H3_CENTER function.
- Add H3_RESOLUTION function.
Module retail
New
- Add FIND_WHITESPACE_AREAS procedure.
Module retail
New
- Remove features importance tables from BUILD_REVENUE_MODEL procedure.
Module statistics
New
- Add LOCAL_MORANS_I_H3 function.
- Add LOCAL_MORANS_I_QUADKEY function.
Improvement
- Improve MORANS_I_H3 and MORANS_I_QUADKEY implementation.
Module statistics
Fix
- Replace STDDEV by STDDEV_POP in GETIS_ORD_H3 and GETIS_ORD_QUADKEY.
Module retail
New
- Add BUILD_REVENUE_MODEL_DATA procedure.
- Add BUILD_REVENUE_MODEL procedure.
- Add PREDICT_REVENUE_AVERAGE procedure.
Module accessors
Improvement
- Deployment schema "carto" instead of "accessors".
Removed
- Remove VERSION function.
Module constructors
Improvement
- Deployment schema "carto" instead of "constructors".
Removed
- Remove VERSION function.
Module geohash
Improvement
- Deployment schema "carto" instead of "geohash".
Removed
- Remove VERSION function.
Module h3
Improvement
- Deployment schema "carto" instead of "h3".
- Rename ST_ASH3 function to H3_FROMGEOGPOINT.
- Rename LONGLAT_ASH3 function to H3_FROMLONGLAT.
- Rename ST_ASH3_POLYFILL function to H3_POLYFILL.
- Rename ST_BOUNDARY function to H3_BOUNDARY.
- Rename ISVALID function to H3_ISVALID.
- Rename COMPACT function to H3_COMPACT.
- Rename UNCOMPACT function to H3_UNCOMPACT.
- Rename TOPARENT function to H3_TOPARENT.
- Rename TOCHILDREN function to H3_TOCHILDREN.
- Rename ISPENTAGON function to H3_ISPENTAGON.
- Rename DISTANCE function to H3_DISTANCE.
- Rename KRING function to H3_KRING.
- Rename KRING_DISTANCES function to H3_KRING_DISTANCES.
- Rename HEXRING function to H3_HEXRING.
Removed
- Remove VERSION function.
Module measurements
Improvement
- Deployment schema "carto" instead of "measurements".
Removed
- Remove ST_ANGLE, already present in Bigquery.
- Remove VERSION function.
Module placekey
Improvement
- Deployment schema "carto" instead of "placekey".
- Rename H3_ASPLACEKEY function to PLACEKEY_FROMH3.
- Rename PLACEKEY_ASH3 function to PLACEKEY_TOH3.
- Rename ISVALID function to PLACEKEY_ISVALID.
Removed
- Remove VERSION function.
Module processing
Improvement
- Deployment schema "carto" instead of "processing".
Removed
- Remove VERSION function.
Module s2
Improvement
- Deployment schema "carto" instead of "s2".
- Rename ID_FROMHILBERTQUADKEY function to S2_FROMHILBERTQUADKEY.
- Rename HILBERTQUADKEY_FROMID function to S2_TOHILBERTQUADKEY.
- Rename LONGLAT_ASID function to S2_FROMLONGLAT.
- Rename ST_ASID function to S2_FROMGEOGPOINT.
- Rename ID_FROMTOKEN function to S2_FROMTOKEN.
- Rename TOKEN_FROMID function to S2_TOTOKEN.
- Rename ID_FROMUINT64REPR function to S2_FROMUINT64REPR.
- Rename UINT64REPR_FROMID function to S2_TOUINT64REPR.
- Rename ST_BOUNDARY function to S2_BOUNDARY.
Removed
- Remove VERSION function.
Module transformations
Improvement
- Deployment schema "carto" instead of "transformations".
Removed
- Remove VERSION function.
Module clustering
Improvement
- Deployment schema "carto" instead of "clustering".
Removed
- Remove VERSION function.
Module data
Improvement
- Deployment schema "carto" instead of "data".
Removed
- Remove VERSION function.
Module random
Improvement
- Deployment schema "carto" instead of "random".
Removed
- Remove VERSION function.
Module routing
Improvement
- Deployment schema "carto" instead of "routing".
Removed
- Remove VERSION function.
Module statistics
Improvement
- Deployment schema "carto" instead of "statistics".
Removed
- Remove VERSION function.
Module tiler
Improvement
- Deployment schema "carto" instead of "tiler".
Removed
- Remove VERSION function.
Module tiler
Improvement
- Add support for legacy project names that contain dots or other special characters.
Module statistics
New
- Add KNN function.
- Add GFUN function.
- Add LOF function.
- Add LOF_TABLE procedure.
Module tiler
Fix
- Escape single-quoted properties in the
CREATE_TILESET
metadata when obtaining the geometry type.
Module statistics
New
- Add GWR_GRID function.
Module tiler
Fix
- Use
zoom_min_column
aszoom_min
andzoom_max_column
aszoom_max
when provided.
Improvement
- Optimize simplification method instead of the native BigQuery.
Module data
Improvement
- Fields named
dimension
,total
,intersection
andinput_area
are now__carto_dimension
,__carto_total
and__carto_intersection
and__carto_input_area
. Also the column_carto_enrichment_
is now__carto_enrichment
. This affects all the raw enrichment procedures:ENRICH_POINTS_RAW
,ENRICH_POLYGONS_RAW
,ENRICH_GRID_RAW
,DATAOBS_ENRICH_POINTS_RAW
,DATAOBS_ENRICH_POLYGONS_RAW
,DATAOBS_ENRICH_GRID_RAW
.
Fix
- User provided queries can now have columns named
dimension
,total
,intersection
,input_area
,_nonglobal
, which could have collided previously with internal columns. All internal columns are now prefixed with__carto_
. This affects all the enrichment procedures:ENRICH_POINTS
,ENRICH_POLYGONS
,ENRICH_GRID
,DATAOBS_ENRICH_POINTS
,DATAOBS_ENRICH_POLYGONS
,DATAOBS_ENRICH_GRID
,ENRICH_POINTS_RAW
,ENRICH_POLYGONS_RAW
,ENRICH_GRID_RAW
,DATAOBS_ENRICH_POINTS_RAW
,DATAOBS_ENRICH_POLYGONS_RAW
,DATAOBS_ENRICH_GRID_RAW
.
Module data
Improvement
- Add
associated_geography_slug
column toDATAOBS_SUBSCRIPTIONS
. - Some column names have changed in
DATAOBS_SUBSCRIPTIONS
:table
is nowdataset_table
associated_geography
is nowassociated_geography_table
- Some column names have changed in
DATAOBS_SUBSCRIPTION_VARIABLES
:geography_slug
is nowassociated_geography_slug
.
- Some column names have changed in
DATAOBS_SAMPLES
:table
is nowdataset_sample_table
.
- In
DATAOBS_SUBSCRIPTION_VARIABLES
, the columnassociated_geography_slug
is now NULL for geography datasets.
Fix
- The output of
DATAOBS_SUBSCRIPTIONS
andDATAOBS_SUBSCRIPTION_VARIABLES
now omits items that are not Data Observatory products.
Module data
Fix
- Enrichment results could mix input rows and values, specially for large inputs due to multiple evaluation of nondeterministic ROW_NUMBERS expression.
Improvement
- New API, with consistent naming of procedures `
Module s2
Improvement
- Rename functions ID_FROMUINT64REPR, UINT64REPR_FROMID to follow convention.
Module statistics
New
- Create statistics module.
- Add GETIS_ORD_H3 function.
- Add GETIS_ORD_QUADKEY function.
- Add MORANS_I_H3 function.
- Add MORANS_I_QUADKEY function.
- Add VERSION function.
Module h3
Improvement
- Review HEXRING, KRING functions.
- Change KRING_INDEXED to KRING_DISTANCES.
Module tiler
Fix
- Fix support for DATE in
CREATE_TILESET
. - Fix naming of internal variables to avoid name collisions.
New
- Add
fraction_dropped_per_zoom
in tileset metadata.