Several colleagues of mine have asked whether it is possible to
visualise the results of a SELECT statement on a PostGIS database
that returns spatial data in QGIS. In other words, can we map the
results of something like:
SELECT id, st_union(the_geom) FROM spatial_table GROUP BY id;
My usual answer to this in the past has been “not yet…”, but now thanks to Giuseppe Sucameli and Jürgen E. Fischer, the answer is a resounding “yes!”. A recent patch to QGIS trunk now makes custom Postgres queries possible via the postgres data provider.
Unfortunately there is no user interface implemented to take advantage
of this functionality (yet!)There is now a plugin available from the
Faunalia python plugin repository called RT Sql Layer which provides
a GUI for loading PostGIS SELECT statements as layer, but you can also
access this handy feature via the QGIS Python console:
db_conn = "dbname='gis' host=localhost port=5432 user='cfarmer' password='xxxx'"
id_field = "id"
table = "(select id, st_union(the_geom) from spatial_table group by id)"
uri = "%s key=%s table=%s (the_geom) sql=" % (db_conn,id_field,table,)
layer = QgsVectorLayer(uri, "testlayer", "postgres")
we can then add the layer to the map canvas via:
QgsMapLayerRegistry.instance().addMapLayer(layer)
and even query/measure it via something like:
provider = layer.dataProvider()
feat = QgsFeature()
provider.select([], QgsRectangle())
provider.nextFeature(feat)
dist = QgsDistanceArea()
dist.measure(feat.geometry())
Just another one of the many new features being added to QGIS every day!
