4

Right now I am stumped on how to get two layers to interact. I have one layer that is just a map with town borders, with attributes for town codes. My other layer are data for businesses, wages for example. I setup a relate in the project properties menu between them using town codes, and I can see in the attribute table the the map layer knows what businesses exist in each town now.

However, I can't seem to make use of it. If, for example want to select by expression to just select towns where the average wage is over a certain level, I can't even begin because I can't write an expression using values from both layers, even though I set them up in a relation. How would I really go about doing this?

I am trying to figure out how to query, select, or anything using attributes from two related layers.

Improve this question
2

4 Answers 4

Reset to default
4

Instead of a relate, have you tried joining the tables?

Assuming QGIS handles relates and joins the same as ArcMap, you will not be able to query on the relate. Basically, a relate just defines a relationship within the map document, and does nothing to relate the tables in the geodatabase (such as a join). In order to query, a database relationship needs to be defined.

On a quick search for QGIS relates vs joins, I didn't see anything that really spelled out the difference, so I'm using a link to an ArcMap help page that defines relates vs joins. I know it's not QGIS, but I assume that they are defined similarly. Quoted text below in case of future link failure.

ArcMap provides two methods to associate data stored in tables with geographic features: joins and relates. When you join two tables, you append the attributes from one onto the other based on a field common to both. Relating tables defines a relationship between two tables—also based on a common field—but doesn't append the attributes of one to the other; instead, you can access the related data when necessary.

Typically, you'll join a table of data to a layer based on the value of a field that can be found in both tables. The name of the field does not have to be the same, but the data type has to be the same; you join numbers to numbers, strings to strings, and so on. You can perform a join with either the Join Data dialog box accessed by right-clicking a layer in ArcMap or the Add Join geoprocessing tool.

Suppose you obtain daily weather forecasts by county and generate weather maps based on this information. As long as the weather data is stored in a table in your database and shares a common field with your layer, you can join it to your geographic features and use any of the additional fields to symbolize, label, query, or analyze the layer's features.

Unlike joining tables, relating tables simply defines a relationship between two tables. The associated data isn't appended to the layer's attribute table like it is with a join. Instead, you can access the related data when you work with the layer's attributes.

...

Relates defined in ArcMap are essentially the same as simple relationship classes defined in a geodatabase, except that they are saved with the map instead of in a geodatabase.

Improve this answer
4
  • Mary Beth thank you! I am going through the join process now, however I have a concern about what will happen at the end. The data are a list of businesses, so there will be many businesses for each town. I originally chose relate because it sounded like that would make more sense. I can't imagine a table that would have all the towns and all the businesses contained within plus the attributes for the businesses, without needing a third axis. The "one to many" idea I read associated with relates, sounds like the kind of thing I need to achieve.
    – Luca
    Dec 29, 2015 at 14:59
  • Yes, so it only took the first business associated with each town code gave the town its attributes. How can I setup a one to many relationship that I can use?
    – Luca
    Dec 29, 2015 at 15:27
  • There are multple questions on one to many relationships in QGIS that may help you out. I'm not a QGIS expert--but hoped I could help get you to a point where you were able to query. :) Try these, or search for more. gis.stackexchange.com/questions/10788/… or gis.stackexchange.com/questions/149831/… for example
    – MaryBeth
    Dec 29, 2015 at 15:31
  • Heres an example of something I would like to do. I can understand if it needs to be done as a separate join or other operation for each one, I just need to know how. I would like to ask QGIS to take every business associated with a certain town code, and average the value in the wage column. So I can see the average wage for a town. Ideally I'd like to do all the towns on my map at once, but I'd be happy to start with one.
    – Luca
    Dec 29, 2015 at 15:33
2

I'd suggest a two step process:

  1. If your businesses do not have the town codes associated with them, then you need to do a spatial join. In QGIS this is under Vector - Data Management Tools - Join Attributes by Location. The target layer will be your businesses and the join layer will be the towns (I'm assuming the towns are a polygon layer). Every business (assuming these are points) will be assigned the attributes of the town where it is located. Once this is done, you'll be able to query the new business layer using the attributes of the towns - and create summary tables (next step).

  2. If you want to join summarized data from the businesses back to the towns (so you can map by town or do town-based queries) you'll need to download a QGIS plugin that will allow you to sum by attributes to create new tables. Under Plugins - Manage and Install Plugins, take a look at Group Stats or Statist. You can use one of these to summarize the businesses by the town attributes you assigned in step 1, to create average wages for the town (for example). Once you have business data summarized by town, then you can do a regular one to one attribute table join, to join your summary table back to the towns layer to map the summarized data.

Improve this answer
2

You can join tables in QGIS GUI under layer Properties > Joins. Here you can join attributes to the town border layer. This is just a 1:1 join take the first match in you buisness layer. If you want more control you can use Spatialite or PostGIS.

Improve this answer
3
  • Yes, having more than 1:1 is my problem. So, there is no way to do it in QGIS?
    – Luca
    Dec 29, 2015 at 15:12
  • 1
    No, you will need a spatial database like PostGIS to build a query like that. QGIS could visualize the query from PostGIS.
    – Jakob
    Dec 29, 2015 at 22:15
  • agreed with @Jakob ... the sooner you start using Spatialite or especially PostGIS the better off you'll be!
    – DPSSpatial_BoycottingGISSE
    Jan 12, 2016 at 20:56
1

What you want is relation_aggregate in Field Calculator

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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