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.
