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

> Copy a table whose geometry column has no declared CRS into a table with a GEOMETRY(<srid>) column.

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 Coordinate Reference System (CRS) and is readable as geometry by other engines that support geospatial types. See [Geometry Support in Havasu](/reference/havasu/geometry/geometry-overview) for how those columns work.

| Table | What to do |
| :- | :- |
| A table you created | Follow the steps on this page. |
| A read-only public table that Wherobots manages, such as those in the `wherobots_open_data` catalog | Nothing. Wherobots upgrades these automatically. |

<Note>
  Tables that have not been upgraded keep working. Reads, spatial filter push-down, and every `ST_` function behave the same on them.
</Note>

<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
  ```

  Upgrading is create-and-copy.
</Warning>

<Note>
  `org_catalog` throughout this page is an example catalog name. Replace it with the name of the catalog you are writing to.
</Note>

## Upgrade a table

<Steps>
  <Step title="Check whether the table needs upgrading" icon="magnifying-glass">
    ```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.
  </Step>

  <Step title="Check which SRIDs the column holds" icon="list-check">
    ```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(<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, another SRID such as `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 |
    | 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 |

    `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.
  </Step>

  <Step title="Create the new table" icon="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.
  </Step>

  <Step title="Copy the rows" icon="copy">
    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
    """)
    ```
  </Step>

  <Step title="Compare the tables and switch over" icon="right-left">
    ```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()
    ```

    When the counts match, point your queries and jobs at the new table. If the source table was clustered, run `CREATE SPATIAL INDEX` on the new table as described in [Cluster by Geospatial Fields for Faster Queries](/reference/havasu-table/geometry-data/cluster-geometry-table). Drop the source table once nothing reads from it.
  </Step>
</Steps>

<Accordion title="Error: Cannot write geometry with SRID ..." icon="triangle-exclamation">
  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.
  ```
</Accordion>

## Related pages

<CardGroup cols={2}>
  <Card title="Geometry Support in Havasu" icon="draw-polygon" href="/reference/havasu/geometry/geometry-overview">
    Creating, writing, updating, and querying geometry tables.
  </Card>

  <Card title="Cluster by Geospatial Fields" icon="layer-group" href="/reference/havasu-table/geometry-data/cluster-geometry-table">
    Spatial filter push-down and the full `CREATE SPATIAL INDEX` syntax.
  </Card>
</CardGroup>


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.