2

I am trying to use PyQGIS to load PostGIS layers into QGIS 3 using the code below:

from qgis.core import QgsProject, QgsVectorLayer, QgsDataSourceUri, QgsApplication

#init Qgis
QgsApplication.setPrefixPath('/usr', True)
qgs = QgsApplication([], False)
qgs.initQgis() 

# create postgis layer
schema = 'public'
table = 'line_table'
uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432","","","")
uri.setDataSource(schema, table, 'geom')


#add layer to QGIS
layer = QgsVectorLayer(uri.uri(False), table, "postgres")
if not layer.isValid():
  print("Layer failed to load!")

qgs.exitQgis();

This has been successful if the PostGIS table (as of PostGIS 3.2) has only one type of geometry (e.g. the line_table above). When the PostGIS layer contains mixed geometry types (e.g. points AND lines; in the line_point_table), the above code fails complaining about invalid layer.

By comparison, if I load the same mixed geometry table into QGIS (3.22), QGIS automatically detects the mixed geometry status and creates two layers with the same name on the TOC.

My Question:

How do I use PyQGIS to implement the exact same behavior of the QGIS GUI to automatically create multiple layers, one for each of the geometry type in a mixed geometry table?

I read about creating views and I am not sure if that's how QGIS 3 did it internally. Can someone familiar with the QGIS source code give me some pointers?


Below is a small example for line_table and line_point_table:


DROP TABLE public.line_table;
DROP TABLE public.line_point_table;
SET default_table_access_method = heap;

--
-- Name: line_point_table; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.line_point_table (
    id bigint,
    geom public.geometry
);


--
-- Name: line_table; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.line_table (
    id bigint,
    geom public.geometry
);


--
-- Data for Name: line_point_table; Type: TABLE DATA; Schema: public; Owner: -
--

COPY public.line_point_table (id, geom) FROM stdin;
1   010100002041690000062DAEF799203C411F9B58EFC06C2241
2   01050000204169000001000000010200000002000000062DAEF799203C411F9B58EFC06C2241721B4C2201213C413806057FB16D2241
3   010100002041690000721B4C2201213C413806057FB16D2241
\.


--
-- Data for Name: line_table; Type: TABLE DATA; Schema: public; Owner: -
--

COPY public.line_table (id, geom) FROM stdin;
1   01050000204169000001000000010200000002000000062DAEF799203C411F9B58EFC06C2241721B4C2201213C413806057FB16D2241
\.

1 Answer 1

1
+50

To load geometries from a mixed geometry table you need to specify the geometry type using the type parameter in the data source URI. This is possible using setParam():

uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "test", "postgres", "secret")
uri.setDataSource(schema, table, 'geom')
uri.setParam('type', 'Point') #

This will of course only select the Point geometries from your table. To create multiple layers, one for each of the geometry types, you could query all the different geometry types that exist in the table first and then loop over them to create layers:

import psycopg2

schema = 'public'
table = 'line_point_table'
    
conn = psycopg2.connect(dbname="test", user="postgres", password="secret")
cur = conn.cursor()
sql = f'select geometrytype(geom) from {schema}.{table} group by geometrytype(geom);'
cur.execute(sql)
results = cur.fetchall()

for res in results:
    uri = QgsDataSourceUri()
    uri.setConnection("localhost", "5432", "test", "postgres", "secret")
    uri.setDataSource(schema, table, 'geom')
    uri.setParam('type', res[0])

    #add layer to QGIS
    layer = QgsVectorLayer(uri.uri(False), table, "postgres")
    if not layer.isValid():
        print("Layer failed to load!")

    QgsProject.instance().addMapLayer(layer)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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