Hello,<div><br></div><div>I made this :</div><div><br></div><div><div>drop table if exists graph_vertices_tmp;</div><div>create table graph_vertices_tmp as</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>select id, count(*) as cnt from</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>(</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>select source as id from edges</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>union all</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>select target as id from edges</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>)</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>as foo</div>
<div>GROUP BY id;</div><div>delete from graph_vertices_tmp where cnt <> 2;</div><div>create unique index graph_vertices_tmp_idx_id ON graph_vertices_tmp(id);</div><div>REINDEX TABLE graph_vertices_tmp;</div><div><br>
</div><div>create unique index osm_new_way_edges_save_idx_id ON edges(id);</div><div>create index osm_new_way_edges_save_idx_source ON edges(source);</div><div>create index osm_new_way_edges_save_idx_target ON edges(target);</div>
<div>REINDEX TABLE edges;</div><div><br></div><div>create or replace function fct_simplify() RETURNS VOID AS '</div><div>DECLARE</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>c CURSOR for select id from graph_vertices_tmp;</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>v integer;</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>edge_id_to_remove integer;</div><div>BEGIN</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>open c;</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>LOOP</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>fetch c into v;</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>EXIT WHEN NOT FOUND;</div>
<div><br></div><div><span class="Apple-tab-span" style="white-space:pre"> </span>SELECT id FROM edges INTO edge_id_to_remove WHERE source = v;</div><div><span class="Apple-tab-span" style="white-space:pre"> </span></div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>UPDATE edges e SET</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>geometry = ST_LineMerge(St_Collect(geometry, sub.subgeo)),</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>d = (d + sub.subd),</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>t = (t + sub.subt),</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>cost = (cost + sub.subcost),</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>reverse_cost = (reverse_cost + sub.subreverse_cost),</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>target = sub.subtarget</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>FROM</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>(</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>SELECT</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>geometry as subgeo,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>d as subd,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>t as subt,</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>cost as subcost,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>reverse_cost as subreverse_cost,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>source as subsource,</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>target as subtarget</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>FROM edges</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>WHERE id=edge_id_to_remove</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>) sub</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>WHERE target=sub.subsource;</div><div><br></div><div><span class="Apple-tab-span" style="white-space:pre"> </span>DELETE FROM edges WHERE id = edge_id_to_remove;</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>END LOOP;</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>close c;</div><div>END;</div><div>'LANGUAGE plpgsql;</div><div><br></div>
<div> select fct_simplify()</div><div><br></div><div>With a graph of about 38.000.000 segments, and about 25.000.000 vertex to delete, the script is still running after 48H of execution.</div><div><br></div><div>Is these indexes are good ?</div>
<div><br></div><div>Is there is better approach ?</div><div><br></div><div>Thank you :)</div><div>Kin</div><br><div class="gmail_quote">2012/3/11 Aurélien FILEZ <span dir="ltr"><<a href="mailto:kinju59@gmail.com">kinju59@gmail.com</a>></span><br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">I'll try starting from this.<div><br></div><div>Thank you very much<div><div></div><div class="h5"><br><br><div class="gmail_quote">
On Sat, Mar 10, 2012 at 3:51 PM, Stephen Woodbridge <span dir="ltr"><<a 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"><div>On 3/10/2012 2:20 AM, Aurélien FILEZ wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Hi,<br>
<br>
This table is build with lines I decompose in segments of 2 points.<br>
<br>
Maybe it is better to act from these full lines, searching points used<br>
by more than one line ?<br>
</blockquote>
<br></div>
Yes, but the question that I am trying to answer is How are you going to do the efficiently?<br>
<br>
I just noticed that you already have v1 and v2 assigned, so you can do something like this:<br>
<br>
create table vertices_tmp as<br>
select id, count(*) as cnt from<br>
(select v1 as id from edge union all select v2 as id from edge) as foo group by id;<br>
<br>
Now to find all the the nodes where cnt=2<br>
<br>
select id from vertices_tmp where cnt=2;<br>
<br>
And you write a stored procedure to merge the two edges.<br>
<br>
-Steve<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div>
Thanks,<br>
Kin<br>
<br>
On Fri, Mar 9, 2012 at 5:48 PM, Stephen Woodbridge<br></div><div><div></div><div>
<<a href="mailto:woodbri@swoodbridge.com" target="_blank">woodbri@swoodbridge.com</a> <mailto:<a href="mailto:woodbri@swoodbridge.com" target="_blank">woodbri@swoodbridge.<u></u>com</a>>> wrote:<br>
<br>
On 3/9/2012 11:39 AM, Aurélien FILEZ wrote:<br>
<br>
Hi all,<br>
<br>
My edge table has some useless nodes. For example :<br>
<br>
edge_id v1 v2 the_geom<br>
1 1 2 LINESTRING(x1 y1, x2 y2)<br>
2 2 3 LINESTRING(x2 y2, x3 y3)<br>
<br>
But the vertex 2 is no used in any other edge.<br>
<br>
So I would like reduce my graph to transforme this previous<br>
example to :<br>
edge_id v1 v2 the_geom<br>
1 1 3 LINESTRING(x1 y1, x2 y2, x3 y3)<br>
<br>
Is there is a way to do that ?<br>
<br>
Thank you all :)<br>
<br>
<br>
There is no automatic way to do this. I have done a lot of graph<br>
analysis by adding a cnt column to the vertices_tmp column and then<br>
updating it to the count of edges connected to that vertex.<br>
<br>
Then you can do things like look for:<br>
<br>
deadends - cnt=1<br>
connected lines - cnt=2<br>
<br>
You could write a stored procedure to that the connected lines, join<br>
them together, and update the relevant tables to reflect the new<br>
topology.<br>
<br>
-Steve<br>
______________________________ _________________<br>
Pgrouting-users mailing list<br>
Pgrouting-users@lists.osgeo. org<br></div></div>
<mailto:<a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.<u></u>osgeo.org</a>><br>
<a href="http://lists.osgeo.org/" target="_blank">http://lists.osgeo.org/</a> mailman/listinfo/pgrouting- users<br>
<<a href="http://lists.osgeo.org/mailman/listinfo/pgrouting-users" target="_blank">http://lists.osgeo.org/<u></u>mailman/listinfo/pgrouting-<u></u>users</a>><div><br>
<br>
<br>
<br>
<br>
______________________________<u></u>_________________<br>
Pgrouting-users mailing list<br>
<a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.<u></u>org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/pgrouting-users" target="_blank">http://lists.osgeo.org/<u></u>mailman/listinfo/pgrouting-<u></u>users</a><br>
</div></blockquote><div><div></div><div>
<br>
______________________________<u></u>_________________<br>
Pgrouting-users mailing list<br>
<a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.<u></u>org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/pgrouting-users" target="_blank">http://lists.osgeo.org/<u></u>mailman/listinfo/pgrouting-<u></u>users</a><br>
</div></div></blockquote></div><br></div></div></div>
</blockquote></div><br></div>