4

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?

7

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.

  • Thank you! Assigning an alias appears to fix my errors. The only difference between your suggestion and what ended up working is that I still had to specify the table. In other words instead of 'join_tbl,csv' AS joinedtb, it's 'join_tbl.csv'.join_tbl AS joinedtb. – maptastik Nov 21 at 23:04
  • If you note in your comment, you have a typo - you put a ',' comma in 'join_tbl,csv'. That is incorrect syntax. What I specified, is 'join_tbl.csv', which is your table name. So I think my answer was correct, or you need to specify what are your CSV file name and sheet if not the same. – MartinT Nov 21 at 23:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.