{"id":6234,"date":"2018-09-04T04:40:50","date_gmt":"2018-09-04T02:40:50","guid":{"rendered":"http:\/\/www.sigterritoires.fr\/?p=6234"},"modified":"2018-10-17T12:04:23","modified_gmt":"2018-10-17T10:04:23","slug":"python-methods-for-qgis-how-to-access-vector-data-postgis-spatiality","status":"publish","type":"post","link":"https:\/\/www.sigterritoires.fr\/index.php\/en\/python-methods-for-qgis-how-to-access-vector-data-postgis-spatiality\/","title":{"rendered":"Python Methods for QGis: How to access vector data (postgis, spatiality)"},"content":{"rendered":"\r\n\r\n\r\n

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<\/a> vector (shapefile)<\/a> ) we have discussed \u00a0how to load a shapefile<\/a> file . In this article we ‘ll see how to load a Postgresql \/ Postgis<\/a> table and a Spatialite table .<\/p>\r\n\r\n\r\n\r\n

How to load<\/strong> a<\/strong> postgis<\/strong> layer<\/strong><\/h2>\r\n\r\n\r\n\r\n
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.<\/p>\r\n\r\n\r\n\r\n
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.<\/p>\r\n\r\n\r\n\r\n
In ScripRunner create a new script and name it charger_database.py.<\/em><\/strong><\/p>\r\n\r\n\r\n\r\n
Enter the following code:<\/p>\r\n\r\n\r\n\r\n
$\"\"$ <\/div>
<\/div>
<\/div><\/div>
\r\n
# 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.<\/p>\r\n\r\n\r\n\r\n
\u00a0\u00bb \u00a0\u00a0 \u00a0\u00bb \u00a0 \u00bbYour description of the script goes here\u00ab \u00a0\u00a0 \u00a0\u00bb \u00a0 \u00ab\u00a0<\/p>\r\n\r\n\r\n\r\n
# Some commonly used imports<\/p>\r\n\r\n\r\n\r\n
from PyQt4.QtCore import *
from PyQt4.QtGui import *
from qgis.core import *
from qgis.gui import <\/p>\r\n\r\n\r\n\r\n
def run_script ( iface ):
uri = QgsDataSourceURI ()
uri.setConnection (\u00a0\u00bb \u00a0 localhost \u00a0 \u00bb,\u00ab \u00a0 5432 \u00a0 \u00bb,\u00ab \u00a0 postgres \u00a0 \u00a0\u00bb \u00a0 \u00ab\u00a0User1″, \u00a0\u00bb \u00a0 user1 \u00a0 \u00ab\u00a0)
uri.setDataSource (\u00a0\u00bb \u00a0 public \u00a0 \u00bb,\u00ab \u00a0 details \u00a0 \u00bb,\u00ab \u00a0 geom \u00a0 \u00a0\u00bb \u00a0 \u00ab\u00a0Version = 1\u00a0\u00bb)
layer = QgsVectorLayer ( uri.uri (), \u00a0\u00bb \u00a0 reefs \u00a0 \u00bb,\u00ab \u00a0 postgres \u00a0 \u00ab\u00a0)
if not layer.isValid ():
print \u00ab \u00a0 Layer% s did not load \u00a0 \u00bb% Layer.name ()
QgsMapLayerRegistry.instance (). addMapLayers ([layer])<\/p>\r\n<\/div><\/div><\/figure>\r\n\r\n\r\n\r\n\r\n\r\n
The script must look like this: <\/p>\r\n\r\n\r\n\r\n
$\"\"$ <\/a><\/figure>\r\n\r\n\r\n\r\n
You will observe that the line of code that creates the layer<\/p>\r\n\r\n\r\n\r\n
layer<\/em><\/strong> =<\/em><\/strong> QgsVectorLayer<\/em><\/strong> (<\/em><\/strong> uri.uri<\/em><\/strong> (), \u00ab\u00a0<\/em><\/strong> reefs<\/em><\/strong> \u00bb,\u00ab<\/em><\/strong> postgres<\/em><\/strong> \u00ab\u00a0)<\/em><\/strong><\/p>\r\n\r\n\r\n\r\n*
contains a provider postgres<\/em><\/strong> instead of ogr<\/em><\/strong> and that the file name is replaced by a uri<\/em><\/strong> object . It is this object that is in charge of the connection with the Postgis database.<\/p>\r\n\r\n\r\n\r\n
uri<\/em><\/strong> =<\/em><\/strong> QgsDataSourceURI<\/em><\/strong> ()<\/em><\/strong><\/p>\r\n\r\n\r\n\r\n
creates the object ,<\/p>\r\n\r\n\r\n\r\n
uri.setConnection<\/em><\/strong> (\u00ab\u00a0<\/em><\/strong> localhost<\/em><\/strong> \u00bb,\u00ab<\/em><\/strong> 5432<\/em><\/strong> \u00bb,\u00ab<\/em><\/strong> postgres<\/em><\/strong> \u00ab\u00a0<\/em><\/strong> \u00ab\u00a0User1\u00a0\u00bb, \u00ab\u00a0<\/em><\/strong> user1<\/em><\/strong> \u00ab\u00a0)<\/em><\/strong><\/p>\r\n\r\n\r\n\r\n
creates the connection string :<\/p>\r\n\r\n\r\n\r\n
\r\n
server address (here localhost<\/em> )<\/li>\r\n
postgres listening port (here 5432<\/em> )<\/li>\r\n
name of the database ( here postgres<\/em> )<\/li>\r\n
name of the user (here user1<\/em> )<\/li>\r\n
user password (here user1<\/em> )<\/li>\r\n<\/ul>\r\n\r\n\r\n\r\n
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.<\/p>\r\n\r\n\r\n\r\n
uri.setDataSource<\/em><\/strong> (<\/em><\/strong> \u00ab\u00a0<\/em><\/strong> public<\/em><\/strong> \u00bb,\u00ab<\/em><\/strong> details<\/em><\/strong> \u00bb,\u00ab<\/em><\/strong> geom<\/em><\/strong> \u00ab\u00a0<\/em><\/strong> \u00ab\u00a0Version = 1\u00a0\u00bb)<\/em><\/strong><\/p>\r\n\r\n\r\n\r\n
This line defines which Postgis table to use in the connected database:<\/p>\r\n\r\n\r\n\r\n
\r\n
source name ( here public<\/em> )<\/li>\r\n
table name ( here details<\/em> )<\/li>\r\n
\u00a0geometry field of the table name( here geom<\/em> )<\/li>\r\n
eventually a SQL clause of type WHERE.<\/li>\r\n<\/ul>\r\n\r\n\r\n\r\n
Right here version = 1<\/em><\/strong> corresponds to a clause<\/p>\r\n\r\n\r\n\r\n
SELECT * FROM details WHERE<\/em> version = 1<\/em><\/strong><\/p>\r\n\r\n\r\n\r\n
If you do not want to apply a filter to the table, simply return \u00a0\u00bb \u00a0\u00a0 \u00ab\u00a0.<\/p>\r\n\r\n\r\n\r\n
How to load<\/strong> a<\/strong> Spatialite layer<\/strong><\/h2>\r\n\r\n\r\n\r\n
Very little used, therefore very much practical and powerful, the Spatialite bases can also be loaded simply with Python.<\/p>\r\n\r\n\r\n\r\n
Here is the code for a script comparable to the previous one but which loads a table from a Spatialite base:<\/p>\r\n\r\n\r\n\r\n
$\"\"$
<\/div>
<\/div>
<\/div><\/div>
\r\n
# 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.<\/p>\r\n\r\n\r\n\r\n
\u00a0\u00bb \u00a0\u00a0 \u00a0\u00bb \u00a0 \u00bbYour description of the script goes here\u00ab \u00a0\u00a0 \u00a0\u00bb \u00a0 \u00ab\u00a0<\/p>\r\n\r\n\r\n\r\n
# Some commonly used imports<\/p>\r\n\r\n\r\n\r\n
from PyQt4.QtCore import *
from PyQt4.QtGui import *
from qgis.core import *
from qgis.gui import <\/p>\r\n\r\n\r\n\r\n
def run_script ( iface ):
uri = QgsDataSourceURI ()
uri.setDatabase (\u00a0\u00bb \u00a0 H: \/repertoiresblog\/QGis\/validation.sqlite \u00a0 \u00ab\u00a0)
uri.setDataSource (\u00a0\u00bb \u00a0\u00a0 \u00bb,\u00ab \u00a0 com2011orig \u00a0 \u00bb,\u00ab \u00a0 geom \u00a0 \u00a0\u00bb \u00a0 \u00bb Cod_reg = 9″)
layer = QgsVectorLayer ( uri.uri (), \u00a0\u00bb \u00a0 Commons \u00a0 \u00bb,\u00ab \u00a0 spatialite \u00a0 \u00ab\u00a0)
if not layer.isValid ():
print \u00ab \u00a0 Layer% s did not load \u00a0 \u00bb% Layer.name ()
QgsMapLayerRegistry.instance (). addMapLayers ([layer])<\/p>\r\n<\/div><\/div><\/figure>\r\n\r\n\r\n\r\n\r\n\r\n
Your script must look like this: <\/p>\r\n\r\n\r\n\r\n
$\"\"$ <\/a><\/figure>\r\n\r\n\r\n\r\n
uri.setDatabase<\/em><\/strong> (<\/em><\/strong> \u00ab\u00a0<\/em><\/strong> H: \/repertoiresblog\/QGis\/validation.sqlite<\/em><\/strong> \u00ab\u00a0)<\/em><\/strong><\/p>\r\n\r\n\r\n\r\n*
This line defines the path and name of the Spatialite base concerned .<\/p>\r\n\r\n\r\n\r\n
\u00a0 uri.setDataSource<\/em><\/strong> (<\/em><\/strong> \u00ab\u00a0<\/em><\/strong> \u00bb,\u00ab<\/em><\/strong> com2011orig<\/em><\/strong> \u00bb,\u00ab<\/em><\/strong> geom<\/em><\/strong> \u00ab\u00a0<\/em><\/strong> \u00bb<\/em><\/strong> Cod_reg<\/em><\/strong> = 9″)<\/em><\/strong><\/p>\r\n\r\n\r\n\r\n
The first parameter, source, does not apply to Spatiality databases .<\/p>\r\n\r\n\r\n\r\n
\r\n
name of the table to load ( here com2011orig<\/em> )<\/li>\r\n
name of the geometry field ( here geom<\/em> )<\/li>\r\n
SQL filtering clause , optional ( here cod_reg<\/em> = 9<\/em> )<\/li>\r\n<\/ul>\r\n\r\n\r\n\r\n
\u00a0<\/em><\/strong> layer<\/em><\/strong> =<\/em><\/strong> QgsVectorLayer<\/em><\/strong> (<\/em><\/strong> uri.uri<\/em><\/strong> (), \u00ab\u00a0<\/em><\/strong> Commons<\/em><\/strong> \u00bb,\u00ab<\/em><\/strong> spatialite<\/em><\/strong> \u00ab\u00a0)<\/em><\/strong><\/p>\r\n\r\n\r\n\r\n
We create the layer with the object uri we have just built and as data provider spatialite<\/em><\/strong> .<\/p>\r\n\r\n\r\n\r\n
Other<\/strong> data<\/strong> providers<\/strong> available<\/strong><\/h2>\r\n\r\n\r\n\r\n
We have discussed three data providers for QgsVectorLayer: ogr,<\/em><\/strong> postmaster,<\/em><\/strong> and spatialite<\/em><\/strong> . The first is, in fact, a library that allows access to many formats.<\/p>\r\n\r\n\r\n\r\n
In addition to these three providers, you can use the following:<\/p>\r\n\r\n\r\n\r\n
\r\n
memory<\/em><\/strong> which loads the created layers in memory<\/li>\r\n
mssql<\/em><\/strong> which allows to load a layer a Microsoft SQL database<\/li>\r\n
wfs<\/em><\/strong> which allows to load a layer of a WFS stream<\/li>\r\n
delimitedtext<\/em><\/strong> which allows to load a layer from a file text<\/li>\r\n
gpx<\/em><\/strong> which loads routes and waypoints from a file gpx<\/li>\r\n
grass<\/em><\/strong> which charges a GRASS layer<\/li>\r\n<\/ul>\r\n\r\n\r\n\r\n
In the next article we will discuss how to consult the attributes and geometries of the vector layers loaded in QGis.<\/p>\r\n","protected":false},"excerpt":{"rendered":"
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 \u00a0how to…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"give_campaign_id":0,"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"sfsi_plus_gutenberg_text_before_share":"","sfsi_plus_gutenberg_show_text_before_share":"","sfsi_plus_gutenberg_icon_type":"","sfsi_plus_gutenberg_icon_alignemt":"","sfsi_plus_gutenburg_max_per_row":"","_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[1260],"tags":[],"class_list":["post-6234","post","type-post","status-publish","format-standard","hentry","category-non-classe-en"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p6XU0A-1Cy","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts\/6234","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/comments?post=6234"}],"version-history":[{"count":0,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts\/6234\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/media?parent=6234"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/categories?post=6234"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/tags?post=6234"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}