Join non-spatial CSV to GeoJSON data source using ogr2ogr and SQLite dialect

Question

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?

Answer 1

My guess is the FROM part of the statement ( untested). The error states

no such table: join_tbl

In the query, you have:

""select input.*, join_tbl.* from input join 'join_tbl.csv'"

There is no alias assigned to input ( and that is then fine, it can find the qualified attributes input.*, but there is no join_tbl.

Try:

""select input.*, joinedtb.* from input, 'join_tbl.csv'.join_tbl joinedtb ..."

To be even more clear, consider using the AS statement:

""select input.*, joinedtb.* from input, 'join_tbl.csv'.join_tbl AS joinedtb ..."

HTH.