Skip to main content

​
Create Table using CREATE TABLE Command

To create a geospatial table 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 (id bigint, data string, geom geometry)")
This will create an empty table. Notice that Havasu introduced a new data type geometry to represent geospatial data. Data in columns with geometry type will be loaded as GeometryUDT values in WherobotsDB, user can use any ST_ functions provided by WherobotsDB to manipulate the geospatial data.

​
Create Table using DataFrame

User can also create a table using a DataFrame. The geometry column in the DataFrame will goes into the GEOMETRY column in the created geospatial table.
  • Python
  • Scala
  • Java
df.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 Geometry Data

Once your table is created, you can insert data using INSERT INTO:
  • Python
  • Scala
  • Java
sedona.sql("INSERT INTO org_catalog.test_db.test_table VALUES (1, 'a', ST_GeomFromText('POINT (1 2)')), (2, 'b', ST_Point(2, 3))")
You may have noticed that we use ST_GeomFromText and ST_Point provided by WherobotsDB to construct geometry values and directly inserted them into the Havasu table. Users can also write a DataFrame containing geometry data to a Havasu table:
  • Python
  • Scala
  • Java
df.writeTo("wherobots.db_name.table_name").create()
Or insert data into an existing Havasu table:
  • Python
  • Scala
  • Java
df.writeTo("wherobots.db_name.table_name").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 Geometry 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 Geometry Data using Spatial SQL

Users can use any ST_ functions provided by WherobotsDB to manipulate geometry data read from Havasu.
  • Python
  • Scala
  • Java
# Read and manipulate geospatial data in a Havasu table
df = sedona.table("wherobots.db_name.table_name")
df.withColumn("wkt", expr("ST_AsText(geom)")).show()
df2 = df.withColumn("geom", expr("ST_Buffer(geom, 0.01)"))

# Write geospatial data back to a Havasu table
df2.writeTo("wherobots.db_name.table_name").append()

​
Further Reading

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