Join on attributes and write vector or create feature from Oracle SDO with pyqgis

Question

QGIS 2.14 & 2.18

Step 1 : I do an extraction with QgsDataSourceURI()

#get geometries
uri = QgsDataSourceURI()
uri.setConnection("svdbocsva003.test.blavla.lan", "1521", "tata", "toto", "pwd")
uri.setSrid ('31370');
uri.setWkbType(QGis.WKBMutiPolygon)
uri.setDataSource("OSIRIS", "OSI_SITES" , "SHAPE",b1query ,"OBJECTID")
uri.uri()
sdoLayer = QgsVectorLayer(uri.uri(), "requete", 'oracle')
sdoLayer.setCrs(QgsCoordinateReferenceSystem(31370))
QgsMapLayerRegistry.instance().addMapLayer(sdoLayer)
sdoFeatures = sdoLayer.getFeatures()

All works fine until this step. The layer is added to the project correctly. Features are displayed.

Step 2 : I try to write this layer to hard disk

error = QgsVectorFileWriter.writeAsVectorFormat(sdoLayer, "C:\\user\\test\\test0.shp", "test",None, "ESRI Shapefile")
if error == QgsVectorFileWriter.NoError:
    print "success!"
else : 
    print "error"
del error

I get an error message. The file is written but empty as seen as an error has occurred.

Step 3 : I try to create new vector layer with joined data from oracle attributes table and the geom layer with a common field

writer = QgsVectorFileWriter("C:\\user\\test\\test1.shp","test", fields, QGis.WKBMultiPolygon, QgsCoordinateReferenceSystem(31370), "ESRI Shapefile")

# add a feature
for attr in attributes :
    print(attr[0])
    exp = QgsExpression('ID_WS = {}' .format(attr[0]))
    request = QgsFeatureRequest(exp)
    request.setLimit(1)
    feat = QgsFeature()
    feat.setGeometry(sdoLayer.getFeatures(request).next().geometry())
    #feat.setAttributes([1, "text"])
    writer.addFeature(feat)
# delete the writer to flush features to disk
del writer

QGIS is crashing randomly when i iterate on attributes. OGR Logs gives me Source de données invalide () (invalid data source) in the OGR logs. It looks like the problem. Sometimes it does not crash so i can note the file is empty.

---

EDIT1 : With a workaround by activating the debugger as explained in this post, QGIS is crashing less frequently.When it's not crashing, i get a feature without geometry with a field FID. By the way, i had defined an output layer processing variable. It was generating error in the log. I deleted it as it was not used. So now the only error message i get is :

Traitement(1): Cannot find variable: svdbocsva003 
Traitement(1): Cannot find variable: svdbocsva003

Where svdbocsva003 is the beginning of the host address. I don't understandwhat what is wrong.

EDIT2 : STEP2 solved

• The crash occurs when i want to write a file for a second time. So the overwrite is the cause. No need to be loaded in a project for crashing
• The right usage of QgsVectorFileWriter.writeAsVectorFormat is with the overrideGeometryType as QgsWKBTypes.MultiPolygon in my case and forceMulti as True. With that it's generating well the vector layer

Step 3 solved in the selected answer, my answer

Answer 1

doesn't crash here when I use

feat.setGeometry(attr.geometry())

instead of

feat.setGeometry(sdoLayer.getFeatures(request).next().geometry())

I assume "fields" are just the columns/fields of "sdoLayer"?

Answer 2

Thanks for all your answers maybe they helped me indirectly but the code that worked for me to join a sdo geometry layer to an oracle table/view. I don't know why but the joinGeombyID_WS writed as below works without crashing.

def joinGeombyID_WS(attributes, sdogeometry_layer) : #b1/ID_WS is the common field in sdolayer and attributes table
    joinedFeature = QgsFeature()
    exp = QgsExpression("ID_WS = '{}'".format(attributes[0]))
    request = QgsFeatureRequest(exp)
    request.setLimit(1)
    spatialFeature = sdogeometry_layer.getFeatures(request).next()
    geom = spatialFeature.geometry()
    joinedFeature.setGeometry(geom)
    joinedFeature.setAttributes(list(attributes))

    return joinedFeature;

def getGeoms(query) :
    uri = QgsDataSourceURI()
    uri.setConnection("s**********.lan", "1521", "*****", "user", "pwd")
    uri.setSrid ('31370');
    uri.setWkbType(QGis.WKBMultiPolygon)
    uri.setDataSource("Ozzy", "ozzy_SITES" , "SHAPE", query ,"OBJECTID") #add b1query
    uri.uri()
    sdoLayer = QgsVectorLayer(uri.uri(), "requete", 'oracle')

    return sdoLayer;

def writeGeomquery(attributesList) : #b1/ID_WS is the common field in sdolayer and attributes table
    b1List =[]
    for attr in attributesList :
        b1List.append(attr[B1_column_position-1])
    sB1List = [str(b1) for b1 in b1List]
    b1Separated = ','.join(sB1List)
    b1query = "ID_WS IN (" + b1Separated + ")"

for i in range(numberOfqueriestodo):  #oracle is limited to 1000 arguments in a query
    rage = i*1000
    attributesList1000 = convertedDatesattributesList[rage:rage+1000]
    geometriesLayer = getGeoms(writeGeomquery(attributesList1000))
    for attr in attributesList1000 :
        writer.addFeature(joinGeombyID_WS(attr, geometriesLayer))

Answer 3

I didnt' test it, but you can try to use this code:

writer = QgsVectorFileWriter("C:\\user\\test\\test1.shp","test", fields, QGis.WKBMultiPolygon, QgsCoordinateReferenceSystem(31370), "ESRI Shapefile")

# add a feature
for attr in attributes : # if you only use 'attrs[0]', this loop is not needed
    print(attr[0])
    exp = '"ID_WS" = %s' %(attr[0])
    request = sdoLayer.getFeatures(QgsFeatureRequest().setFilterExpression(exp))
    feats = [k for k in request]
    for feature in feats:
        feat = QgsFeature()
        feat.setGeometry(feature.geometry())
        #feat.setAttributes([1, "text"])
        writer.addFeature(feat)
# delete the writer to flush features to disk
del writer

Answer 4

The easiest solution is to export to CSV with geometries as WKT

SDO_UTIL.TO_WKTGEOMETRY(geom)

In QGis, import CSV and welect the geometry column as WKT