[pgrouting-users] Splitting a network to create intersections

[Greg Allensworth]

On 5/7/2013 8:57 AM, Stephen Woodbridge wrote:
> If you could encapsulate this process into a stored procedure like:
> select pgr_node_network(table_in text, table_out text, ...);
> That would be a nice addition to pgrouting.

https://github.com/gregallensworth/PostGIS/blob/master/PGRouting_create_intersections.txt

The technique given by dkastl was causing multiple splits of the same 
line: if a line is crossed by two separate other lines, then there may 
be multiple overlapping cut-ups.

But ST_Node() on the ST_Union() seems to work just perfectly. And it 
accommodates linear intersections, while ST_Split() bails in those cases.



> I think the tricky part will be transfering the attribute columns from
> table_in records to the potentially multiple records that these get
> chopped into in table_out. I saw somewhere, that someone was using
> st_contains to find the original segment, but I suspect that there is a
> better way to do this.

Eventually I gave up on this, and am writing an external program to do 
it instead. What I have so far, FYI:

- ST_INTERSECTS() isn't suitable as it would find any original line 
segments which intersect, and by nature there should be multiple such 
matches (at least 2, at endpoints).

- ST_CONTAINS() only works for very simple cases, it seems due to 
rounding errors. While linestring (0 0, 10 10) does contain (2 2, 3 3), 
the linestrings cut up from (0 6, 2 0) don't necessarily map back to the 
original one precisely.

- What I have come up with is inefficient but seems effective:
ORDER BY 
ST_INTERSECTION(ST_BUFFER(original.geom,0.01),ST_BUFFER(splits.geom,0.01))
DESC LIMIT 1;
It's crude, but seems logical: the line which has the most overlap with 
this one, accounting for the fact that they may in fact only intersect 
at one point and diverge very slightly, is most likely to be the original.

- But actually applying this to fetch and update the fields, is so far a 
boondoggle. The "record" datatype in Pl/PgSQL won't allow access to 
fields named in a variable, the "row" datatype assumes a fixed table 
name in the declaration, etc. This question seems to be asked a lot, the 
answers other than "you can't" assume significant foreknowledge of the 
target table.

-- 
Greg Allensworth, Web GIS Developer
BS  A+  Network+  Security+  Linux+  Server+
GreenInfo Network - Information and Mapping in the Public Interest
564 Market Street, Suite 510  San Francisco CA 94104
PH: 415-979-0343 x302  FX: 415-979-0371    email: gregor at greeninfo.org
Web: www.GreenInfo.org     www.MapsPortal.org

Subscribe to MapLines, our e-newsletter, at www.GreenInfo.org