2

Using pyqgis in a QGIS 3 Plugin, I can get features from a layer and geometry from features. Is there a way to pass the geometry into psycopg2 so that it will work in PostGIS?

I did find the PPyGIS http://www.fabianowski.eu/projects/ppygis/ but could not get it to work with Python 3.7

# Create table
    cursor.execute("CREATE TABLE structures (name VARCHAR(32) PRIMARY KEY, start VARCHAR(16), geom GEOMETRY);")


    self.get_layers()
    pipe_lyr = QgsProject.instance().mapLayersByName(self.pipe_layer)[0]
    if not pipe_lyr.isValid():
        self.popMsg('Layer is not valid')        
        return
    pipes = pipe_lyr.getFeatures()
    for pipe in pipes:
        geom = pipe.geometry()     
        # Insert some data into table
        cursor.execute("INSERT INTO structures (name, start, geom) VALUES (%s, %s, %s);", (str(pipe['name']), str(pipe['start']), geom))
        print( pipe['name'])

It errors out with

TypeError: not all arguments converted during string formatting Traceback (most recent call last): File "C:/Users/Cary/AppData/Roaming/QGIS/QGIS3\profiles\default/python/plugins\invert_builder\invert_builder.py", line 288, in azure_db cursor.execute("INSERT INTO structures (name, start, geom) VALUES (%s, %s);", (str(pipe['name']), str(pipe['start']), geom)) TypeError: not all arguments converted during string formatting<

This is a plugin for QGIS 3.

1 Answer 1

4

You could use WKT and the PostGIS "ST_GeomFromText" function as a go-between:

cursor.execute("INSERT INTO structures (name, start, geom) VALUES (%s, %s, ST_GeomFromText(%s));", (str(pipe['name']), str(pipe['start']), geom.asWkt()))

QgsGeometry.asWkt() converts the geometry to a string representing that geometry, and PostGIS' ST_GeomFromText converts the string back to an actual geometry object.

Alternatively, you may want to conside avoiding the need to use psycopg2 here and reading in the destination layer as a normal QgsVectorLayer, and then you can directly add new features using the PyQGIS API.

Your Answer

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

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