I've been trying to use a ogr2ogr -sql call to create a ESRI.shp file with the joined attributes - but it keeps on failing due to

"ERROR 1 SELECT from table inshape failed, no such table/feature"

My code is:

ogr2ogr -sql "select inshape.*, joincsv.* from inshape left join 'D:\testfolder\attributes.csv'.joincsv on inshape.ID = joincsv.ID" shape_join.shp D:\testfolder\europe_shapes.shp  

The .shp and the .csv may have some ID's that not apear in the other table, I wish to join them so that any .csv IDs that don't exist in the .shp file are droped, while the new colums of .shp IDs that don't exist in the .csv are filled with "NULL".

To be honest I don't quite understand the syntax:

  • Why is the .csv source defined within the sql statement while the input .shp and shape_join.shp outside of the SQL statement?
  • Is shape_join.shp the output file/ path?
  • Your addendum was significant. Please Edit the question to specify exactly how many records are missing on either side of the join. – Vince Apr 26 at 11:36
  • in my case it can vary, depending on the situation (i plan to run the code on multiple instances with varying input data. the result should just add attributes to any shape record with corresponding "ID" and fill any surplus / notfound shp. records with "NULL" or similar – John Lloyd Apr 26 at 11:54
  • 1
    Shapefiles don't support NULLs; this is the primary reason I don't use them. CSVs aren't tables; I won't use them either (except as a source for conversion to real tables). – Vince Apr 26 at 12:11
  • 1
    Your table names do not match, ogr2ogr -sql "select europe_shapes.*, attributes.* from europe_shapes left join 'D:\testfolder\attributes.csv'.joincsv on europe_shapes.ID = attributes.ID" shape_join.shp D:\testfolder\europe_shapes.shp – klewis Apr 26 at 12:56

Thanks for the input everyone! I found a solution that worked:

ogr2ogr -sql "select europe_shapes.*, attributes.* from europe_shapes left join 'attributes.csv'.attributes on europe_shapes.ID = status.ID" shape_join.shp D:\testfolder\europe_shapes.shp

it seems that maybe the variable has to have the same name as the input.csv

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.