I am attempting to do a simple join of a non-spatial CSV file to a GeoJSON data source using the built-in SQL capabilities in ogr2ogr. I have successfully managed to do this using the OGR SQL dialect with a command like this:

ogr2ogr -f geojson -sql "select input.*, join_tbl.* from input join 'join_tbl.csv'.join_tbl on input.id = join_tbl.id" output.geojson input.geojson 

To run it using the SQLite dialect I added -dialect sqlite to the previous command:

ogr2ogr -f geojson -dialect sqlite -sql "select input.*, join_tbl.* from input join 'join_tbl.csv'.join_tbl on input.id = join_tbl.id" output.geojson input.geojson 

This results in the following error:

ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2(select input.*, join_tbl.* from input join "_OGR_1" on input.id = join_tbl.id): no such table: join_tbl 

I opened up the CSV in Excel and as expected, the table name is the same as the filename. That makes sense. CSVs are flat files and can't contain multiple tables.

So next, I tried removing the table assignment ('join_tbl.csv'.join_tbl -> 'join_tbl.csv') from the previous command:

ogr2ogr -f geojson -dialect sqlite -sql "select input.*, join_tbl.* from input join 'join_tbl.csv' on input.id = join_tbl.id" output.geojson input.geojson 

However, this still resulted in an error:

ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2(select input.*, join_tbl.* from input join 'join_tbl.csv' on input.id = pg.id): no such table: join_tbl.csv 

There is an example for carrying out a join using the SQLite dialect in the ogr documentation, but it is for a .dbf file:

SELECT p.*, NAME FROM poly p JOIN "idlink.dbf"."idlink" il USING (eas_id) 

Based on the errors I've been getting, this syntax does not appear to suggest my path forward. However, I've been looking at this for a while now and would appreciate fresh eyes and any ideas of how best to proceed.

I am attempting to do a simple join of a non-spatial CSV file to a GeoJSON data source using the built-in SQL capabilities in ogr2ogr. I have successfully managed to do this using the OGR SQL dialect with a command like this:

ogr2ogr -f geojson -sql "select input.*, join_tbl.* from input join 'join_tbl.csv'.join_tbl on input.id = join_tbl.id" output.geojson input.geojson 

To run it using the SQLite dialect I added -dialect sqlite to the previous command:

ogr2ogr -f geojson -dialect sqlite -sql "select input.*, join_tbl.* from input join 'join_tbl.csv'.join_tbl on input.id = join_tbl.id" output.geojson input.geojson 

This results in the following error:

ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2(select input.*, join_tbl.* from input join "_OGR_1" on input.id = join_tbl.id): no such table: join_tbl 

I opened up the CSV in Excel and as expected, the table name is the same as the filename. That makes sense. CSVs are flat files and can't contain multiple tables.

So next, I tried removing the table assignment ('join_tbl.csv'.join_tbl -> 'join_tbl.csv') from the previous command:

ogr2ogr -f geojson -dialect sqlite -sql "select input.*, join_tbl.* from input join 'join_tbl.csv' on input.id = join_tbl.id" output.geojson input.geojson 

However, this still resulted in an error:

ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2(select input.*, join_tbl.* from input join 'join_tbl.csv' on input.id = pg.id): no such table: join_tbl.csv 

There is an example for carrying out a join using the SQLite dialect in the ogr documentation, but it is for a .dbf file:

SELECT p.*, NAME FROM poly p JOIN "idlink.dbf"."idlink" il USING (eas_id) 

Based on the errors I've been getting, this syntax does not appear to suggest my path forward.

How should I proceed?