18

I have a shapefile with several attributes, for example "YEAR", "COUNTY", and "AREA". I also have a CSV file with more fields that I want in the shapefile, such as "POPULATION". Both the shapefile and the CSV file have a field "GISJOIN".

I know how to do a join in QGIS. But how can I make a permanent join and write to a shapefile using ogr2ogr or one of the other tools in GDAL/OGR?

Improve this question
2
  • Does your GDAL/OGR build have spatialite or sqlite support?
    – BradHards
    Commented May 13, 2014 at 0:30
  • @BradHards: It doesn't look like it. It's the packaged GDAL/OGR build from Ubuntu 14.04.
    – Lincoln Mullen
    Commented May 13, 2014 at 0:48

3 Answers 3

Reset to default
32

The ogr2ogr utility supports a limited sql syntax. You can join your CSV to the shapefile using something like the following:

ogr2ogr -sql "select inshape.*, joincsv.* from inshape left join 'joincsv.csv'.joincsv on inshape.GISJOIN = joincsv.GISJOIN" shape_join.shp inshape.shp
Improve this answer
11
  • 12
    Oh gdal/ogr - you never cease to amaze me..
    – user173
    Commented May 13, 2014 at 7:49
  • 6
    ogr2ogr -sql "select inshape.*, joincsv.* from inshape left join 'path/to/joincsv.csv'.joincsv on inshape.GISJOIN = joincsv.GISJOIN" shape_join.shp path/to/inshape.shp
    – user2856
    Commented Jun 30, 2016 at 9:40
  • 1
    I'm not able to get this working by following the solution to join a shape with a .csv file, I get an error message 'SELECT from table joincsv failed, no such table/featureclass'. I'm reasonably sure that the .csv can be found as it's all in one folder, and if I write a wrong filename the error message is different (Unable to open secondary datasource...)
    – stefan0n
    Commented Feb 2, 2021 at 18:47
  • 1
    @stefan0n ask a new question and say you're trying to apply this answer. Include a link to this answer and add your full command and error as text.
    – user2856
    Commented Feb 3, 2021 at 5:09
  • 1
    Thanks, while I was writing it I've found the solution: here in 'path/to/joincsv.csv'.joincsv it's essential to use the same name of the CSV file after the 'dot', so use exactly 'whatever.csv'.whatever (and then take into account the .shp field name limit of 10) chars.
    – stefan0n
    Commented Feb 3, 2021 at 15:41
15

The accepted answer is really useful, but I found that it was slow with a large-ish database. I believe it also limits your options when joining the data.

My method now is to pull everything into SQLite (using a combination of csvkit and ogr2ogr):

csvsql --db sqlite:///myjoindb.db --insert myjoincsv.csv
ogr2ogr -append -f "SQLite" myjoindb.db myjoinshp.shp

Then join everything and create a shapefile out of it:

ogr2ogr -f "ESRI Shapefile" -sql "SELECT csv.*, shp.* FROM myjoinshp shp INNER JOIN myjoincsv csv ON csv.joinfield = shp.joinfield" joined_output.shp myjoindb.db
Improve this answer
5
  • Hey Eric, I'm getting an error that myjoindb.db already exists. Is that secondline db supposed to be named same as first?
    – GIS Danny
    Commented Apr 17, 2015 at 21:46
  • Ah good point, on the second line you'll want to add a -append in there to give ogr2ogr permission to open up an existing SQLite db and add to it. I've edited the answer to show this.
    – eric brelsford
    Commented Apr 19, 2015 at 20:07
  • Thanks! Working great. One more question, hopefully quick. The first line, I have a field geoid that keeps getting weirdly converted to negative floats. It's a census id that I want to treat as string, I've tried wrapping the string with quotes and that didn't work. Is there a command like: csvsql --db sqlite:///myjoindb.db --insert myjoincsv.csv geoid.map(str) or something?
    – GIS Danny
    Commented Apr 23, 2015 at 16:27
  • What's weird is the join occurs correctly on geoid but the output has the geoid with -2147184982 instead of what went into it.
    – GIS Danny
    Commented Apr 23, 2015 at 16:41
  • Sounds like geoid is being converted to a number, but the number is so large that it overflows and becomes negative. Check the documentation for csvsql, you might try specifying a table where you explicitly say that geoid is a string, else --no-inference might help.
    – eric brelsford
    Commented Apr 23, 2015 at 20:30
5

Based on Eric's answer, this is an approach using the SQLite command instead of csvkit. If you're not already starting from a GeoPackage, convert to it first:

ogr2ogr -f GPKG myjoinshp.gpkg myjoinshp.shp

Then, exploiting the fact that a GeoPackage is actually an SQLite database, import the CSV as a new table (named myjoincsv), which modifies the existing GeoPackage:

sqlite3 myjoinshp.gpkg --cmd '.mode csv' '.import myjoincsv.csv myjoincsv'

This step could also be done in a GUI, e.g. DB Browser for SQLite supports importing a CSV into the loaded database by drag and drop.

Finally, make a new GeoPackage or shapefile, using a join, as in Eric's answer:

ogr2ogr -f GPKG joined_output.gpkg myjoinshp.gpkg -sql "SELECT shp.*, csv.* FROM myjoinshp shp JOIN myjoincsv csv ON shp.joinfield = csv.joinfield"
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.