0

I'm writing an internal QGIS plugin to query a remote database. I'm not administrating the server, but administrator has set up a virtual machine on a windows file server, with PostGIS set up.

I wrote a script to download CSV files from the web, upload them on SFTP and load them in the database.

I achieved to create a view in this table from within QGIS using sshtunnel. I can fetch it (still not have the headers but I guess it's on another topic).

Thing is I cannot succeed to get a proper QgsVectorLayer : i don't know which parameters I should pass to QgsDataSourceUri - even if psycopg2 connector is working.

So I guess there are different possibilities :

  • get a layer from cur.fetchall() method, last column being a geometry (generated from the CREATE VIEW statement), maybe using other external modules such as geopandas or fiona,
  • find which parameters I should pass to QGSDataSourceUri.

Which one should I consider?

What I've accomplished so far:

from qgis.utils import iface
import processing
import tempfile
import os

with sshtunnel.open_tunnel(('xxx.xxx.xxx.x', x),ssh_username="user",ssh_password="password",remote_bind_address=('localhost', 5432)) as server:
   server.start()
   params = {'database': 'db','user': 'postgres','host': 'localhost','password':'postgres','port': server.local_bind_port}
   conn = psycopg2.connect(**params)
   with conn.cursor() as cur:
       cur.execute('''CREATE OR REPLACE VIEW view_name AS SELECT *, Tt_MakePoint(longitude,latitude) as geom FROM table1 JOIN table2 on "column"."table1"="column"."table2" WHERE SPATIAL CONDITION''')
       cur.execute('''SELECT * FROM extrait;''')
       cur.fetchall()

I first tried to use this answer to add the view as layer. I keep stuck at which parameters I could pass to setConnection : remote or local host/port (if only this class is able to connect to postgresql through ssh)? For now, I tried with localhost because it worked for psycopg2 connection (inspired from this answer).

What I understand is that the setConnection "doesn't know" that I'm using a tunnel, even if I'm calling in from within the with block, but I may be wrong.

         uri = QgsDataSourceUri()
         uri.setConnection('localhost', str(server.local_bind_port),"dbname","postgres","postgres")
         uri.setDataSource ("public", "view_name", 'geom')
         vlayer=QgsVectorLayer (uri.uri(False), "etablissements_complets", 'postgres')
         QgsProject.instance().addMapLayer(vlayer) 

I should precise that I set up my postgresql.conf with listen_adresses='*' and my pg_hba.conf as mentionned in various topics.

0

I eventually ended using first option, parsing the cur.fetchall() method.

Here's the way I did it. I'm sure it's not the most elegant way, but it satisfied my needs.

def addAtts(layer, attName, varType):
    layer.dataProvider().addAttributes([QgsField(attName, varType)])
    layer.updateFields()

def conversion(pg_type):
    if pg_type== 25:
       qvariant_type=QVariant.String
    elif pg_type==23:
       qvariant_type=QVariant.Int
    elif pg_type==700:
       qvariant_type=QVariant.Double
    else:
        qvariant_type=QVariant.Invalid
    return(qvariant_type)

with conn.cursor() as cur:
    cur.execute('''DROP VIEW extract;''')
    cur.execute('''CREATE OR REPLACE VIEW extract AS SELECT * FROM TABLE, St_AsText(St_MakePoint(longitude, latitude)) as geom from table 1 join table 2 on "column".table1="column".table2 WHERE SPATIAL CONDITION''')
    cur.execute('''COMMIT;''')
    cur.execute('''SELECT * FROM extrait;''')
    conn.commit()
    headers=cur.description
    table=cur.fetchall()
    cur.close()
    vlayer=QgsVectorLayer('Point','Name','memory')
    vlayer.startEditing()
    for i in headers[:-1]:
        addAtts(vlayer,i[0],conversion(i[1]))           
    vlayer.commitChanges()
    total_features=[]
    for f in table:
        feature=QgsFeature(vlayer.fields())
        feature.setAttributes(list(f)[:-1])
        feature.setGeometry(QgsGeometry.fromWkt(f[-1]))
        ensemble_feature.append(feature)
    vlayer.dataProvider().addFeatures(ensemble_feature)

| improve this answer | |

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.