Skip to main content

​
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)")
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()
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()
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()

​
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()
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")
Or using DataFrame API:
  • Python
  • Scala
  • Java
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()

​
Further Reading

For more information, please refer to Raster Support in Havasu.