<div dir="ltr">Yep,<div>We tried a modified version of <font face="arial, sans-serif"><span style="font-size:12.666666984558105px">AddNode without check (because I already delete duplicates nodes before calling the function), it was not incredibly faster, and anyway way faster than line insertion (where I feel we need to focus now).</span></font></div>
<div><span style="font-family:arial,sans-serif;font-size:12.666666984558105px"><br></span></div><div><font face="arial, sans-serif"><span style="font-size:12.666666984558105px">I may try to simplify the add edge function, but I don't think this is the way to go.</span></font></div>
<div><font face="arial, sans-serif"><span style="font-size:12.666666984558105px"><br></span></font></div><div><font face="arial, sans-serif"><span style="font-size:12.666666984558105px">It would be better to do batch direct insertion in table </span></font></div>
<div><font face="arial, sans-serif"><span style="font-size:12.666666984558105px">(to be precise : insert all edges into edge_data for the columns edge_id, start_node, end_node, </span></font><span style="font-family:arial,sans-serif;font-size:12.666666984558105px">geom </span><span style="font-size:12.666666984558105px;font-family:arial,sans-serif">)</span></div>
<div><span style="font-size:12.666666984558105px;font-family:arial,sans-serif">, then batch edge_data completion (next_left_edge, next_right_edge, found with querry on node_id),</span></div><div><font face="arial, sans-serif"><span style="font-size:12.666666984558105px">then batch compute face (with polygonize) .</span></font></div>
<div><font face="arial, sans-serif"><span style="font-size:12.666666984558105px"><br></span></font></div><div><font face="arial, sans-serif"><span style="font-size:12.666666984558105px">This way there would be no edge by edge insertion.</span></font></div>
<div><font face="arial, sans-serif"><span style="font-size:12.666666984558105px"><br></span></font></div><div><font face="arial, sans-serif"><span style="font-size:12.666666984558105px"><br></span></font></div><div><font face="arial, sans-serif"><span style="font-size:12.666666984558105px">About your question : </span></font></div>
<div><font face="arial, sans-serif"><span style="font-size:12.666666984558105px">only filling the edge_data with geometry (on next_left etc) is about 4.5 sec (on par with inserting all the points) for 10k lines. ON this 4.5 sec you can remove about 3 sec related to my data selection process (I only insert a part of a table).</span></font></div>
<div><font face="arial, sans-serif"><span style="font-size:12.666666984558105px">This way, I would say that taking 1.5 sec to insert 10 k lines+ check constraints and update index is OK for me.</span></font></div><div><font face="arial, sans-serif"><span style="font-size:12.666666984558105px"><br>
</span></font></div><div><font face="arial, sans-serif"><span style="font-size:12.666666984558105px">Still a long way to go from 80 sec to few sec (10 sec would be cool, if it scales well).</span></font></div><div><font face="arial, sans-serif"><span style="font-size:12.666666984558105px"><br>
</span></font></div><div><font face="arial, sans-serif"><span style="font-size:12.666666984558105px">Cheers,</span></font></div><div><font face="arial, sans-serif"><span style="font-size:12.666666984558105px"><br>
Rémi-C</span></font></div><div><font face="arial, sans-serif"><span style="font-size:12.666666984558105px"><br></span></font><div><br></div></div>
</div><div class="gmail_extra"><br><br><div class="gmail_quote">2014-03-20 13:54 GMT+01:00 Sandro Santilli <span dir="ltr"><<a href="mailto:strk@keybit.net" target="_blank">strk@keybit.net</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div class="">On Thu, Mar 20, 2014 at 01:39:53PM +0100, Rémi Cura wrote:<br>
> Hey,<br>
</div><div class="">> addlinestring is topology.topogeo_addlinestring(atopology character<br>
> varying, aline geometry, tolerance double precision DEFAULT 0).<br>
> My version : POSTGIS="2.2.0dev r12325" GEOS="3.5.0dev-CAPI-1.9.0 r3963"<br>
> PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11dev, released 2013/04/13"<br>
> LIBXML="2.8.0" TOPOLOGY RASTER<br>
> PostgreSQL 9.3.3 on i686-pc-linux-gnu, compiled by gcc (Ubuntu/Linaro<br>
> 4.6.3-1ubuntu5) 4.6.3, 32-bit<br>
<br>
</div>Ok, that one has all the performance improvements in topology done so far.<br>
<div class=""><br>
> About your idea,<br>
> it was our first intend, hence the test with manually using<br>
> "topology.addnode(atopology character varying, apoint geometry)",<br>
> then manually adding the line : topology.st_addedgemodface(atopology<br>
> character varying, anode integer, anothernode integer, acurve geometry).<br>
> In an ideal world we would have then used polygonize, but as you said there<br>
> is no way to build only adjacency without faces.<br>
<br>
</div>Calling ST_AddEdgeModFace already does that for you.<br>
In an ideal world you would use another function for that.<br>
Actually, in that ideal world you would also avoid to call<br>
AddNode once for each connected edge, right ? And in the same<br>
ideal world AddNode would not go checking for hitting an edge<br>
or another pre-existing node...<br>
<div class=""><br>
> In my test , adding points and generating the table for line insertion was<br>
> 10 sec, the other 70 sec being for line insertion.<br>
><br>
> I don't understand your last question, do you mean just fill the table<br>
> manually without computing adjacencies?<br>
<br>
</div>Yes, just filling the table.<br>
<br>
--strk;<br>
<div class="HOEnZb"><div class="h5"><br>
> 2014-03-19 19:08 GMT+01:00 Sandro Santilli <<a href="mailto:strk@keybit.net">strk@keybit.net</a>>:<br>
><br>
> > On Wed, Mar 19, 2014 at 06:43:21PM +0100, Rémi Cura wrote:<br>
> > > Hey,<br>
> > ><br>
> > > We tried several way to load an already topologically correct shapefile<br>
> > > into postgis topology .<br>
> > ><br>
> > > 10k lines loaded<br>
> > > _Using totopogeom : 750 sec<br>
> > > _Using addlinestring : 246 sec<br>
> > > _Manually using addnode and st_addedgemodface : 86 sec.<br>
> > ><br>
> > > Still very slow :-/<br>
> > ><br>
> > > Considering manually filling table /and/or using GEOS/or using grass<br>
> > loader.<br>
> ><br>
> > Interesting numbers, thanks.<br>
> > By "addlinestring" you mean TopoGeo_addLinestring ?<br>
> > Is this with postgis trunk ?<br>
> ><br>
> > Note that ST_AddEdgeModFace (called by TopoGeo_addLinestring)<br>
> > still performs checks to verify the line you add does not<br>
> > cross othe redges. There's currently no API exposed for simply<br>
> > adding the linking attributes (face left/right and next edges).<br>
> ><br>
> > I was thinking that one idea could be to manually insert all<br>
> > the nodes and edges and then have a function to "polygonize"<br>
> > the supposedly pre-noded resultance set. This would be pretty<br>
> > much the same model used by GEOSPolygonize (requires pre-noded<br>
> > input) and would probably be faster for batch constructions.<br>
> ><br>
> > To complete the picture, how long does it take to simply insert<br>
> > all your lines as edges ? So we get a feeling of IO and referential<br>
> > integrity checks cost...<br>
> ><br>
> > --strk;<br>
> > _______________________________________________<br>
> > postgis-users mailing list<br>
> > <a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
> > <a 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>
> ><br>
<br>
> _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
> <a 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>
<br>
<br>
</div></div><span class="HOEnZb"><font color="#888888">--<br>
<br>
() ASCII ribbon campaign -- Keep it simple !<br>
/\ <a href="http://strk.keybit.net/rants/ascii_mails.txt" target="_blank">http://strk.keybit.net/rants/ascii_mails.txt</a><br>
</font></span><div class="HOEnZb"><div class="h5">_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a 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>
</div></div></blockquote></div><br></div>