[postgis-users] Need a method for "noding" a street network

[Stephen Mather]

Hmm, thinking about it, I may have to retract my kibitz-- it may only be in
edge cases where my approach is useful, like somewhat disconnected networks.

Oh well.  I have had coffee now.


On Thu, May 9, 2013 at 9:17 AM, Stephen Mather <mather.stephen at gmail.com>wrote:

> A couple quick thoughts on approach 2:
>
> 1) The union need only apply to intersecting geometries, rather than the
> whole dataset.  This helps considerably with memory footprint, but in some
> edge cases could still be a real problem.
>
> 2) We need Mr. Davis to hurry up and demonstrate streaming geometry
> processing so we can port it to c and put it in postgis... .  :)
>
> And so, with some kibitzing , this morning I contribute no code to the
> conversation... .  Apologies-- I've got a deadline elsewhere... .
>
> Best,
> Steve
>
>
>
>
>
>
>
> On Wed, May 8, 2013 at 10:27 AM, Stephen Woodbridge <
> woodbri at swoodbridge.com> wrote:
>
>> Hi all,
>>
>> This question comes up reasonably often on the pgRouting list and has
>> been posted he on occasion under titles like "How to break streets at
>> intersections?"
>>
>> It seems to me that this would be a good function to create in either
>> postgis or pgrouting.
>>
>> THE PROBLEM:
>>
>> I have a table of 10's of thousands of street segments to 10's of
>> millions of street segments. These street segments are LINSTRING or
>> MULTILINESTRING geometries with some arbitrary number of attribute columns.
>> The geometries may cross one another and are not noded correctly for use
>> with pgRouting.
>>
>> THE RESULTS:
>>
>> We want to process the table and create a new table with the same
>> structure (see comment about primary key below), and in the new table all
>> the geometries are broken at intersections and all the new pieces of the
>> original segment that have been broken have the original attributes
>> propagated to them. So if the original segment has column foo='abc' and was
>> split into 3 new segments, each of the three new segments would also have
>> foo='abc'. The exception to this might be that the new table needs a new
>> primary column as the old primary key will now be duplicated for the
>> multiple parts.
>>
>> POTENTIAL SOLUTIONS:
>>
>> 1. I think one way to do this would be to create a topology and load the
>> table into it, then extra a new table from the topology. Although I'm not
>> sure of the specifics for doing this or the efficency of doing it this way.
>>
>> 2. Another way seems to be using a query like:
>>
>> select (st_dump(bar.the_geom)).* from (
>>     select st_union(foo.the_geom) as the_geom from mytable foo
>> ) as bar;
>>
>> And then taking each of the dump.geom objects and using st_contains to
>> find which original segment it belonged to so we can move the attributes to
>> the new segment. This method also loose any association to the original
>> record and forces the use of st_contains to re-associate the new segments
>> to the original segments.
>>
>> My concern with this is that the st_union has to load the whole table
>> which may be 10's of millions of street segments and this will likely be a
>> memory problem. Also running the st_contains() does not seems to me to be
>> optimal.
>>
>> 3. Is there a good recipe for doing this somewhere that I have not found?
>> or other better approaches to this problem?
>>
>> What would be the best way to add tolerance to the problem? using snap to
>> grid?
>>
>> Thoughts on how to do this efficiently?
>>
>> Since I'm working on the pgRouting 2.0 release I thought this might be a
>> nice function to add to that if we can come up with a generic way to do
>> this.
>>
>> Thanks,
>>   -Steve
>>
>>
>> -- Example to demonstrate st_union above
>> select st_astext((st_dump(bar.the_**geom)).geom) from (
>>     select st_union(foo.the_geom) as the_geom from (
>>         select 'MULTILINESTRING((0 1,2 1))'::geometry as the_geom
>>         union all
>>         select 'MULTILINESTRING((1 0,1 2))'::geometry as the_geom
>>         union all
>>         select 'LINESTRING(1 1.5,2 2)'::geometry as the_geom
>>         ) as foo
>>     ) as bar;
>>
>> "LINESTRING(1 1.5,2 2)"
>> "LINESTRING(1 0,1 1)"
>> "LINESTRING(1 1,1 1.5)"
>> "LINESTRING(1 1.5,1 2)"
>> "LINESTRING(0 1,1 1)"
>> "LINESTRING(1 1,2 1)"
>> ______________________________**_________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-**bin/mailman/listinfo/postgis-**users<http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users>
>>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130509/1a5d9a1d/attachment.html>