2

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?
Improve this question
4
  • Your addendum was significant. Please Edit the question to specify exactly how many records are missing on either side of the join.
    – Vince
    Commented Apr 26, 2019 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
    Commented Apr 26, 2019 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
    Commented Apr 26, 2019 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
    Commented Apr 26, 2019 at 12:56

1 Answer 1

Reset to default
2

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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