Upgrade Existing Geometry Tables

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

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

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.

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, 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.

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.

Copy the rows

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 and switch over

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. Drop the source 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.

Geometry Support in Havasu

Creating, writing, updating, and querying geometry tables.

Cluster by Geospatial Fields

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

Wherobots Cloud REST API

WherobotsDB

Vector tiles (PMTiles)

Havasu (Iceberg) table management

GeoStats and Map Matching

Rasterflow

Upgrade Existing Geometry Tables

Upgrade a table

Geometry Support in Havasu

Cluster by Geospatial Fields

​Upgrade a table

​Related pages

Geometry Support in Havasu

Cluster by Geospatial Fields

Upgrade a table

Related pages