1

While you can build a atlas with one page for every single feature in a coverage layer, it seems to be more difficult, to build one atlas page from a couple of features.

Example: I have 2 tracks and each is split in several parts. In attribute table it looks something like this:

Feature   track
Part 1      1
Part 2      1
Part 3      2
Part 4      2
Part 5      2

If I build a atlas filtered by track, I will get 2 pages for every part of track 1 and 3 pages for every part of track 2. But I need just one for all parts of track 1 and one for all parts of track 2. Of course I could merge the parts but I can/will not do any changes to the layer. I could also build a new file with merged parts - but also this won't be a good solution, too. I'm looking for a more "dynamic" way with PostGIS, Python, QGIS

  • I don't have time to fully think through this, but my initial thought is to create a view of multipart features in PostGIS using SQL and then create the atlas in QGIS from the view. – Nate Wanner Feb 22 '17 at 12:54
  • This could be a solution! But how could I do that? I'm not very versed in SQL querying... – MAP Feb 24 '17 at 13:35
  • 1
    I'll try to play around with it this weekend. – Nate Wanner Feb 24 '17 at 18:25
2

In your question, you say that a merge would work for your atlas needs, but would not be dynamic. I presume you are referring to a merge function in QGIS, which creates a new feature class.

Since you are using PostGIS, you can create a "view" which is an alternate way of looking at data in tables, without actually creating more tables or changing the tables themselves. If you've worked in MS Access, they are the same as "queries." Postgres/PostGIS provides the ability to do a host of spatial operations on the queries. You say in the comments that you aren't very versed in SQL, so I've tried to break it down. Unfortunately, getting this to work reliably involves multiple PostGIS functions simultaneously so it isn't the simplest example for starting into PostGIS SQL.

I setup a simple table in PostGIS using QGIS's DB Manager and added five parts to match the sample in your question:

five parts

In DB Manager, click on the icon that has the sheet with a wrench, between the refresh icon and upload/download table icons in the top-left. This will open the SQL Window.

We will create the view in pgAdmin, as I don't know a way to do it in QGIS directly. Test the SQL below by entering it in the top, then clicking "Execute (F5)". You may need to adjust for your table and column names. The query language is borrowed from the aggregate sample here. You may find ST_Union more appropriate depending on the situation, but as explained on the linked web pages you will take a performance hit. I am assuming that there aren't any problems with the geometry of your parts within your tracks and they pass basic topology checks that would trip up ST_Collect.

In theory, all we need is the following (and this may work just fine for you):

SELECT 
    t.track,
    ST_Collect(t.geom) AS geom
  FROM track_table AS t
  GROUP BY track

Instead, we'll use the following to guard against potential problems:

SELECT 
    t.track,
    ST_Multi(ST_Collect(t.geom)) AS geom
  FROM (SELECT track, (ST_Dump(geom)).geom AS geom
        FROM track_table) AS t
GROUP BY track;

ST_Multi puts everything inside the parentheses into a multipart geometry.

ST_Collect aggregates the various geometries. This may be easier to visualize looking at simpler SQL used for SUM without spatial.

The FROM (SELECT)... creates a subquery - notice the AS t at the end which makes the subquery behave as if it were a table in the outer query.

ST_Dump breaks apart your paths if into single lines if they are multilines. This prevents PostGIS from forming geometry collections, which it apparently doesn't like to work with. To get only the spatial geometry from the ST_Dump, it is in its own parentheses with .geom on the end.

The GROUP BY at the end specifies how you want to group the rows before combining. If you had grouped by path id (or included it in the select statement) then you would have five results because they are all unique. Assuming you will want to bring other fields into this query, anything you add to the SELECT statement (other than geometry) will generally need added to the GROUP BY. Keeping this straight with the subquery will likely be the most difficult part of modifying the SQL for your tables.

QGIS SQL Window

Note that executing the query gives only two results - the lines have been grouped by the track ID, and there is a geometry column that isn't readable to us humans, but we can at least see it is present.

When it is working properly, click the "Create a view" button, give it a name, and then click OK. I will sometimes prefix views with "v_" or something similar so I don't confuse them with columns on the table tree in the left window panes.

If you were to look at this in pgAdmin, you can see clearly that the view is separate from the table. I only created one table and one view for this testing - the others are always present to keep PostGIS working:

tables and views

Through the DB Manager, you can load new layers from views using the options at the bottom of the window. You can also refresh the database table list and should see the new view appear. Right-click on it and hit "Add to canvas". There will likely be a question mark icon next to it rather than a line, but this shouldn't matter. If we were not aggregating the geometries and had simpler SQL, you may possibly see a line symbol identical to the track_table above it.

Add view in DB Manager

I occasionally have trouble adding a view from the DB Manager if the SRID or unique integer column is not recognizable. This more commonly happens when views were created in pgAdmin or other software. If this happens, go into the main QGIS window and click the elephant to add a layer from PostGIS. Connect to the database, and you will see your new view listed twice. Notice that one of the rows recognizes the spatial type and SRID. On that row, click the dropdown on "Feature id" and select a column with a unique identifier (the 'track' integer). QGIS requires a unique identifier integer column to display geometries. If we didn't have the track column integer, we could have add a row_number to our query. After choosing the identifier, select the row and click "Add".

enter image description here

You now have a layer that, as far as QGIS is concerned, consists of only two features (note the attribute table and colors in the screenshot). By using a view in PostGIS, you avoided creating a new table. As a result, changes to the original track table will update dynamically. From here, you should be able to create your atlas without getting multiple pages.

resulting view

  • Great and comprehensive description! It's what I was looking for! – MAP Feb 27 '17 at 7:28

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.