Feature request #16375
Updated by Giovanni Manghi almost 7 years ago
Background.
I executed an "identify" in QGIS against a single mapped object that comes from an Oracle table with sdo_geometry column.
Using database SQL Trace, I dumped the SQL that QGIS executes and I got the following (which I have compared to the C++ source code):
SELECT "GEOM","ROWID","FID","FID_INT","FID_NUM9","FID_NUM8","FID_NUM7",
"AN_ATTRIBUTE"
FROM
"QGIS"."FOO" "FEATUREREQUEST"
WHERE sdo_filter("GEOM",
mdsys.sdo_geometry(2003,2872,NULL,mdsys.sdo_elem_info_array(1,1003,3),
mdsys.sdo_ordinate_array(5992381.68001308757811785,
2112468.83721287222579122,5992389.06444318685680628,
2112476.22164297243580222)))='TRUE'
AND sdo_relate("GEOM",
mdsys.sdo_geometry(2003,2872,NULL,mdsys.sdo_elem_info_array(1,1003,3),
mdsys.sdo_ordinate_array(5992381.68001308757811785,
2112468.83721287222579122,5992389.06444318685680628,
2112476.22164297243580222)),'mask=ANYINTERACT')='TRUE'
Looking at qgsoracleconn.cpp there's an hasSpatial() method which runs:
"SELECT 1 FROM v$option WHERE parameter='Spatial' AND value='TRUE'"
This is then used at line 96 of qgsoraclefeatureiterator.cpp where it says:
// sdo_relate requires Spatial
If the connection has Spatial, it appends this to the query where clause:
AND sdo_relate(%1,%2,'mask=ANYINTERACT')='TRUE'
Now, SDO_RELATE has always been a Location spatial filter, and so is free for all Oracle users.
Only SDO_GEOM.RELATE requires Spatial in 11g but not 12c.
Additionally, SDO_RELATE is a secondary filter that executes a Primary filter (SDO_FILTER) internally to generate a candidate set of possible objects that are sent to the internal Secondary filter which does the topological analysis.
Even if SDO_RELATE was a licensed filter, it does not need to be used in conjunction with SDO_FILTER.
Additionally, the Oracle Query Optimizer is still not smart enough to realise that the SDO_FILTER is not needed so the use of it with SDO_RELATE doubles the primary filtering.
So the above query should always be running SDO_RELATE… ANYINTERACT and leaving out the SDO_FILTER part.
But, also, the "mask=ANYINTERACT" should be a bind variable as it could be set to "mask=CONTAINS" etc.
In summary (*the sentences in bold are the requested functional changes*):
1. SDO_RELATE is a free function and not subject to Spatial licensing
- *the hasSpatial() test in the C++ is invalid and should be removed*;
2. SDO_GEOM.RELATE is a spatial function up till 12c and free afterwards
- AFAIK QGIS currently doesn't use SDO_GEOM.RELATE;
3. Because SDO_RELATE executes its own primary filtering, SDO_FILTER is not needed
- *Modify the C++ to remove SDO_FILTER when SDO_RELATE is needed*.
4. *SDO_FILTER mask parameter should be a bind variable.*
regards
Simon
I executed an "identify" in QGIS against a single mapped object that comes from an Oracle table with sdo_geometry column.
Using database SQL Trace, I dumped the SQL that QGIS executes and I got the following (which I have compared to the C++ source code):
SELECT "GEOM","ROWID","FID","FID_INT","FID_NUM9","FID_NUM8","FID_NUM7",
"AN_ATTRIBUTE"
FROM
"QGIS"."FOO" "FEATUREREQUEST"
WHERE sdo_filter("GEOM",
mdsys.sdo_geometry(2003,2872,NULL,mdsys.sdo_elem_info_array(1,1003,3),
mdsys.sdo_ordinate_array(5992381.68001308757811785,
2112468.83721287222579122,5992389.06444318685680628,
2112476.22164297243580222)))='TRUE'
AND sdo_relate("GEOM",
mdsys.sdo_geometry(2003,2872,NULL,mdsys.sdo_elem_info_array(1,1003,3),
mdsys.sdo_ordinate_array(5992381.68001308757811785,
2112468.83721287222579122,5992389.06444318685680628,
2112476.22164297243580222)),'mask=ANYINTERACT')='TRUE'
Looking at qgsoracleconn.cpp there's an hasSpatial() method which runs:
"SELECT 1 FROM v$option WHERE parameter='Spatial' AND value='TRUE'"
This is then used at line 96 of qgsoraclefeatureiterator.cpp where it says:
// sdo_relate requires Spatial
If the connection has Spatial, it appends this to the query where clause:
AND sdo_relate(%1,%2,'mask=ANYINTERACT')='TRUE'
Now, SDO_RELATE has always been a Location spatial filter, and so is free for all Oracle users.
Only SDO_GEOM.RELATE requires Spatial in 11g but not 12c.
Additionally, SDO_RELATE is a secondary filter that executes a Primary filter (SDO_FILTER) internally to generate a candidate set of possible objects that are sent to the internal Secondary filter which does the topological analysis.
Even if SDO_RELATE was a licensed filter, it does not need to be used in conjunction with SDO_FILTER.
Additionally, the Oracle Query Optimizer is still not smart enough to realise that the SDO_FILTER is not needed so the use of it with SDO_RELATE doubles the primary filtering.
So the above query should always be running SDO_RELATE… ANYINTERACT and leaving out the SDO_FILTER part.
But, also, the "mask=ANYINTERACT" should be a bind variable as it could be set to "mask=CONTAINS" etc.
In summary (*the sentences in bold are the requested functional changes*):
1. SDO_RELATE is a free function and not subject to Spatial licensing
- *the hasSpatial() test in the C++ is invalid and should be removed*;
2. SDO_GEOM.RELATE is a spatial function up till 12c and free afterwards
- AFAIK QGIS currently doesn't use SDO_GEOM.RELATE;
3. Because SDO_RELATE executes its own primary filtering, SDO_FILTER is not needed
- *Modify the C++ to remove SDO_FILTER when SDO_RELATE is needed*.
4. *SDO_FILTER mask parameter should be a bind variable.*
regards
Simon