QGIS » QGIS Application

When connecting to an Oracle table with multiple geometry columns, these columns are mixed up for rendering.

Example
In a legend two entries are created, L1 and L2. L1 and L2 use the same Oracle table with two geometry columns. L1 is created referencing one column, G1, and G2 is used for L2. When items from legend entry L1 are rendered in the map geometries from G2 are used, and for legend entry L2 it's geometries from G1. When clicking in the map or zooming to selected items the correct geometries are used. It seems only for rendering the columns are mixed up. When the geometry types for the two columns don't match rendering fails and nothing is displayed.

Steps
Oracle 11.2 server + client, create table:

create table tb_test 
( id number (20,0)
, geometrie1 sdo_geometry
, geometrie2 sdo_geometry
, constraint tb_test_pk primary key (id)
);

insert into user_sdo_geom_metadata (table_name,column_name,diminfo,srid)
values ('TB_TEST','GEOMETRIE1',mdsys.sdo_dim_array(mdsys.sdo_dim_element('X',60000,90000,0.005),mdsys.sdo_dim_element('Y',430000,450000,0.005)),28992);

insert into user_sdo_geom_metadata (table_name,column_name,diminfo,srid)
values ('TB_TEST','GEOMETRIE2',mdsys.sdo_dim_array(mdsys.sdo_dim_element('X',60000,90000,0.005),mdsys.sdo_dim_element('Y',430000,450000,0.005)),28992);

create index tb_test1_sidx on tb_test (geometrie1) indextype is mdsys.spatial_index;
create index tb_test2_sidx on tb_test (geometrie2) indextype is mdsys.spatial_index;

insert into tb_test (id, geometrie1, geometrie2)
values (1
, sdo_geometry(2003, 28992, null, sdo_elem_info_array(1,1003,1)
, sdo_ordinate_array(87000,437000,87007,437002,87002,437007,87000,437000))
, sdo_geometry(2003, 28992, null, sdo_elem_info_array(1,1003,1)
, sdo_ordinate_array(87000,437010,87007,437012,87002,437017,87000,437010))
);

insert into tb_test (id, geometrie1, geometrie2)
values (2
, sdo_geometry(2003, 28992, null, sdo_elem_info_array(1,1003,1)
, sdo_ordinate_array(87010,437000,87014,437003,87011,437007,87010,437000))
, sdo_geometry(2003, 28992, null, sdo_elem_info_array(1,1003,1)
, sdo_ordinate_array(87010,437010,87014,437013,87011,437017,87010,437010))
);

commit;

Notice how all objects in GEOMETRIE2 are North of those in GEOMETRIE1.

QGIS 2.8.2, also tested in 2.4:

• Menu Layer > Add Layer > Add Oracle Spatial Layer...
• Connect to Oracle database using existing or new connection
• Select the two lines for Table TB_TEST, Geometry column GEOMETRIE1 and GEOMETRIE2 and click the [Add] button.

The legend now shows two entries TB_TEST with different colours. Hovering over them shows one as using GEOMETRIE1 and the other GEOMETRIE2. Renaming them makes things more clear, say we use TB_TEST.GEOMETRIE1 and TB_TEST.GEOMETRIE2.

• Menu View > Identify Features
• Click in the map view on the location of one of the top (North) shapes

Details for a record from legend entry TB_TEST.GEOMETRIE2 are shown. And the corresponding bottom geometry (from column GEOMETRIE1) is highlighted - this is not where we clicked.

• Turn off layer TB_TEST.GEOMETRIE1

The geometries from column GEOMETRIE2 disappear from the map view.

• Menu View > Identify Features
• Click in the map view on one of the displayed shapes.

The status bar shows 'No features at this position found'.