> ## Documentation Index
> Fetch the complete documentation index at: https://docs.wherobots.com/llms.txt
> Use this file to discover all available pages before exploring further.

# 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:

<Tabs>
  <Tab title="Python">
    ```python theme={"system"}
    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)")
    ```
  </Tab>

  <Tab title="Scala">
    ```scala theme={"system"}
    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)")
    ```
  </Tab>

  <Tab title="Java">
    ```java theme={"system"}
    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)");
    ```
  </Tab>
</Tabs>

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](/tutorials/wherobotsdb/raster-data/raster-load/).

Then we can write this DataFrame to a Havasu table:

<Tabs>
  <Tab title="Python">
    ```python theme={"system"}
    df_geotiff.writeTo("org_catalog.test_db.test_table").create()
    ```
  </Tab>

  <Tab title="Scala">
    ```scala theme={"system"}
    df_geotiff.writeTo("org_catalog.test_db.test_table").create()
    ```
  </Tab>

  <Tab title="Java">
    ```java theme={"system"}
    df_geotiff.writeTo("org_catalog.test_db.test_table").create();
    ```
  </Tab>
</Tabs>

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`](/reference/wherobots-db/raster-data/raster-loader/#rs_fromgeotiff) to construct a raster value and write the DataFrame using `.writeTo(...).append()`

<Tabs>
  <Tab title="Python">
    ```python theme={"system"}
    # 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()
    ```
  </Tab>

  <Tab title="Scala">
    ```scala theme={"system"}
    // 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()
    ```
  </Tab>

  <Tab title="Java">
    ```java theme={"system"}
    // 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();
    ```
  </Tab>
</Tabs>

Or user can first load the GeoTIFF file as out-db raster using [`RS_FromPath`](/reference/wherobots-db/raster-data/raster-loader/#rs_frompath), then use [`RS_AsInDB`](/reference/wherobots-db/raster-data/raster-loader/#rs_asindb) to convert the out-db raster to in-db raster:

<Tabs>
  <Tab title="Python">
    ```python theme={"system"}
    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()
    ```
  </Tab>

  <Tab title="Scala">
    ```scala theme={"system"}
    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()
    ```
  </Tab>

  <Tab title="Java">
    ```java theme={"system"}
    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();
    ```
  </Tab>
</Tabs>

### Out-DB Raster

To construct out-db raster data and write them to a Havasu table, user can use [`RS_FromPath`](/reference/wherobots-db/raster-data/raster-loader/#rs_frompath) to construct a raster value and insert it into the table:

<Tabs>
  <Tab title="Python">
    ```python theme={"system"}
    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()
    ```
  </Tab>

  <Tab title="Scala">
    ```scala theme={"system"}
    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()
    ```
  </Tab>

  <Tab title="Java">
    ```java theme={"system"}
    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();
    ```
  </Tab>
</Tabs>

Havasu also supports a range of table update operations, such as `UPDATE`, `DELETE`, `MERGE INTO`, etc. Please refer to [Apache Iceberg - Spark Writes](https://iceberg.apache.org/docs/latest/spark-writes/) for details.

## Reading Raster Table

Havasu tables can be queried using SQL `SELECT` statements:

<Tabs>
  <Tab title="Python">
    ```python theme={"system"}
    df = sedona.sql("SELECT * FROM wherobots.db_name.table_name")
    ```
  </Tab>

  <Tab title="Scala">
    ```scala theme={"system"}
    val df = sedona.sql("SELECT * FROM wherobots.db_name.table_name")
    ```
  </Tab>

  <Tab title="Java">
    ```java theme={"system"}
    Dataset<Row> df = sedona.sql("SELECT * FROM wherobots.db_name.table_name");
    ```
  </Tab>
</Tabs>

Or using DataFrame API:

<Tabs>
  <Tab title="Python">
    ```python theme={"system"}
    df = sedona.table("wherobots.db_name.table_name")
    ```
  </Tab>

  <Tab title="Scala">
    ```scala theme={"system"}
    val df = sedona.table("wherobots.db_name.table_name")
    ```
  </Tab>

  <Tab title="Java">
    ```java theme={"system"}
    Dataset<Row> df = sedona.table("wherobots.db_name.table_name");
    ```
  </Tab>
</Tabs>

Havasu also supports time travel and inspecting metadata of table. Please refer to [Apache Iceberg - Queries](https://iceberg.apache.org/docs/latest/spark-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.

<Tabs>
  <Tab title="Python">
    ```python theme={"system"}
    # 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()
    ```
  </Tab>

  <Tab title="Scala">
    ```scala theme={"system"}
    // 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()
    ```
  </Tab>

  <Tab title="Java">
    ```java theme={"system"}
    // 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();
    ```
  </Tab>
</Tabs>

## Further Reading

For more information, please refer to [Raster Support in Havasu](/reference/havasu/raster/raster-overview/).