4  Storing spatial data in Delta Lake as GEOMETRY type

You can store geometry or geography data in a Delta Lake table in a GEOMETRY column. See also GEOMETRY_vs_GEOGRAPHY.

Note

As of 2025-08-03, the GEOMETRY and GEOGRAPHY types are not available yet in Serverless Compute and you need a DBR 17.1+ cluster to use this feature. This means that this is also not available in the Databricks Free Edition. This limitation is expected to be lifted in the very near future.

%sql
create or replace table tmp_geometries as
select
  st_point(0, 0, 4326) as geometry,
  "Null Island" as name
union all
select
  st_transform(st_point(155000, 463000, 28992), 4326) as geometry,
  "Onze Lieve Vrouwetoren" as name
union all
select
  st_makepolygon(st_makeline(array(
    st_point(-80.1935973, 25.7741566, 4326),
    st_point(-64.7563086, 32.3040273, 4326),
    st_point(-66.1166669, 18.4653003, 4326),
    st_point(-80.1935973, 25.7741566, 4326)
  ))) as geometry,
  "Bermuda Triangle" as name;

select
  *
from
  geometries
-- Returns:

-- _sqldf:pyspark.sql.connect.dataframe.DataFrame
-- geometry:geometry(SRID:ANY)
-- name:string

-- geometry name
-- SRID=4326;POINT(0 0) Null Island
-- SRID=28992;POINT(155000 463000)  Onze Lieve Vrouwetoren
-- SRID=4326;POLYGON((-80.1935973 25.7741566,-64.7563086 32.3040273,-66.1166669 18.4653003,-80.1935973 25.7741566)) Bermuda Triangle

(Note that you could mix multiple SRID’s in one GEOMETRY column inside a Spark Dataframe, including inside a view, but must have a single SRID in a table.)

Let’s calculate the distance in kilometers (on the surface of the spheroid Earth) between the two point examples above. st_distancespheroid expects lat/lon, so we’ll first convert everything to SRID 4326 – Null Island already is though.

%sql
with geoms_lonlat as (
  select
    st_transform(geometry, 4326) geom_lonlat,
    name
  from
    tmp_geometries
),
nullisland as (
  select
    *
  from
    geoms_lonlat
  where
    name = "Null Island"
),
onzelieve as (
  select
    *
  from
    geoms_lonlat
  where
    name = "Onze Lieve Vrouwetoren"
)
select
  st_distancesphere(nullisland.geom_lonlat, onzelieve.geom_lonlat) / 1e3 as distance_kms
from
  nullisland,
  onzelieve
-- Returns:
-- distance_kms
-- 5821.233209229107

The GEOGRAPHY type also offers further functions that take lon/lat as input and produce meters as output, such as st_area. So let’s calculate the size of the Bermuda Triangle in square kms:

%sql
with bermuda_geog as (
  select
    try_cast(st_setsrid(geometry, 4326) as geography(4326)) geography
  from
    tmp_geometries
  where
    name = 'Bermuda Triangle'
)
select
  st_area(geography) / 1e6 area_km2s
from
  bermuda_geog
-- Returns:
-- area_km2s
-- 1144371.82556931

4.1 Cleanup

%sql
-- drop table tmp_geometries