[GRASS-dev] v.out.postgis -l running for hours

[Sandro Santilli]

I'm forwarding this mail from -user as it didn't get attention there
yesterday. Today I've tried again a v.out.postgis -l, this time
with a different (bigger) dataset.

This other dataset took 20 minutes to import/build, and took 85 minutes
to export to PostGIS (4 times more expensive to export).

 v.out.postgis complete. 224655 primitives written to <grass_ulfareale>.
 real    85m16.237s
 user    2m48.962s
 sys     1m19.671s

Is the export doing anything else than INSERT calls ?
I've seen some UPDATE calls against edges (which could maybe be avoided,
same problem in PostGIS itself: http://trac.osgeo.org/postgis/ticket/2993;
but I hadn't noticed if GRASS is also calling SELECT against the ISO
topology functions.

The input GRASS map:

 nodes=107553
 points=0
 lines=0
 boundaries=158084
 centroids=66571
 areas=66577
 islands=16046
 primitives=224655
 map3d=0

The created PostGIS topology:

 Topology topo_grass_ulfareale (5), SRID 0, precision 0       
 174124 nodes, 158084 edges, 82623 faces, 0 topogeoms, 1 layers
 Layer 1, type Polygonal (3), 0 topogeoms                     
  Deploy: public.grass_ulfareale.topo                         

The "0 topogeoms" part of the summary reveals a bug somewhere.
It means TopoGeometry objects were not registered in the relation
table. How are them created ? I see literal TopoGeometry objects
present in the public.grass_ulfareale.topo table, but those
numbers have no meaning on themselves w/out corresponding entries
in topo_grass_ulfareale.relation.

--strk;

----- Forwarded message from Sandro Santilli <strk at keybit.net> -----

Date: Wed, 19 Nov 2014 00:17:33 +0100
From: Sandro Santilli <strk at keybit.net>
To: grass-user at lists.osgeo.org
Cc: landa.martin at gmail.com
Subject: v.out.postgis -l running for hours
User-Agent: Mutt/1.5.21 (2010-09-15)
Bcc: sandro.santilli at gmail.com

I've imported a ~1 million small polygons dataset from PostGIS into
GRASS with v.in.ogr, which tool less than 30 minutes and was now
trying to export it into the PostGIS Topology model with v.out.postgis -l.

Unexpectedly this latter step is beeing running for hours and did not
even complete the first part:

GRASS 7.0.0beta3 (world):/usr/src/grass/grass-7.0.0beta3 > time \
v.out.postgis -l input=million_poly_topo1 \
olayer=grass_million_poly_topo1 dsn="PG:dbname=strk" \
olink=pg_million_poly_topo1
Copying features...
  35%

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
15997 strk      20   0 4928752 2.069g 1.578g R  98.5 13.3 280:28.46 postgres: strk strk [local] UPDATE
15994 strk      20   0 2796056 2.506g   7432 S   1.6 16.1  10:07.11 v.out.postgis -l input=million_poly_topo1 olayer=grass_million_poly_topo1 dsn+

On the PostgreSQL side there's no data load visible:

 strk=# select count(*) from grass_million_poly_topo1;
  count
 -------
      0
 (1 row)

 strk=# select topologysummary('topo_grass_million_poly_topo1');
                           topologysummary
 --------------------------------------------------------------------
  Topology topo_grass_million_poly_topo1 (2405), SRID 0, precision 0+
  0 nodes, 0 edges, 0 faces, 0 topogeoms, 1 layers                  +
  Layer 1, type Polygonal (3), 0 topogeoms                          +
   Deploy: public.grass_million_poly_topo1.topo                     +
 
 (1 row)

I guess things are being done in a transaction ?

>From the output table structure it looks like v.out.postgis is also
trying to export simple features (the "geom" field):

 strk=# \d grass_million_poly_topo1
                                Table "public.grass_million_poly_topo1"
  Column |       Type        |                               Modifiers
 --------+-------------------+------------------------------------------------------------------------
  fid    | integer           | not null default nextval('grass_million_poly_topo1_fid_seq'::regclass)
  cat    | integer           |
  value  | integer           |
  geom   | geometry(Polygon) |
  topo   | topogeometry      |
 Indexes:
     "grass_million_poly_topo1_pkey" PRIMARY KEY, btree (fid)
     "grass_million_poly_topo1_cat_idx" btree (cat)
     "grass_million_poly_topo1_geom_idx" gist (geom)
     "public_grass_million_poly_topo1_topo_idx" btree (((topo).id))
 Check constraints:
     "check_topogeom_topo" CHECK ((topo).topology_id = 2405 AND (topo).layer_id = 1 AND (topo).type = 3)

Could that be the cause of the slowness ? Is there a way to disable
conversion to simple-feature, which is already supported by
PostGIS Topology itself ?

Thanks in advance.

--strk;

  ()   Free GIS & Flash consultant/developer
  /\   http://strk.keybit.net/services.html

----- End forwarded message -----