TransWikia.com

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

Geographic Information Systems Asked by MattEnvSys on August 27, 2021

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?

One Answer

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.

Answered by klewis on August 27, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP