5  Storing spatial data in Delta Lake as GEOGRAPHY type

You can store geography data in a Delta Lake table in a GEOGRAPHY 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_geographies as
select
  st_point(0, 0, 4326)::geography(4326) as geography,
  "Null Island" as name
union all
select
  st_transform(st_point(155000, 463000, 28992), 4326)::geography(4326) as geography,
  "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)
      )
    )
  )::geography(4326) as geography,
  "Bermuda Triangle" as name;

select
  *
from
  geographies
-- Returns:

-- _sqldf:pyspark.sql.connect.dataframe.DataFrame
-- geography:geography(OGC:CRS84, SPHERICAL)
-- 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

Let’s calculate the distance in kilometers (on the surface of the spheroid Earth) between the two point examples above. st_distancespheroid expects GEOMETRIES of lat/lon, so we’ll need to cast.

%sql
with geoms_lonlat as (
  select
    geography::geometry(4326) geom_lonlat,
    name
  from
    tmp_geographies
),
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
select
  st_area(geography) / 1e6 area_km2s
  from
    geographies
  where
    name = 'Bermuda Triangle'
-- Returns:
-- area_km2s
-- 1144371.82556931

5.1 Cleanup

%sql
-- drop table tmp_geographies