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?

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

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 | |
  • 8
    Oh gdal/ogr - you never cease to amaze me.. – radek May 13 '14 at 7:49
  • @luke This is brilliant, but how can I do this, if my shapefile and csv are located in a folder in my PC...where must I include my Path to that folder? – user32496 Jun 30 '16 at 9:27
  • @Luke I have, BUT I definitely did not do it right, ogr2ogr -sql "select C:\Path\inshape.*, C:\Pathjoincsv.* from C:\Path\inshape left join 'C:\Path\joincsv.csv'.joincsv on C:\Path\inshape.GISJOIN = joincsv.GISJOIN" shape_join.shp inshape.shp – user32496 Jun 30 '16 at 9:33
  • 4
    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 Jun 30 '16 at 9:40
  • I get the following error: "Warning 1: Value 1060008100 of field inshape.MA of feature 86 not successfully written. Possibly due to too larger number with respect to field width" Any ideas how to solve this? – RutgerH Apr 3 '17 at 10:25

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 | |
  • 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 Apr 17 '15 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 Apr 19 '15 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 Apr 23 '15 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 Apr 23 '15 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 Apr 23 '15 at 20:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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