Joining CSV file to Shp file using ogr2ogr and python?

Question

I am having problems with the code below i.e. parsing the SQL string in python. I keep getting an error. I am joining a CSV file called lookup_scales.csv to a shp file called RC_CATALOGUE_GRANTED. I am running the ogr2ogr command within python and using os.system to call it. It doesn't like the 'lookup_scales.csv' part i.e the apostrophes around it. BTW I was able to run the command successfully within a DOS window so not sure why is fails in the os.system call?

Also the shp file and csv file actually sit in different directories and would like to specify those locations in the ogr2ogr command. At the moment to get the command to work in DOS I have had to put the shp file and csv file in the directory I am running the python code I have.

Code:

os.system('ogr2ogr -sql "select RC_CATALOGUE_GRANTED.*, lookup_scales.* from RC_CATALOGUE_GRANTED left join 'lookup_scales.csv'.lookup_scales on RC_CATALOGUE_GRANTED.SCALE = lookup_scales.Scale" shape_join.shp RC_CATALOGUE_GRANTED.shp')

Above based on the Join CSV file to shapefile thread on here

Answer 1

Seems that your apostrophes ('lookup_scales.csv') are simply unnecessary - I really didn't get why they are there: when realizing left join the syntax is JOIN Table1_name (alias if need) ON Table1_name.field = Table2_name.field

os.system('ogr2ogr -sql "select RC_CATALOGUE_GRANTED.*, lookup_scales.* from RC_CATALOGUE_GRANTED left join lookup_scales.csv lookup_scales on RC_CATALOGUE_GRANTED.SCALE = lookup_scales.Scale" shape_join.shp RC_CATALOGUE_GRANTED.shp')