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.

%pip install "databricks-connect>=17.1.0" --quiet
%restart_python

%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;

describe tmp_geometries
-- Returns:
-- col_name data_type   comment
-- geometry geometry(4326)  null
-- name string  null

Note the geometry(4326) type above.

Note

Make sure you are using Serverless environment version 4+, or else outputting GEOMETRY/GEOGRAPHY types directly (without e.g. st_asewkt()) will not work.

%sql
from
  tmp_geometries
-- Returns:
-- geometry name
-- SRID=4326;POLYGON((-80.1935973 25.7741566,-64.7563086 32.3040273,-66.1166669 18.4653003,-80.1935973 25.7741566)) Bermuda Triangle
-- SRID=4326;POINT(5.3872035084137675 52.15517230119224)    Onze Lieve Vrouwetoren
-- SRID=4326;POINT(0 0) Null Island

(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

Cleanup

%sql
 -- drop table tmp_geometries