2

I am trying to find a way to join CSV data to a vector layer only when they both share matching values in multiple fields, rather than just one field.

This is a problem I'm running into with multiple projects, but I'll just describe one here as an example:

The data I am working with is as follows:

  • a shapefile layer containing a list of all U.S. towns/cities with population over 10,000.
  • 49 CSV files (one for each of the lower 48 states + 1 for D.C.) from the Bureau of Justice Statistics (BJS), containing data on violent crime rates by city.

I am trying to join the data from the BJS .csv files onto the "U.S. Towns and Cities" layer so that each town/city feature contains data for violent crime in that city.

The problem is that the the "Joins" interface will only let me select one field to join on. So for instance, when go to "US Towns & Cities" --> Properties --> Joins and click "+" to create a new join, this is what I have (as of QGIS version 2.12): Join dialog in QGIS (joining CSV to vector layer)

So here I am joining the BJS data for Georgia to the "U.S Towns and Cities" layer by city name ... now assume that I've done the same for the BJS Florida data.

Now, there are several ways that this causes trouble. The most significant problem is that there are name collisions for cities in different states. For instance, the "U.S. towns & cities" map layer contains 8 cities named "Gainesville", ALL of which now have the BJS data for both Gainesville, Georgia AND Gainesville, Florida associated with them:

Name collisions cause data to incorrectly be added to multiple cities

I need to only join the crime data for for a city when both the city name AND the state name coincide so that Gainesville, Florida's data only gets associated with Gainesville, Florida and not Gainesville, Georgia ... How do I do this?

Also, another problem caused by the above approach, which isn't necessarily causing incorrect results, but seems "wrong" is that every single city now contains nearly 1000 NULL fields (~20 of them for each of the 49 CSV files). ... I feel like there must be a better way to do this.

  • Also, I just wanted to say that I'm fairly comfortable with Python programming so even if there isn't a way to do what I want easily via the GUI, I'm open to suggestions for how to write a script that could join the crime data to the appropriate features. – J. Taylor Nov 30 '15 at 9:50
3

One way would be to concatenate the two id columns to a new virtual field with the field calculator for each table. Then join on the virtual fields.

enter image description here

Personally I would go for a db solution instead like Spatialite or PostGIS where you have more control of the join.

  • Thanks for your reply. Could you describe to me what the benefits of using a Spatialite/PostGIS DB would be in this particular scenario? What, specifically, would they enable me to do that would make this easier? – J. Taylor Nov 30 '15 at 20:31
  • 1
    It will not make it easier upfront using a real database, but you will be able to join tables on multiple columns (and a hole lot more) through the SQL standard. A more pure solution. Joining through a user interface is a quick and some would say dirty solution. If you do a lot of joining, you will run into scenarios where the UI wont be able to help you. If the concat and virtuel field works for you go with it. – Jakob Dec 1 '15 at 7:16
  • I accepted your answer, because the Spatialite database is exactly what I needed. I installed the QSpatialite plugin which made it extremely easy to import layer/CSV data into a database. This has made the types of queries I want to do way easier. Thanks! – J. Taylor Dec 3 '15 at 10:51

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.