[postgis-users] postgis topology performance

[Rémi Cura]

Yep,
We tried a modified version of 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).

I may try to simplify the add edge function, but I don't think this is the
way to go.

It would be better to do batch direct insertion in table
(to be precise : insert all edges into edge_data for the columns edge_id,
start_node, end_node, geom )
, then batch edge_data completion (next_left_edge, next_right_edge, found
with querry on node_id),
then batch compute face (with polygonize) .

This way there would be no edge by edge insertion.


About your question :
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).
This way, I would say that taking 1.5 sec to insert 10 k lines+ check
constraints and update index is OK for me.

Still a long way to go from 80 sec to few sec (10 sec would be cool, if it
scales well).

Cheers,

Rémi-C




2014-03-20 13:54 GMT+01:00 Sandro Santilli <strk at keybit.net>:

> On Thu, Mar 20, 2014 at 01:39:53PM +0100, Rémi Cura wrote:
> > Hey,
> > addlinestring is topology.topogeo_addlinestring(atopology character
> > varying, aline geometry, tolerance double precision DEFAULT 0).
> > My version : POSTGIS="2.2.0dev r12325" GEOS="3.5.0dev-CAPI-1.9.0 r3963"
> > PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11dev, released 2013/04/13"
> > LIBXML="2.8.0" TOPOLOGY RASTER
> > PostgreSQL 9.3.3 on i686-pc-linux-gnu, compiled by gcc (Ubuntu/Linaro
> > 4.6.3-1ubuntu5) 4.6.3, 32-bit
>
> Ok, that one has all the performance improvements in topology done so far.
>
> > About your idea,
> > it was our first intend, hence the test with manually using
> > "topology.addnode(atopology character varying, apoint geometry)",
> > then manually adding the line : topology.st_addedgemodface(atopology
> > character varying, anode integer, anothernode integer, acurve geometry).
> > In an ideal world we would have then used polygonize, but as you said
> there
> > is no way to build only adjacency without faces.
>
> Calling ST_AddEdgeModFace already does that for you.
> In an ideal world you would use another function for that.
> Actually, in that ideal world you would also avoid to call
> AddNode once for each connected edge, right ? And in the same
> ideal world AddNode would not go checking for hitting an edge
> or another pre-existing node...
>
> > In my test , adding points and generating the table for line insertion
> was
> > 10 sec, the other 70 sec being for line insertion.
> >
> > I don't understand your last question, do you mean just fill the table
> > manually without computing adjacencies?
>
> Yes, just filling the table.
>
> --strk;
>
> > 2014-03-19 19:08 GMT+01:00 Sandro Santilli <strk at keybit.net>:
> >
> > > On Wed, Mar 19, 2014 at 06:43:21PM +0100, Rémi Cura wrote:
> > > > Hey,
> > > >
> > > > We tried several way to load an already topologically correct
> shapefile
> > > > into postgis topology .
> > > >
> > > > 10k lines loaded
> > > > _Using totopogeom : 750 sec
> > > > _Using addlinestring : 246 sec
> > > > _Manually using addnode and st_addedgemodface : 86 sec.
> > > >
> > > > Still very slow  :-/
> > > >
> > > > Considering manually filling table /and/or using GEOS/or using grass
> > > loader.
> > >
> > > Interesting numbers, thanks.
> > > By "addlinestring" you mean TopoGeo_addLinestring ?
> > > Is this with postgis trunk ?
> > >
> > > Note that ST_AddEdgeModFace (called by TopoGeo_addLinestring)
> > > still performs checks to verify the line you add does not
> > > cross othe redges. There's currently no API exposed for simply
> > > adding the linking attributes (face left/right and next edges).
> > >
> > > I was thinking that one idea could be to manually insert all
> > > the nodes and edges and then have a function to "polygonize"
> > > the supposedly pre-noded resultance set. This would be pretty
> > > much the same model used by GEOSPolygonize (requires pre-noded
> > > input) and would probably be faster for batch constructions.
> > >
> > > To complete the picture, how long does it take to simply insert
> > > all your lines as edges ? So we get a feeling of IO and referential
> > > integrity checks cost...
> > >
> > > --strk;
> > > _______________________________________________
> > > postgis-users mailing list
> > > postgis-users at lists.osgeo.org
> > > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> > >
>
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at lists.osgeo.org
> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
> --
>
>  ()  ASCII ribbon campaign  --  Keep it simple !
>  /\  http://strk.keybit.net/rants/ascii_mails.txt
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> 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/20140320/ff2b30a8/attachment.html>