GDAL-VECTOR-SQL(1)

📦
version 3.13.3 · 7 versions
, in alpine 3.24, arch rolling, ubuntu stonking

💖 GDAL-VECTOR-SQL(1) — the gdal-vector-sql glow-up 💖

okay bestie, sit DOWN, because we're about to talk about gdal-vector-sql, the part of the GDAL command line fam that runs SQL statement(s) against a dataset. she's from the GDAL manual, and she was Added in version 3.11 ✨ (so yes she's newer than your fave, sorry not sorry).

🌸 NAME 🌸

gdal-vector-sql — apply SQL statement(s) to a dataset, periodt. 💅

Added in version 3.11.

🎀 SYNOPSIS 🎀

Usage: gdal vector sql [OPTIONS] <INPUT> [<OUTPUT>]

Apply SQL statement(s) to a dataset.

Positional arguments:
  -i, --dataset, --input <INPUT>                       Input vector datasets [required] [not available in pipelines]
  -o, --output <OUTPUT>                                Output vector dataset [not available in pipelines]

Common Options:
  -h, --help                                           Display help message and exit
  --json-usage                                         Display usage as JSON document and exit
  --config <KEY>=<VALUE>                               Configuration option [may be repeated]
  -q, --quiet                                          Quiet mode (no progress bar or warning message) [not available in pipelines]

Options:
  -f, --of, --format, --output-format <OUTPUT-FORMAT>  Output format ("GDALG" allowed) [not available in pipelines]
  --co, --creation-option <KEY>=<VALUE>                Creation option [may be repeated] [not available in pipelines]
  --lco, --layer-creation-option <KEY>=<VALUE>         Layer creation option [may be repeated] [not available in pipelines]
  --overwrite                                          Whether overwriting existing output dataset is allowed [not available in pipelines]
  --update                                             Whether to open existing dataset in update mode [not available in pipelines]
  --overwrite-layer                                    Whether overwriting existing output layer is allowed [not available in pipelines]
  --append                                             Whether appending to existing layer is allowed [not available in pipelines]
                                                       Mutually exclusive with --upsert
  --skip-errors                                        Skip errors when writing features [not available in pipelines]
  --sql <statement>|@<filename>                        SQL statement(s) [may be repeated] [required]
  --output-layer <OUTPUT-LAYER>                        Output layer name(s) [may be repeated]
  --dialect <DIALECT>                                  SQL dialect (e.g. OGRSQL, SQLITE)

Advanced Options:
  --if, --input-format <INPUT-FORMAT>                  Input formats [may be repeated] [not available in pipelines]
  --oo, --open-option <KEY>=<VALUE>                    Open options [may be repeated] [not available in pipelines]
  --output-oo, --output-open-option <KEY>=<VALUE>      Output open options [may be repeated] [not available in pipelines]
  --upsert                                             Upsert features (implies 'append') [not available in pipelines]
                                                       Mutually exclusive with --append

✨ DESCRIPTION ✨

so gdal vector sql gives you back one or several layers, all evaluated from your SQL statements. iconic behavior fr fr. 💫

and new glow-up alert: starting with GDAL 3.12, when you use --update and you don't specify an output dataset, you can use this to execute statements that actually modify the input dataset — think UPDATE, DELETE, and the whole chaotic crew. 😈

🦋 GDALG OUTPUT (ON-THE-FLY / STREAMED DATASET) 🦋

this program supports serializing the whole command line into a JSON file using the GDALG output format. the resulting file can then be opened as a vector dataset using the GDALG: GDAL Streamed Algorithm driver, and it'll apply the specified pipeline in a on-the-fly / streamed way. no cap, it's giving main character energy. 🌈

👑 PROGRAM-SPECIFIC OPTIONS 👑

  • --output-layer <OUTPUT-LAYER> — output SQL layer name(s). if you don't specify any, a generic layer name like "SELECT" may be generated for you. gotta be specified as many times as there are SQL statements — either as several --output-layer arguments, or a single one with the layer names combined with comma. slay. 💅
  • --quiet — ✨ Added in version 3.12. ✨ silencing those potential information messages. she's not rude, she's just focused. 🤫
  • --sql <sql_statement>|@<filename> — the SQL statement to execute that returns a table/layer (typically a SELECT statement). can be repeated to generate multiple output layers (repeating --sql <value> for each output layer). stacking them like bracelets bestie. 🫶
  • --dialect <DIALECT> — the SQL dialect. okay so here's the tea: ☕

by default the native SQL of an RDBMS is used when you're using gdal vector sql. if you're using sql as a step of gdal vector pipeline, this is only true if the step preceding sql is read — otherwise the OGRSQL dialect is used.

if a datasource does not support SQL natively, the default is to use the OGRSQL dialect, which can also be specified with any data source.

the SQL SQLite dialect can be chosen with the SQLITE and INDIRECT_SQLITE dialect values, and this can be used with any data source. overriding the default dialect may be beneficial because the capabilities of the SQL dialects vary.

supported dialects can be checked with gdal --format. for example:

$ gdal --format "PostgreSQL"
[...]
Supported SQL dialects: NATIVE OGRSQL SQLITE
[...]

$ gdal --format "ESRI Shapefile"
[...]
Supported SQL dialects: OGRSQL SQLITE
[...]

🌟 STANDARD OPTIONS 🌟

  • --append — whether appending features to existing layer(s) is allowed. this also creates the output dataset if it does not exist yet. go off, make new friends. 🍓
  • --co, --creation-option <NAME>=<VALUE> — lots of formats have one or more optional dataset creation options that control the particulars about the file created. for instance, the GeoPackage driver supports creation options to control the version. may be repeated.

the dataset creation options available vary by format driver, and some simple formats have no creation options at all. a list of options supported for a format can be listed with the --formats command line option, but the documentation for the format is the definitive source of information on driver creation options. see Vector drivers format specific documentation for legal creation options for each format.

note that dataset creation options are different from layer creation options. (yes bestie, they're different. don't mix them up. 💀)
  • --if, --input-format <format> — format/driver name to be attempted to open the input file(s). it's generally not necessary to specify it, but it can be used to skip automatic driver detection, when it fails to select the appropriate driver. this option can be repeated several times to specify several candidate drivers. note that it does not force those drivers to open the dataset. in particular, some drivers have requirements on file extensions.

may be repeated. 🎀

  • --lco, --layer-creation-option <NAME>=<VALUE> — lots of formats have one or more optional layer creation options that control particulars about the layer created. for instance, the GeoPackage driver supports layer creation options to control the feature identifier or geometry column name, setting the identifier or description, etc. may be repeated.

the layer creation options available vary by format driver, and some simple formats have no layer creation options at all. a list of options supported for a format can be listed with the --formats command line option, but the documentation for the format is the definitive source of information on driver creation options. see Vector drivers format specific documentation for legal creation options for each format.

note that layer creation options are different from dataset creation options. (repeat after me: they are NOT the same. 🙈)
  • --oo, --open-option <NAME>=<VALUE> — dataset open option (format specific). may be repeated. 🔥
  • -f, --of, --format, --output-format <OUTPUT-FORMAT> — which output vector format to use. allowed values may be given by
gdal --formats | grep vector | grep rw | sort
  • --output-open-option, --output-oo <NAME>=<VALUE> — ✨ Added in version 3.12. ✨ dataset open option for output dataset (format specific). may be repeated. 💜
  • --overwrite — allow program to overwrite existing target file or dataset. otherwise, by default, gdal errors out if the target file or dataset already exists. lowkey ruthless but honestly valid. 😭
  • --overwrite-layer — whether overwriting the existing output vector layer is allowed.
  • --skip-errors — ✨ Added in version 3.12. ✨ whether failures to write feature(s) should be ignored.
heads up bestie: this option sets the size of the transaction unit to one feature at a time, which may cause severe slowdown when inserting into databases. it's giving "efficient in spirit, slow in practice." 💀
  • --update — whether to open an existing output dataset in update mode.
  • --upsert — ✨ Added in version 3.12. ✨ a variant of --append where the OGRLayer::UpsertFeature() operation is used to insert or update features instead of appending with OGRLayer::CreateFeature().

this is currently implemented only in a few drivers: GPKG (GeoPackage vector), Elasticsearch (Geographically Encoded Objects for Elasticsearch) and MongoDBv3 (drivers that implement upsert expose the GDAL_DCAP_UPSERT capability).

the upsert operation uses the FID of the input feature, when it is set (and the FID column name is not the empty string), as the key to update existing features. it is crucial to make sure that the FID in the source and target layers are consistent. ⚠️

for the GPKG driver, it's also possible to upsert features whose FID is unset or non-significant (the --unset-fid option of gdal vector edit can be used to ignore the FID from the source feature), when there is a UNIQUE column that is not the integer primary key. periods. ☕

  • -q, --quiet — suppress progress bar and some warning messages. sometimes silence is the loudest flex. 🤫

🧚 RETURN STATUS CODE 🧚

the program returns status code 0 in case of success, and non-zero in case of error (non-blocking errors emitted as warnings are considered as a successful execution). so like, if she warns you but doesn't flop, she still ate. 💅✨

✨ EXAMPLES ✨

bestie, this is the part where it all clicks 👑 five real commands, all ready to copy-paste, no notes needed. every single one of these is verbatim from the page, so go wild 🫶

Example 1: Generate a GeoPackage file with a layer sorted by descending population

so you got a GeoPackage and you want a whole new one where the country layer is sorted by pop — biggest population first, descending, the way nature intended 💅

$ gdal vector sql in.gpkg out.gpkg --output-layer country_sorted_by_pop --sql="SELECT * FROM country ORDER BY pop DESC"

note the <italic>--output-layer</italic> naming your shiny result layer, and <italic>--sql</italic> doing the actual sorting work with that ORDER BY pop DESC. iconic behavior fr fr ✨

Example 2: Generate a GeoPackage file with 2 SQL result layers

two result layers, one GeoPackage, zero chaos. the way this works is you pass <italic>--output-layer</italic> a comma-separated list — here beginning,end — and then you feed it one <italic>--sql</italic> per layer, in that order. first one grabs 100 rows from the top, second one skips 100000 rows and grabs 100. matching energy, matched up 1:1 🌸

$ gdal vector sql in.gpkg out.gpkg --output-layer=beginning,end --sql="SELECT * FROM my_layer LIMIT 100" --sql="SELECT * FROM my_layer OFFSET 100000 LIMIT 100"

🦋 two --sql flags, two layer names, and they line up like besties at a concert.

Example 3: Modify in-place a GeoPackage dataset

in-place edits, no new file, we're changing the actual dataset here. the <italic>--update</italic> flag is the whole plot twist, and the SQL is a DELETE that drops every country with a population over 1e6.

$ gdal vector sql --update my.gpkg --sql "DELETE FROM countries WHERE pop > 1e6"
⚠️ okay listen — this one is <bold>in-place</bold>. it edits my.gpkg itself. that DELETE is not a lil preview, it is the real thing. so maybe breathe, double check your WHERE clause, and don't come crying to me if you nuke rows you lowkey needed 😭 (1e6 is one million, iykyk.)

Example 4: Add a new field to an existing layer of a GeoPackage

schema glow-up time 💅 again with <italic>--update</italic> so it lands right on the existing my.gpkg, and the SQL here is an ALTER TABLE adding a brand new column called abbrev of type STRING(10).

$ gdal vector sql --update my.gpkg --sql "ALTER TABLE countries ADD COLUMN abbrev STRING(10)"

same energy as example 3: <bold>--update` means the file changes for real. there's no undo button, bestie, this is not a Notes app 🙈

Example 5: Append to an existing layer of a GeoPackage file

and for the finale — the <bold>pipeline</bold> one 🔥 this is gdal pipeline chaining three steps together with !, reading europe.gpkg, running sql to pull only the countries with pop > 1e6, then <bold>appending</bold> the results into the world output layer of world.gpkg.

the backslashes \ at the end of those lines are just line continuations — it's all one command, it just looks prettier broken up like that ✨

$ gdal pipeline read europe.gpkg ! \
                       sql --sql "SELECT * FROM country WHERE pop > 1e6" ! \
                       write --append --output-layer-name=world world.gpkg

see how read, sql and write are separated by !? that's the pipeline vibe, each stage handing off to the next, main character energy all the way down the chain 🌈

AUTHOR

Even Rouault &lt;even.rouault@spatialys.com&gt; 🫶

1998-2026 ⭐

okay that's the whole page, bestie, you made it to the end 🎀 go forth and SELECT responsibly. this was GDAL-VECTOR-SQL(1), dated Aug 18, 2026, and honestly? it ate. periodt 💖 <anything else? no — that's the footer, and I've delivered it. slay 🦋>

📦 where this page comes from