1

I have a view v and table st with attributes and SDO_GEOMETRY columns in an oracle database.

I want to do an LEFT OUTER JOIN a common value with data filtering on the v.

SELECT ST.OBJECTID, V.ID_WS, V.NATURE, ST.SHAPE
FROM SCHEMA.VIEW V 
LEFT OUTER JOIN SCHEMA.SPATIALTABLE ST   
ON V.ID_WS = ST.ID_WS 
WHERE V.NATURE IN ('ANIMAL','MACHINE')

I want to export this query (that works with cx_oracle) to QGIS or a shapefile

I tested the post with cx_Oracle, it works:

import cx_Oracle
#connect DB oracle to query tables names
con = cx_Oracle.connect('user/password@hosting.org:1521/SERVICENAME')
cursor = con.cursor()
cursor.execute(ABOVE_QUERY)
# Get features
features = cursor.fetchall() 
#close properly
cursor.close()
con.close()

In the features list, the values are structured as tuples :

[(156, 163354,'ANIMAL',<cx_Oracle.OBJECT object at 0x000000002E91DE68>),(25, 8964,'MACHINE',<cx_Oracle.OBJECT object at 0x000000002E91DF48>)]

How to convert this list to Qgis compatible or shapefile ?

OR

I tested with pyqgis and that works but not LEFT OUTER JOIN :

uri = QgsDataSourceURI()
uri.setConnection("hosting.org", "1521", "SERVICENAME", "user", "password")
uri.setDataSource("SCHEMA", "SPATIALTABLE" , "SHAPE", 'OBJECTID=156',"OBJECTID")
uri.uri()
vlayer = QgsVectorLayer(uri.uri(), "requete", 'oracle')
vlayer.setCrs(QgsCoordinateReferenceSystem(31370))
QgsMapLayerRegistry.instance().addMapLayer(vlayer)

How to add the LEFT OUTER JOIN in this request ?

I have only SELECT pivilege on this Oracle DB.

EDIT : That could be explained by this feature (Qgis bug tracker) not yet implemented. The join is occured on the layer not at the driver level.

  • I had a smilar problem with postgres, where i wasnt able to display my query directly. My solution was to create a table out of the query and then load this newly created table into QGIS – LaughU May 31 '17 at 6:03
  • OK, nice feedback. I thinked about that but i want to avoid to load the 200.000 polygons. This is time consuming. – jlSta May 31 '17 at 6:52
  • @jiSta I tested my approach with something around 1. mio. polygones and the query finished in no time althrough I dont know how fast oracel is. The next step would be to save this table as sahpefile via the DB- Manager – LaughU May 31 '17 at 7:04
  • Ok. mybe it's the database because launching the query builder is very slow and the query much more. I have fast computer and connection. With pyqgis and cx_oracle much less for the same query without left outer join of course. – jlSta May 31 '17 at 9:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.