<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Working on it...<br>
<br>
I ran the provided sample dataset through the streaming overlay
algorithm - took about 2 sec to node it. For comparison, the
regular "UnionUnary" method took about the same amount of time. But
it won't scale to huge datasets, whereas the streaming algorithm
will.<br>
<br>
<div class="moz-cite-prefix">On 5/9/2013 6:17 AM, Stephen Mather
wrote:<br>
</div>
<blockquote
cite="mid:CAPkJWLWCHRi80DnjWWk_iHTfiTBxruzFMMiW+H2o-uTZs+vuLg@mail.gmail.com"
type="cite">
<div dir="ltr">
<div>
<div>
<div>A couple quick thoughts on approach 2:<br>
<br>
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.<br>
<br>
</div>
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... . :)<br>
<br>
</div>
And so, with some kibitzing , this morning I contribute no
code to the conversation... . Apologies-- I've got a deadline
elsewhere... .<br>
<br>
Best,<br>
</div>
Steve<br>
<div>
<div>
<div><br>
<br>
<br>
<br>
<br>
</div>
</div>
</div>
</div>
<div class="gmail_extra"><br>
<br>
<div class="gmail_quote">On Wed, May 8, 2013 at 10:27 AM,
Stephen Woodbridge <span dir="ltr"><<a
moz-do-not-send="true"
href="mailto:woodbri@swoodbridge.com" target="_blank">woodbri@swoodbridge.com</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">Hi all,<br>
<br>
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?"<br>
<br>
It seems to me that this would be a good function to create
in either postgis or pgrouting.<br>
<br>
THE PROBLEM:<br>
<br>
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.<br>
<br>
THE RESULTS:<br>
<br>
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.<br>
<br>
POTENTIAL SOLUTIONS:<br>
<br>
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.<br>
<br>
2. Another way seems to be using a query like:<br>
<br>
select (st_dump(bar.the_geom)).* from (<br>
select st_union(foo.the_geom) as the_geom from mytable
foo<br>
) as bar;<br>
<br>
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.<br>
<br>
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.<br>
<br>
3. Is there a good recipe for doing this somewhere that I
have not found? or other better approaches to this problem?<br>
<br>
What would be the best way to add tolerance to the problem?
using snap to grid?<br>
<br>
Thoughts on how to do this efficiently?<br>
<br>
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.<br>
<br>
Thanks,<br>
-Steve<br>
<br>
<br>
-- Example to demonstrate st_union above<br>
select st_astext((st_dump(bar.the_geom)).geom) from (<br>
select st_union(foo.the_geom) as the_geom from (<br>
select 'MULTILINESTRING((0 1,2 1))'::geometry as
the_geom<br>
union all<br>
select 'MULTILINESTRING((1 0,1 2))'::geometry as
the_geom<br>
union all<br>
select 'LINESTRING(1 1.5,2 2)'::geometry as the_geom<br>
) as foo<br>
) as bar;<br>
<br>
"LINESTRING(1 1.5,2 2)"<br>
"LINESTRING(1 0,1 1)"<br>
"LINESTRING(1 1,1 1.5)"<br>
"LINESTRING(1 1.5,1 2)"<br>
"LINESTRING(0 1,1 1)"<br>
"LINESTRING(1 1,2 1)"<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a moz-do-not-send="true"
href="mailto:postgis-users@lists.osgeo.org"
target="_blank">postgis-users@lists.osgeo.org</a><br>
<a moz-do-not-send="true"
href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users"
target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
</blockquote>
</div>
<br>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
<br>
</body>
</html>