[pgrouting-users] Splitting a network to create intersections

[Stephen Woodbridge]

Greg,

Before you spend much more time on this, check out this thread:

https://groups.google.com/forum/?fromgroups=#!topic/postgis-users/nTN67mm8IuA

And scroll down to the node-table-proc.sql attachment and give it a try.

-Steve

On 5/9/2013 10:24 PM, Greg Allensworth wrote:
> 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.
>