[Oracle] SCR Unknown : Lambert 93 (2154)

[troopa81]

Describe the bug

When QGIS loads data stored in a Oracle database with the French projection Lambert 93 (EPSG:2154), the SCR is marked as unknow and the following message is printed

../src/core/qgscoordinatetransform_p.cpp:305 : (proj_logger) [301ms] proj_as_proj_string: Unsupported conversion method: Lambert Conformal Conic

How to Reproduce

1. start Oracle

$ docker run -d -e ORACLE_SID="XE" -e ORACLE_PWD="adminpass" -e ORACLE_PDB="ORCLPDBTEST" -e ORACLE_CHARACTERSET="AL32UTF8" -p 0.0.0.0:1521:1521 "oslandia/oracle-for-qgis-tests:18.4.0-xe"

2. start sqlplus and Create Lambert 93 data

$ rlwrap sqlplus QGIS/qgis@$HOST/XEPDB1
SQL> CREATE TABLE QGIS.LINE_DATA ( "pk" INTEGER PRIMARY KEY, GEOM SDO_GEOMETRY);
SQL> INSERT INTO QGIS.LINE_DATA ("pk", GEOM) SELECT 1, SDO_GEOMETRY(2006,2154,NULL, SDO_ELEM_INFO_ARRAY(1,4,3, 1,2,1, 3,2,2, 11,2,1, 15,2,2, 25,2,1), SDO_ORDINATE_ARRAY(-1, -5, 1, 2, 5, 4, 7, 2.2, 10, .1, 13, 4, 17, -6, 1, 3, 5, 5, 7, 3.2, 10, 1.1, 13, 5, -11, -3, 5, 7, 10, -1)) from dual;

2. Load layer into QGIS
3. The SCR is marked as unkown and the above error message is printed

QGIS and OS versions

QGIS master Debian bullseye Oracle 18.3

Additional context

The issue is that the proj identification of the WKTEXT for code 2154 is below 70% of confidence.

SQL> SELECT WKTEXT FROM mdsys.cs_srs WHERE srid=2154;

WKTEXT
--------------------------------------------------------------------------------
PROJCS["RGF93 / Lambert-93", GEOGCS [ "RGF93", DATUM ["Reseau Geodesique Francai
s 1993 (EPSG ID 6171)", SPHEROID ["GRS 1980 (EPSG ID 7019)", 6378137.0, 298.2572
22101]], PRIMEM [ "Greenwich", 0.000000000 ], UNIT ["Decimal Degree", 0.01745329
25199433]], PROJECTION ["Lambert Conformal Conic"], PARAMETER ["Latitude_Of_Orig
in", 46.5], PARAMETER ["Central_Meridian", 3.0], PARAMETER ["Standard_Parallel_1
", 49.0], PARAMETER ["Standard_Parallel_2", 44.0], PARAMETER ["False_Easting", 7
00000.0], PARAMETER ["False_Northing", 6600000.0], UNIT ["Meter", 1.0]]

$ projinfo --identify 'PROJCS["RGF93 / Lambert-93", GEOGCS [ "RGF93", DATUM ["Reseau Geodesique Francais 1993 (EPSG ID 6171)", SPHEROID ["GRS 1980 (EPSG ID 7019)", 6378137.0, 298.257222101]], PRIMEM [ "Greenwich", 0.000000000 ], UNIT ["Decimal Degree", 0.0174532925199433]], PROJECTION ["Lambert Conformal Conic"], PARAMETER ["Latitude_Of_Origin", 46.5], PARAMETER ["Central_Meridian", 3.0], PARAMETER ["Standard_Parallel_1", 49.0], PARAMETER ["Standard_Parallel_2", 44.0], PARAMETER ["False_Easting", 700000.0], PARAMETER ["False_Northing", 6600000.0], UNIT ["Meter", 1.0]]'
PROJ.4 string:
Error when exporting to PROJ string: Unsupported conversion method: Lambert Conformal Conic

WKT2:2019 string:
PROJCRS["RGF93 / Lambert-93",
    BASEGEOGCRS["RGF93",
        DATUM["Reseau Geodesique Francais 1993 (EPSG ID 6171)",
            ELLIPSOID["GRS 1980 (EPSG ID 7019)",6378137,298.257222101,
                LENGTHUNIT["metre",1,
                    ID["EPSG",9001]]]],
        PRIMEM["Greenwich",0,
            ANGLEUNIT["Decimal Degree",0.0174532925199433]]],
    CONVERSION["unnamed",
        METHOD["Lambert Conformal Conic"],
        PARAMETER["Latitude_Of_Origin",46.5,
            ANGLEUNIT["Decimal Degree",0.0174532925199433]],
        PARAMETER["Central_Meridian",3,
            ANGLEUNIT["Decimal Degree",0.0174532925199433]],
        PARAMETER["Standard_Parallel_1",49,
            ANGLEUNIT["Decimal Degree",0.0174532925199433]],
        PARAMETER["Standard_Parallel_2",44,
            ANGLEUNIT["Decimal Degree",0.0174532925199433]],
        PARAMETER["False_Easting",700000,
            LENGTHUNIT["Meter",1]],
        PARAMETER["False_Northing",6600000,
            LENGTHUNIT["Meter",1]]],
    CS[Cartesian,2],
        AXIS["(E)",east,
            ORDER[1],
            LENGTHUNIT["Meter",1]],
        AXIS["(N)",north,
            ORDER[2],
            LENGTHUNIT["Meter",1]]]

Identification match count: 1
EPSG:2154: 25 %

If I lower the bestConfidence condition to greater or equals to 25 (or remove), it works and there is no more error message. I can propose a PR to do so, but I would like to discuss if it's OK to accept a so little confidence percentage projection?

I can't see other way to fix this (except hopping that Oracle will change their 2154 WKT projection string.

[haubourg]

cc @rouault does that ring any bell to you ?

[rouault]

I can propose a PR to do so

That would be a bad idea IMHO . That could result in very wrong guesses.

does that ring any bell to you ?

Yes, I've a feeling of deja-vu of discussing a similar issue few weeks ago, but can't find where. Basically the issue is that Oracle WKT1 is one of the many dialects of WKT1, and that PROJ >= 6 has never been tuned to deal with the peculiarities of that dialect (contrary to what was done with ESRI WKT). Here that would be mapping "Lambert Conformal Conic" to one of the two official variants of LCC in EPSG. And also dealing with those DATUM and ELLIPSOID names that include the EPSG code in the name.

[troopa81]

@rouault Thank you for your prompt answer

Here that would be mapping "Lambert Conformal Conic" to one of the two official variants of LCC in EPSG. And also dealing with those DATUM and ELLIPSOID names that include the EPSG code in the name.

That means modifications inside proj? Is it mandatory to define a new dialect? or is it possible to just add a new projection definition inside proj.db which will be detected as 2154?

I'm no proj expert so I fail to measure the impact of such a modification.

[rouault]

That means modifications inside proj?

Yes

Is it mandatory to define a new dialect?

PROJ does support explicit dialects, but here not necessarily. I was speaking here in a generic way.

or is it possible to just add a new projection definition inside proj.db which will be detected as 2154?

That would be a hack, and would not "scale" to deal properly with other Oracle WKT. The clean way would be to modify the WKT import code to deal with those pecularities

I fail to measure the impact of such a modification.

A few hours by someone familiar with the code.

[troopa81]

@Jean-Roc did you ever meet this issue with french projections in Oracle?

[Jean-Roc]

@troopa81 yes we do, I did a check on a table where both USER_SDO_GEOM_METADATA and MDSYS.SDO_GEOMETRY are using 2154, what we usually do is manually correcting it in our projects.

[jdesboeufs]

Hello. I have the same kind of problem with a 3946 (CC46), using GDAL/PROJ.

Error: PROJ: proj_as_proj_string: Unsupported conversion method: Lambert_Conformal_Conic

Original PRJ file:

PROJCS["RGF93 / CC46",GEOGCS["RGF93",DATUM["Reseau Geodesique Francais 1993 (EPSG ID 6171)",SPHEROID["GRS 1980 (EPSG ID 7019)",6378137.0,298.257222101]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]],PROJECTION["Lambert_Conformal_Conic"],PARAMETER["False_Easting",1700000.0],PARAMETER["False_Northing",5200000.0],PARAMETER["Central_Meridian",3.0],PARAMETER["Standard_Parallel_1",45.25],PARAMETER["Standard_Parallel_2",46.75],PARAMETER["Latitude_Of_Origin",46.0],UNIT["Meter",1.0]]

Original issue: https://github.com/contra/node-gdal-next/issues/6

[troopa81]

Thanks @rouault