官术网_书友最值得收藏!

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…

  1. Open the DB Manager plugin that comes with QGIS. You can find this in the Database menu.
  2. 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.

  3. Now, open the SQL window (the second icon from the left in top toolbar of the plugin window).
  4. 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.

See also

  • Refer to the Creating views in SpatiaLite and Creating views in PostGIS sections in this chapter to learn how to make views of the query results.
  • For more general information on writing SQL queries refer to http://sqlzoo.net/
  • Refer to Chapter 1, Data Input and Output, about using the cookbook.db database
主站蜘蛛池模板: 雷山县| 探索| 平泉县| 奉化市| 中阳县| 西宁市| 洛浦县| 山丹县| 陇西县| 利川市| 潜江市| 隆化县| 天等县| 温州市| 康马县| 寿光市| 来凤县| 华阴市| 永兴县| 航空| 宜宾县| 阳曲县| 共和县| 嵊泗县| 西林县| 泸定县| 山西省| 昌乐县| 巴东县| 汝南县| 绥德县| 宁波市| 桃源县| 华阴市| 麟游县| 罗定市| 年辖:市辖区| 儋州市| 当阳市| 湖南省| 长乐市|