How to Add a Custom CRS to gpkg_spatial_ref_sys

Insert one row into gpkg_spatial_ref_sys carrying the srs_id, an authority name and code, a WKT definition, and a description — then bind the layer to that srs_id in both gpkg_contents and gpkg_geometry_columns, all inside a single transaction. A GeoPackage ships with three mandatory rows and nothing else, so adding the reference system your data actually uses is a normal build step rather than a repair.

This page belongs to the Datum Transformations & Projection Accuracy guide.

Why This Matters #

Unlike SpatiaLite, which typically initialises with a large EPSG catalogue, the GeoPackage specification requires only three rows: WGS 84, an undefined Cartesian system, and an undefined geographic system. Everything else is the producer’s responsibility. A container written by a tool that did not add the definition still records an srs_id on the layer — it simply points at nothing, and every consumer that tries to resolve it gets a dangling reference.

The symptom is unhelpfully indirect. ST_Transform returns NULL rather than raising, a desktop GIS offers to “choose a CRS” as though the file did not state one, and a reprojection pipeline produces a layer full of empty geometry. All three are the same missing row.

Prerequisites #

• Python 3.9+ with sqlite3, and pyproj 3.4+ to produce the WKT
• Write access to the container
• The authority and code for the reference system, or a complete WKT definition
• Familiarity with the registry model from GeoPackage Specification Deep Dive

Primary Method #

# Insert a CRS definition and bind a layer to it, atomically
import sqlite3
from pyproj import CRS


def register_crs(gpkg_path: str, epsg: int, layer: str | None = None) -> int:
    """
    Ensure gpkg_spatial_ref_sys holds a definition for `epsg`, and optionally
    bind `layer` to it. Returns the srs_id. Safe to call repeatedly.
    """
    crs = CRS.from_epsg(epsg)
    conn = sqlite3.connect(gpkg_path, isolation_level=None)
    try:
        conn.execute("BEGIN IMMEDIATE")

        existing = conn.execute(
            "SELECT definition FROM gpkg_spatial_ref_sys WHERE srs_id = ?", (epsg,)
        ).fetchone()

        if existing is None:
            conn.execute(
                """
                INSERT INTO gpkg_spatial_ref_sys
                    (srs_name, srs_id, organization, organization_coordsys_id,
                     definition, description)
                VALUES (?, ?, 'EPSG', ?, ?, ?)
                """,
                (crs.name, epsg, epsg, crs.to_wkt(version="WKT1_GDAL"), crs.name),
            )

        if layer is not None:
            conn.execute(
                "UPDATE gpkg_contents SET srs_id = ? WHERE table_name = ?", (epsg, layer)
            )
            conn.execute(
                "UPDATE gpkg_geometry_columns SET srs_id = ? WHERE table_name = ?",
                (epsg, layer),
            )

        conn.execute("COMMIT")
        return epsg
    except Exception:
        conn.execute("ROLLBACK")
        raise
    finally:
        conn.close()

The insert is conditional rather than an upsert, and that is deliberate: overwriting an existing definition changes the meaning of every geometry already bound to it. If the stored definition differs from the one you expect, that is something to investigate, not something to silently replace.

Step-by-Step Walkthrough #

1. Check what the container already holds #

-- GeoPackage: the reference systems this container can resolve
SELECT srs_id, organization, organization_coordsys_id, srs_name
FROM gpkg_spatial_ref_sys
ORDER BY srs_id;

2. Find layers pointing at a definition that is missing #

This is the query worth running against any container you did not build.

-- Layers whose srs_id has no matching definition
SELECT c.table_name, c.srs_id
FROM gpkg_contents c
WHERE c.data_type = 'features'
  AND c.srs_id NOT IN (SELECT srs_id FROM gpkg_spatial_ref_sys);

3. Produce the WKT #

pyproj generates the definition, and the WKT flavour matters more than it looks. GeoPackage 1.2 and earlier expect WKT 1; 1.3 added an optional definition_12_063 column carrying WKT 2. Writing WKT 2 into the definition column of a container that consumers read as WKT 1 produces a definition that parses in some tools and not others.

from pyproj import CRS

crs = CRS.from_epsg(27700)
print(crs.to_wkt(version="WKT1_GDAL")[:120], "…")   # for `definition`
print(crs.to_wkt(version="WKT2_2019")[:120], "…")   # for `definition_12_063`, if present

4. Insert with the identifier you intend to keep #

For an EPSG system, use the EPSG code as srs_id. It is what every other tool will look for, and using something else makes the container correct but surprising. For a genuinely custom system with no authority code, the specification allows any unused identifier — pick from a private range well above the EPSG space, such as 100000 upward, and record organization as something other than EPSG so nobody mistakes it for one.

-- A custom definition with no authority code
INSERT INTO gpkg_spatial_ref_sys
    (srs_name, srs_id, organization, organization_coordsys_id, definition, description)
VALUES
    ('Site grid — north quarry', 100001, 'NONE', 100001,
     'PROJCS["Site grid",GEOGCS[...]]',
     'Local engineering grid, origin at the survey monument');

5. Bind the layer in both registry tables #

gpkg_contents and gpkg_geometry_columns each carry an srs_id, and different consumers read different ones. Updating only one produces a container that behaves differently in two tools.

-- Both rows, one transaction
UPDATE gpkg_contents SET srs_id = 27700 WHERE table_name = 'parcels';
UPDATE gpkg_geometry_columns SET srs_id = 27700 WHERE table_name = 'parcels';

Note that this binds the declaration only. It does not transform the coordinates — if the geometry is in a different system, this relabels it, which is the set_crs-versus-to_crs mistake in another form. Transform first, then rebind, as described in the parent guide.

Verification #

-- No layer may point at a definition that does not exist
SELECT c.table_name, c.srs_id
FROM gpkg_contents c
WHERE c.data_type = 'features'
  AND c.srs_id NOT IN (SELECT srs_id FROM gpkg_spatial_ref_sys);

-- The two registry rows must agree for every layer
SELECT c.table_name, c.srs_id AS contents, g.srs_id AS geometry_columns
FROM gpkg_contents c
JOIN gpkg_geometry_columns g ON g.table_name = c.table_name
WHERE c.srs_id <> g.srs_id;

Both queries should return nothing. Then confirm the definition actually parses, because a syntactically broken WKT satisfies the referential check and fails at use:

from pyproj import CRS

definition = conn.execute(
    "SELECT definition FROM gpkg_spatial_ref_sys WHERE srs_id = ?", (27700,)
).fetchone()[0]

crs = CRS.from_wkt(definition)          # raises if the WKT is malformed
assert crs.to_epsg() == 27700, f"definition resolves to {crs.to_epsg()}"
print("definition parses and resolves correctly")

Alternative Approaches or Edge Cases #

Letting GDAL do it. Writing a layer through OGR with -t_srs EPSG:27700 inserts the definition and binds both registry rows automatically, correctly, and with the right WKT flavour for the container version. Where you control the write path, this is strictly better than hand-inserting; the manual route matters when repairing a container someone else produced.

Containers at GeoPackage 1.3. These may carry a definition_12_063 column holding WKT 2 alongside the WKT 1 definition. Populate both when the column exists — some consumers prefer the WKT 2 form, and leaving it as the placeholder undefined string means those consumers fall back or fail.

Vertical and compound systems. A container recording elevation may need a compound CRS. The insert is identical, but the WKT is larger and fewer consumers handle it; where interchange matters more than completeness, storing the horizontal system and carrying elevation as an attribute is the more portable choice.

Troubleshooting #

IntegrityError: UNIQUE constraint failed: gpkg_spatial_ref_sys.srs_id #

Cause: A definition for that identifier already exists. Fix: This is the guard working. Read the existing definition and compare it with the one you intended — if they differ, that is a real conflict to resolve deliberately, not something to overwrite.

ST_Transform returns NULL after the insert #

Cause: SpatiaLite’s transform functions read spatial_ref_sys, not gpkg_spatial_ref_sys, and the two are separate tables even in a container that has both. Fix: For SpatiaLite-side operations, register the definition in its registry too — see Managing Spatial Reference Systems in SQLite.

A desktop GIS still asks which CRS the layer uses #

Cause: The layer’s srs_id is 0 or -1, the undefined placeholders, rather than the one you inserted. Fix: Update both registry rows for the layer; inserting the definition alone does not bind anything to it.

Frequently Asked Questions #

Related #

• Datum Transformations & Projection Accuracy — parent guide: routes, accuracy and reproducibility
• Managing Spatial Reference Systems in SQLite — the SpatiaLite registry, which is a separate table
• GeoPackage Specification Deep Dive — the registry model this insert participates in
• Choosing Between EPSG:4326 and a Projected CRS — deciding which system to register in the first place