Python Methods for QGis: How to access vector data (postgis, spatiality)

In this series of articles we discuss how to access vector type data in Python and QGis. In the previous article ( Python Recipes for QGis: access data vector (shapefile) ) we have discussed  how to load a shapefile file . In this article we ‘ll see how to load a Postgresql / Postgis table and a Spatialite table .

How to load a postgis layer

The other common data source for a QGis application is a vector layer contained in a Postgres / Postgis database. We will see how to load it using the Python code.

The principle is the same as for a shapefile layer except that we will use another data provider and that, unlike to a shp file; the connection to the database must be created.

In ScripRunner create a new script and name it charger_database.py.

Enter the following code:

# Customize this starter script by adding code
# to the run_script function. See the Help for
# complete information on how to create a script
# and use Script Runner.

”    ”   »Your description of the script goes here«    ”   “

# Some commonly used imports

from PyQt4.QtCore import *
from PyQt4.QtGui import *
from qgis.core import *
from qgis.gui import *

def run_script ( iface ):
uri = QgsDataSourceURI ()
uri.setConnection (”   localhost   »,«   5432   »,«   postgres   ”   “User1″, ”   user1   “)
uri.setDataSource (”   public   »,«   details   »,«   geom   ”   “Version = 1”)
layer = QgsVectorLayer ( uri.uri (), ”   reefs   »,«   postgres   “)
if not layer.isValid ():
print «   Layer% s did not load   »% Layer.name ()
QgsMapLayerRegistry.instance (). addMapLayers ([layer])

The script must look like this:

You will observe that the line of code that creates the layer

layer = QgsVectorLayer ( uri.uri (), “ reefs »,« postgres “)

contains a provider postgres instead of ogr and that the file name is replaced by a uri object . It is this object that is in charge of the connection with the Postgis database.

uri = QgsDataSourceURI ()

creates the object ,

uri.setConnection (“ localhost »,« 5432 »,« postgres “User1”, “ user1 “)

creates the connection string :

  • server address (here localhost )
  • postgres listening port (here 5432 )
  • name of the database ( here postgres )
  • name of the user (here user1 )
  • user password (here user1 )

If you want to run the script on your machine you must change these values for those of an existing database on your device ( if you keep localhost) or return a basic address accessible via the Internet.

uri.setDataSource ( public »,« details »,« geom “Version = 1”)

This line defines which Postgis table to use in the connected database:

  • source name ( here public )
  • table name ( here details )
  •  geometry field of the table name( here geom )
  • eventually a SQL clause of type WHERE.

Right here version = 1 corresponds to a clause

SELECT * FROM details WHERE version = 1

If you do not want to apply a filter to the table, simply return ”    “.

How to load a Spatialite layer

Very little used, therefore very much practical and powerful, the Spatialite bases can also be loaded simply with Python.

Here is the code for a script comparable to the previous one but which loads a table from a Spatialite base:

# Customize this starter script by adding code
# to the run_script function. See the Help for
# complete information on how to create a script
# and use Script Runner.

”    ”   »Your description of the script goes here«    ”   “

# Some commonly used imports

from PyQt4.QtCore import *
from PyQt4.QtGui import *
from qgis.core import *
from qgis.gui import *

def run_script ( iface ):
uri = QgsDataSourceURI ()
uri.setDatabase (”   H: /repertoiresblog/QGis/validation.sqlite   “)
uri.setDataSource (”    »,«   com2011orig   »,«   geom   ”   » Cod_reg = 9″)
layer = QgsVectorLayer ( uri.uri (), ”   Commons   »,«   spatialite   “)
if not layer.isValid ():
print «   Layer% s did not load   »% Layer.name ()
QgsMapLayerRegistry.instance (). addMapLayers ([layer])

Your script must look like this:

uri.setDatabase ( H: /repertoiresblog/QGis/validation.sqlite “)

This line defines the path and name of the Spatialite base concerned .

  uri.setDataSource ( »,« com2011orig »,« geom » Cod_reg = 9″)

The first parameter, source, does not apply to Spatiality databases .

  • name of the table to load ( here com2011orig )
  • name of the geometry field ( here geom )
  • SQL filtering clause , optional ( here cod_reg = 9 )

  layer = QgsVectorLayer ( uri.uri (), “ Commons »,« spatialite “)

We create the layer with the object uri we have just built and as data provider spatialite .

Other data providers available

We have discussed three data providers for QgsVectorLayer: ogr, postmaster, and spatialite . The first is, in fact, a library that allows access to many formats.

In addition to these three providers, you can use the following:

  • memory which loads the created layers in memory
  • mssql which allows to load a layer a Microsoft SQL database
  • wfs which allows to load a layer of a WFS stream
  • delimitedtext which allows to load a layer from a file text
  • gpx which loads routes and waypoints from a file gpx
  • grass which charges a GRASS layer

In the next article we will discuss how to consult the attributes and geometries of the vector layers loaded in QGis.

Leave a Reply

Your email address will not be published. Required fields are marked *