[GRASS-dev] Output to Postgis Topology very slow

GRASS GIS GRASS GIS developer
, ,
1

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?

Thank you for your time,

Cheers,
Rémi-C

2

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 ?

Moritz

3

2014-05-07 10:23 GMT+02:00 Moritz Lennert <mlennert@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

4

Update :

using v.build dump ,
importing it into postgres

parsing it to reconstitue tables (node, line, node-line_connection_info)

is about 10 sec(dump) +15 sec (import+parsing).
However if those table are functionnaly equivalent to postgis topology (node, egde), I didn’t write the conversion.

This figures seems to indicate that grass v.out.postgis topology=yes should be about 5+10 sec (not 180 sec).

Is there any way to change the current behaviour to group the insert before sending to data base
and/or
to just output grass topology table (node_grass, line_grass, area_grass, isle_grass), so I could try batch conversion to postgis_topology?

Cheers,
Rémi-C

···

2014-05-07 17:37 GMT+02:00 Rémi Cura <remi.cura@gmail.com>:

2014-05-07 10:23 GMT+02:00 Moritz Lennert <mlennert@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

5

Hi,

2014-05-06 16:28 GMT+02:00 Rémi Cura <remi.cura@gmail.com>:

first of all, thanks for testing!

(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).

GRASS uses by default only createtopology() and
addtopogeometrycolumn(). Other functions from PostGIS Topology are not
used at all. Adding, deleting or modifying topological elements are
done by simple insert/delete/update SQL statements.

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)

First `v.in.ogr` builds topology in native format, than it copies
topological elements to DB and after that updates topological
information (left/right face/edge...) in DB. There seems to be problem
with copying elements to DB. I will try to fix during this or the next
week.

Martin

--
Martin Landa * http://geo.fsv.cvut.cz/gwiki/Landa

6

Hi,

2014-05-07 17:37 GMT+02:00 Rémi Cura <remi.cura@gmail.com>:

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

This will have no effect when you use GRASS-PG data provider.
Implement copy statements is still in TODO. You can force to use
GRASS-OGR data provider to export simple features to PostGIS
(`GRASS_VECTOR_OGR=1`). Than v.in.ogr will use PostgreSQL driver from
OGR library.

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

Are you sure about last one?

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.

[...]

Thanks for detailed report, Martin

--
Martin Landa * http://geo.fsv.cvut.cz/gwiki/Landa

7

Hi,

2014-05-12 15:59 GMT+02:00 Rémi Cura <remi.cura@gmail.com>:

[...]

to just output grass topology table (node_grass, line_grass, area_grass,
isle_grass), so I could try batch conversion to postgis_topology?

it's not possible, anyway interesting to have it. Putting to my TODO. Martin