Join a CSV file to GeoJSON using ogr2ogr in a Python script?

Question

I have a CSV which I would like to join to a GeoJSON.

I found this answer which I tried to implement but I can't get it to work just yet.

Here's an extract of my python:

join_command = ['ogr2ogr', '-f', 'GeoJSON', '-sql',
 'SELECT wfp.*, wfp_desc.* FROM wfp.geojson left join "/home/wfp/wfp_desc.csv".wfpdesc ON wfp.vw_group_id = wfp_desc.vw_group_id',
 '/home/wfp/wfp_join.geojson', '/home/wfp/wfp.geojson']
subprocess.check_call(join_command)

But I get this error when I try to run it:

ERROR 1: Unable to open secondary datasource `wfp' required by JOIN.

I've tried running it after removing the .geojson before 'left join':

join_command = ['ogr2ogr', '-f', 'GeoJSON', '-sql',
 'SELECT wfp.*, wfp_desc.* FROM wfp left join "/home/wfp/wfp_desc.csv".wfpdesc ON wfp.vw_group_id = wfp_desc.vw_group_id',
 '/home/wfp/wfp_join.geojson', '/home/wfp/wfp.geojson']
subprocess.check_call(join_command)

But then I get this error:

ERROR 1: SELECT from table wfp failed, no such table/featureclass.

What am I missing?

Edit: 28/05/2019

Following the comment from klewis. I now have a working ogr command ready to go into python.

Using ogrinfo I found the correct layer name for the GeoJSON file. It was originally a shapefile and the original layer name carried over instead of the converted one.

However, following the join. The attribute fields have the layer names prefixed to them.

For example: wfp.vw_group_id / wfp.name / wfp_desc.description

Is it possible to carry out the join without prefixing the layer name to the attribute fields?

• zwnk: gdal.org/programs/ogr2ogr.html -nln lets you set a layer name
• klewis: About the table_name.field_name issue, instead of using a wildcard in -sql statement, try Select wfp.name as name, etc for each field.

Answer 1

Starting with GDAL 2.2, the Geojson Layer name is built with the following logic: You do not reference the GeoJSON file like a csv file.

1. If a "name" member is found at the FeatureCollection level, it is used.
2. Otherwise if the filename is regular (ie not a URL with query parameters), then the filename without extension and path is used as the layer name.
3. Otherwise OGRGeoJSON is used.

This should be close to working if wfp.geojson does NOT have a Name member, otherwise use the Name value for the Layer name. Your single quotes, double quotes were reversed

ogr2ogr -f "GeoJSON" -sql "SELECT wfp.\*, wfpdesc.\* FROM wfp LEFT JOIN '/home/wfp/wfp_desc.csv'.wfpdesc ON wfp.vw_group_id = wfpdesc.vw_group_id" /home/wfp/wfp_join.geojson /home/wfp/wfp.geojson

Once you have the command-line syntax working, then work on feeding it to Python.