[GIS] QGIS Add PostGIS layer: restricting to single schema

postgispostgresqlqgis

My primary PostGIS database has 12 schemas (schemae? TNIRN…), some of which have nasty-sized tables. The DB is on localhost, on my own machine.

For the most part, the actual working tables are in one schema ('aa') – and are at least 1, and often 2 or 3, order[s] of magnitude smaller than the nasty-sized tables.

Every time I want to add a PostGIS layer, the Layer->Add Layer->Add PostGIS layers tool makes me wait while (10 minutes) it adds every.single.schema and every.single.geometry-column to the selection universe, when I really want it to stop when it's finished loading schema 'aa'.

Top marks to QGIS for finding all the geometries in a pretty massive database, but in the ordinary course of events I am only interested in 'aa'.

The name 'aa' is chosen specifically because it does not overlap in any way with names of the 'big-ugly' shemas (schemae? TNIRN). When I use 'Search Options' and tell it 'Search in columns'=Schema for the search term 'aa', it still loads every table.

Is it possible to restrict the domain of 'Add PostGIS Layers' to only look at one schema? I've R'd TFD to no avail.

inb4 "have a different database for your working schema". Not possible, since there is beaucoup interaction between 'aa' and other schemas (schemae? TNIRN) when actual SQL-ish work is to be done.

Versions: PostgreSQL 9.3.5, PostGIS 2.1.3, QGIS 2.6.1-Brighton.

Best Answer

UPDATE (20170419)

Here in the future, most tasks of relatively low complexity can be resolved by SLOP (Several Lines Of Python) in the Python console rather than hackily copying tables across to public.

Bear in mind that if you copy tables across, you need to remember to copy the changed table back to its original schema: more things to remember is always a bad thing.

Because it's now the future, I get the exact layer that I want - regardless of schema - by opening the Python console and entering the right parameters into GetArbitraryPostGIS.py, which looks like

schema="{schema_name}"
table="{table_name}"
geom="{geom}"
pk="{pk}"
where="{filters}"
uri = QgsDataSourceURI()
uri.setConnection("[h]", "[p]", "[db]", "[u]", "[pw]")
uri.setDataSource(schema,table, geom, where,pk_field)
uri.setSrid("[4283]")
vlayer = QgsVectorLayer(uri.uri(), "{Layer Name for QGIS}", "[postgres]")
QgsMapLayerRegistry.instance().addMapLayer(vlayer)
if not vlayer.isValid():
    print 'Layer not loaded', uri.host()

Where {} indicates a layer-specific parameter, and [] indicates a hard-coded variable.

A dozen lines: I can get a layer in from public faster than the Add PostGIS route, by

  • opening the console,
  • entering the required {} parameters into the script, and
  • hitting 'Go'.

-- The malakies in my answer from the dinosaur days still holds, when considering QGIS 'native' functionality, so I'll leave it intact below --

It seems that it is not possible to restrict Layer->Add Layer->Add PostGIS layers to a subset of the schemas (schemae? TNIRN) except if that subset is schema = public. The restriction to only load tables in the public schema, is in the connection information ("only look in the public schema" checkbox).

The solution I have hit on is to copy the table/s that I want to add to the public schema, and restrict the Layer-add process to the public layer.

This is fine for my purposes, because the tables with the desired target geometries are temporary... usually when I want to add and drop layers rapidly, it's because I want to 'eyeball' the results of some aspatial filtering prior to implementing it on the bigger datasets.

Obviously for tables that are going to be permanent, this is not a very good option - but for quick-n-dirty eyeballing, it's good enough: reduces the load time from ~7 minutes to ~8 sec (I have almost nothing except spatial_ref_sys in my public schema).

Related Question