> ## 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. # Upgrade Existing Geometry Tables > Check whether a table's geometry column has a declared CRS, and copy a table without one into a table with a GEOMETRY() column. 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](#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()` rejects values in the wrong CRS and is readable as geometry by other engines that support geospatial types. See [Geometry and Geography Tables](https://docs.wherobots.com/data-engineering/iceberg-tables/geometry-tables.md) 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()` 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()` 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](https://docs.wherobots.com/get-started/initial-storage/aws/connect-to-glue-catalog.md#geometry-and-geography-columns). | | 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](https://docs.wherobots.com/get-started/initial-storage/aws/connect-to-s3-tables-catalog.md#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`](https://docs.wherobots.com/reference/wherobots-db/geometry-data/constructors/ST_GeomFromWKB), [`ST_GeomFromWKT`](https://docs.wherobots.com/reference/wherobots-db/geometry-data/constructors/ST_GeomFromWKT), or [`ST_GeomFromGeoJSON`](https://docs.wherobots.com/reference/wherobots-db/geometry-data/constructors/ST_GeomFromGeoJSON). | > [!NOTE] > > 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. > [!WARNING] > > 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. > [!NOTE] > > `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 **1. Check whether the table needs upgrading** ```python theme={"system"} 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. **2. Check which SRIDs the column holds** ```python theme={"system"} 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()` 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()` column can declare SRID 0, 3857, or 4326, and a `GEOGRAPHY()` 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](https://docs.wherobots.com/data-engineering/iceberg-tables/geometry-tables.md#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. **3. Create the new table** Use the CRS from the previous step. This example assumes longitude and latitude. ```python theme={"system"} 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()` 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](#tables-created-from-a-query). If the source table has a raster column, declare it as `raster` in the new table. See [Raster columns](#raster-columns) before you continue. **4. 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: ```python theme={"system"} 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: ```python theme={"system"} 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 """) ``` **5. Compare the tables** ```python theme={"system"} 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](https://docs.wherobots.com/data-engineering/iceberg-tables/spatial-index.md). **6. 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: ```python theme={"system"} 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](https://docs.wherobots.com/develop/dbt/materializations#incremental-models): incremental runs write into the existing table and keep its `GEOMETRY()` 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`](https://docs.wherobots.com/develop/dbt/materializations#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`](https://docs.wherobots.com/develop/dbt/materializations#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()` table | `GEOMETRY()`, with the same CRS | To get a declared CRS for computed geometry, create the table with `CREATE TABLE ... GEOMETRY()` 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. ## Related pages > **[Geometry and Geography Tables](https://docs.wherobots.com/data-engineering/iceberg-tables/geometry-tables.md)** > > Creating, writing, updating, and querying geometry tables. > **[Cluster Tables with a Spatial Index](https://docs.wherobots.com/data-engineering/iceberg-tables/spatial-index.md)** > > Spatial filter push-down and the full `CREATE SPATIAL INDEX` syntax. This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.