pgr_nodeNetwork, change to support views

[cvvergara]

Allow the user to node only a particular section of the edge-table.

[kippandrew]

It would actually be awesome if you could specify a view rather then a table as the input to pgr_nodeNetwork().

[woodbri]

Yes, that would be cool, but I believe pgr_nodenetwork modifies the table and that is not so easy to do with a view. If you have two lines that intersect then you have to delete those lines and insert 4 new lines, or update the original lines and insert 2 new lines.

You could potentially do this by having an input table or view and then creating a new table for the output results, but doing this on a large area like the US or Germany, then you are forcing essentially to copy the whole table as you make changes to it which is time consuming and disk intensive.

This function is more of a utility to help us help users when their data is not well formed. If we can make some small improvements, that probably makes sense, but I'm not sure how complicated we want to get with this. Also be aware that this only works on 2D data, and if you have realistic road networks with overpass/underpass that should not be connected, this command will not know better and will connect them.

[robe2]

I don't think it modifies the existing table or at least in testing and cursory glance looking at the code (writing chapter in pgRouting Practical guide book on it at moment http://locatepress.com/pgrouting ) , I don't see where it does.

It creates a new table which defaults to name originalname_noded with references back to ids of the original table. It doesn't even seem to use the vertices_pgr associated table.

So I can't think of a reason we can't use a view and would consider the lack of view support a bug :)

[robe2]

If people are interested I'm willing to tackle adding view support to this function and suspect it is a fairly trivial change. The only question is whether to backport that to 2.1.1 or just have in 2.2 branch

[kippandrew]

I took a quick look before submitting this issue and it looks to me that pgr_nodeNetwork calls pgr_getTableName which looks up the table, but restricts these allowable types to BASE TABLE.

https://github.com/pgRouting/pgrouting/blob/a368fd81823dc1054eaf0067735d1410dee6e44e/src/common/sql/pgrouting_utilities.sql#L66

[woodbri]

That would be cool, I didn't actually look at the code, but now that you mention it that does make sense that it creates a *_noded table. In this case, I would agree that the change should be straight forward. I would think that the change should be a drop-in replacement for the existing function so once it is ready Vicky can decide if she wants to pull it into 2.1.1

[cvvergara]

@robe2 I would be nice a complete revision of pgr_nodeNetwork the reason is the following: It has a lot of issues.... (@woodbri remember the famous circle) I am opening an issue for general discussion of pgr_nodeNetowork that will also hold all the related issues, including this one.

[robe2]

I was thinking of isolating the view change to just pgr_nodeNetwork, but then it occurred to me that it doesn't make much sense for _pgr_getTableName to just look at tables and not views. Views that involve single tables and include the primary key have been updateable since PostgreSQL 9.1. Even in those cases where you absolutely need a table (like where you are adding keys), I think if we just throw back an error on those functions that would be sufficient.

The only case I can think of where there might be an issue is in create topology where we create a side-line vertices to go along with the main table. In that case it is possible for a pgr_vertices to be created from a view and become out of synch if data changes or have two different views against the same table and end up with overwriting one set of nodes..

What do you think?

Alternatively we could have _pgr_getTableName take an optional argument to denote whether to include views or not.

[robe2]

Another thought have the _pgr_getTableName also return the table_type so a function can decide whether to use it or not if it absolutely needs to work on a real table.

[woodbri]

I think both of these ideas would be very useful and allow us to make use of them elsewhere also.

If you have time to review pgr_nodenetwork and look at the other issues Vicky listed, I think getting another set of eyes on it would be very helpful and there might be some other simple improvements and fixes to be had.

[cvvergara]

_pgr_getTableName makes sure the table exists its used also in pgr_createTable and that one cant use views, so maybe adding a flag to _pgr_getTableName so to look only to BASE or also to VIEW. This is a comment about it

[cvvergara]

Main discussion in #419

[cvvergara]

Not working on pgr_nodeNetwork for version 3.0