Havasu (Iceberg) Table Management

Create Table using `CREATE TABLE` Command

To create a table with raster column in WherobotsDB, use sedona.sql(...) to run a CREATE TABLE command:

Python
Scala
Java

sedona.sql("CREATE DATABASE IF NOT EXISTS org_catalog.test_db")
sedona.sql("CREATE TABLE org_catalog.test_db.test_table (data string, rast raster)")

sedona.sql("CREATE DATABASE IF NOT EXISTS org_catalog.test_db")
sedona.sql("CREATE TABLE org_catalog.test_db.test_table (data string, rast raster)")

sedona.sql("CREATE DATABASE IF NOT EXISTS org_catalog.test_db");
sedona.sql("CREATE TABLE org_catalog.test_db.test_table (data string, rast raster)");

This will create an empty table. Notice that Havasu introduced a new data type raster to represent raster data. Data in columns with rast type will be loaded as RasterUDT values in WherobotsDB; users can use any RS_ functions provided by WherobotsDB to manipulate the raster data.

Create Table using DataFrame

User can also create a table using a DataFrame. The raster column in the DataFrame will go into the raster column in the created geospatial table, as described in Load Raster Data. Then we can write this DataFrame to a Havasu table:

Python
Scala
Java

df_geotiff.writeTo("org_catalog.test_db.test_table").create()

df_geotiff.writeTo("org_catalog.test_db.test_table").create()

df_geotiff.writeTo("org_catalog.test_db.test_table").create();

Havasu supports a full range of SQL DDL and DML commands which Apache Iceberg supports, such as ALTER TABLE, DROP TABLE, etc.

Writing Raster Data

Raster data in Havasu is similar to PostGIS. It provides two options for storing raster data:

in-db raster: raster data completely stored in a Havasu table.
out-db raster: raster data is stored in an external file (such as GeoTIFF file on S3) and the Havasu table stores the path to the file and geo-referencing metadata of that raster.

In-DB Raster

To construct in-db raster data and write them to a Havasu table, user can use RS_FromGeoTiff to construct a raster value and write the DataFrame using .writeTo(...).append()

Python
Scala
Java

# Read GeoTIFF files using RS_FromGeoTiff
df_binary = sedona.read.format("binaryFile")\
    .option("pathGlobFilter", "*.tif")\
    .option("recursiveFileLookup", "true")\
    .load('s3://wherobots-examples/data/eurosat_small')
df_geotiff = df_binary.withColumn("rast", expr("RS_FromGeoTiff(content)"))\
    .selectExpr("rast", "path AS data")

# Writing the data into Havasu table
df_geotiff.writeTo("org_catalog.test_db.test_table").append()

// Read GeoTIFF files using RS_FromGeoTiff
val df_binary = sedona.read.format("binaryFile")
    .option("pathGlobFilter", "*.tif")
    .option("recursiveFileLookup", "true")
    .load("s3://wherobots-examples/data/eurosat_small")

val df_geotiff = df_binary.withColumn("rast", expr("RS_FromGeoTiff(content)"))
    .selectExpr("rast", "path AS data")

// Writing the data into Havasu table
df_geotiff.writeTo("org_catalog.test_db.test_table").append()

// Read GeoTIFF files using RS_FromGeoTiff
Dataset<Row> df_binary = sedona.read().format("binaryFile")
    .option("pathGlobFilter", "*.tif")
    .option("recursiveFileLookup", "true")
    .load("s3://wherobots-examples/data/eurosat_small");

Dataset<Row> df_geotiff = df_binary.withColumn("rast", expr("RS_FromGeoTiff(content)"))
    .selectExpr("rast", "path AS data");

// Writing the data into Havasu table
df_geotiff.writeTo("org_catalog.test_db.test_table").append();

Or user can first load the GeoTIFF file as out-db raster using RS_FromPath, then use RS_AsInDB to convert the out-db raster to in-db raster:

Python
Scala
Java

df_paths = spark.createDataFrame([
    ("s3://wherobots-examples/data/eurosat_small/AnnualCrop/AnnualCrop_10.tif",),
    ("s3://wherobots-examples/data/eurosat_small/AnnualCrop/AnnualCrop_11.tif",)
], ["path"])

# Read GeoTIFF files as out-db rasters using RS_FromPath, then convert to in-db rasters using RS_AsInDB
df_rast = df_paths.withColumn("rast", expr("RS_AsInDB(RS_FromPath(path))"))

# Writing the data into Havasu table
df_rast.selectExpr("rast", "path AS data").writeTo("org_catalog.test_db.test_table").append()

val df_paths = Seq(
    "s3://wherobots-examples/data/eurosat_small/AnnualCrop/AnnualCrop_10.tif",
    "s3://wherobots-examples/data/eurosat_small/AnnualCrop/AnnualCrop_11.tif"
).toDF("path")

// Read GeoTIFF files as out-db rasters using RS_FromPath, then convert to in-db rasters using RS_AsInDB
val df_rast = df_paths.withColumn("rast", expr("RS_AsInDB(RS_FromPath(path))"))

// Writing the data into Havasu table
df_rast.selectExpr("rast", "path AS data").writeTo("org_catalog.test_db.test_table").append()

List<Row> data = Arrays.asList(
    RowFactory.create("s3://wherobots-examples/data/eurosat_small/AnnualCrop/AnnualCrop_10.tif"),
    RowFactory.create("s3://wherobots-examples/data/eurosat_small/AnnualCrop/AnnualCrop_11.tif")
);
List<StructField> fields = Arrays.asList(
    DataTypes.createStructField("path", DataTypes.StringType, true)
);
StructType schema = DataTypes.createStructType(fields);
Dataset<Row> df_paths = spark.createDataFrame(data, schema);

// Read GeoTIFF files as out-db rasters using RS_FromPath, then convert to in-db rasters using RS_AsInDB
Dataset<Row> df_rast = df_paths.withColumn("rast", expr("RS_AsInDB(RS_FromPath(path))"));

// Writing the data into Havasu table
df_rast.selectExpr("rast", "path AS data").writeTo("org_catalog.test_db.test_table").append();

Out-DB Raster

To construct out-db raster data and write them to a Havasu table, user can use RS_FromPath to construct a raster value and insert it into the table:

Python
Scala
Java

df_paths = spark.createDataFrame([
    ("s3://wherobots-examples/data/eurosat_small/AnnualCrop/AnnualCrop_10.tif",),
    ("s3://wherobots-examples/data/eurosat_small/AnnualCrop/AnnualCrop_11.tif",)
], ["path"])

# Read GeoTIFF files as out-db rasters using RS_FromPath
df_rast = df_paths.withColumn("rast", expr("RS_FromPath(path)"))

# Writing the data into Havasu table
df_rast.selectExpr("rast", "path AS data").writeTo("org_catalog.test_db.test_table").append()

val df_paths = Seq(
    "s3://wherobots-examples/data/eurosat_small/AnnualCrop/AnnualCrop_10.tif",
    "s3://wherobots-examples/data/eurosat_small/AnnualCrop/AnnualCrop_11.tif"
).toDF("path")

// Read GeoTIFF files as out-db rasters using RS_FromPath
val df_rast = df_paths.withColumn("rast", expr("RS_FromPath(path)"))

// Writing the data into Havasu table
df_rast.selectExpr("rast", "path AS data").writeTo("org_catalog.test_db.test_table").append()

List<Row> data = Arrays.asList(
    RowFactory.create("s3://wherobots-examples/data/eurosat_small/AnnualCrop/AnnualCrop_10.tif"),
    RowFactory.create("s3://wherobots-examples/data/eurosat_small/AnnualCrop/AnnualCrop_11.tif")
);
List<StructField> fields = Arrays.asList(
    DataTypes.createStructField("path", DataTypes.StringType, true)
);
StructType schema = DataTypes.createStructType(fields);
Dataset<Row> df_paths = spark.createDataFrame(data, schema);

// Read GeoTIFF files as out-db rasters using RS_FromPath
Dataset<Row> df_rast = df_paths.withColumn("rast", expr("RS_FromPath(path)"));

// Writing the data into Havasu table
df_rast.selectExpr("rast", "path AS data").writeTo("org_catalog.test_db.test_table").append();

Havasu also supports a range of table update operations, such as UPDATE, DELETE, MERGE INTO, etc. Please refer to Apache Iceberg - Spark Writes for details.

Reading Raster Table

Havasu tables can be queried using SQL SELECT statements:

Python
Scala
Java

df = sedona.sql("SELECT * FROM wherobots.db_name.table_name")

val df = sedona.sql("SELECT * FROM wherobots.db_name.table_name")

Dataset<Row> df = sedona.sql("SELECT * FROM wherobots.db_name.table_name");

Or using DataFrame API:

Python
Scala
Java

df = sedona.table("wherobots.db_name.table_name")

val df = sedona.table("wherobots.db_name.table_name")

Dataset<Row> df = sedona.table("wherobots.db_name.table_name");

Havasu also supports time travel and inspecting metadata of table. Please refer to Apache Iceberg - Queries for details.

Processing Raster Data using Spatial SQL

Users can use any RT_ functions provided by WherobotsDB to manipulate raster data read from Havasu.

Python
Scala
Java

# Read and manipulate geospatial data in a Havasu table
df = sedona.table("org_catalog.test_db.test_table")
df_mapalg = df.withColumn("rast", expr("RS_MapAlgebra(rast, NULL, 'out = 0.5 * (rast[0] + rast[1]);')"))

# Write geospatial data back to a Havasu table
df_mapalg.writeTo("org_catalog.test_db.test_table_2").create()

// Read and manipulate geospatial data in a Havasu table
val df = sedona.table("org_catalog.test_db.test_table")
val df_mapalg = df.withColumn("rast", expr("RS_MapAlgebra(rast, NULL, 'out = 0.5 * (rast[0] + rast[1]);')"))

// Write geospatial data back to a Havasu table
df_mapalg.writeTo("org_catalog.test_db.test_table_2").create()

// Read and manipulate geospatial data in a Havasu table
Dataset<Row> df = sedona.table("org_catalog.test_db.test_table");
Dataset<Row> df_mapalg = df.withColumn("rast", expr("RS_MapAlgebra(rast, NULL, 'out = 0.5 * (rast[0] + rast[1]);')"));

// Write geospatial data back to a Havasu table
df_mapalg.writeTo("org_catalog.test_db.test_table_2").create();

Getting Started

Spatial Queries

WherobotsDB

Data Connections

RasterFlow

Advanced Topics

GeoStats, Map Matching & RasterFlow

Havasu (Iceberg) Table Management

Create Table using `CREATE TABLE` Command

Create Table using DataFrame

Writing Raster Data

In-DB Raster

Out-DB Raster

Reading Raster Table

Processing Raster Data using Spatial SQL

Further Reading

​Create Table using CREATE TABLE Command

​Create Table using DataFrame

​Writing Raster Data

​In-DB Raster

​Out-DB Raster

​Reading Raster Table

​Processing Raster Data using Spatial SQL

​Further Reading

Create Table using `CREATE TABLE` Command

Create Table using DataFrame

Writing Raster Data

In-DB Raster

Out-DB Raster

Reading Raster Table

Processing Raster Data using Spatial SQL

Further Reading