1

I use PyQGIS 3.6.1 Noosa to load layer from my database storage PostgreSQL database with PostGIS extension using qgis.core.QgsVectorLayer method. But layer is invalid somehow. Performing same operation with QGIS interface - successfully creates the layer (see picture).

How to create a layer with qgis.core.QgsVectorLayer method?

Here is part of my code:

def load_layer_from_DB(aSchema, aTable, aGeometryColumn, host, port, dbname, user, password):
    uri = qgis.core.QgsDataSourceUri()
    uri.setConnection(host, port, dbname, user, password)
    uri.setDataSource(aSchema, aTable, aGeometryColumn)    
    layer = qgis.core.QgsVectorLayer(uri.uri(), 'some_name', "postgis") # i have tried `postgres`, `postgresql` but no luck

# connect stuff
dbname = 'db_visdata'
host = 'ххх.ххх.ххх.ххх'
port = 'хххх'
user = 'хххххххххх'
password = 'хххххххххх'

# path in database
aSchema = "test_data"
aTable = 'test_satellite_boarders'
aGeometryColumn = 'MultiPolygon'

layer = load_layer_from_DB(aSchema, aTable, aGeometryColumn, host, port, dbname, user, password)

print('layer.isValid=', layer.isValid())
print('layer.type=', layer.type())
print('layer.name=', layer.name())
print('layer.featureCount=', layer.featureCount())
return layer

Output:

layer.isValid= False
layer.type= 0
layer.name= some_name
layer.featureCount= -1
2
+50

You're near the solution.

First, create data in Postgis :

CREATE SCHEMA test_data;
CREATE EXTENSION postgis;
DROP TABLE IF EXISTS test_data.test_satellite_boarders;
CREATE TABLE test_data.test_satellite_boarders (
id SERIAL PRIMARY KEY,
name VARCHAR(50)
);

SELECT AddGeometryColumn ('test_data', 'test_satellite_boarders', 'geom', 4326, 'POINT', 2);

INSERT INTO test_data.test_satellite_boarders (name, geom)
SELECT 'pt1' AS name, ST_GeomFromText('POINT(1 9)', 4326) UNION ALL
SELECT 'pt1' AS name, ST_GeomFromText('POINT(2 8)', 4326) UNION ALL
SELECT 'pt1' AS name, ST_GeomFromText('POINT(3 7)', 4326) UNION ALL
SELECT 'pt1' AS name, ST_GeomFromText('POINT(4 6)', 4326) UNION ALL
SELECT 'pt1' AS name, ST_GeomFromText('POINT(5 5)', 4326) UNION ALL
SELECT 'pt1' AS name, ST_GeomFromText('POINT(6 4)', 4326) UNION ALL
SELECT 'pt1' AS name, ST_GeomFromText('POINT(7 3)', 4326) UNION ALL
SELECT 'pt1' AS name, ST_GeomFromText('POINT(8 2)', 4326) UNION ALL
SELECT 'pt1' AS name, ST_GeomFromText('POINT(9 1)', 4326);

Secondly, fill parameters and correct a little the PyQGIS code :

def load_layer_from_DB(aSchema, aTable, aGeometryColumn, host, port, dbname, user, password):
    uri = qgis.core.QgsDataSourceUri()
    uri.setConnection(host, port, dbname, user, password)
    uri.setDataSource(aSchema, aTable, aGeometryColumn)
    layer = qgis.core.QgsVectorLayer(uri.uri(), "some_name", "postgres")
    return layer

# connect stuff
dbname = "db_visdata"
host = "127.0.0.1"
port = "5432"
user = "postgres"
password = "admin"

# path in database
aSchema = "test_data"
aTable = "test_satellite_boarders"
aGeometryColumn = "geom"

layer = load_layer_from_DB(aSchema, aTable, aGeometryColumn, host, port, dbname, user, password)

print('layer.isValid=', layer.isValid())
print('layer.type=', layer.type())
print('layer.name=', layer.name())
print('layer.featureCount=', layer.featureCount())

QgsProject.instance().addMapLayer(layer)

Errors :

  1. return layer must be the last line for the function, if not, the function doesn't return the QgsVectorLayer object ;
  2. the provider for a Postgis QgsVectorLayer is "postgres", here the API doc ;
  3. the geometry column is the name of the geometry field. I didn't knwow if you made the error with MultiPolygon which is a geometry type, but I prefer to specify.

  4. Bonus tip : if you use a view instead of a table, you'll have to specify the key column with uri.setKeyColumn("id")

  • Jesus! I was unable to add layer for a week because of this line: aGeometryColumn = 'MultiPolygon' instead of aGeometryColumn = "geom". God bless you and gis.stackexchange.com. All the documentation is broken or I am a blind. – Mr. Che Apr 2 at 4:25
  • The official documentation is here : docs.qgis.org/testing/en/docs/pyqgis_developer_cookbook/… and it's working. So ... – J. Monticolo Apr 2 at 6:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.