Upgrade Existing Geometry Tables

Since September 15, 2026, a WherobotsDB table declares the Coordinate Reference System (CRS) of a geometry column in the column type, such as GEOMETRY(4326). Tables you created before that date were created at Apache Iceberg format-version 2 and have geometry columns with no declared CRS, and so do tables created from the result of an ST_ function, as described in Tables created from a query. If you own such a table, this page shows how to check it and copy it into a table with a declared CRS. A geometry column created as geometry, with no CRS in parentheses, does not check the Spatial Reference Identifier (SRID) of the values written to it, and an Apache Iceberg engine outside Wherobots reads it as a binary column. A column created as geometry(<srid>) rejects values in the wrong CRS and is readable as geometry by other engines that support geospatial types. See Geometry and Geography Tables for how those columns work.

Table	What to do
A table you created	Follow the steps on this page.
A table in an AWS Glue catalog	Nothing to convert, because a Glue table can’t have a geometry column with no declared CRS. Create new Glue tables with `GEOMETRY(<srid>)` columns. Add a `LOCATION` to the `CREATE TABLE` statement when the Glue database has no default location, and check the Lake Formation permissions on that location. A table with a `GEOMETRY(<srid>)` column is format-version 3, which Amazon Athena can’t read, and Amazon Redshift can’t read its geometry column. See Geometry and geography columns in Glue.
A table in an Amazon S3 Tables catalog	Nothing. The Wherobots S3 Tables integration can’t create geometry or geography columns, so keep geometries as WKB in a `binary` column, as described in S3 Tables limitations.
A read-only public table in the `wherobots_open_data` or `wherobots_pro_data` catalog	Nothing. Wherobots will upgrade these tables.
Parquet files, or a table that stores geometry as WKB, WKT, or GeoJSON	Follow the steps from step 2, and parse the column wherever the steps use `geom`, for example with `ST_GeomFromWKB`, `ST_GeomFromWKT`, or `ST_GeomFromGeoJSON`.

Tables you haven’t upgraded stay readable and writable. Reads, writes, and every ST_ function behave the same on them. Spatial filters on their geometry columns skip data files while the table is format-version 2. In a format-version 3 table, such as one created from a query since September 15, 2026, they read every data file. Wherobots plans to retire this column format; the changelog will announce the date.

There is no in-place conversion. ALTER TABLE ... ALTER COLUMN cannot add a CRS to an existing geometry column:

[NOT_SUPPORTED_CHANGE_COLUMN] ALTER TABLE ALTER/CHANGE COLUMN is not supported for
changing `org_catalog`.`my_db`.`places`'s column `geom` with type UDT("BINARY") to
`geom` with type "GEOMETRY(4326)". SQLSTATE: 0A000

Raising the format version in place with ALTER TABLE ... SET TBLPROPERTIES ('format-version'='3') is not an upgrade either. The existing geometry column keeps no declared CRS, and data files written after the change carry no spatial bounds for that column, so spatial filters on it can no longer skip the new files.Upgrading is create-and-copy.

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

Upgrade a table

Check whether the table needs upgrading

print(sedona.sql("SHOW CREATE TABLE org_catalog.my_db.places").collect()[0][0])

The geometry column in the statement	Meaning
`geom GEOMETRY`	No declared CRS. Continue with the next step.
`geom GEOMETRY(4326)`, or another SRID in parentheses	The column already has a declared CRS. There is nothing to do.

DESCRIBE TABLE shows geometry for both kinds of column, so it does not tell them apart.

Check which SRIDs the column holds

sedona.sql("""
SELECT ST_SRID(geom) AS srid, COUNT(*) AS row_count
FROM org_catalog.my_db.places
GROUP BY ST_SRID(geom)
""").show()

A column with no declared CRS can hold more than one SRID; a geometry(<srid>) column cannot. What comes back decides both the CRS you declare in the next step and the expression you copy with in the step after it:

Result	Declare	Copy with
One row, `4326` or `0`, and the coordinates are longitude and latitude	`GEOMETRY(4326)`	`ST_SetSRID(geom, 4326)`, which labels the values and leaves the coordinates alone
One row, `3857`	`GEOMETRY(3857)` to keep the CRS, or `GEOMETRY(4326)` to move to longitude and latitude	`geom` for the first; `ST_Transform(geom, 'EPSG:4326')` for the second
One row, another SRID such as `32633`	`GEOMETRY(4326)`, or keep the CRS as described below this table	`ST_Transform(geom, 'EPSG:4326')`, which reprojects the rows to longitude and latitude
More than one row, none of them `0`	`GEOMETRY(4326)`	`ST_Transform(geom, 'EPSG:4326')`, which reprojects each row from the SRID it carries
More than one row, one of them `0`	`GEOMETRY(4326)`	The `CASE` expression in the last step, after you confirm which CRS the SRID 0 rows are in

Currently, a GEOMETRY(<srid>) column can declare SRID 0, 3857, or 4326, and a GEOGRAPHY(<srid>) column can declare 4326. Declaring another SRID, such as 4269 or 32633, fails with ST_INVALID_SRID_VALUE. Geometry values themselves can still carry any SRID. To keep data in another CRS instead of reprojecting it:

If the table is format-version 2, you can leave it as it is until its SRID can be declared. Spatial filters on its geometry column keep skipping data files.
Otherwise, create a GEOMETRY(0) column and copy with ST_SetSRID(geom, 0), or copy the table into a new format-version 2 table. See Data in another CRS.

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. SRID 0 rows carry no CRS, so ST_Transform has nothing to reproject them from and fails on them: look at their coordinates to find out which CRS they are in before you label them.

Create the new table

Use the CRS from the previous step. This example assumes longitude and latitude.

sedona.sql("""
CREATE TABLE org_catalog.my_db.places_upgraded (
    id BIGINT,
    name STRING,
    geom GEOMETRY(4326)
) USING iceberg
""")

The SHOW CREATE TABLE output from step 1 shows the partitioning and table properties of the source table. Carry them over to the new statement, except the format-version property, whatever its value. A table with a GEOMETRY(<srid>) column is format-version 3, which Wherobots sets when you leave the property out. Copying 'format-version' = '2' (or '1', from an older table) fails:

Invalid schema for v2: - Invalid type for geom: geometry(OGC:CRS84) is not supported until v3

Create the table with CREATE TABLE as shown, then copy the rows into it. A CREATE TABLE ... AS SELECT over ST_SetSRID or ST_Transform creates another column with no declared CRS. See Tables created from a query.If the source table has a raster column, declare it as raster in the new table. See Raster columns before you continue.

Copy the rows

Pause the jobs that write to the source table before you copy, and keep them paused until you switch over. Rows written to the source table after the copy don’t reach the new table, and they are lost when you drop the old table.When every row is SRID 4326 or 0 and the coordinates are longitude and latitude:

sedona.sql("""
INSERT INTO org_catalog.my_db.places_upgraded
SELECT id, name, ST_SetSRID(geom, 4326) FROM org_catalog.my_db.places
""")

When the column mixes SRID 0 rows that are longitude and latitude with rows in other SRIDs, reproject the rows that carry an SRID and label all of them:

sedona.sql("""
INSERT INTO org_catalog.my_db.places_upgraded
SELECT id, name,
       ST_SetSRID(
           CASE WHEN ST_SRID(geom) = 0 THEN geom
                ELSE ST_Transform(geom, 'EPSG:4326') END,
           4326)
FROM org_catalog.my_db.places
""")

Compare the tables

sedona.sql("""
SELECT
  (SELECT COUNT(*) FROM org_catalog.my_db.places) AS source_rows,
  (SELECT COUNT(*) FROM org_catalog.my_db.places_upgraded) AS upgraded_rows
""").show()

If the source table was clustered, run CREATE SPATIAL INDEX on the new table as described in Cluster Tables with a Spatial Index.

Rename the tables and switch over

When the counts match, give the new table the name of the source table, so queries and jobs that use the name read the new table:

sedona.sql("ALTER TABLE org_catalog.my_db.places RENAME TO my_db.places_old")
sedona.sql("ALTER TABLE org_catalog.my_db.places_upgraded RENAME TO my_db.places")

Leave the catalog name out of the new name. A rename stays within the catalog, and a new name that starts with the catalog name fails with TABLE_OR_VIEW_NOT_FOUND.The two renames are separate statements, so between them no table has the name my_db.places. A query that reads the table in that window fails with TABLE_OR_VIEW_NOT_FOUND, and if the second rename fails, the name stays missing until you run it again. Run the renames back to back while nothing reads the table, or make the readers retry.AWS Glue and Amazon S3 Tables catalogs don’t support ALTER TABLE ... RENAME TO. In a Glue catalog, point your queries and jobs at the new table name instead.Resume the paused writers once they use the new table.If a dbt table model builds the source table, change the model before you resume it. A table model rebuilds its table with create or replace table ... as select on every run, so its next run replaces the upgraded table, and a geometry column computed with an ST_ function loses its declared CRS again. Change it to an incremental model: incremental runs write into the existing table and keep its GEOMETRY(<srid>) column, as long as the model’s geometry values carry that SRID. Changing the materialization alone duplicates rows. The model’s query still selects every row on each run, and the default append strategy inserts all of them into a table that already holds the copied rows. Add an is_incremental() filter that selects only rows the table doesn’t have yet, or use the merge strategy with a unique_key so each run upserts its rows. A full refresh rebuilds the table from the query and drops the declared CRS.The new table starts its own snapshot history, so time travel to a snapshot from before the upgrade needs the old table. Drop the old table once nothing reads from it.

Error: Cannot write geometry with SRID ...

A copied value disagrees with the CRS of the new column, which means the copy expression does not cover every SRID the source holds. Go back to the SRID check and pick the expression for that result.

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.

Tables created from a query

CREATE TABLE ... AS SELECT, writeTo(...).create(), and writeTo(...).createOrReplace() take the column type from the query:

Geometry column in the query	Column in the new table
The result of an `ST_` function, such as `ST_Point`, `ST_SetSRID`, or `ST_Buffer`	`GEOMETRY`, with no declared CRS
A column read unchanged from a `GEOMETRY(<srid>)` table	`GEOMETRY(<srid>)`, with the same CRS

To get a declared CRS for computed geometry, create the table with CREATE TABLE ... GEOMETRY(<srid>) first, then write to it with INSERT INTO ... SELECT or writeTo(...).append(). dbt table models that compute geometry with ST_ functions build their tables with create or replace table ... as select, so they also get a column with no declared CRS. A new table in a Wherobots-managed catalog is format-version 3 unless the statement sets 'format-version', and in a format-version 3 table, spatial filters on a column with no declared CRS read every data file.

Raster columns

A raster column copies across unchanged, because raster values are stored the same way on format-version 2 and 3 tables. On a format-version 3 table, SHOW CREATE TABLE prints the raster column as BINARY; it still holds rasters.

Geometry and Geography Tables

Creating, writing, updating, and querying geometry tables.

Cluster Tables with a Spatial Index

Spatial filter push-down and the full CREATE SPATIAL INDEX syntax.

Start here

Connect your data

Vector data

Raster data

Iceberg tables

Maps and visualization

Upgrade Existing Geometry Tables

Upgrade a table

Tables created from a query

Raster columns

Geometry and Geography Tables

Cluster Tables with a Spatial Index

​Upgrade a table

​Tables created from a query

​Raster columns

​Related pages

Geometry and Geography Tables

Cluster Tables with a Spatial Index

Upgrade a table

Tables created from a query

Raster columns

Related pages