Feature request #16252

SDO_DIMNAME values QGIS Oracle Data Provider

Added by Simon Greener 3 months ago. Updated 23 days ago.

Status:Open
Priority:Normal
Assignee:Jürgen Fischer
Category:Data Provider/Oracle
Pull Request or Patch supplied:No Resolution:
Easy fix?:No

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 23 days ago

  • Easy fix? set to No

Also available in: Atom PDF