[postgis-users] postgis topology performance

[Sandro Santilli]

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