Problem with spatial index detection on oracle spatial views

[qgib]

Author Name: Jan Lippmann (@j-lippmann)
Original Redmine Issue: 12230
Affected QGIS version: 2.6.0
Redmine category:data_provider
Assignee: Jürgen Fischer

---

hello,
qgis don't detect the spatial index for spatial query in Oracle. I do not know if there is a fault in qgis or a problem in my environment. the sql script and the debug log view I have attached. can anyone confirm the problem or give you a hint solution? what's the complete sql statement which raise the ora error:ORA-29902?

hints:

• the srid of all features in OBJEKTLAYER match the srid values in MDSYS.USER_SDO_GEOM_METADATA
• spatial index is valid and the origintable works without index problems...

thanks
jan

oracleserver:
Oracle Database 11g Release 11.2.0.4.0

qgis:
QGIS-Version 2.7.0-Master QGIS-Codeversion aab3bfd
Kompiliert gegen Qt 4.8.5 Laufendes Qt 4.8.5
Kompiliert mit GDAL/OGR 1.11.1 Läuft mit GDAL/OGR 1.11.2
Kompiliert mit GEOS 3.4.2-CAPI-1.8.2 Läuft mit GEOS 3.4.2-CAPI-1.8.2 r3921
PostgreSQL-Client-Version 9.2.4 SpatiaLite-Version 4.1.1
QWT-Version 5.2.3 PROJ.4-Version 480
QScintilla2-Version 2.7.2 Diese QGIS-Kopie schreibt Debugausgaben.

--run the script with GIS_ADM user
--use GIS_ADM user within QGIS

--Create table
CREATE TABLE "GIS_ADM"."OBJEKTLAYER"
  (
    "GEOM" "MDSYS"."SDO_GEOMETRY" ,
    "GRUENG6F_O" VARCHAR2(2047 BYTE),
    "ID"         NUMBER(10,0)
  )
  SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )
  TABLESPACE "USERS" ;
CREATE UNIQUE INDEX "GIS_ADM"."SYS_IL0000095052C00008$$" ON "GIS_ADM"."OBJEKTLAYER"
  (
    PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" PARALLEL (DEGREE 0 INSTANCES 0) ;
CREATE UNIQUE INDEX "GIS_ADM"."SYS_IL0000095052C00007$$" ON "GIS_ADM"."OBJEKTLAYER" ( PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" PARALLEL (DEGREE 0 INSTANCES 0) ;
  
--Insert USER_SDO_GEOM_METADATA for OBJEKTLAYER
INSERT INTO MDSYS.USER_SDO_GEOM_METADATA (
  TABLE_NAME 
, COLUMN_NAME 
, DIMINFO 
, SRID 
) VALUES ( 
  'OBJEKTLAYER' 
, 'GEOM' 
,  MDSYS.SDO_DIM_ARRAY( 
                         MDSYS.SDO_DIM_ELEMENT('X',-2147483648,2147483647,0.00005) 
                        ,MDSYS.SDO_DIM_ELEMENT('Y',-2147483648,2147483647,0.00005) 
                      ) 
, 25832
) 
;
  
--create spatial index  on OBJEKTLAYER
  CREATE INDEX "GIS_ADM"."IDX_GEOM" ON "GIS_ADM"."OBJEKTLAYER"
    (
      "GEOM"
    )
    INDEXTYPE IS "MDSYS"."SPATIAL_INDEX" ;
	
	
--create simple 1:1 view from the origintable
CREATE OR REPLACE FORCE VIEW "GIS_ADM"."V_OBJEKTLAYER" ("GEOM", "GRUENG6F_O", "ID")
AS
  SELECT "GEOM","GRUENG6F_O","ID" FROM OBJEKTLAYER;
  
  
--Insert USER_SDO_GEOM_METADATA for V_OBJEKTLAYER
INSERT INTO MDSYS.USER_SDO_GEOM_METADATA (
  TABLE_NAME 
, COLUMN_NAME 
, DIMINFO 
, SRID 
) VALUES ( 
  'V_OBJEKTLAYER' 
, 'GEOM' 
,  MDSYS.SDO_DIM_ARRAY( 
                         MDSYS.SDO_DIM_ELEMENT('X',-2147483648,2147483647,0.00005) 
                        ,MDSYS.SDO_DIM_ELEMENT('Y',-2147483648,2147483647,0.00005) 
                      ) 
, 25832
) 
;

---

• debug-view-log.txt (Jan Lippmann)

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

Fixed in changeset "582d009f434260bb0efd9c0914c7f854f856a647".

---

• status_id was changed from Open to Closed

[qgib]

Author Name: Jan Lippmann (@j-lippmann)

---

hello jürgen,

thank you for the quick fixing. it works. :-)