SQL API Reference

GeoBrix functions can be used directly in SQL after registration, providing a familiar interface for SQL users.

Registration

SQL functions must be registered via Python or Scala before use:

Via Python

from databricks.labs.gbx.rasterx import functions as rx
from databricks.labs.gbx.gridx.bng import functions as bx
from databricks.labs.gbx.vectorx.jts.legacy import functions as vx

rx.register(spark)
bx.register(spark)
vx.register(spark)

Via Scala

import com.databricks.labs.gbx.rasterx.{functions => rx}
import com.databricks.labs.gbx.gridx.bng.{functions => bx}
import com.databricks.labs.gbx.vectorx.jts.legacy.{functions => vx}

rx.register(spark)
bx.register(spark)
vx.register(spark)

Function Naming

All GeoBrix SQL functions use the gbx_ prefix:

• RasterX: gbx_rst_*
• GridX/BNG: gbx_bng_*
• VectorX: gbx_st_*

Listing Functions

-- List all GeoBrix functions
SHOW FUNCTIONS LIKE 'gbx_*';

-- List RasterX functions
SHOW FUNCTIONS LIKE 'gbx_rst_*';

-- List GridX functions
SHOW FUNCTIONS LIKE 'gbx_bng_*';

-- List VectorX functions
SHOW FUNCTIONS LIKE 'gbx_st_*';

Example output

+----------------------+
|function              |
+----------------------+
|gbx_rst_asformat      |
|gbx_rst_avg           |
|gbx_rst_bandmetadata  |
|gbx_rst_boundingbox   |
|...                   |
|gbx_bng_cellarea      |
|gbx_bng_pointascell    |
|...                   |
|gbx_st_legacyaswkb    |
|...                   |
+----------------------+

Describing Functions

-- Get function details
DESCRIBE FUNCTION gbx_rst_boundingbox;

-- Get extended information
DESCRIBE FUNCTION EXTENDED gbx_rst_boundingbox;

Example output

Function: gbx_rst_boundingbox
Type: SCALAR
Input: (tile BINARY)
Returns: STRUCT<...>

RasterX SQL Functions

Reading and Querying Rasters

SQL_READ_AND_QUERY_RASTERS = f"""-- Read rasters (use your sample data path)
CREATE OR REPLACE TEMP VIEW rasters AS
SELECT * FROM gdal.`{SAMPLE_NYC_RASTERS}`;

-- Extract metadata
SELECT
    path,
    gbx_rst_boundingbox(tile) as bbox,
    gbx_rst_width(tile) as width,
    gbx_rst_height(tile) as height,
    gbx_rst_numbands(tile) as num_bands,
    gbx_rst_metadata(tile) as metadata
FROM rasters;"""

Example output

+--------------------+------------------+-----+------+---------+--------+
|path                |bbox              |width|height|num_bands|metadata|
+--------------------+------------------+-----+------+---------+--------+
|.../nyc_sentinel2...|POLYGON ((-74....)|10980|10980 |1        |{...}   |
+--------------------+------------------+-----+------+---------+--------+

Filtering Rasters

-- Filter by dimensions
SELECT *
FROM rasters
WHERE gbx_rst_width(tile) > 1000
  AND gbx_rst_height(tile) > 1000;

-- Filter by band count
SELECT *
FROM rasters
WHERE gbx_rst_numbands(tile) >= 3;

Example output

+--------------------+----+
|path                |tile|
+--------------------+----+
|...                 |... |
+--------------------+----+

Raster Transformations

-- Clip raster (geometry as WKT; GeoBrix does not accept st_geomfromtext)
SELECT
    path,
    gbx_rst_clip(tile, 'POLYGON((-122 37, -122 38, -121 38, -121 37, -122 37))', true) as clipped_tile
FROM rasters;

-- Create raster catalog
CREATE OR REPLACE TABLE raster_catalog AS
SELECT
    path,
    gbx_rst_boundingbox(tile) as bounds,
    gbx_rst_width(tile) as width,
    gbx_rst_height(tile) as height,
    gbx_rst_numbands(tile) as bands,
    gbx_rst_metadata(tile) as metadata
FROM rasters;

Example output

-- Clip: same columns with clipped_tile
-- Catalog table created; query returns path, bounds, width, height, bands, metadata
+--------------------+------------------+-----+------+-----+--------+
|path                |bounds            |width|height|bands|metadata|
+--------------------+------------------+-----+------+-----+--------+
|...                 |POLYGON ((...))   |10980|10980 |1    |{...}   |
+--------------------+------------------+-----+------+-----+--------+

GridX SQL Functions (BNG)

Cell Operations

-- Calculate cell area (returns square kilometres)
SELECT gbx_bng_cellarea('TQ') as area_km2;

-- Example with full cell id
SELECT
    'TQ3080' as grid,
    gbx_bng_cellarea('TQ3080') as area_km2;

Example output

+----------+
|area_km2  |
+----------+
|1.0       |
+----------+

+------+----------+
|grid |area_km2  |
+------+----------+
|TQ3080|1.0       |
+------+----------+

Point to Cell Conversion

Use gbx_bng_pointascell with WKT or WKB for the point; do not use st_point() (GeoBrix does not accept DBR native geometry).

-- Convert points to BNG cells (point as WKT; GeoBrix does not accept st_point)
CREATE OR REPLACE TEMP VIEW uk_points_bng AS
SELECT
    location_id,
    latitude,
    longitude,
    gbx_bng_pointascell(concat('POINT(', cast(longitude as string), ' ', cast(latitude as string), ')'), 1000) as bng_cell_1km,
    gbx_bng_pointascell(concat('POINT(', cast(longitude as string), ' ', cast(latitude as string), ')'), 100) as bng_cell_100m
FROM uk_locations;

SELECT * FROM uk_points_bng;

Example output

+-----------+----------+---------+------------+-------------+
|location_id|latitude  |longitude|bng_cell_1km|bng_cell_100m|
+-----------+----------+---------+------------+-------------+
|1          |51.5074   |-0.1278  |TQ 31 SW    |TQ 308 105   |
|...        |...       |...      |...         |...          |
+-----------+----------+---------+------------+-------------+

Spatial Aggregation

-- Aggregate by BNG cell (point as WKT; GeoBrix does not accept st_point)
CREATE OR REPLACE TABLE bng_aggregated AS
SELECT
    gbx_bng_pointascell(concat('POINT(', cast(longitude as string), ' ', cast(latitude as string), ')'), 1000) as bng_cell,
    COUNT(*) as point_count,
    AVG(temperature) as avg_temp,
    MAX(temperature) as max_temp,
    MIN(temperature) as min_temp
FROM weather_stations
WHERE country = 'GB'
GROUP BY bng_cell;

SELECT * FROM bng_aggregated ORDER BY point_count DESC LIMIT 10;

Example output

+----------+-----------+---------+---------+---------+----------+
|bng_cell  |point_count|avg_temp |max_temp |min_temp |...       |
+----------+-----------+---------+---------+---------+----------+
|TQ 31 SW  |42        |14.2     |28.1     |0.5      |...        |
|...       |...       |...      |...      |...      |...        |
+----------+-----------+---------+---------+---------+----------+

Multi-Resolution Analysis

-- Analyze at multiple resolutions (location column must be WKT or WKB)
CREATE OR REPLACE VIEW multi_resolution AS
SELECT
    location_id,
    gbx_bng_pointascell(location, 10000) as bng_10km,
    gbx_bng_pointascell(location, 1000) as bng_1km,
    gbx_bng_pointascell(location, 100) as bng_100m
FROM locations;

-- Count by resolution
SELECT '10km' as resolution, COUNT(DISTINCT bng_10km) as cell_count FROM multi_resolution
UNION ALL
SELECT '1km', COUNT(DISTINCT bng_1km) FROM multi_resolution
UNION ALL
SELECT '100m', COUNT(DISTINCT bng_100m) FROM multi_resolution;

Example output

+-----------+----------+-----+
|resolution |cell_count|
+-----------+----------+-----+
|10km       |...       |
|1km        |...       |
|100m       |...       |
+-----------+----------+-----+

VectorX SQL Functions

Legacy Geometry Conversion

Databricks Runtime integration

This example uses st_geomfromwkb and requires Databricks Runtime 17.1+. The example and test live under docs/tests-dbr/.

-- Convert legacy Mosaic geometries
CREATE OR REPLACE TEMP VIEW converted_geometries AS
SELECT
    feature_id,
    properties,
    gbx_st_legacyaswkb(mosaic_geom) as wkb_geom,
    st_geomfromwkb(gbx_st_legacyaswkb(mosaic_geom)) as geometry
FROM legacy_mosaic_table;

SELECT * FROM converted_geometries;

Example output

+----------+----------+--------+--------------------+
|feature_id|properties|wkb_geom|geometry            |
+----------+----------+--------+--------------------+
|1         |{...}     |[BINARY]|POLYGON ((...))     |
|...       |...       |...     |...                 |
+----------+----------+--------+--------------------+

Reading Data with SQL

Databricks Runtime integration

This example uses st_geomfromwkb, st_area, and st_centroid and requires Databricks Runtime 17.1+. The example and test live under docs/tests-dbr/.

-- Read shapefile (use your path)
CREATE OR REPLACE TEMP VIEW shapes AS
SELECT * FROM shapefile_ogr.`/Volumes/main/default/geobrix_samples/geobrix-examples/nyc/subway/nyc_subway.shp.zip`;

-- Convert geometry
CREATE OR REPLACE VIEW shapes_geom AS
SELECT
    *,
    st_geomfromwkb(geom_0) as geometry
FROM shapes;

SELECT
    name,
    st_area(geometry) as area,
    st_centroid(geometry) as center
FROM shapes_geom;

Example output

+----+--------+--------------------+
|name|area    |center              |
+----+--------+--------------------+
|... |12345.67|POINT (...)         |
|... |...     |...                 |
+----+--------+--------------------+

Next Steps

• Python API Reference
• Scala API Reference
• Examples
• Package Documentation