Bug report #12230

Problem with spatial index detection on oracle spatial views

Added by Jan Lippmann over 4 years ago. Updated over 4 years ago.

Status:Closed
Priority:High
Assignee:Jürgen Fischer
Category:Data Provider
Affected QGIS version:2.6.0 Regression?:No
Operating System:WIN Easy fix?:No
Pull Request or Patch supplied:No Resolution:
Crashes QGIS or corrupts data:No Copied to github as #:20418

Description

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 Magnifier (5.71 KB) Jan Lippmann, 2015-02-20 01:27 AM

Associated revisions

Revision 582d009f
Added by Jürgen Fischer over 4 years ago

oracle provider: consider srid when looking trying spatial index (followup 991f94d; fixes #12230)

Revision d001c9d9
Added by Jürgen Fischer over 4 years ago

oracle provider: consider srid when looking trying spatial index (followup 991f94d; fixes #12230)

History

#1 Updated by Jürgen Fischer over 4 years ago

  • Status changed from Open to Closed

#2 Updated by Jan Lippmann over 4 years ago

hello jürgen,

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

Also available in: Atom PDF