2

I am attempting to recreate a flowmap workflow from @underdark

The first step involves joining a non-spatial edges layer to a spatial nodes layer:

SELECT StartID, DestID, Weight, 
       make_line(a.geometry, b.geometry)
FROM OD_all_long
JOIN nodesData a ON OD_all_long.StartID = a.ID
JOIN nodesData b ON OD_all_long.DestID = b.ID
WHERE a.ID != b.ID 

The logic of this process is simple enough and I was able to achieve the same thing using R: create a line between each O-D point using geometry from node with a common ID, and then assigning the spatial data of Weight to that line from the edges dataset.

In the above code, a and b are references to the nodes table which is logical, as it is the only location with any geometry information. However, I do not understand how a and b are actually established as "references" to the node table (either through the Virtual Layers or through QGIS).

I actually get a join when I run this at the moment, but it has no geometry. As there is no further syntax explanation for make_line in the QGIS Expressions guide, I'm a little lost.

Edges data looks like:

enter image description here

Nodes data looks like:

enter image description here

  • 1
    Are the node spatial data and the edges table both in a SQLite / SpatiaLite database? Have you tried creating a spatial view in SpatiaLite and then adding it to QGIS? – Carlos MSF Jul 2 at 15:07
1

The a and b are aliases for the nodesData table, you use the alias to refer to the table in the rest of the query. The two queries below are the same except the second uses aliases for the table names.

SELECT Customers.name, Customers.address, Customers.email, tableY.OrderNum, tableY.OrderDate
FROM Customers JOIN Orders ON Customers.CustomerID = tableY.CustomerID

and

SELECT a.name, a.address, a.email, c.OrderNum, c.OrderDate
FROM Customers a JOIN Orders b ON a.CustomerID = b.CustomerID

In this case the aliases are just a syntactic nicety that let you write a shorter query by saving you having to use the full table name over and over.

In the case of your query

SELECT StartID, DestID, Weight, 
       make_line(a.geometry, b.geometry)
FROM OD_all_long
JOIN nodesData a ON OD_all_long.StartID = a.ID
JOIN nodesData b ON OD_all_long.DestID = b.ID
WHERE a.ID != b.ID

The aliases become vital because they are used to disambiguate the two separate instances of the nodesData table - one for the start point and one for the end point. The first join matches up each row in the edges table with the row in the nodes table where the ID equals the StartID, once the right row is found it can then use the node's geometry as the start point. The second join does a similar thing to find the end point.

The make_line expression creates a line between the two points. The query returns four columns of data: StartID, DestID, Weight and a Line Geometry output by the make_line expression. I find it helpful to develop my queries in the Database|DB Manager and load them as a virtual layer from there. Try pasting your query into a DB Manager SQL Window and Executing it to see the results it produces. You can rewrite the query slightly by adding as geom to the end of the second line, ie:

SELECT StartID, DestID, Weight, 
       make_line(a.geometry, b.geometry) as geom
FROM OD_all_long
JOIN nodesData a ON OD_all_long.StartID = a.ID
JOIN nodesData b ON OD_all_long.DestID = b.ID
WHERE a.ID != b.ID

This another alias, this time for the make_line column. If you Execute the query now in the DB Manager you will see a fourth column in the results showing the WKT of the Lines. When you say run the query and get a join but no geometry I'm not sure what you mean. I tested your query and it works. Perhaps it just wasn't obvious that the query was creating the lines. If you can't see any lines on the map maybe the CRS for the virtual layer is wrong? Or something's not right with the query or the data. The DB Manager is your friend for troubleshooting query problems. Regards.

  • Thanks for this detailed response M Bain - it makes things a lot clearer. The sticky point for me was the so called "disambiguate" the origin and destination instance of what are in the data the same set of nodes. I just tried again without explicitly setting up any alias' in the properties and it worked. Perhaps I also had a problem with data types, as the edges data was reading in as a string which I solved using an accompanying .csvt file. Thanks for help! – mr.illington Jul 3 at 9:58
  • Glad to hear you got it working. – M Bain Jul 4 at 4:55

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.