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

[Stephen Mather]

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/b59ab231/attachment.html>