Feature request #16252

SDO_DIMNAME values QGIS Oracle Data Provider

Added by Simon Greener 29 days ago.

Status:Open Start Date:02/28/2017
Priority:Normal Due date:
Assigned to:J├╝rgen Fischer % Done:

0%

Category:Oracle Data Provider
Target version:Future Release - Lower Priority
Platform: Resolution:
Platform version:2.18.3 Pull Request or Patch supplied:No
Status info: Tag:

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

Also available in: Atom