pgr_createTopology using UPDATE

[dkastl]

This is just an idea for a major release. It's more of a personal reminder or start for a discussion.

Right now there are some functions, that populate the network table with values. The most prominent function is pgr_createTopology, another one might be ~~pgr_brokenGraph~~ pgr_labelGraph (see #376).

They are currently used like this:

SELECT * FROM pgr_createTopology(...);

or just

SELECT pgr_createTopology(...);

But wouldn't it look more "natural" to do something like:

UPDATE network_table SET (source, target) = pgr_createTopology();

Not sure this is even possible, but what we actually do is an update of the source and target column, and the columns might even have different names.

Note: I tested, that you can actually do something like UPDATE mytable SET (a,b) = (1,2);.

[woodbri]

I don't think you can do this with create topology because you are actually creating a table of unique ids if they don't exist AND assigning to the edge.

Your idea is good, but I believe it needs to be row atomic for that to work. It might be possible to do the label connected edges (for broken graph) using a WITH RECURSIVE ... structure like these examples: http://stackoverflow.com/questions/4074794/simple-graph-search-algorithm-in-sql-postgresql http://www.vertabelo.com/blog/technical-articles/sql-recursive-queries http://jdb.github.io/dependencies/bfs_dfs.html http://drtom.ch/posts/2012/02/11/Encoding_and_Querying_Graphs_in_the_Relational_Model/

with recursive graph_cte (node1, node2, start_id) 
as
( 
  select node1, node2, id as start_id
  from graphs
  where node1 = 1 -- alternatively elect the starting element using where id = xyz
  union all
  select nxt.node1, nxt.node2, prv.start_id
  from graphs nxt
    join graph_cte prv on nxt.node1 = prv.node2
)
select start_id, node1, node2
from graph_cte
order by start_id;

where you update the label on each edge, then you need to repeat this by finding an unlabeled edge and increamenting the label number until there are no unlabeled edges.

[woodbri]

Also see #376

[Zia-]

UPDATE is certainly better than SELECT as it's exhibiting what's happening. But there are few complicacies which should be given a prior thought before any future alteration.

1. If you are planning to use something like this UPDATE network_table SET (source, target) = pgr_createTopology(); then the source and target values of very first row of pgr_createTopology() will be assigned to all the network_table rows. I found this behavior by running this UPDATE mytable SET c=x from (select c as x from mytable2) t;
2. Or otherwise one has to use a WHERE clause also while using UPDATE to specify which row to be targeted. Something like this UPDATE mytable SET c=x from (select c as x from mytable2) t WHERE a=3; But this way one has to run UPDATE command n-number of times depending upon network_table count(*). Or may be use UPDATE inside WITH RECURSIVE clause, a mere imagination.
3. Additionally, one has to generate new columns also before any UPDATE command. For example ALTER TABLE network_table ADD COLUMN subgraph INTEGER; UPDATE network_table SET (subgraph) = pgr_labelGraph(); I dunno if it is possible to merge the above two commands?
4. Regarding @woodbri suggestion of using WITH RECURSIVE clause, I think that is exactly what the functions (pgr_createTopology and pgr_labelGraph) are doing inside, i.e. looping. And I don't think it's a good idea to expose these loops directly to the end user for simplicity. In pgr_labelGraph three dependent loops are running (https://github.com/Zia-/pgrouting/blob/develop/src/common/sql/pgrouting_brokenGraph.sql#L126), I am afraid if WITH RECURSIVE clause could be used somewhat like that. There is also one IF-ELSE condition to not incorporate those edges in a loop which have already been considered for edge labeling. UPDATE suggestion is interesting and scary at the same time.

[dkastl]

Thanks for the comments!

My intention was not to make this an urgent issue. I just wanted to share the idea and write it down for some eventual change in the future.

[Zia-]

Yaa yaa! I knew that. Your idea was thoughtful.