[GRASS-dev] Grass to postgis topology export performance

GRASS GIS GRASS GIS developer
,
1

Hello everybody,

I’m an active user of Postgis and postgis topology.

Currently Postgis topology is slow,

so I did a benchmark of other available options (see at the end).

Grass GIS is very fast to construct a topology over vectors (5 secs), and then surprisingly slow in exporting this model to Postgis topology (145 sec).

Is there a perticular reason for this? I tried to read the code, but couldn’t get to the sql querries.

Also, in a more prospective way, could Grass import from geometry (shapefile for example) then export to postgis topology be isolated (into an exe or a lib?).

Many Thanks,

Cheers,
Rémi-C

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, exact kernel) :

4 sec in batch mode (inserting all lines at once)

40min in line by line insertion

2

Hi,

2014-04-01 11:43 GMT+02:00 Rémi Cura <remi.cura@gmail.com>:

Grass GIS is very fast to construct a topology over vectors (5 secs), and
then surprisingly slow in exporting this model to Postgis topology (145
sec).

Is there a perticular reason for this? I tried to read the code, but
couldn't get to the sql querries.

Also, in a more prospective way, could Grass import from geometry (shapefile
for example) then export to postgis topology be isolated (into an exe or a
lib?).

could you post exact commands you run, or some sample data to play
with. Thanks, Martin

3

Hey,
thanks for your answer.

I uploaded a new test file of about the same number of line (32k),

also already topological (meaning in theory there is no need to split line, just compute node, neighbourhood, and faces).

It is here : http://trac.osgeo.org/postgis/ticket/2695

About the command I used, I followed the wiki page on grass, I tried both suggested methods:

I issued those with grass 7 svn, on windows, using the GUI to have a terminal.

As I stated grass is extremely fast to build topology, and then the export to postgis topology is already faster than using pure postgis topology, but yet surprisingly slow compared to the grass internal.

v.in.ogr dsn=“PG:host=localhost port=5433 dbname=street_gen user=postgres password=??” layer=bdtopo.route out=test_postgis_import
v.out.postgis -l -2 input=route@T0111151 dsn=“PG:host=localhost dbname=street_gen port=5433 user=postgres password=??” olayer=test_layer -t

v.external.out dsn=“PG:host=localhost dbname=street_gen port=5433 user=postgres password=??” options=topology=yes format=PostgreSQL
v.in.ogr dsn=“…\route_utf8.shp” -t

Cheers,
Rémi-C

···

2014-04-01 13:42 GMT+02:00 Martin Landa <landa.martin@gmail.com>:

Hi,

2014-04-01 11:43 GMT+02:00 Rémi Cura <remi.cura@gmail.com>:

Grass GIS is very fast to construct a topology over vectors (5 secs), and
then surprisingly slow in exporting this model to Postgis topology (145
sec).

Is there a perticular reason for this? I tried to read the code, but
couldn’t get to the sql querries.

Also, in a more prospective way, could Grass import from geometry (shapefile
for example) then export to postgis topology be isolated (into an exe or a
lib?).

could you post exact commands you run, or some sample data to play
with. Thanks, Martin