Feature request #16252

SDO_DIMNAME values QGIS Oracle Data Provider

Added by Simon Greener over 7 years ago. Updated about 6 years ago.

Status:Open
Priority:Normal
Assignee:-
Category:Data Provider/Oracle
Pull Request or Patch supplied:No Resolution:
Easy fix?:No Copied to github as #:24162

Description

When QGIS (2.18.3) queries Oracle to discover metadata properties, it executes the following to discover the spatial extent in X and Y dimensions:

SELECT sdo_lb,sdo_ub FROM mdsys.all_sdo_geom_metadata m, table(m.diminfo) WHERE owner='QGIS' AND table_name='FOO' AND column_name='GEOM' AND sdo_dimname='X'
SELECT sdo_lb,sdo_ub FROM mdsys.all_sdo_geom_metadata m, table(m.diminfo) WHERE owner='QGIS' AND table_name='FOO' AND column_name='GEOM' AND sdo_dimname='Y'

Now, it is possible that a user may create lower case sdo_dimnames.
That is, 'x' instead of 'X'.

Request 1: upper case the query sdo_dimname values to ensure match:

SELECT sdo_lb,sdo_ub FROM mdsys.all_sdo_geom_metadata m, table(m.diminfo) WHERE owner='QGIS' AND table_name='FOO' AND column_name='GEOM' AND UPPER(sdo_dimname)='X'
SELECT sdo_lb,sdo_ub FROM mdsys.all_sdo_geom_metadata m, table(m.diminfo) WHERE owner='QGIS' AND table_name='FOO' AND column_name='GEOM' AND UPPER(sdo_dimname)='Y'

Additionally, geodetic data does not normally use the X,Y dimnames.
Rather, they use LONG (for X) and LAT (for Y).

SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.05),
SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.05)

Request 2: that the metadata sdo_dimname query be modified to allow for geodetic names.
For Example:

SELECT sdo_lb,sdo_ub FROM mdsys.all_sdo_geom_metadata m, table(m.diminfo) WHERE owner='QGIS' AND table_name='FOO' AND column_name='GEOM' AND UPPER(sdo_dimname) IN ('X','LONG')
SELECT sdo_lb,sdo_ub FROM mdsys.all_sdo_geom_metadata m, table(m.diminfo) WHERE owner='QGIS' AND table_name='FOO' AND column_name='GEOM' AND UPPER(sdo_dimname) IN ('Y','LAT')

I have seen use of LATITUDE and LONGITUDE dim names but the Oracle documentation mainly refers to the LONG/LAT names.

regards
Simon

History

#1 Updated by Giovanni Manghi over 7 years ago

  • Easy fix? set to No

#2 Updated by Jürgen Fischer about 7 years ago

  • Assignee deleted (Jürgen Fischer)

#3 Updated by Lukas Künzel about 6 years ago

We recently ran into the same problem, we even tend to use complete custom names for the dimensions, because Oracle itself does not need any specific names.

Is there any specific reason why the spatial extent of a layer has to be determined each time? Would it be possible in the future to use the extent that is already stored in the project file? I see some similarities to #19793
When you've really large spatial data sets in your database then some of the current methods (like SDO_AGGR_MBR) to determine spatial extent will fail.

Please share your thoughts!

#4 Updated by Lukas Künzel about 6 years ago

@Jürgen Fischer

Are there any plans to fix this issue?

Also available in: Atom PDF