SDO_DIMNAME values QGIS Oracle Data Provider

[qgib]

Author Name: Simon Greener (Simon Greener)
Original Redmine Issue: 16252

Redmine category:data_provider/oracle

---

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

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

• easy_fix was configured as 0

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

• assigned_to_id removed Jürgen Fischer

[qgib]

Author Name: Lukas Künzel (Lukas Künzel)

---

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 [[#27618]]
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!

[qgib]

Author Name: Lukas Künzel (Lukas Künzel)

---

@jürgen Fischer

Are there any plans to fix this issue?