LogoLogo
HomeAcademyLoginTry for free
  • Welcome
  • What's new
    • Q2 2025
    • Q1 2025
    • Q4 2024
    • Q3 2024
    • Q2 2024
    • Q1 2024
    • Q4 2023
    • Q3 2023
    • Q2 2023
    • Q1 2023
    • Q4 2022
    • Q3 2022
  • FAQs
    • Accounts
    • Migration to the new platform
    • User & organization setup
    • General
    • Builder
    • Workflows
    • Data Observatory
    • Analytics Toolbox
    • Development Tools
    • Deployment Options
    • CARTO Basemaps
    • CARTO for Education
    • Support Packages
    • Security and Compliance
  • Getting started
    • What is CARTO?
    • Quickstart guides
      • Connecting to your data
      • Creating your first map
      • Creating your first workflow
      • Developing your first application
    • CARTO Academy
  • CARTO User Manual
    • Overview
      • Creating your CARTO organization
      • CARTO Cloud Regions
      • CARTO Workspace overview
    • Maps
      • Data sources
        • Simple features
        • Spatial Indexes
        • Pre-generated tilesets
        • Rasters
        • Defining source spatial data
        • Managing data freshness
        • Changing data source location
      • Layers
        • Point
          • Grid point aggregation
          • H3 point aggregation
          • Heatmap point aggregation
          • Cluster point aggregation
        • Polygon
        • Line
        • Grid
        • H3
        • Raster
        • Zoom to layer
      • Widgets
        • Formula widget
        • Category widget
        • Pie widget
        • Histogram widget
        • Range widget
        • Time Series widget
        • Table widget
      • SQL Parameters
        • Date parameter
        • Text parameter
        • Numeric parameter
        • Publishing SQL parameters
      • Interactions
      • Legend
      • Basemaps
        • Basemap selector
      • AI Agents
      • SQL analyses
      • Map view modes
      • Map description
      • Feature selection tool
      • Search locations
      • Measure distances
      • Exporting data
      • Download PDF reports
      • Managing maps
      • Sharing and collaboration
        • Editor collaboration
        • Map preview for editors
        • Map settings for viewers
        • Comments
        • Embedding maps
        • URL parameters
      • Performance considerations
    • Workflows
      • Workflow canvas
      • Results panel
      • Components
        • Aggregation
        • Custom
        • Data Enrichment
        • Data Preparation
        • Generative AI
        • Input / Output
        • Joins
        • Parsers
        • Raster Operations
        • Spatial Accessors
        • Spatial Analysis
        • Spatial Constructors
        • Spatial Indexes
        • Spatial Operations
        • Statistics
        • Tileset Creation
        • BigQuery ML
        • Snowflake ML
        • Google Earth Engine
        • Google Environment APIs
        • Telco Signal Propagation Models
      • Data Sources
      • Scheduling workflows
      • Sharing workflows
      • Using variables in workflows
      • Executing workflows via API
      • Temporary data in Workflows
      • Extension Packages
      • Managing workflows
      • Workflows best practices
    • Data Explorer
      • Creating a map from your data
      • Importing data
        • Importing rasters
      • Geocoding data
      • Optimizing your data
    • Data Observatory
      • Terminology
      • Browsing the Spatial Data Catalog
      • Subscribing to public and premium datasets
      • Accessing free data samples
      • Managing your subscriptions
      • Accessing your subscriptions from your data warehouse
        • Access data in BigQuery
        • Access data in Snowflake
        • Access data in Databricks
        • Access data in Redshift
        • Access data in PostgreSQL
    • Connections
      • Google BigQuery
      • Snowflake
      • Databricks
      • Amazon Redshift
      • PostgreSQL
      • CARTO Data Warehouse
      • Sharing connections
      • Deleting a connection
      • Required permissions
      • IP whitelisting
      • Customer data responsibilities
    • Applications
    • Settings
      • Understanding your organization quotas
      • Activity Data
        • Activity Data Reference
        • Activity Data Examples
        • Activity Data Changelog
      • Users and Groups
        • Inviting users to your organization
        • Managing user roles
        • Deleting users
        • SSO
        • Groups
        • Mapping groups to user roles
      • CARTO Support Access
      • Customizations
        • Customizing appearance and branding
        • Configuring custom color palettes
        • Configuring your organization basemaps
        • Enabling AI Agents
      • Advanced Settings
        • Managing applications
        • Configuring S3 Bucket for Redshift Imports
        • Configuring OAuth connections to Snowflake
        • Configuring OAuth U2M connections to Databricks
        • Configuring S3 Bucket integration for RDS for PostgreSQL Exports in Builder
        • Configuring Workload Identity Federation for BigQuery
      • Data Observatory
      • Deleting your organization
    • Developers
      • Managing Credentials
        • API Base URL
        • API Access Tokens
        • SPA OAuth Clients
        • M2M OAuth Clients
      • Named Sources
  • Data and Analysis
    • Analytics Toolbox Overview
    • Analytics Toolbox for BigQuery
      • Getting access
        • Projects maintained by CARTO in different BigQuery regions
        • Manual installation in your own project
        • Installation in a Google Cloud VPC
        • Core module
      • Key concepts
        • Tilesets
        • Spatial indexes
      • SQL Reference
        • accessors
        • clustering
        • constructors
        • cpg
        • data
        • http_request
        • import
        • geohash
        • h3
        • lds
        • measurements
        • placekey
        • processing
        • quadbin
        • random
        • raster
        • retail
        • routing
        • s2
        • statistics
        • telco
        • tiler
        • transformations
      • Guides
        • Running queries from Builder
        • Working with Raster data
      • Release notes
      • About Analytics Toolbox regions
    • Analytics Toolbox for Snowflake
      • Getting access
        • Native App from Snowflake's Marketplace
        • Manual installation
      • Key concepts
        • Spatial indexes
        • Tilesets
      • SQL Reference
        • accessors
        • clustering
        • constructors
        • data
        • http_request
        • import
        • h3
        • lds
        • measurements
        • placekey
        • processing
        • quadbin
        • random
        • raster
        • retail
        • s2
        • statistics
        • tiler
        • transformations
      • Guides
        • Running queries from Builder
        • Working with Raster data
      • Release Notes
    • Analytics Toolbox for Databricks
      • Getting access
        • Personal (former Single User) cluster
        • Standard (former Shared) cluster
      • Reference
        • lds
        • tiler
      • Guides
      • Release Notes
    • Analytics Toolbox for Redshift
      • Getting access
        • Manual installation in your database
        • Installation in an Amazon Web Services VPC
        • Core version
      • Key concepts
        • Tilesets
        • Spatial indexes
      • SQL Reference
        • clustering
        • constructors
        • data
        • http_request
        • import
        • lds
        • placekey
        • processing
        • quadbin
        • random
        • s2
        • statistics
        • tiler
        • transformations
      • Guides
        • Running queries from Builder
      • Release Notes
    • Analytics Toolbox for PostgreSQL
      • Getting access
        • Manual installation
        • Core version
      • Key concepts
        • Tilesets
        • Spatial Indexes
      • SQL Reference
        • h3
        • quadbin
        • tiler
      • Guides
        • Creating spatial index tilesets
        • Running queries from Builder
      • Release Notes
    • CARTO + Python
      • Installation
      • Authentication Methods
      • Visualizing Data
      • Working with Data
        • How to work with your data in the CARTO Data Warehouse
        • How to access your Data Observatory subscriptions
        • How to access CARTO's Analytics Toolbox for BigQuery and create visualizations via Python notebooks
        • How to access CARTO’s Analytics Toolbox for Snowflake and create visualizations via Python notebooks
        • How to visualize data from Databricks
      • Reference
    • CARTO QGIS Plugin
  • CARTO for Developers
    • Overview
    • Key concepts
      • Architecture
      • Libraries and APIs
      • Authentication methods
        • API Access Tokens
        • OAuth Access Tokens
        • OAuth Clients
      • Connections
      • Data sources
      • Visualization with deck.gl
        • Basemaps
          • CARTO Basemap
          • Google Maps
            • Examples
              • Gallery
              • Getting Started
              • Basic Examples
                • Hello World
                • BigQuery Tileset Layer
                • Data Observatory Tileset Layer
              • Advanced Examples
                • Arc Layer
                • Extrusion
                • Trips Layer
            • What's New
          • Amazon Location
            • Examples
              • Hello World
              • CartoLayer
            • What's New
        • Rapid Map Prototyping
      • Charts and widgets
      • Filtering and interactivity
      • Summary
    • Quickstart
      • Make your first API call
      • Visualize your first dataset
      • Create your first widget
    • Guides
      • Build a public application
      • Build a private application
      • Build a private application using SSO
      • Visualize massive datasets
      • Integrate CARTO in your existing application
      • Use Boundaries in your application
      • Avoid exposing SQL queries with Named Sources
      • Managing cache in your CARTO applications
    • Reference
      • Deck (@deck.gl reference)
      • Data Sources
        • vectorTableSource
        • vectorQuerySource
        • vectorTilesetSource
        • h3TableSource
        • h3QuerySource
        • h3TilesetSource
        • quadbinTableSource
        • quadbinQuerySource
        • quadbinTilesetSource
        • rasterSource
        • boundaryTableSource
        • boundaryQuerySource
      • Layers (@deck.gl/carto)
      • Widgets
        • Data Sources
        • Server-side vs. client-side
        • Models
          • getFormula
          • getCategories
          • getHistogram
          • getRange
          • getScatter
          • getTimeSeries
          • getTable
      • Filters
        • Column filters
        • Spatial filters
      • CARTO APIs Reference
    • Release Notes
    • Examples
    • CARTO for React
      • Guides
        • Getting Started
        • Views
        • Data Sources
        • Layers
        • Widgets
        • Authentication and Authorization
        • Basemaps
        • Look and Feel
        • Query Parameters
        • Code Generator
        • Sample Applications
        • Deployment
        • Upgrade Guide
      • Examples
      • Library Reference
        • Introduction
        • API
        • Auth
        • Basemaps
        • Core
        • Redux
        • UI
        • Widgets
      • Release Notes
  • CARTO Self-Hosted
    • Overview
    • Key concepts
      • Architecture
      • Deployment requirements
    • Quickstarts
      • Single VM deployment (Kots)
      • Orchestrated container deployment (Kots)
      • Advanced Orchestrated container deployment (Helm)
    • Guides
      • Guides (Kots)
        • Configure your own buckets
        • Configure an external in-memory cache
        • Enable Google Basemaps
        • Enable the CARTO Data Warehouse
        • Configure an external proxy
        • Enable BigQuery OAuth connections
        • Configure Single Sign-On (SSO)
        • Use Workload Identity in GCP
        • High availability configuration for CARTO Self-hosted
        • Configure your custom service account
      • Guides (Helm)
        • Configure your own buckets (Helm)
        • Configure an external in-memory cache (Helm)
        • Enable Google Basemaps (Helm)
        • Enable the CARTO Data Warehouse (Helm)
        • Configure an external proxy (Helm)
        • Enable BigQuery OAuth connections (Helm)
        • Configure Single Sign-On (SSO) (Helm)
        • Use Workload Identity in GCP (Helm)
        • Use EKS Pod Identity in AWS (Helm)
        • Enable Redshift imports (Helm)
        • Migrating CARTO Self-hosted installation to an external database (Helm)
        • Advanced customizations (Helm)
        • Configure your custom service account (Helm)
    • Maintenance
      • Maintenance (Kots)
        • Updates
        • Backups
        • Uninstall
        • Rotating keys
        • Monitoring
        • Change the Admin Console password
      • Maintenance (Helm)
        • Monitoring (Helm)
        • Rotating keys (Helm)
        • Uninstall (Helm)
        • Backups (Helm)
        • Updates (Helm)
    • Support
      • Get debug information for Support (Kots)
      • Get debug information for Support (Helm)
    • CARTO Self-hosted Legacy
      • Key concepts
        • Architecture
        • Deployment requirements
      • Quickstarts
        • Single VM deployment (docker-compose)
      • Guides
        • Configure your own buckets
        • Configure an external in-memory cache
        • Enable Google Basemaps
        • Enable the CARTO Data Warehouse
        • Configure an external proxy
        • Enable BigQuery OAuth connections
        • Configure Single Sign-On (SSO)
        • Enable Redshift imports
        • Configure your custom service account
        • Advanced customizations
        • Migrating CARTO Self-Hosted installation to an external database
      • Maintenance
        • Updates
        • Backups
        • Uninstall
        • Rotating keys
        • Monitoring
      • Support
    • Release Notes
  • CARTO Native App for Snowflake Containers
    • Deploying CARTO using Snowflake Container Services
  • Get Help
    • Legal & Compliance
    • Previous libraries and components
    • Migrating your content to the new CARTO platform
Powered by GitBook
On this page
  • H3_BOUNDARY
  • H3_CENTER
  • H3_COMPACT
  • H3_DISTANCE
  • H3_FROMGEOGPOINT
  • H3_FROMLONGLAT
  • H3_HEXRING
  • H3_INT_TOSTRING
  • H3_ISPENTAGON
  • H3_ISVALID
  • H3_KRING
  • H3_KRING_DISTANCES
  • H3_POLYFILL
  • H3_POLYFILL_MODE
  • H3_POLYFILL_TABLE (BETA)
  • H3_RESOLUTION
  • H3_STRING_TOINT
  • H3_TOCHILDREN
  • H3_TOPARENT
  • H3_UNCOMPACT

Was this helpful?

Export as PDF
  1. Data and Analysis
  2. Analytics Toolbox for BigQuery
  3. SQL Reference

h3

PreviousgeohashNextlds

Last updated 6 months ago

Was this helpful?

is Uber’s Hexagonal Hierarchical Spatial Index. Full documentation of the project can be found at . You can also learn more about H3 in the of this documentation.

H3_BOUNDARY

H3_BOUNDARY(index)

Description

Returns a geography representing the H3 cell. It will return null on error (invalid input).

  • index: STRING The H3 cell index.

Return type

GEOGRAPHY

Example

SELECT `carto-un`.carto.H3_BOUNDARY('84390cbffffffff');
-- POLYGON((-3.57692743539573 40.6134385959352, -3.85975632308016 ...
SELECT `carto-un-eu`.carto.H3_BOUNDARY('84390cbffffffff');
-- POLYGON((-3.57692743539573 40.6134385959352, -3.85975632308016 ...
SELECT carto.H3_BOUNDARY('84390cbffffffff');
-- POLYGON((-3.57692743539573 40.6134385959352, -3.85975632308016 ...

Additional examples

H3_CENTER

H3_CENTER(index)

Description

Returns the center of the H3 cell as a GEOGRAPHY point. It will return null on error (invalid input).

  • index: STRING The H3 cell index.

Return type

GEOGRAPHY

Example

SELECT `carto-un`.carto.H3_CENTER('84390cbffffffff');
-- POINT(-3.61760324662829 40.3725405821658)
SELECT `carto-un-eu`.carto.H3_CENTER('84390cbffffffff');
-- POINT(-3.61760324662829 40.3725405821658)
SELECT carto.H3_CENTER('84390cbffffffff');
-- POINT(-3.61760324662829 40.3725405821658)

H3_COMPACT

H3_COMPACT(indexArray)

Description

Returns an array with the indexes of a set of hexagons across multiple resolutions that represent the same area as the input set of hexagons.

  • indexArray: ARRAY<STRING> of H3 cell indices of the same resolution.

Return type

ARRAY<STRING>

Example

SELECT `carto-un`.carto.H3_COMPACT(['85390ca3fffffff', '85390ca7fffffff', '85390cabfffffff', '85390caffffffff', '85390cb3fffffff', '85390cb7fffffff', '85390cbbfffffff']);
-- 84390cbffffffff
SELECT `carto-un-eu`.carto.H3_COMPACT(['85390ca3fffffff', '85390ca7fffffff', '85390cabfffffff', '85390caffffffff', '85390cb3fffffff', '85390cb7fffffff', '85390cbbfffffff']);
-- 84390cbffffffff
SELECT carto.H3_COMPACT(['85390ca3fffffff', '85390ca7fffffff', '85390cabfffffff', '85390caffffffff', '85390cb3fffffff', '85390cb7fffffff', '85390cbbfffffff']);
-- 84390cbffffffff

H3_DISTANCE

H3_DISTANCE(origin, destination)

Description

Returns the grid distance between two hexagon indexes. This function may fail to find the distance between two indexes if they are very far apart or on opposite sides of a pentagon. Returns null on failure or invalid input.

  • origin: STRING origin H3 cell index.

  • destination: STRING destination H3 cell index.

Return type

INT64

Example

SELECT `carto-un`.carto.H3_DISTANCE('84390c1ffffffff', '84390cbffffffff');
-- 1
SELECT `carto-un-eu`.carto.H3_DISTANCE('84390c1ffffffff', '84390cbffffffff');
-- 1
SELECT carto.H3_DISTANCE('84390c1ffffffff', '84390cbffffffff');
-- 1

tip

Additional examples

H3_FROMGEOGPOINT

H3_FROMGEOGPOINT(point, resolution)

Description

Returns the H3 cell index that the point belongs to in the requested resolution. It will return null on error (invalid geography type or resolution out of bounds). This function is an alias for H3_FROMGEOPOINT.

  • point: GEOGRAPHY point to get the H3 cell from.

Return type

STRING

Example

SELECT `carto-un`.carto.H3_FROMGEOGPOINT(ST_GEOGPOINT(-3.7038, 40.4168), 4);
-- 84390cbffffffff
SELECT `carto-un-eu`.carto.H3_FROMGEOGPOINT(ST_GEOGPOINT(-3.7038, 40.4168), 4);
-- 84390cbffffffff
SELECT carto.H3_FROMGEOGPOINT(ST_GEOGPOINT(-3.7038, 40.4168), 4);
-- 84390cbffffffff

tip

Additional examples

H3_FROMLONGLAT

H3_FROMLONGLAT(longitude, latitude, resolution)

Description

Returns the H3 cell index that the point belongs to in the required resolution. It will return null on error (resolution out of bounds).

  • longitude: FLOAT64 horizontal coordinate of the map.

  • latitude: FLOAT64 vertical coordinate of the map.

Return type

STRING

Example

SELECT `carto-un`.carto.H3_FROMLONGLAT(-3.7038, 40.4168, 4);
-- 84390cbffffffff
SELECT `carto-un-eu`.carto.H3_FROMLONGLAT(-3.7038, 40.4168, 4);
-- 84390cbffffffff
SELECT carto.H3_FROMLONGLAT(-3.7038, 40.4168, 4);
-- 84390cbffffffff

H3_HEXRING

H3_HEXRING(origin, size)

Description

  • origin: STRING H3 cell index of the origin.

  • size: INT64 size of the ring (distance from the origin).

Return type

ARRAY<STRING>

Example

SELECT `carto-un`.carto.H3_HEXRING('84390cbffffffff', 1);
-- 84392b5ffffffff
-- 84390c9ffffffff
-- 84390c1ffffffff
-- 84390c3ffffffff
-- 84390ddffffffff
-- 84392b7ffffffff
SELECT `carto-un-eu`.carto.H3_HEXRING('84390cbffffffff', 1);
-- 84392b5ffffffff
-- 84390c9ffffffff
-- 84390c1ffffffff
-- 84390c3ffffffff
-- 84390ddffffffff
-- 84392b7ffffffff
SELECT carto.H3_HEXRING('84390cbffffffff', 1);
-- 84392b5ffffffff
-- 84390c9ffffffff
-- 84390c1ffffffff
-- 84390c3ffffffff
-- 84390ddffffffff
-- 84392b7ffffffff

H3_INT_TOSTRING

H3_INT_TOSTRING(index)

Description

Converts the integer representation of the H3 index to the string representation.

  • index: INT64 The H3 cell index.

Return type

STRING

Example

SELECT `carto-un`.carto.H3_INT_TOSTRING(595478781590765567);
-- 84390cbffffffff
SELECT `carto-un-eu`.carto.H3_INT_TOSTRING(595478781590765567);
-- 84390cbffffffff
SELECT carto.H3_INT_TOSTRING(595478781590765567);
-- 84390cbffffffff

H3_ISPENTAGON

H3_ISPENTAGON(index)

Description

Returns true if given H3 index is a pentagon. Returns false otherwise, even on invalid input.

  • index: STRING The H3 cell index.

Return type

BOOLEAN

Example

SELECT `carto-un`.carto.H3_ISPENTAGON('84390cbffffffff');
-- false
SELECT `carto-un-eu`.carto.H3_ISPENTAGON('84390cbffffffff');
-- false
SELECT carto.H3_ISPENTAGON('84390cbffffffff');
-- false
SELECT `carto-un`.carto.H3_ISPENTAGON('8075fffffffffff');
-- true
SELECT `carto-un-eu`.carto.H3_ISPENTAGON('8075fffffffffff');
-- true
SELECT carto.H3_ISPENTAGON('8075fffffffffff');
-- true

H3_ISVALID

H3_ISVALID(index)

Description

Returns true when the given index is a valid H3 index, false otherwise.

  • index: STRING The H3 cell index.

Return type

BOOLEAN

Examples

SELECT `carto-un`.carto.H3_ISVALID('84390cbffffffff');
-- true
SELECT `carto-un-eu`.carto.H3_ISVALID('84390cbffffffff');
-- true
SELECT carto.H3_ISVALID('84390cbffffffff');
-- true
SELECT `carto-un`.carto.H3_ISVALID('1');
-- false
SELECT `carto-un-eu`.carto.H3_ISVALID('1');
-- false
SELECT carto.H3_ISVALID('1');
-- false

H3_KRING

H3_KRING(origin, size)

Description

Returns all cell indexes in a filled hexagonal k-ring centered at the origin in no particular order.

  • origin: STRING H3 cell index of the origin.

  • size: INT64 size of the ring (distance from the origin).

Return type

ARRAY<STRING>

Example

SELECT `carto-un`.carto.H3_KRING('84390cbffffffff', 1);
-- 84390cbffffffff
-- 84390c9ffffffff
-- 84390c1ffffffff
-- 84390c3ffffffff
-- 84390ddffffffff
-- 84392b7ffffffff
-- 84392b5ffffffff
SELECT `carto-un-eu`.carto.H3_KRING('84390cbffffffff', 1);
-- 84390cbffffffff
-- 84390c9ffffffff
-- 84390c1ffffffff
-- 84390c3ffffffff
-- 84390ddffffffff
-- 84392b7ffffffff
-- 84392b5ffffffff
SELECT carto.H3_KRING('84390cbffffffff', 1);
-- 84390cbffffffff
-- 84390c9ffffffff
-- 84390c1ffffffff
-- 84390c3ffffffff
-- 84390ddffffffff
-- 84392b7ffffffff
-- 84392b5ffffffff

Additional examples

H3_KRING_DISTANCES

H3_KRING_DISTANCES(origin, size)

Description

Returns all cell indexes and their distances in a filled hexagonal k-ring centered at the origin in no particular order.

  • origin: STRING H3 cell index of the origin.

  • size: INT64 size of the ring (distance from the origin).

Return type

ARRAY<STRUCT<index STRING, distance INT64>>

Example

SELECT `carto-un`.carto.H3_KRING_DISTANCES('84390cbffffffff', 1);
-- {"index": "84390cbffffffff", "distance": "0"}
-- {"index": "84390c9ffffffff", "distance": "1"}
-- {"index": "84390c1ffffffff", "distance": "1"}
-- {"index": "84390c3ffffffff", "distance": "1"}
-- {"index": "84390ddffffffff", "distance": "1"}
-- {"index": "84392b7ffffffff", "distance": "1"}
-- {"index": "84392b5ffffffff", "distance": "1"}
SELECT `carto-un-eu`.carto.H3_KRING_DISTANCES('84390cbffffffff', 1);
-- {"index": "84390cbffffffff", "distance": "0"}
-- {"index": "84390c9ffffffff", "distance": "1"}
-- {"index": "84390c1ffffffff", "distance": "1"}
-- {"index": "84390c3ffffffff", "distance": "1"}
-- {"index": "84390ddffffffff", "distance": "1"}
-- {"index": "84392b7ffffffff", "distance": "1"}
-- {"index": "84392b5ffffffff", "distance": "1"}
SELECT carto.H3_KRING_DISTANCES('84390cbffffffff', 1);
-- {"index": "84390cbffffffff", "distance": "0"}
-- {"index": "84390c9ffffffff", "distance": "1"}
-- {"index": "84390c1ffffffff", "distance": "1"}
-- {"index": "84390c3ffffffff", "distance": "1"}
-- {"index": "84390ddffffffff", "distance": "1"}
-- {"index": "84392b7ffffffff", "distance": "1"}
-- {"index": "84392b5ffffffff", "distance": "1"}

H3_POLYFILL

H3_POLYFILL(geog, resolution)

Description

  • geog: GEOGRAPHY representing the shape to cover.

  • You want to provide the minimum covering set of a Polygon, MultiPolygon.

  • The input geography type is Point, MultiPoint, LineString, MultiLineString.

Return type

ARRAY<STRING>

Examples

SELECT `carto-un`.carto.H3_POLYFILL(
  ST_GEOGFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
  9
);
-- [89390cb1b4bffff]
SELECT `carto-un-eu`.carto.H3_POLYFILL(
  ST_GEOGFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
  9
);
-- [89390cb1b4bffff]
SELECT carto.H3_POLYFILL(
  ST_GEOGFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
  9
);
-- [89390cb1b4bffff]
SELECT h3
FROM UNNEST(`carto-un`.carto.H3_POLYFILL(
  ST_GEOGFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
  9
)) AS h3;
-- 89390cb1b4bffff
SELECT h3
FROM UNNEST(`carto-un-eu`.carto.H3_POLYFILL(
  ST_GEOGFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
  9
)) AS h3;
-- 89390cb1b4bffff
SELECT h3
FROM UNNEST(carto.H3_POLYFILL(
  ST_GEOGFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
  9
)) AS h3;
-- 89390cb1b4bffff
SELECT h3
FROM <project>.<dataset>.<table>,
  UNNEST(`carto-un`.carto.H3_POLYFILL(geog, 9)) AS h3;
SELECT h3
FROM <project>.<dataset>.<table>,
  UNNEST(`carto-un-eu`.carto.H3_POLYFILL(geog, 9)) AS h3;
SELECT h3
FROM <project>.<dataset>.<table>,
  UNNEST(carto.H3_POLYFILL(geog, 9)) AS h3;

Additional examples

H3_POLYFILL_MODE

H3_POLYFILL_MODE(geog, resolution, mode)

Description

Returns an array of H3 cell indexes contained in the given geography at a requested resolution. Containment is determined by the mode: center, intersects, contains.

  • geog: GEOGRAPHY representing the shape to cover.

  • mode: STRING

    • intersects returns the indexes of the H3 cells that intersect the input geography. The resulting H3 set will completely cover the input geography (point, line, polygon).

    • contains returns the indexes of the H3 cells that are entirely contained inside the input geography (polygon). This mode is not compatible with points or lines.

Mode center:

Mode intersects:

Mode contains:

Return type

ARRAY<STRING>

Examples

SELECT `carto-un`.carto.H3_POLYFILL_MODE(
  ST_GEOGFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
  9, 'intersects'
);
-- [89390cb1b5bffff, 89390ca34b3ffff, 89390ca3487ffff, 89390ca3497ffff, 89390cb1b4bffff, 89390cb1b4fffff]
SELECT `carto-un-eu`.carto.H3_POLYFILL_MODE(
  ST_GEOGFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
  9, 'intersects'
);
-- [89390cb1b5bffff, 89390ca34b3ffff, 89390ca3487ffff, 89390ca3497ffff, 89390cb1b4bffff, 89390cb1b4fffff]
SELECT carto.H3_POLYFILL_MODE(
  ST_GEOGFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
  9, 'intersects'
);
-- [89390cb1b5bffff, 89390ca34b3ffff, 89390ca3487ffff, 89390ca3497ffff, 89390cb1b4bffff, 89390cb1b4fffff]
SELECT h3
FROM UNNEST(`carto-un`.carto.H3_POLYFILL_MODE(
  ST_GEOGFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
  9, 'intersects'
)) AS h3;
-- 89390cb1b5bffff
-- 89390ca34b3ffff
-- 89390ca3487ffff
-- 89390ca3497ffff
-- 89390cb1b4bffff
-- 89390cb1b4fffff
SELECT h3
FROM UNNEST(`carto-un-eu`.carto.H3_POLYFILL_MODE(
  ST_GEOGFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
  9, 'intersects'
)) AS h3;
-- 89390cb1b5bffff
-- 89390ca34b3ffff
-- 89390ca3487ffff
-- 89390ca3497ffff
-- 89390cb1b4bffff
-- 89390cb1b4fffff
SELECT h3
FROM UNNEST(carto.H3_POLYFILL_MODE(
  ST_GEOGFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))'),
  9, 'intersects'
)) AS h3;
-- 89390cb1b5bffff
-- 89390ca34b3ffff
-- 89390ca3487ffff
-- 89390ca3497ffff
-- 89390cb1b4bffff
-- 89390cb1b4fffff
SELECT h3
FROM <project>.<dataset>.<table>,
  UNNEST(`carto-un`.carto.H3_POLYFILL_MODE(geog, 9, 'intersects')) AS h3;
SELECT h3
FROM <project>.<dataset>.<table>,
  UNNEST(`carto-un-eu`.carto.H3_POLYFILL_MODE(geog, 9, 'intersects')) AS h3;
SELECT h3
FROM <project>.<dataset>.<table>,
  UNNEST(carto.H3_POLYFILL_MODE(geog, 9, 'intersects')) AS h3;

H3_POLYFILL_TABLE (BETA)

H3_POLYFILL_TABLE(input_query, resolution, mode, output_table)

Description

Returns a table with the H3 cell indexes contained in the given geography at a requested resolution. Containment is determined by the mode: center, intersects, contains. All the attributes except the geography will be included in the output table, clustered by the h3 column.

  • input_query: STRING input data to polyfill. It must contain a column geom with the shape to cover. Additionally, other columns can be included.

  • mode: STRING

    • intersects returns the indexes of the H3 cells that intersect the input geography. The resulting H3 set will completely cover the input geography (point, line, polygon).

    • contains returns the indexes of the H3 cells that are entirely contained inside the input geography (polygon). This mode is not compatible with points or lines.

  • output_table: STRING name of the output table to store the results of the polyfill.

Mode center:

Mode intersects:

Mode contains:

Output

The results are stored in the table named <output_table>, which contains the following columns:

  • h3: STRING the geometry of the considered point.

  • The rest of columns included in input_query except geom.

Examples

CALL `carto-un`.carto.H3_POLYFILL_TABLE(
  "SELECT ST_GEOGFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))') AS geom",
  9, 'intersects',
  '<project>.<dataset>.<output_table>'
);
-- The table `<project>.<dataset>.<output_table>` will be created
-- with column: h3
CALL `carto-un-eu`.carto.H3_POLYFILL_TABLE(
  "SELECT ST_GEOGFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))') AS geom",
  9, 'intersects',
  '<project>.<dataset>.<output_table>'
);
-- The table `<project>.<dataset>.<output_table>` will be created
-- with column: h3
CALL carto.H3_POLYFILL_TABLE(
  "SELECT ST_GEOGFROMTEXT('POLYGON ((-3.71219873428345 40.413365349070865, -3.7144088745117 40.40965661286395, -3.70659828186035 40.409525904775634, -3.71219873428345 40.413365349070865))') AS geom",
  9, 'intersects',
  '<project>.<dataset>.<output_table>'
);
-- The table `<project>.<dataset>.<output_table>` will be created
-- with column: h3
CALL `carto-un`.carto.H3_POLYFILL_TABLE(
  'SELECT geom, name, value FROM `<project>.<dataset>.<table>`',
  9, 'center',
  '<project>.<dataset>.<output_table>'
);
-- The table `<project>.<dataset>.<output_table>` will be created
-- with columns: h3, name, value
CALL `carto-un-eu`.carto.H3_POLYFILL_TABLE(
  'SELECT geom, name, value FROM `<project>.<dataset>.<table>`',
  9, 'center',
  '<project>.<dataset>.<output_table>'
);
-- The table `<project>.<dataset>.<output_table>` will be created
-- with columns: h3, name, value
CALL carto.H3_POLYFILL_TABLE(
  'SELECT geom, name, value FROM `<project>.<dataset>.<table>`',
  9, 'center',
  '<project>.<dataset>.<output_table>'
);
-- The table `<project>.<dataset>.<output_table>` will be created
-- with columns: h3, name, value

H3_RESOLUTION

H3_RESOLUTION(index)

Description

Returns the H3 cell resolution as an integer. It will return null on error (invalid input).

  • index: STRING The H3 cell index.

Return type

INT64

Example

SELECT `carto-un`.carto.H3_RESOLUTION('84390cbffffffff');
-- 4
SELECT `carto-un-eu`.carto.H3_RESOLUTION('84390cbffffffff');
-- 4
SELECT carto.H3_RESOLUTION('84390cbffffffff');
-- 4

H3_STRING_TOINT

H3_STRING_TOINT(index)

Description

Converts the string representation of the H3 index to the integer representation.

  • index: STRING The H3 cell index.

Return type

INT64

Example

SELECT `carto-un`.carto.H3_STRING_TOINT('84390cbffffffff');
-- 595478781590765567
SELECT `carto-un-eu`.carto.H3_STRING_TOINT('84390cbffffffff');
-- 595478781590765567
SELECT carto.H3_STRING_TOINT('84390cbffffffff');
-- 595478781590765567

H3_TOCHILDREN

H3_TOCHILDREN(index, resolution)

Description

Returns an array with the H3 indexes of the children/descendents of the given hexagon at the given resolution.

  • index: STRING The H3 cell index.

Return type

ARRAY<STRING>

Example

SELECT `carto-un`.carto.H3_TOCHILDREN('83390cfffffffff', 4);
-- 84390c1ffffffff
-- 84390c3ffffffff
-- 84390c5ffffffff
-- 84390c7ffffffff
-- 84390c9ffffffff
-- 84390cbffffffff
-- 84390cdffffffff
SELECT `carto-un-eu`.carto.H3_TOCHILDREN('83390cfffffffff', 4);
-- 84390c1ffffffff
-- 84390c3ffffffff
-- 84390c5ffffffff
-- 84390c7ffffffff
-- 84390c9ffffffff
-- 84390cbffffffff
-- 84390cdffffffff
SELECT carto.H3_TOCHILDREN('83390cfffffffff', 4);
-- 84390c1ffffffff
-- 84390c3ffffffff
-- 84390c5ffffffff
-- 84390c7ffffffff
-- 84390c9ffffffff
-- 84390cbffffffff
-- 84390cdffffffff

H3_TOPARENT

H3_TOPARENT(index, resolution)

Description

Returns the H3 cell index of the parent of the given hexagon at the given resolution.

  • index: STRING The H3 cell index.

Return type

STRING

Example

SELECT `carto-un`.carto.H3_TOPARENT('84390cbffffffff', 3);
-- 83390cfffffffff
SELECT `carto-un-eu`.carto.H3_TOPARENT('84390cbffffffff', 3);
-- 83390cfffffffff
SELECT carto.H3_TOPARENT('84390cbffffffff', 3);
-- 83390cfffffffff

H3_UNCOMPACT

H3_UNCOMPACT(indexArray, resolution)

Description

  • indexArray: ARRAY<STRING> of H3 cell indices.

Return type

ARRAY<STRING>

Example

SELECT `carto-un`.carto.H3_UNCOMPACT(['83390cfffffffff'], 5);
-- 85390ca3fffffff
-- 85390ca7fffffff
-- 85390cabfffffff
-- 85390caffffffff
-- 85390cb3fffffff
-- 85390cb7fffffff
-- 85390cbbfffffff
SELECT `carto-un-eu`.carto.H3_UNCOMPACT(['83390cfffffffff'], 5);
-- 85390ca3fffffff
-- 85390ca7fffffff
-- 85390cabfffffff
-- 85390caffffffff
-- 85390cb3fffffff
-- 85390cb7fffffff
-- 85390cbbfffffff
SELECT carto.H3_UNCOMPACT(['83390cfffffffff'], 5);
-- 85390ca3fffffff
-- 85390ca7fffffff
-- 85390cabfffffff
-- 85390caffffffff
-- 85390cb3fffffff
-- 85390cb7fffffff
-- 85390cbbfffffff

If you want the distance in meters use between the cells () or their centroid.

resolution: INT64 number between 0 and 15 with the .

If you want the cells covered by a POLYGON see .

resolution: INT64 number between 0 and 15 with the .

Returns all cell indexes in a hollow hexagonal ring centered at the origin in no particular order. Unlike , this function will throw an exception if there is a pentagon anywhere in the ring.

Returns an array of H3 cell indexes contained in the given geography (Polygon, MultiPolygon) at a requested resolution. Containment is determined by the cells' center. This function is equivalent to with mode center.

resolution: INT64 level of detail. The value must be between 0 and 15 ().

Use with mode intersects in the following cases:

resolution: INT64 level of detail. The value must be between 0 and 15 ().

center returns the indexes of the H3 cells which centers intersect the input geography (polygon). The resulting H3 set does not fully cover the input geography, however, this is significantly faster that the other modes. This mode is not compatible with points or lines. Equivalent to .

resolution: INT64 level of detail. The value must be between 0 and 15 ().

center returns the indexes of the H3 cells which centers intersect the input geography (polygon). The resulting H3 set does not fully cover the input geography, however, this is significantly faster that the other modes. This mode is not compatible with points or lines. Equivalent to .

resolution: INT64 number between 0 and 15 with the .

resolution: INT64 number between 0 and 15 with the .

Returns an array with the H3 indexes of a set of hexagons of the same resolution that represent the same area as the input hexagons.

resolution: INT64 number between 0 and 15 with the .

This project has received funding from the research and innovation programme under grant agreement No 960401.

H3
h3geo
Spatial Indexes section
An H3 grid of Starbucks locations and simple cannibalization analysis
Opening a new Pizza Hut location in Honolulu
H3 resolution
An H3 grid of Starbucks locations and simple cannibalization analysis
H3 resolution
An H3 grid of Starbucks locations and simple cannibalization analysis
H3 resolution table
Opening a new Pizza Hut location in Honolulu
H3 resolution table
H3 resolution table
H3 resolution
H3 resolution
H3 resolution
ST_DISTANCE
H3_BOUNDARY
H3_POLYFILL
H3_KRING
H3_POLYFILL_MODE
H3_POLYFILL_MODE
H3_POLYFILL
H3_POLYFILL
compacted
European Union’s Horizon 2020