Geometry Support in Havasu

Havasu supports the GEOMETRY and GEOGRAPHY data types and allows users to use the spatial functions in WherobotsDB for manipulating them. This document describes how to create, write, and query tables with geometry and geography columns.

Readable as geometry by other engines

The geometry type is recorded in the table schema and in the Parquet files, so an Apache Iceberg engine outside Wherobots that supports geospatial types sees a geometry column instead of a binary column it has to decode itself.

One CRS per column, enforced

A column carries exactly one Coordinate Reference System (CRS). A value whose Spatial Reference Identifier (SRID) disagrees with it is rejected instead of being stored under the wrong CRS.

org_catalog throughout this page is an example catalog name. Replace it with the name of the catalog you are writing to.

Creating Table with Geometry Column

Besides the primitive types supported by Apache Iceberg, Havasu has a GEOMETRY data type to represent geospatial data. Declare the column with its CRS in parentheses, as an SRID:

Python
Scala
Java

sedona.sql("""
CREATE TABLE IF NOT EXISTS org_catalog.test_db.test_table (
    id bigint,
    data string,
    geom geometry(4326)
) USING iceberg
PARTITIONED BY (bucket(4, id))""")

sedona.sql("""
CREATE TABLE IF NOT EXISTS org_catalog.test_db.test_table (
    id bigint,
    data string,
    geom geometry(4326)
) USING iceberg
PARTITIONED BY (bucket(4, id))""")

sedona.sql(
    "CREATE TABLE IF NOT EXISTS org_catalog.test_db.test_table (" +
    "id bigint," +
    "data string," +
    "geom geometry(4326)" +
    ") USING iceberg " +
    "PARTITIONED BY (bucket(4, id))"
);

This will create an empty table partitioned by the bucketed value of id, with a geometry column in SRID 4326, which is World Geodetic System 1984 (WGS84) longitude and latitude. A geometry column requires a declared CRS: write geometry(4326), not geometry.

Error: bare GEOMETRY in a column definition does not declare a CRS

The column was declared as geometry with no CRS in parentheses. Add the SRID:

Operation not allowed: bare GEOMETRY in a column definition does not declare a CRS:
with spark.sql.geospatial.enabled=true it would silently create a Havasu-legacy column
with no CRS enforcement. Declare a CRS (e.g. GEOMETRY(4326)) to create an Iceberg V3
native column, or write the table from a Sedona GeometryUDT DataFrame if a Havasu-legacy
column is intended.

We can inspect the table schema using DESCRIBE command:

Python
Scala
Java

sedona.sql("DESCRIBE TABLE org_catalog.test_db.test_table").show()

sedona.sql("DESCRIBE TABLE org_catalog.test_db.test_table").show()

sedona.sql("DESCRIBE TABLE org_catalog.test_db.test_table").show();

+--------------+-------------+-------+
|      col_name|    data_type|comment|
+--------------+-------------+-------+
|            id|       bigint|   NULL|
|          data|       string|   NULL|
|          geom|     geometry|   NULL|
|              |             |       |
|# Partitioning|             |       |
|        Part 0|bucket(4, id)|       |
+--------------+-------------+-------+

DESCRIBE TABLE shows the type as geometry without the CRS. To see the CRS, run SHOW CREATE TABLE, which renders the column as geom GEOMETRY(4326):

Python
Scala
Java

print(sedona.sql("SHOW CREATE TABLE org_catalog.test_db.test_table").collect()[0][0])

println(sedona.sql("SHOW CREATE TABLE org_catalog.test_db.test_table").collect()(0).getString(0))

System.out.println(sedona.sql("SHOW CREATE TABLE org_catalog.test_db.test_table").collectAsList().get(0).getString(0));

CREATE TABLE org_catalog.test_db.test_table (
id BIGINT,
data STRING,
geom GEOMETRY(4326))
USING iceberg
PARTITIONED BY (bucket(4, id))
...

The statement SHOW CREATE TABLE returns recreates an equivalent table, so it is also the way to clone the schema of an existing table.

A geometry column created without a CRS, which is how tables were created before geometry(<srid>) was available, renders as geom GEOMETRY with no parentheses in SHOW CREATE TABLE. DESCRIBE TABLE shows geometry for both kinds of column. To see a table’s Apache Iceberg format version, run SHOW TBLPROPERTIES org_catalog.test_db.test_table ('format-version').Tables created without a CRS keep working: reads, spatial filter push-down, and every ST_ function behave the same. They do not check the SRID of written values, and other Iceberg engines read the column as binary. Wherobots upgrades the read-only public tables it manages, such as those in the wherobots_open_data catalog, automatically. To upgrade your own tables, see Upgrade Existing Geometry Tables.

Writing Data

INSERT INTO

User can insert data into a Havasu table using INSERT INTO table_name VALUES. Every value has to carry the SRID of the column:

Python
Scala
Java

sedona.sql("""
INSERT INTO org_catalog.test_db.test_table
VALUES (1, 'a', ST_SetSRID(ST_GeomFromText('POINT (1 2)'), 4326)),
       (2, 'b', ST_SetSRID(ST_Point(2, 3), 4326))
""")

sedona.sql("""
INSERT INTO org_catalog.test_db.test_table
VALUES (1, 'a', ST_SetSRID(ST_GeomFromText('POINT (1 2)'), 4326)),
       (2, 'b', ST_SetSRID(ST_Point(2, 3), 4326))
""")

sedona.sql(
        "INSERT INTO org_catalog.test_db.test_table " +
        "VALUES (1, 'a', ST_SetSRID(ST_GeomFromText('POINT (1 2)'), 4326)), " +
        "(2, 'b', ST_SetSRID(ST_Point(2, 3), 4326))"
);

Or using INSERT INTO table_name SELECT ... to insert result set of a query into the table:

Python
Scala
Java

sedona.sql("""
INSERT INTO org_catalog.test_db.test_table
SELECT 3, 'c', ST_SetSRID(ST_Point(3, 4), 4326)
""")

sedona.sql("""
INSERT INTO org_catalog.test_db.test_table
SELECT 3, 'c', ST_SetSRID(ST_Point(3, 4), 4326)
""")

sedona.sql(
        "INSERT INTO org_catalog.test_db.test_table " +
        "SELECT 3, 'c', ST_SetSRID(ST_Point(3, 4), 4326)"
);

Matching the column CRS

Geometry constructors such as ST_GeomFromText and ST_Point return SRID 0, which is a distinct CRS rather than “unset”, so a geometry(4326) column rejects it. Which function you need depends on the SRID your values already carry:

Your values	Apply	Effect
SRID 0, from a constructor, from shapely, or from a file with no CRS metadata	`ST_SetSRID(geom, 4326)`	Labels the values. Coordinates are unchanged.
A different CRS, such as SRID 3857	`ST_Transform(geom, 'EPSG:3857', 'EPSG:4326')`	Reprojects the coordinates.
Already SRID 4326	Nothing	Insert as-is.
A `geometry(4326)` column on another table	Nothing	The CRS carries through the read.

ST_SetSRID relabels without touching the coordinates. Applying it to metre coordinates declares them as longitude and latitude, which puts the data in the wrong place. Use ST_Transform when the coordinates are in another CRS.

Error: Cannot write geometry with SRID ...

A value disagrees with the column CRS. The message names the fix:

java.lang.IllegalArgumentException: Cannot write geometry with SRID 0 into a column
with SRID 4326: an Iceberg geometry column stores one CRS for every value. Use
ST_SetSRID to label an unset SRID, or ST_Transform to reproject the values to SRID 4326.

Writing DataFrame to Havasu table

User can write a DataFrame containing geometry data to a Havasu table. The same SRID rule applies to the DataFrame’s values:

Python
Scala
Java

from pyspark.sql.functions import expr

# Create a DataFrame with geometry data
df = sedona.createDataFrame([
    (1, "a", "POINT (1 2)"),
    (2, "b", "POINT (2 3)")
], ["id", "data", "geom"]) \
    .withColumn("geom", expr("ST_SetSRID(ST_GeomFromText(geom), 4326)"))

# Write the DataFrame to a Havasu table
df.writeTo("org_catalog.test_db.test_table").append()

// Create a DataFrame with geometry data
val df = Seq(
    (1, "a", "POINT (1 2)"),
    (2, "b", "POINT (2 3)")
).toDF("id", "data", "geom")
.withColumn("geom", expr("ST_SetSRID(ST_GeomFromText(geom), 4326)"))

// Write the DataFrame to a Havasu table
df.writeTo("org_catalog.test_db.test_table").append()

List<Row> data = Arrays.asList(
    RowFactory.create(1, "a", "POINT (1 2)"),
    RowFactory.create(2, "b", "POINT (2 3)")
);

List<StructField> fields = Arrays.asList(
    DataTypes.createStructField("id", DataTypes.IntegerType, true),
    DataTypes.createStructField("data", DataTypes.StringType, true),
    DataTypes.createStructField("geom", DataTypes.StringType, true)
);

StructType schema = DataTypes.createStructType(fields);

Dataset<Row> df = sedona.createDataFrame(data, schema)
    .withColumn("geom", expr("ST_SetSRID(ST_GeomFromText(geom), 4326)"));

df.writeTo("org_catalog.test_db.test_table").append();

This is semantically equivalent to the INSERT INTO table_name SELECT ... statement.

Creating a table from a query or a DataFrame

CREATE TABLE ... AS SELECT and writeTo(...).create() create a table from the result of a query or a DataFrame. When the geometry comes from a geometry(<srid>) column read from a table, the new column has the same declared CRS. To choose the CRS of the new column yourself, for example for a geometry computed with an ST_ function, create the table with CREATE TABLE first and then write to it with INSERT INTO ... SELECT or writeTo(...).append(), as shown in Working with Geometry Data.

Updating data in Havasu table

Havasu supports UPDATE queries that update matching rows in tables. Update queries accept a filter to match rows to update. Spatial filters are also supported in Havasu.

Python
Scala
Java

sedona.sql("UPDATE org_catalog.test_db.test_table SET data = 'd' WHERE id = 1")

# Use ST_Intersects to update rows that intersect with a polygon
sedona.sql("""
UPDATE org_catalog.test_db.test_table
SET data = 'd'
WHERE ST_Intersects(geom, ST_GeomFromText('POLYGON ((0 0, 0 2, 2 2, 2 0, 0 0))'))
""")

sedona.sql("UPDATE org_catalog.test_db.test_table SET data = 'd' WHERE id = 1")

// Use ST_Intersects to update rows that intersect with a polygon
sedona.sql("""
UPDATE org_catalog.test_db.test_table
SET data = 'd'
WHERE ST_Intersects(geom, ST_GeomFromText('POLYGON ((0 0, 0 2, 2 2, 2 0, 0 0))'))
""")

sedona.sql("UPDATE org_catalog.test_db.test_table SET data = 'd' WHERE id = 1");

// Use ST_Intersects to update rows that intersect with a polygon
sedona.sql(
        "UPDATE org_catalog.test_db.test_table " +
        "SET data = 'd' " +
        "WHERE ST_Intersects(geom, ST_GeomFromText('POLYGON ((0 0, 0 2, 2 2, 2 0, 0 0))'))"
);

Deleting data from Havasu table

Havasu supports DELETE FROM queries to remove data from tables. Delete queries accept a filter to match rows to delete. Spatial filters are also supported in Havasu.

Python
Scala
Java

sedona.sql("DELETE FROM org_catalog.test_db.test_table WHERE id = 1")

# Use ST_Intersects to delete rows that intersect with a polygon
sedona.sql("""
DELETE FROM org_catalog.test_db.test_table
WHERE ST_Intersects(geom, ST_GeomFromText('POLYGON ((0 0, 0 2, 2 2, 2 0, 0 0))'))
""")

sedona.sql("DELETE FROM org_catalog.test_db.test_table WHERE id = 1")

// Use ST_Intersects to delete rows that intersect with a polygon
sedona.sql("""
DELETE FROM org_catalog.test_db.test_table
WHERE ST_Intersects(geom, ST_GeomFromText('POLYGON ((0 0, 0 2, 2 2, 2 0, 0 0))'))
""")

sedona.sql("DELETE FROM org_catalog.test_db.test_table WHERE id = 1");

// Use ST_Intersects to delete rows that intersect with a polygon
sedona.sql(
        "DELETE FROM org_catalog.test_db.test_table " +
        "WHERE ST_Intersects(geom, ST_GeomFromText('POLYGON ((0 0, 0 2, 2 2, 2 0, 0 0))'))"
);

Merging DataFrame into Havasu table using `MERGE INTO`

Havasu supports MERGE INTO by rewriting data files that contain rows that need to be updated in an overwrite commit. The syntax is identical to the open source Apache Iceberg, please refer to Apache Iceberg - MERGE INTO for more information.

Querying Data

User can load data from a Havasu table using sedona.table(...):

Python
Scala
Java

df = sedona.table("org_catalog.test_db.test_table")

val df = sedona.table("org_catalog.test_db.test_table")

Dataset<Row> df = sedona.table("org_catalog.test_db.test_table");

You can apply some configurations for reading the table, such as the split size if you want to read the table into a DataFrame with more partitions:

Python
Scala
Java

df = sedona.read.option('split-size', '1000').table("org_catalog.test_db.test_table")

val df = sedona.read.option("split-size", "1000").table("org_catalog.test_db.test_table")

Dataset<Row> df = sedona.read().option("split-size", "1000").table("org_catalog.test_db.test_table");

You can run spatial range query on the table using Spatial SQL:

Python
Scala
Java

df = sedona.sql("""
SELECT * FROM org_catalog.test_db.test_table
WHERE ST_Intersects(geom, ST_GeomFromText('POLYGON ((0 0, 0 2, 2 2, 2 0, 0 0))'))
""")

val df = sedona.sql("""
SELECT * FROM org_catalog.test_db.test_table
WHERE ST_Intersects(geom, ST_GeomFromText('POLYGON ((0 0, 0 2, 2 2, 2 0, 0 0))'))
""")

Dataset<Row> df = sedona.sql(
        "SELECT * FROM org_catalog.test_db.test_table " +
        "WHERE ST_Intersects(geom, ST_GeomFromText('POLYGON ((0 0, 0 2, 2 2, 2 0, 0 0))'))"
);

Or using the DataFrame API:

Python
Scala
Java

df = sedona.table("org_catalog.test_db.test_table")\
    .where("ST_Intersects(geom, ST_GeomFromText('POLYGON ((0 0, 0 2, 2 2, 2 0, 0 0))'))")

val df = sedona.table("org_catalog.test_db.test_table")
    .where("ST_Intersects(geom, ST_GeomFromText('POLYGON ((0 0, 0 2, 2 2, 2 0, 0 0))'))")

Dataset<Row> df = sedona.table("org_catalog.test_db.test_table")
    .where("ST_Intersects(geom, ST_GeomFromText('POLYGON ((0 0, 0 2, 2 2, 2 0, 0 0))'))");

Users can also load a Havasu table by specifying the name of the data source explicitly using .format("havasu.iceberg"), this will load an isolated table reference that will not automatically refresh tables used by queries.

Python
Scala
Java

df = sedona.read.format("havasu.iceberg").load("org_catalog.test_db.test_table")\
    .where("ST_Intersects(geom, ST_GeomFromText('POLYGON ((0 0, 0 2, 2 2, 2 0, 0 0))'))")

val df = sedona.read.format("havasu.iceberg").load("org_catalog.test_db.test_table")
    .where("ST_Intersects(geom, ST_GeomFromText('POLYGON ((0 0, 0 2, 2 2, 2 0, 0 0))'))")

Dataset<Row> df = sedona.read().format("havasu.iceberg").load("org_catalog.test_db.test_table")
    .where("ST_Intersects(geom, ST_GeomFromText('POLYGON ((0 0, 0 2, 2 2, 2 0, 0 0))'))");

Spatial range queries are very efficient in Havasu. Havasu supports spatial filter based data skipping. This feature allows user to skip reading data files that don’t contain data that satisfy the spatial filter. Please refer to Cluster by geospatial fields for faster queries for more information.

Working with Geometry Data

User can use any ST_ functions provided by WherobotsDB to manipulate the data in a geometry column. For example, user can use ST_Buffer to create a buffer around the geometry column:

Python
Scala
Java

sedona.sql("SELECT ST_Buffer(geom, 0.001) FROM org_catalog.test_db.test_table").show()

sedona.sql("SELECT ST_Buffer(geom, 0.001) FROM org_catalog.test_db.test_table").show()

sedona.sql("SELECT ST_Buffer(geom, 0.001) FROM org_catalog.test_db.test_table").show();

+----------------------+
|st_buffer(geom, 0.001)|
+----------------------+
|  POLYGON ((3.001 4...|
|  POLYGON ((1.001 2...|
|  POLYGON ((2.001 3...|
|  POLYGON ((1.001 2...|
|  POLYGON ((2.001 3...|
+----------------------+

To write the result back to a Havasu table, create the table with its geometry column first, then insert the result of the query. ST_Buffer keeps the SRID of its input, so the buffered values match a geometry(4326) column:

Python
Scala
Java

sedona.sql("""
CREATE TABLE org_catalog.test_db.test_table_buf (
    geom geometry(4326)
) USING iceberg""")

sedona.sql("""
INSERT INTO org_catalog.test_db.test_table_buf
SELECT ST_Buffer(geom, 0.001) FROM org_catalog.test_db.test_table
""")

sedona.sql("""
CREATE TABLE org_catalog.test_db.test_table_buf (
    geom geometry(4326)
) USING iceberg""")

sedona.sql("""
INSERT INTO org_catalog.test_db.test_table_buf
SELECT ST_Buffer(geom, 0.001) FROM org_catalog.test_db.test_table
""")

sedona.sql(
        "CREATE TABLE org_catalog.test_db.test_table_buf (" +
        "geom geometry(4326)" +
        ") USING iceberg"
);

sedona.sql(
        "INSERT INTO org_catalog.test_db.test_table_buf " +
        "SELECT ST_Buffer(geom, 0.001) FROM org_catalog.test_db.test_table"
);

Or call writeTo(...).append() on the resulting DataFrame of the query:

Python
Scala
Java

sedona.sql("SELECT ST_Buffer(geom, 0.001) AS geom FROM org_catalog.test_db.test_table")\
    .writeTo("org_catalog.test_db.test_table_buf").append()

sedona.sql("SELECT ST_Buffer(geom, 0.001) AS geom FROM org_catalog.test_db.test_table")
    .writeTo("org_catalog.test_db.test_table_buf").append()

sedona.sql("SELECT ST_Buffer(geom, 0.001) AS geom FROM org_catalog.test_db.test_table")
    .writeTo("org_catalog.test_db.test_table_buf").append();

Geography Columns

GEOGRAPHY(<srid>) interprets coordinates on a sphere rather than a plane, so ST_Distance, ST_Area, and ST_Length return spherical results in meters without projecting first.

sedona.sql("""
CREATE TABLE org_catalog.test_db.stations (
    id bigint,
    geog geography(4326)
) USING iceberg
""")

sedona.sql("""
INSERT INTO org_catalog.test_db.stations
SELECT 1L, ST_GeogFromWKT('POINT (-122.3937 37.7955)', 4326)
""")

A geography column rejects a value whose SRID is a different CRS, the same as a geometry column. Unlike a geometry column, it accepts a value with no SRID, such as the result of ST_GeogFromWKT without the SRID argument, and stores it under the column CRS. There is no cast from GEOMETRY to GEOGRAPHY; use ST_GeomToGeography to convert, and ST_GeogToGeometry in the other direction. See the geography function reference for the full list.

Wherobots Cloud REST API

WherobotsDB

Vector tiles (PMTiles)

Havasu (Iceberg) table management

GeoStats and Map Matching

Rasterflow

Geometry Support in Havasu

Readable as geometry by other engines

One CRS per column, enforced

Creating Table with Geometry Column

Writing Data

INSERT INTO

Matching the column CRS

Writing DataFrame to Havasu table

Creating a table from a query or a DataFrame

Updating data in Havasu table

Deleting data from Havasu table

Merging DataFrame into Havasu table using `MERGE INTO`

Querying Data

Working with Geometry Data

Geography Columns

Further Reading

Readable as geometry by other engines

One CRS per column, enforced

​Creating Table with Geometry Column

​Writing Data

​INSERT INTO

​Matching the column CRS

​Writing DataFrame to Havasu table

​Creating a table from a query or a DataFrame

​Updating data in Havasu table

​Deleting data from Havasu table

​Merging DataFrame into Havasu table using MERGE INTO

​Querying Data

​Working with Geometry Data

​Geography Columns

​Further Reading

Creating Table with Geometry Column

Writing Data

INSERT INTO

Matching the column CRS

Writing DataFrame to Havasu table

Creating a table from a query or a DataFrame

Updating data in Havasu table

Deleting data from Havasu table

Merging DataFrame into Havasu table using `MERGE INTO`

Querying Data

Working with Geometry Data

Geography Columns

Further Reading