1

There is a PostgreSQL database containing some layers that have few types of geometry (multipolygons and linestrings). I need to distinguish these geometries in two separate layers.

My basic strings that make layer for QGIS are:

uri.setConnection('srs', '5432', 'db_name', 'postgres' , 'password') 
for i, name in enumerate(sorted(list_of_layers)): # list containing elements like ['schema_name', 'layer_name']
    if name[1] in ['layer_with_geometry_collection']:
        uri.setDataSource(name[0], name[1], 'SP_GEOMETRY')
        vlayer=QgsVectorLayer (uri.uri(), name[1], "postgres")

Where do I need to set the geometry type? If I check vlayer.wkbType(), it throws me 0. Manual set uri.setWkbType() hadn't helped yet.

  • It is very dangerous to casually connect to an RDBMS as the administrative user. Doing so can often result in casual destruction of the database. Instead, create a login user to own your data, and another to manage read-only or read-mostly connections. The postgres user should never own spatial data. – Vince Apr 10 at 12:22
  • @Vince you mean not to use postgres as a username? – Pavel Pereverzev Apr 10 at 12:24
  • Yes. Only connect as postgres to create new login and group roles. – Vince Apr 10 at 12:49
2

Found an answer in PosGIS reference guide. According to types of geometry that can be stored in PostGIS database, a filter should be written to get the specific geometry.

For example, I need to get only polygon features. The full code will be:

filter = """ST_GeometryType("{}")  LIKE '{}'""".format('SP_GEOMETRY', 'ST_Polygon')
uri.setConnection('srs', '5432', 'db_name', 'postgres' , 'password') 
for i, name in enumerate(sorted(list_of_layers)): # list containing elements like ['schema_name', 'layer_name']
    if name[1] in ['layer_with_geometry_collection']:
        uri.setDataSource(name[0], name[1], 'SP_GEOMETRY', filter) # filter added
        vlayer=QgsVectorLayer (uri.uri(), name[1], "postgres")
  • You'd have to build a covering index to make this query performant, and doing do would conflict with use of a spatial index in the application. – Vince Apr 10 at 12:51

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.