3

I am developing a QGIS plugin. I need to load some polygons from a table in PostGIS. This table is defined with this SQL query :

CREATE TABLE cad.polygon(
    id_polygon serial NOT NULL,
    geometry geometry NOT NULL,
    id_plan integer NOT NULL,
    manually boolean NOT NULL,
    creation_date date NOT NULL,

    CONSTRAINT polygon_pkey PRIMARY KEY(id_polygon),
    CONSTRAINT fk           FOREIGN KEY(id_plan) REFERENCES cad.plan(id_plan)
) WITH (OIDS = FALSE) TABLESPACE pg_default;

and contains 163 entities with two geometry types (curve polygon and polygon). I wrote a Python script to get these polygons in QGIS :

uri = QgsDataSourceUri()

# Connect to the database on the local host
uri.setConnection(
    "localhost",
    "5432",
    "dbname",
    "username",
    "*****"
)

# SELECT * FROM cad.polygon WHERE id_plan = <plan ID>
uri.setDataSource(
    "cad",
    "polygon",
    "geometry",    # Column name for the geometry
    "id_plan = 2"  # whatever the plan ID
)

uri.setKeyColumn("id_polygon")
uri.setWkbType(QgsWkbTypes.Polygon)

# Create a new QGIS vector layer
layer = QgsVectorLayer(uri.uri(False), "polygons", "postgres")
QgsProject.instance().addMapLayer(layer)

When I run this script, near the layer generated in the legend, there is this message :

Unusable layer : The data source of this layer has not been found. Click to choose a new data source.

I don't understand where this error comes from and how to fix it.

1
  • I'd say that the mixed geometries cause QGIS to fail loading the layer; QGIS can handle the generic GEOMETRY (data) type, but will not resolve actual different geometry types into the same layer. try selecting only one type and see if it loads. – geozelot Sep 26 '19 at 17:36
2

The geometry column must not be defined in the table definition. In the table definition you just define all other columns besides the 1 or n geometry columns.

To define these you need the PostGIS AddGeometryColumn function, refer to https://postgis.net/docs/AddGeometryColumn.html

In your case:

CREATE TABLE cad.polygon(
    id_polygon serial NOT NULL,
    id_plan integer NOT NULL,
    manually boolean NOT NULL,
    creation_date date NOT NULL,

    CONSTRAINT polygon_pkey PRIMARY KEY(id_polygon),
    CONSTRAINT fk           FOREIGN KEY(id_plan) REFERENCES cad.plan(id_plan)
) WITH (OIDS = FALSE) TABLESPACE pg_default;

SELECT AddGeometryColumn ('cad', 'polygon', 'geometry', 4326, 'MULTIPOLYGON', 2);

4326 is the srid of the geometry wich you might replace if applies.

2
  • 1
    I am using the SRID 3857 for this project. I will try to change the definition of my table in pgAdmin. – e-Viegas Sep 26 '19 at 13:01
  • 1
    actually, the necessity of using AddGeometryColumn to register a spatial column has been overhauled with PostGIS > 2.0; it is recommended to use the PG type mod cast in the table definition, e.g. geometry GEOMETRY(GEOMETRY, 3857) NOT NULL to register it in the PostGIS system tables. – geozelot Sep 26 '19 at 17:27

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.