Speed up topology loading

Sandro Santilli · Sandro Santilli · commit 73b498674794 · 2014-02-08T12:26:19.000+01:00
When loading topology viewer on a PostGIS-2.0+ database, it includes type and srid typmod in the view layers, avoiding a full scan of the datasets. Fix #9510
diff --git a/python/plugins/db_manager/db_plugins/postgis/plugins/qgis_topoview/__init__.py b/python/plugins/db_manager/db_plugins/postgis/plugins/qgis_topoview/__init__.py
@@ -34,8 +34,8 @@
 # @param db is the selected database
 # @param mainwindow is the DBManager mainwindow
 def load(db, mainwindow):
-        # check whether the selected database has topology enabled
-        # (search for topology.topology)
+        # check whether the selected database supports topology
+        # (search for topology.topology) 
         sql = u"""SELECT count(*)
                 FROM pg_class AS cls JOIN pg_namespace AS nsp ON nsp.oid = cls.relnamespace
                 WHERE cls.relname = 'topology' AND nsp.nspname = 'topology'"""
@@ -72,16 +72,25 @@ def run(item, action, mainwindow):
                 return False
 
         if item.schema() != None:
-                sql = u"SELECT count(*) FROM topology.topology WHERE name = %s" % quoteStr(item.schema().name)
+                sql = u"SELECT srid FROM topology.topology WHERE name = %s" % quoteStr(item.schema().name)
                 c = db.connector._get_cursor()
                 db.connector._execute( c, sql )
                 res = db.connector._fetchone( c )
-                isTopoSchema = res != None and int(res[0]) > 0
+                isTopoSchema = res != None 
 
         if not isTopoSchema:
                 QMessageBox.critical(mainwindow, "Invalid topology", u'Schema "%s" is not registered in topology.topology.' % item.schema().name)
                 return False
 
+        toposrid = int(res[0])
+
+        # Check if postgis supports typmod geometries
+        sql = u"SELECT typmodin FROM pg_type WHERE typname = 'geometry' AND typmodin::int > 0"
+        c = db.connector._get_cursor()
+        db.connector._execute( c, sql )
+        res = db.connector._fetchone( c )
+        supportsTypmod = res != None
+
         # load layers into the current project
         toponame = item.schema().name
         template_dir = os.path.join(current_path, 'templates')
@@ -105,8 +114,11 @@ def run(item, action, mainwindow):
                 legend.setGroupVisible(group, False)
 
           # face
-                layer = db.toSqlLayer(u'SELECT face_id, topology.ST_GetFaceGeometry(%s, face_id) as geom ' \
-                                       'FROM %s.face WHERE face_id > 0' % (quoteStr(toponame), quoteId(toponame)),
+                geomcast = ''
+                if supportsTypmod:
+                  geomcast = '::geometry(multipolygon,%s)' % toposrid
+                layer = db.toSqlLayer(u'SELECT face_id, topology.ST_GetFaceGeometry(%s, face_id)%s as geom ' \
+                                       'FROM %s.face WHERE face_id > 0' % (geomcast, quoteStr(toponame), quoteId(toponame)),
                                        'geom', 'face_id', u'%s.face' % toponame)
                 layer.loadNamedStyle(os.path.join(template_dir, 'face.qml'))
                 registry.addMapLayers([layer])
@@ -115,8 +127,11 @@ def run(item, action, mainwindow):
                 legend.moveLayer(layer, group)
 
           # face_seed
-                layer = db.toSqlLayer(u'SELECT face_id, ST_PointOnSurface(topology.ST_GetFaceGeometry(%s, face_id)) as geom ' \
-                                       'FROM %s.face WHERE face_id > 0' % (quoteStr(toponame), quoteId(toponame)),
+                geomcast = ''
+                if supportsTypmod:
+                  geomcast = '::geometry(point,%s)' % toposrid
+                layer = db.toSqlLayer(u'SELECT face_id, ST_PointOnSurface(topology.ST_GetFaceGeometry(%s, face_id))%s as geom ' \
+                                       'FROM %s.face WHERE face_id > 0' % (geomcast ,quoteStr(toponame), quoteId(toponame)),
                                        'geom', 'face_id', u'%s.face_seed' % toponame)
                 layer.loadNamedStyle(os.path.join(template_dir, 'face_seed.qml'))
                 registry.addMapLayers([layer])
