34

I am writing a tutorial for MSc students that uses QGIS. One of the tasks requires the attribute data to be modified (see pdf draft of tutorial here if interested ).

The question is how to get them to edit the attribute data. I know this question has been asked before, and the answer used to be simple: just use LibreOffice calc to modify it and then save it, ensuring the row order and column titles are kept the same. I have used this approach before to great effect.

Unfortunately, the solution no longer works: saving the .dbf file, in LibreOffice (5.4.2) or OpenOffice (3.4.1) Calc scrambles the data from the perspective of QGIS (only random integer numbers appear in the attribute table), and creates an additional .dbt file instead.

So to get to the question: how do I tell a class of MSc students to do this next week? I've considered the following options:

  1. Use R instead (great for me, but scary for students)
  2. Edit the file as a .csv, and then join based on timestamp (fine, but seems overly complex, and could introduce more error in the 2 hour practical)
  3. Use an alternative program: I've tried Gnumeric and Excel, neither of which generated good results quickly.

To help answer this question, I've created a replicable example. Open the file "points.shp" in QGIS, observer the attribute table then close. Edit a couple of cells in the attribute table "points.dbf" in LibreOffice - save. Re-open "points.shp" in QGIS and observe the scrambled attribute table. The other shapefiles in the .zip folder have already been corrupted by LibreOffice and OpenOffice to illustrate what the problem looks like for me.

In short, how do I best solve this problem?

Improve this question
7
  • 2
    Welcome to the site, I'd like to say this is a really well asked question, and hopefully we can get some good solutions worked out. I assume a bit of python is out of the question for these students, as that is how I would approach this problem in the first instance...
    – sgrieve
    Nov 8, 2012 at 14:49
  • 14
    Directly editing the .DBF part of a shapefile is too dangerous to recommend for routine use and should not be used by novices at all--at least not if anyone cares about the data. However, a more reliable solution of a similar nature is available: maintain a unique identifier field in the shapefile. Never edit it. Instead, keep the attributes in a related table (in any format your GIS can read), joined on the same identifier. Now, to a great degree, you can edit the attribute table freely without losing the connection between shape and attribute--as long as you protect the identifier field.
    – whuber
    Nov 8, 2012 at 15:14
  • 1
    Python solutions would be fine, as long as they're easy to implement and understand... Regarding keeping an identifier number that's a good option - (option 2 in my question) and probably my default answer. However, was hoping for a simpler "quick fix" that does not involve additional files (5 per shapefile is quite enough IMO!)
    – RobinLovelace
    Nov 8, 2012 at 15:53
  • 3
    What do you mean, 5 per shapefile? Try 9! :-) (Index files can accumulate, depending on which GIS is being used.) Once you need more than one related file to maintain a set of data--and shapefiles require a minimum of three--it's really not any extra trouble to throw in additional files. Add a metadata file, too, while you're at it. :-) And once you have set up the join, doesn't it occur automatically? (I do not see how the timestamp could be used for the join, though. That would be used instead for a version control system.)
    – whuber
    Nov 8, 2012 at 17:21
  • 3
    If you have any inclination to introduce them to databases, spatiaLite has a GUI for field editing and command line tools for the OSM data. Then you could involve a little SQL and your students would arguably be way ahead of the curve.
    – Scro
    Nov 8, 2012 at 18:29

3 Answers 3

Reset to default
20

Simplest answer I've found, based on Ryan Garnett's advice to do it within QGIS:

Use regexp_replace

This capability was added to field calculator 1 year ago by Jürgen Fischer (as illustrated by this bug report). I tried for a while to find out how to do this, but failed searching for "qgis regex" and other vague terms.

It's functionality is probably best illustrated by the need to standardise all values in the "name" column. For example, if we need to replace all cells that contain the pattern "Tesc" with 'Tesco', removing superfluous information like "Tesco, Infirmary Road" (that OSM contributors frequently add), the following function can be used:

regexp_replace("name", 'Tesc.*','Tesco')

enter image description here

I guess this is Pythonic regular expression functionality, as it's a little different from grep, which I've used before.

I think more documentation (detail on the QGIS website is limited to a few words) is needed on how this works, so I've bookmarked the following pages for future reference:

This solution has a couple of advantages:

  1. Simple: no extra programs or add-ons needed
  2. Introduces students to the power of regular expressions, using (Pythonic?) functions (the "'" representing text is interesting. Warning: " or no symbol fails) and command-line functions.

Apologies for answering my own question, and many thanks to others for guiding me toward the light! I hope this question thread will be of educational use to others facing similar problems.

Improve this answer
1
10

This may be a dumb answer, as it may be a learning objective for the grad students, but is there a reason why you are not editing the attributes in QGIS with the use of the Field Calculator? In previous versions of QGIS (pre 1.7) editing attributes directly within QGIS was not possible; hence the need for editing .DBF files in OpenOffice etc.

As of 1.7 and 1.8 you can edit attribute data directly in the Field Calculator, with the ability to perform conditional expressions. There are also Python Attribute plugins available, which can provide additional options for attribute editing. This may be a solution, and or, an option for your tutorial.

As a previous Masters student and now a university employee, I understand this issue, the previous requirement to perform it outside of QGIS, and now appreciate the ability to perform all the attribute edits within QGIS. I would be happy to assist you with this more, if you desire.

Improve this answer
3
  • 1
    Hi Ryan, yes that would be the ideal solution. It's the ability to search and replace text in the attribute table, to standardise messy OSM names, that is pushing me to try and edit the .dbf file directly. I would rather edit it within QGIS. Will try the Python Attribute plugin - may be the idea solution. Should have made the fact I'm looking for regex search/replace capability clearer in the question.
    – RobinLovelace
    Nov 9, 2012 at 12:05
  • @user1694378 you can still clarify your question.
    – underdark
    Nov 9, 2012 at 13:13
  • Thanks underdark for nudging me to do that - better reflects what I actually wanted to do.
    – RobinLovelace
    Nov 9, 2012 at 13:16
4

You can use LibreOffice's Base to modify your dbf files.

  1. Open Base (get Main Menu)
  2. Select "Connect to an existing database" and select type "dBASE".
  3. Select the folder with your dbf files.
  4. Select final options as desired and "Finish".
  5. Name your database connection file whatever you want (eg "MyxBasefiles.odb) and save it somewhere. Henceforth access your database files by clicking this file. ooOoo, Nice.
  6. Select "Tables" on the left panel, and there are your data files (called "tables"). Mess with them.

You can now start editing them. Hopefully that should be good enough for your purposes.

You can also try DBF Explorer though it's Windows only. It does have search and replace though.

enter image description here

Improve this answer
5
  • 1
    Hi R.K. Thanks for the prompt answer: seems to be a reliable and safe way to edit the .dbf files. I just tried what you said and I'm pleased to say it works.
    – RobinLovelace
    Nov 8, 2012 at 15:34
  • Good to know. Nice lesson you have there. :)
    – R.K.
    Nov 8, 2012 at 15:39
  • But... I just tried what you said and I'm pleased to say it works for individual cells. Unfortunately it's not a 100% solution for this particular application, because it appears that OOBase does not have the search-replace functionality that's required for the tutorial due to a [bug] (lists.freedesktop.org/archives/libreoffice-bugs/2010-December/…). Definitely worked on individual cells though: tested. So that's a 90% solution for cell-by-cell editing, unless I've missed search-replac functionality. Any ideas how to replace all "Tesc.*" entries with "Tesco", for example?
    – RobinLovelace
    Nov 8, 2012 at 15:48
  • Better link for OO Base bug: bugs.freedesktop.org/show_bug.cgi?id=32506
    – RobinLovelace
    Nov 8, 2012 at 15:49
  • Updated my answer.
    – R.K.
    Nov 8, 2012 at 16:14

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.