Using Sequences in OGR SQLite and SpatiaLite

When managing spatial data in a SpatiaLite or SQLite database via GDAL/OGR, maintaining primary key integrity is paramount. Unlike standard SQL databases that use a simple AUTOINCREMENT keyword, the OGR SQLite driver allows for advanced manipulation of sequences during data translation (ogr2ogr) and SQL execution. This is particularly useful when merging multiple Shapefiles into a single database where feature IDs might overlap. By leveraging the sqlite_sequence table and OGR-specific SQL functions, you can automate the generation of unique identifiers for large-scale geographic datasets.

Table of Content

• Purpose of Sequences in Spatial Databases
• Common Use Cases
• Step by Step: Implementing Sequences
• Best Results for Database Integrity
• FAQ
• Disclaimer

Purpose

The primary purpose of a sequence in an OGR-managed SQLite database is to provide a Global Unique Identifier (GUID) across distributed datasets. In GIS workflows, feature IDs (FID) are often reset when data is exported to different formats. By defining a sequence, you ensure that every point, line, or polygon added to your SpatiaLite table receives a predictable, non-clashing integer ID. This enables reliable table joins, relationship classes, and stable web mapping service (WMS) feature identification.

Use Case

Using sequences with OGR SQLite is essential for:

• Data Appending: Merging multiple years of parcel data where you need a continuous ID range starting from the last known entry.
• Field Mapping: Creating a new column during an ogr2ogr conversion that acts as a custom "Asset ID" based on an incrementing counter.
• ETL Pipelines: Automating the ingestion of field-collected GeoJSON data into a centralized SpatiaLite repository.
• Version Control: Tracking edits by assigning a sequence number to "Revision" columns in spatial tables.

Step by Step

1. Creating an Auto-Incrementing Table via OGR

When creating a new SQLite layer using OGR, the FID column is handled automatically as a sequence. You can define this explicitly in an ogr2ogr command:
ogr2ogr -f SQLite output.sqlite input.shp -nln parcels -lco FID=id_column

2. Using SQL Expressions for Custom Sequences

To create a sequence-like effect on a non-FID column during translation, use the -sql flag with the ROWID or COUNT function:
ogr2ogr -f SQLite output.sqlite input.shp -sql "SELECT ROWID AS sequence_id, FROM input"

3. Manipulating the sqlite_sequence Table

SQLite keeps track of the current sequence value in a system table. If you need to "reset" or "jump" a sequence for a GIS layer, execute this SQL via ogrinfo:
ogrinfo database.sqlite -sql "UPDATE sqlite_sequence SET seq = 5000 WHERE name = 'your_layer_name'"

4. Implementing Sequences in SpatiaLite Views

When creating a Spatial View, you may need a virtual sequence to satisfy QGIS or ArcGIS requirements for a unique integer ID:
CREATE VIEW my_spatial_view AS SELECT rowid AS OGC_FID, geometry, attribute FROM my_table;

Best Results

FAQ

Does OGR support the 'AUTOINCREMENT' keyword directly?

Yes, but by default, SQLite's INTEGER PRIMARY KEY acts as an auto-incrementing sequence. Adding the specific AUTOINCREMENT keyword is only necessary if you want to prevent the reuse of IDs from deleted rows.

Why did my sequence skip numbers?

In OGR SQLite, if a transaction fails or a feature is rejected due to geometry validation errors, the sequence counter may still increment. This is standard database behavior to ensure high performance during multi-threaded inserts.

Can I use a sequence based on a string?

Sequences are inherently numeric. To create a string-based sequence (e.g., "ZONE-001"), you must concatenate a string prefix with a numeric sequence using SQLite's || operator in your SQL query.

Disclaimer

Directly modifying the sqlite_sequence system table can lead to primary key collisions if not handled carefully. Always backup your .sqlite or .spatialite file before running bulk UPDATE commands. This tutorial reflects OGR/GDAL 3.x standards as of early 2026. Different versions of the SpatiaLite extension may handle metadata triggers differently.

Tags: GDAL, OGR, SQLite, SpatiaLite