Joining tables in databases_QGIS 2 Cookbook-QQ阅读女生青春网

上QQ阅读APP看书，第一时间看更新

Joining tables in databases

If you use a database (SpatiaLite or PostGIS) to store your data, vector and nonspatial, then you also have the option of using the database and SQL to perform tables joins. The primary advantages of this method include being able to filter data before loading in the map, perform multitable joins (three or more), and have full control over the details of the join via queries.

Getting ready

You'll need at least two layers in either a SpatiaLite or PostGIS database. These two layers need at least one column in common, and the column in common should contain unique values in at least one table. In this case, our example uses the census_wake_2000 polygon layer and census_wake_2000_pop.csv.

How to do it…

Open the DB Manager plugin that comes with QGIS. You can find this in the Database menu.
Select your database from the tree on the left-hand side, use cookbook.db in SpatiaLite (which was created in Chapter 1, Data Input and Output).

Tip

If you don't see this database listed, use Add SpatiaLite Layer (the icon or the menu item), or right-click on SpatiaLite in the Browser window to make a new connection and add it to an existing database.
Now, open the SQL window (the second icon from the left in top toolbar of the plugin window).

Put in the following SQL code to query and JOIN the tables:

SELECT *
FROM census_wake2000 Sas a
JOIN census_wake2000_pop AS b
ON a.stfid = b.stfid;

How it works…

SELECT lists all the columns that you want from the source tables; in this case, * means everything. FROM is the first (left) table, as a is an alias, which is used so that there's less typing later. JOIN is the second (right) table, and ON indicates which columns to should be matched between the two tables. The rest of how this works in relational database theory is best explained in other texts.

There's more…

In databases, there's more than one type of join. You can perform a join where you retain only the matches in both tables, or you can retain all content from the left (first table) and any matches from the right. You can also control how a one-to-many relationship is summarized or select specific records instead of aggregating.

If you want to save the results of a query you have two options. You can make a view or a new table. A view is a saved copy of your query. Every time you open it, the query will be rerun. This is great if your data changes because it will always be up-to-date, and this doesn't use any additional disk space. On the other hand, a table is like saving a new file; it becomes a static new copy of the results. This is good to repeatedly access the same answer, and it is usually faster to use, especially for large tables.