How to decide when to use the SQLite dialect when running ogr2ogr on large files

Question

When using ogr2ogr, I got used to using the -dialect sqlite option. I often see this option in GIS.SE answers (examples), and I notice others also found it helpful:

+1 for the inclusion of -dialect sqlite. I usually include this setting, and when I've messed something up it's usually because I've forgotten this flag.

Recently I noticed however, that processing GeoJSON files using ogr2ogr is sometimes significantly faster without -dialect sqlite. Below I create an example GeoJSON file, then I time its processing with & without -dialect sqlite. The whole process is also followable via this Notebook.

Let's create a GeoJSON file with 10000 features. Each feature will be a Point, and each point will have a property assigned to it: property0. Points and properties are randomly generated. The code:

import numpy as np
import geopandas as gpd
from shapely.geometry import Point

df = gpd.GeoDataFrame(geometry=gpd.GeoSeries([Point(np.random.uniform(-70,70,size=2))
                                              for _ in range(10000)]))
df = df.assign(property0=[np.random.randint(0,1000) for _ in range(len(df))])

df.to_file("test.geojson")

This is how the first 5 rows of the df look like:

                     geometry  property0
0   POINT (-5.63292 26.22310)        802
1  POINT (58.49687 -39.66535)        761
2  POINT (-32.79031 64.25744)        904
3  POINT (-65.62557 61.95311)        993
4   POINT (56.99825 23.47068)        525

(via print(df.head(5)))

Filtering using where

I create a GeoJSON which contains a subset of the features of test.geojson. I randomly generate n distinct property0 values, and filter for those without, and with the -dialect sqlite option.

Filter for 100 distinct properties

Generate property0 values we are filtering for:

proplist = ", ".join([f"{e}" for e in np.random.choice(range(1000), 100, replace=False)])

Then, I use Jupyter's built-in %%timeit functionality to measure how long it took for ogr2ogr to filter out those features from test.geojson which have property0 value in proplist, without -dialect sqlite:

%%timeit
!ogr2ogr -f GeoJSON \
        filtered_without_sqlite.geojson test.geojson \
        -sql "select * from test where property0 in ({proplist})"

Output:

282 ms ± 3.09 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

Now with -dialect sqlite:

%%timeit
!ogr2ogr -f GeoJSON \
        -dialect sqlite \
        filtered_with_sqlite.geojson test.geojson \
        -sql "select * from test where property0 in ({proplist})"

Output:

3.08 s ± 9.79 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

Filter for 200, 300, 400, 500 distinct properties

I repeat the above experiment 4 times, but instead of using:

proplist = ", ".join([f"{e}" for e in np.random.choice(range(1000), 100, replace=False)])

I generate more properties to filter on, ie:

proplist = ", ".join([f"{e}" for e in np.random.choice(range(1000), 200, replace=False)])
proplist = ", ".join([f"{e}" for e in np.random.choice(range(1000), 300, replace=False)])
proplist = ", ".join([f"{e}" for e in np.random.choice(range(1000), 400, replace=False)])
proplist = ", ".join([f"{e}" for e in np.random.choice(range(1000), 500, replace=False)])

and measure runtime for each.

Results

I plotted the results of execution times:

The points in the two generated files are the same (checked via QGIS), albeit they are in different order (and the name field is also different, but it isn't that important).

The first 1000 characters of filtered_without_sqlite.geojson is (head -c1000 filtered_without_sqlite.geojson):

{
"type": "FeatureCollection",
"name": "test",
"features": [
{ "type": "Feature", "properties": { "property0": 229 }, "geometry": { "type": "Point", "coordinates": [ 36.931337051267121, 38.901449137171483 ] } },
{ "type": "Feature", "properties": { "property0": 445 }, "geometry": { "type": "Point", "coordinates": [ -16.567190926238162, 40.151428915321432 ] } },
{ "type": "Feature", "properties": { "property0": 40 }, "geometry": { "type": "Point", "coordinates": [ -9.590425291213656, 39.600409466095158 ] } },
{ "type": "Feature", "properties": { "property0": 941 }, "geometry": { "type": "Point", "coordinates": [ 37.255020897007654, 11.375693951139255 ] } },
{ "type": "Feature", "properties": { "property0": 166 }, "geometry": { "type": "Point", "coordinates": [ -44.239065498718887, -68.406317100380491 ] } },
{ "type": "Feature", "properties": { "property0": 249 }, "geometry": { "type": "Point", "coordinates": [ 3.040414321300872, -50.212356223925809 ] } },
{ "type": "Feature", "propertie

Whereas head -c1000 filtered_with_sqlite.geojson is:

{
"type": "FeatureCollection",
"name": "SELECT",
"features": [
{ "type": "Feature", "properties": { "property0": 4 }, "geometry": { "type": "Point", "coordinates": [ -42.307415860321797, -8.602295333840331 ] } },
{ "type": "Feature", "properties": { "property0": 4 }, "geometry": { "type": "Point", "coordinates": [ 4.140229779813083, 32.240824897447425 ] } },
{ "type": "Feature", "properties": { "property0": 4 }, "geometry": { "type": "Point", "coordinates": [ 51.571767023166004, 24.081018915029489 ] } },
{ "type": "Feature", "properties": { "property0": 4 }, "geometry": { "type": "Point", "coordinates": [ 60.001999788615308, -53.534832599917479 ] } },
{ "type": "Feature", "properties": { "property0": 4 }, "geometry": { "type": "Point", "coordinates": [ 33.216443503270682, -47.345635006424068 ] } },
{ "type": "Feature", "properties": { "property0": 7 }, "geometry": { "type": "Point", "coordinates": [ -59.507097989015783, 19.719582330065677 ] } },
{ "type": "Feature", "properties": { "pr

I notice that sqlite also sorted the features by their property0 value (I don't need this sorting).

And finally, the question:

How to decide when to use -dialect sqlite?

When processing large datasets, the slowness displayed on the above plot is not always workable. How do I decide when to use, and when not to use -dialect sqlite? Often, it makes creating commands much easier, but sometimes the time it takes to execute those easily writable commands is prohibitively long. Some ogr2ogr commands take a long time, regardless of the dialect.

Now, if an ogr2ogr command takes a long time, I try constructing one without using -dialect sqlite. I hope better understanding of -dialect sqlite will lead to more efficient use of my time.

Answer 1

It is impossible to give a generic answer especially because your use case is rather special. What makes it special is:

• The source data is GeoJSON, a format that does not support any kind of indexing.
• You test only one query type with a long list of expressions given within IN (value1,value2,value3...) searching data from an unindexed field.

In this case it seems that the OGR SQL dialect is using a faster route for the query. When the SQLite SQL dialect is used, the source data are first converted into a virtual SQLite database. Then by these answers https://stackoverflow.com/questions/38199080/efficient-sqlite-query-based-on-list-of-primary-keys the long IN (...) query triggers a creation of a temporary index for the values within IN (...) and a few other operations. However, because the property0 does not have an index, the search is not especially fast. Also the linear growth in time suggests that each value is tested separately. I can't say why OGR SQL is faster in this case but perhaps it reads all the GeoJSON data first into memory and builds better temporary indexes than SQLite, making is possible to test the whole array of values inside IN in one run.

A test that proves the inefficiency of GeoJSON can be performed this way:

• convert GeoJSON data into geopackage ogr2ogr -f gpkg test.gpkg test.geojson
• create an index for property0 ogrinfo -sql "create index prop_idx on test(property0)" test.gpkg
• query ogrinfo -dialect sqlite -sql "select count(*) from test where property0 in (46,48,49,50,51,52,53,54,55)" test.gpkg

Adopt the test query as you like. Notice that when you tested with ogr2ogr and select *... the process is using lot of time for writing all the selected features into a file but that is not important - ogrinfo with select count(*) is doing much less extra.

To the question "When to use SQLite dialect" I would say of course when you need such functions from either SQLite https://www.sqlite.org/lang.html or SpatiaLite https://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html that the OGR dialect does not have. When both dialects can do the job, SQLite is using more standard SQL syntax than OGR dialect https://gdal.org/user/ogr_sql_dialect.html and reusing for example PostGIS queries may be easier. But if OGR dialect is faster feel free to use that. Just remember that when SQL query is slow in 95% of cases the indexes are missing or they do not support the query.