[GRASS-dev] Output to Postgis Topology very slow

[Rémi Cura]

2014-05-07 10:23 GMT+02:00 Moritz Lennert <mlennert at club.worldonline.be>:

> On 06/05/14 16:28, Rémi Cura wrote:
>
>> Hello everybody,
>>
>> I'm trying to track why converting from grass 7 vector to postgis
>> topology vector is so slow.
>> (http://trac.osgeo.org/postgis/ticket/2695), few sec to import it into
>> grass , few sec to build topology in grass,*several minutes to push it*
>>
>> into PostGIS topology.
>>
>>
>> So far I found that the only sql function used by grass are
>> topology.createtopology
>> topology.addtopogeometrycolumn
>> topology.addnode
>> topology.addedge
>>
>> I replaced the 2 last with empty function (returning 1) and that doesn't
>> change the execution time (meaning slowness comes from grass side).
>>
>> My command lines executed on grass svn 7 on windows XP (server has
>> latest postgres postgis geos gdal on ubuntu 12.04 virtual box) are:
>>
>> v.external.out dsn="PG:host=localhost dbname=test_grass port=5433
>> user=postgres password=yourguess" format=PostgreSQL
>> options="TOPOLOGY=yes,SCHEMA=test_route_utf8_4,TOPOSCHEMA_
>> NAME=toposchema,TOPOGEOM_NAME=tg,TOPO_TOLERANCE=0.1,TOPO_
>> GEO_ONLY=NO,SIMPLE_FEATURE=NO"
>>
>> time v.in.ogr dsn="....myshapefile.shp"
>> -t output=test --overwrite
>>
>> _1. Can somebody please confirm that grass is building the topology on
>> the vector layer *before* exporting it? (it seems to also build a
>> topolgy *after *export)
>>
>>
>> _2. I am under the impression that the conversion is slow because many
>> queries are getting fired instead of few big ones. Is there any way to
>> get tables in postgis with exact grass topology data (/point,line,called
>> nodes,boundary,centroid,area,face/) to test batch conversion?
>>
>>
> Have you tried v.out.postgis ?
>
>
> Yes of course.
I benched the following way :

shapefile to postgres table
no topology
with attributes
grass (v.external.out then v.in.ogr): about 40 sec
grass (v.external.out (PG_USE_COPY=YES) then v.in.ogr): about 40 sec
grass ( v.in.ogr then v.out.postgis ): about 40 sec
grass ( v.in.ogr then v.out.ogr (PG_USE_COPY=NO)): about 30 sec
grass ( v.in.ogr then v.out.ogr (PG_USE_COPY=YES)): about 4 sec

ogr2ogr (PG_USE_COPY=YES)  : 4 sec
ogr2ogr (PG_USE_COPY=NO)  : 30 sec
shp2pgsql : 3 sec

with topology
grass (v.external.out (PG_USE_COPY=YES) then v.in.ogr ) : about 180 sec
grass (v.external.out (PG_USE_COPY=NO) then v.in.ogr ) : about 180 sec
grass ( v.in.ogr then v.out.postgis ) same.


Cheers,
Rémi-C
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/grass-dev/attachments/20140507/b5cc5e28/attachment.html>