Comment on page
Release notes
Module lds
New
- Add
SETUPprocedure. This procedure helps configuring the Analytics Toolbox in custom installations. Check this documentation about installing the Analytics Toolbox in your own BigQuery project for more information. - Add LDS functions:
GEOCODE,GEOCODE_REVERSE,ISOLINE. This functions complement the already existing procedures. They allow geocoding a single address or getting a single isoline without creating a result table. - Replace
LDS_QUOTA_INFOprocedure withGET_LDS_QUOTA_INFOfunction. This new function allows checking your currently consumed quota with a single query, without the need to store the result as a table.
Fix
Module data
Change
- Change location of spatial catalog tables: This is only an internal change. Now the catalog is hosted along with the Data Observatory subscriptions. This change decouples the Analytics Toolbox location from the Data Observatory location, providing more flexibility for future developments.
Module statistics
Fix
Module retail
Fix
- Fix bugs in kring decay procedures:
KRING_DECAY,KRING_DECAY_GRID. These procedure are used internally by other procedures. They work finding cells that are within the K-Ring of each cell in a grid, and assigns a weight based on the distance. This helps model changes in a variable across neighboring cells.\
Module cpg
Change
- Adapt composite score supervised procedures: The algorithm used for computing supervised composite scores has been changed from
BOOSTED_TREE_REGRESSORtoRANDOM_FOREST_REGRESSORto avoid overfitting.
Module statistics
New
- We have added new PCAMIX procedures:
Module retail
Improvement
Module lds
Improvement
Module statistics
New
- Added
_TABLEprocedures for some statistic functions. The_TABLEversion of the procedures expect a table as input, overcoming performance limitations of the functions that receive an array as input:
Module retail
Fix
- Fix default options for BUILD_REVENUE_MODEL that were causing errors when using linear regression models
New
Module lds
Fix
- Fix
GEOCODE_TABLEnot iterating through input table rows.
Module cpg
New
- We have added the new procedures CREATE_SPATIAL_SCORE and CREATE_SPATIAL_PERFORMANCE_SCORE to allow users create scores to analyze their network of merchants/point-of-sale based on a business performance metrics or geospatial attributes to analyze their suitability for the CPG brands.
Module statistics
Fix
Module h3
New
- We have added a new function H3_POLYFILL_MODE that enables different polyfill modes: center, intersects, contains.
Improvement
Module quadbin
New
- We have added a new function QUADBIN_POLYFILL_MODE that enables different polyfill modes: center, intersects, contains.
Improvement
Module lds
New
- We are now allowing users to send any of the parameter options supported by the isolines service provider in CREATE_ISOLINES.
- We have improved our logging of results in GEOCODE_TABLE so more information about processed and geocoded rows is provided to the user. Also the new option “carto_force_geocode” allows the re-geocoding of already geocoded rows.
Fix
- We have applied some fixes to the CREATE_ROUTES function so the metadata column can be successfully created and informed.
Module data
New
- We have added the new procedures DATAOBS_ENRICH_POLYGONS_WEIGHTED and DATAOBS_ENRICH_GRID_WEIGHTED. They allow users to enrich polygons or grids based on spatial indexes (e.g. H3, Quadbin) with features from Data Observatory subscriptions by taking into account the ratio of a specified feature between the intersected and original areas in a way to weight the contribution of each feature in the enrichment. Note that the original methods only use the geographic area of intersection for this weight.
Fix
- We have updated the GRIDIFY_ENRICH procedure so the default value for the “decay” function is taken into consideration.
Module retail
New
- We have added the possibility of passing custom polygons as trade areas in BUILD_CANNIBALIZATION_DATA and CANNIBALIZATION_OVERLAP.
Fix
- We added exceptions to both FIND_TWIN_AREAS and FIND_TWIN_AREAS_WEIGHTED procedures to ensure that the parameter “origin_query” includes only one row (the origin cell).
Module statistics
New
- We are now allowing to use Numeric and Bignumeric as predictors data types types in the procedures: AREA_OF_APPLICABILITY, ENV_BLOCKING and CREATE_SPATIAL_COMPOSITE_SUPERVISED.
Fix
- We modified the bucketization options in CREATE_SPATIAL_COMPOSITE_SUPERVISED and in CREATE_SPATIAL_COMPOSITE_UNSUPERVISED to better fit potential use cases covered by each procedure.
Module quadbin
Change
- We have updated the formula used in QUADBIN_FROMLONGLAT so it gets standardized. In addition a new implementation of QUADBIN_TOCHILDREN has been added in order to increase performance.
Module lds
New
- We have created a new procedure named CREATE_ROUTES, which calculates the routes (returned as linestrings) between a set of given origins and destinations (points) in a query. It supports different transport modes and advanced options as the other functions of the lds module.
Module statistics
New
- We have added new functions in the statistics module to perform space-time cluster analysis, namely GETIS_ORD_SPACETIME_QUADBIN for quadbin indexes and GETIS_ORD_SPACETIME_H3 for H3 indexes.
Fix
- We have changed the input type and defined default values for some internal parameters that affect CREATE_SPATIAL_COMPOSITE_SUPERVISED and CREATE_SPATIAL_COMPOSITE_UNSUPERVISED. Also added some missing brackets to them.
- Renamed internal function names to avoid possible conflicts in the future.
Module cpg
Fix
- We have introduced a better way to handle large tables and drop intermediate tables in the UNIVERSE_MATCHING function.
Module tiler
New
- We have added metadata_partition option to CREATE_TILESET, CREATE_SIMPLE_TILESET, CREATE_POINT_AGGREGATION_TILESET, CREATE_SPATIAL_INDEX_TILESET. A metadata_partition label is added to the table when the partition is not NULL. This optimizes tilesets for its use within CARTO.
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).
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_GRIDwas 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_WEIGHTEDprocedure.
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_DATAprocedure. - Add
GENERATE_TRADE_AREASprocedure. - Add
RUN_CUSTOMER_SEGMENTATIONprocedure.
Module lds
Fix
- CREATE_ISOLINES output is stored in the column
geominstead 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_URLtoAPI_BASE_URL.
Module retail
Improvement
LINEAR_REGmodel is now supported.
Module tiler
Improvement
- Fix bug in
__BUILD_REVENUE_MODEL_DATA_GRID: Output table now containscompetitor_count.
Module tiler
Improvement
- Set
versionfield in tilers metadata compliant with AT version.
Module tiler
Improvement
- Hotfix: set
generate_feature_idto 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_idparameter 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_metadataintometadatain 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_DECAYparameters.
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".