1

From QGIS I am running analysis on linestring geometry in PostGIS, and the result is updated geometry. I want to update the linestring only in editing mode of QGIS layer, so the user can decide whether to save or discard the change.

With WKT it works like this:

import psycopg2
pg_connection_string = "host=''..."
selected_layer = iface.activeLayer()
selected_features = selected_layer.selectedFeatures()
postgis_table_name = get_table_name_from_layer(selected_layer) # custom function
if postgis_table_name and selected_layer.isEditable() and len(selected_features) > 0:
    dbc = psycopg2.connect(pg_connection_string)
    with dbc:
        with dbc.cursor as db_cursor:
            for f in selected_features:
               gid = f.id()
               db_cursor.execute('with geom_update as (select ... from {0} where gid={1})'
                                 'select st_astext(geom) from geom_update'.format(postgis_table_name, gid))
               geom_wkt = db_cursor.fetchone()[0]
               f.setGeometry(QgsGeometry.fromWkt(geom_wkt))
               selected_layer.updateFeature(f)
            selected_layer.triggerRepaint()

The "absolute no way" problem with WKT is it changes the geometry (stripping decimal places), also it is not greatly efficient - not a concern in this particular case but...

Hence WKB format. I thought it is simple as:

               db_cursor.execute('with geom_update as (select ... from {0} where gid={1})'
                                 'select st_asbinary(geom) from geom_update'.format(postgis_table_name, gid))
               geom_wkb = db_cursor.fetchone()[0]
               f.setGeometry(QgsGeometry.fromWkb(geom_wkb))

But no, it is not. It does not work. I tried many variations with encode, decode, python conversion libraries, using sql without st_asbinary() as pure geom but nothing works.

Do you have any idea how to sort this out?

  • I might be missing sth. here, but what's the exact issue with WKT? Also, stripping decimal places doesn't sound so scary with 15 of them, and where 5 of them represent microscopic to molecular distances. – ThingumaBob Feb 5 at 23:38
  • @ThingumaBob as explained already it is absolutely serious issue. Because if you run equality check it all breaks apart. If you change geometry by 0.00001 your eye might not see it, but the topology becomes corrupted etc. WKT is terrible format when you need to keep geometry as is. – Miro Feb 5 at 23:59
  • 1
    It's not that easy, and not an issue of WKT per se, but I do agree it's most obvious here; arithmetically derived geometries in 64-bit floating point space have typical rounding issues, and technically, even WKB can be 'incorrect' between systems. It's generally a good idea to force any derived geometry into the intrinsic precision of the base data, as well as storing them with their mantissa stripped of the least significant bits. Or simply use ST_AsText(geom, 7) to get consistently rounded ordinates with precision up to 1 cm (7 decimal digits). – ThingumaBob Feb 6 at 9:38
  • @ThingumaBob agreed, it is complicated, and yes I did have problems with float between systems you mention in other cases - and had to take extra steps. This case is all about postgis and qgis and with WKB functions like equality, and all other steps I need for my analysis like intersections, closestpoint etc. work great. Btw my geometries are in metric system so 7 decimal digits take me about ridiculously further than I personally would like to :) – Miro Feb 6 at 13:43
2

Finally figured this out. psycopg2 cursor fetches memory view into geom_wkb which needs to be converted to bytes, then you need to have some existing QgsGeometry which you update by received bytes and finally set feature's geometry to updated geometry:

           db_cursor.execute('with geom_update as (select ... from {0} where gid={1})'
                             'select st_asbinary(geom) from geom_update'.format(postgis_table_name, gid))
           geom_wkb = db_cursor.fetchone()[0] # return memory view
           geom_wkb_bytes = geom_wkb.tobytes() # converts view to bytes
           geom_update = f.geometry() # gets feature's existing geometry
           geom_update.fromWkb(geom_wkb_bytes) # overwrites geometry from wkb
           f.setGeometry(geom_update)

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.