[postgis-users] postgis topology performance

[Rémi Cura]

Back after some other testing .

(_from memory, suppressing checks in addpoints was about 2 sec instead of 3
sec.
_About 21k unique points.)


Here is a little comparison :
(32k polylines,21k unique points,  already 'topological'( no need to cut
the lines) ,with a lot of attributes)

Postgis Topology
Postgis Topology (addtopogeom) : 700+ sec for 10k lines, (??about 1600 sec
for 32k lines??)
Postgis Topology (addlinestring) : 240+ sec for 10k lines,  (??about 780
sec for 32k lines??)
Postgis Topology (addnode, addedge) : 80sec for 10 k lines (??about 260sec
for 32k lines??)

Grass Gis 7 :
Importing everything : 10 sec
Importing only geom (no attributes) : 5 sec

Importing everything then Exporting all topologies to postgis topology :
150 sec
Declare postgis topo as external source and fill it with shapefile : 145 sec

Geos : (upper bound measured trough st_polygonize)
ST_Polygonize on everything (aggregates on 32k lines) : about 5 sec

TopoJSON (according to previous thread : postgis topology/60 )
Importing geometry (extrapolated) : about 30 sec?

CGAL (half edge) :
hopefully soon

Geoxygene (JTS) :
hopefully soon

I succeeded in putting the grass vector layer into postgis topology as it
is proposed here
http://grasswiki.osgeo.org/wiki/PostGIS_Topology.<http://grasswiki.osgeo.org/wiki/PostGIS_Topology>
Maybe it could be better configured, but it is still slow (I tried both
proposed ways).

About using GEOS as backend : currently there is no way to access
topological information from geos (all methods are private).

Cheers,
Rémi-C


2014-03-21 10:40 GMT+01:00 Sandro Santilli <strk at keybit.net>:

> On Thu, Mar 20, 2014 at 06:46:51PM +0100, Rémi Cura wrote:
> > 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).
>
> Show numbers, c'mon!  :)
>
> > I may try to simplify the add edge function, but I don't think this is
> the
> > way to go.
>
> For sure the AddEdge funtion makes a lot more checks.
> I was actually thinking to split that myself between a checking and
> non-checking version.
> Also the checks themselves might have space for optimizations.
>
> > 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) .
>
> I agree this would be also an interesting way to proceed.
> Still I don't think polygonize would be that fast (not right now).
>
> > This way there would be no edge by edge insertion.
>
> But there'll still be edge-by-edge updates, which would be still
> expensive (every update is like a new insert).
>
> > 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.
>
> So it makes 1.5 seconds for 10k edges + 4.5 seconds for their unique points
> (how many ?)
>
> > Still a long way to go from 80 sec to few sec (10 sec would be cool, if
> it
> > scales well).
>
> Well, if you could compute all the linking in memory it'd be about those
> inserts only (< 10 secs for the inserts). Time to look at GEOS GeomGraph
> capability of representing all we need ?
>
> --strk;
> _______________________________________________
> 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/20140325/96829111/attachment.html>