Oracle Provider - CRS detection issue

[wburt]

Description
Using a connection to Oracle 12c QGIS 3.8.4 correctly detects and sets the c ordinate reference system as 'EPSG:3005 - NAD83 / BC Albers - Projected'. Using QGIS 3.10.5 the detected coordinate system is 'Unknown CRS: <PROJCRS STRING FOR 3005>'. The projcrs string looks correct but QGIS does not have a translation from Unknown into epsg:3005 so the data can't be mapped with other named projection data. The only other Oracle based data I have access to is in EPSG:4326 which is also detected as 'Unknown CRS: <PROJCRS STRING FOR 4326>'

Attached screenshots for the layer properties created from the same table in QGIS 3.10.5 and 3.8.4.
When trying to identify which release this changed in I found QGIS 3.10.0 provides no CRS information.

Layer Properties from QGIS 3.10.5

Layer Properties from QGIS 3.8.4

** system info
QGIS version 3.10.5-A Coruña QGIS code revision 984615fe1e Compiled against Qt 5.11.2 Running against Qt 5.11.2 Compiled against GDAL/OGR 3.0.4 Running against GDAL/OGR 3.0.4 Compiled against GEOS 3.8.1-CAPI-1.13.3 Running against GEOS 3.8.1-CAPI-1.13.3 Compiled against SQLite 3.29.0 Running against SQLite 3.29.0 PostgreSQL Client Version 11.5 SpatiaLite Version 4.3.0 QWT Version 6.1.3 QScintilla2 Version 2.10.8 Compiled against PROJ 6.3.1 Running against PROJ Rel. 6.3.1, February 10th, 2020 OS Version Windows Server 2016 (10.0)

[nyalldawson]

Can you copy and paste that wkt string from the screenshot here please?

[nyalldawson]

Please also paste the result of running:

SELECT coalesce(auth_name,'EPSG'),auth_srid,wktext FROM mdsys.cs_srs WHERE srid=????

(With the srid from your table instead instead of ????).

[wburt]

For sure. Thanks for your time.

Can you copy and paste that wkt string from the screenshot here please?

Unknown` CRS: PROJCRS["NAD83 / BC Albers",BASEGEOGCRS["NAD83",DATUM["North American Datum 1983 (EPSG ID 6269)",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["Albers Conical Equal Area"],PARAMETER["Latitude_Of_Origin",45,ANGLEUNIT["Decimal Degree",0.0174532925199433]],PARAMETER["Central_Meridian",-126,ANGLEUNIT["Decimal Degree",0.0174532925199433]],PARAMETER["Standard_Parallel_1",50,ANGLEUNIT["Decimal Degree",0.0174532925199433]],PARAMETER["Standard_Parallel_2",58.5,ANGLEUNIT["Decimal Degree",0.0174532925199433]],PARAMETER["False_Easting",1000000,LENGTHUNIT["Meter",1]],PARAMETER["False_Northing",0,LENGTHUNIT["Meter",1]]],CS[Cartesian,2],AXIS["(E)",east,ORDER[1],LENGTHUNIT["Meter",1]],AXIS["(N)",north,ORDER[2],LENGTHUNIT["Meter",1]]] - Projected

SELECT coalesce(auth_name,'EPSG'),auth_srid,wktext FROM mdsys.cs_srs WHERE srid=????

Government of British Columbia Ministry of Sustainable Resource Management. http://srmwww.gov.bc.ca/gis/bceprojection.html 3005
PROJCS["NAD83 / BC Albers", GEOGCS [ "NAD83", DATUM ["North American Datum 1983 (EPSG ID 6269)", SPHEROID ["GRS 1980 (EPSG ID 7019)", 6378137.0, 298.257222101]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.0174532925199433]], PROJECTION ["Albers Conical Equal Area"], PARAMETER ["Latitude_Of_Origin", 45.0], PARAMETER ["Central_Meridian", -126.0], PARAMETER ["Standard_Parallel_1", 50.0], PARAMETER ["Standard_Parallel_2", 58.5], PARAMETER ["False_Easting", 1000000.0], PARAMETER ["False_Northing", 0.0], UNIT ["Meter", 1.0]]

[nyalldawson]

Thanks !

Can you try

SELECT * FROM mdsys.cs_srs WHERE srid=????

(i also need the returned column names here)

[wburt]

result as csv
SELECT * FROM mdsys.cs_srs WHERE srid=3005
result_csv.txt

that looks bad... let me know if another format might be better. Fields are
CS_NAME SRID AUTH_SRID AUTH_NAME WKTEXT CS_BOUNDS WKTEXT3D

[nyalldawson]

Ok -- so unfortunately the issue is coming from Oracle itself here.

To explain further. When QGIS is resolving the crs for an oracle layer, it checks:

1. coalesce(auth_name,'EPSG'),auth_srid first. This is intended to prioritise matching to standard CRS definitions first. I.e. EPSG codes. Now, for whatever reason 3005 is returning "Government of British Columbia Ministry of Sustainable Resource Management. http://srmwww.gov.bc.ca/gis/bceprojection.html" as the auth_name. This is incorrect -- the authority which has assigned 3005 to this CRS is EPSG, not the "Government of British Colombia...etc". So QGIS first attempts to resolve the identifier "Government of British Columbia Ministry of Sustainable Resource Management. http://srmwww.gov.bc.ca/gis/bceprojection.html:3005" to a known CRS definition, and never QGIS nor the underlying PROJ library knows anything about a "Govt of ..." registry, so this fails to resolve the CRS.

2. QGIS then falls back to using the WKT definition of the CRS. After QGIS builds a CRS from a WKT string, it then tries to match this WKT based definition back to known CRSes (i.e. EPSG:3005).

Using the projinfo utility from the proj library we can test this:

projinfo --identify "PROJCS[\"NAD83 / BC Albers\", GEOGCS [ \"NAD83\", DATUM [\"North American Datum 1983 (EPSG ID 6269)\", SPHEROID [\"GRS 1980 (EPSG ID 7019)\", 6378137.0, 298.257222101]], PRIMEM [ \"Greenwich\", 0.000000 ], UNIT [\"Decimal Degree\", 0.0174532925199433]], PROJECTION [\"Albers Conical Equal Area\"], PARAMETER [\"Latitude_Of_Origin\", 45.0], PARAMETER [\"Central_Meridian\", -126.0], PARAMETER [\"Standard_Parallel_1\", 50.0], PARAMETER [\"Standard_Parallel_2\", 58.5], PARAMETER [\"False_Easting\", 1000000.0], PARAMETER [\"False_Northing\", 0.0], UNIT [\"Meter\", 1.0]]"

WKT2:2019 string:
PROJCRS["NAD83 / BC Albers",
    BASEGEOGCRS["NAD83",
        DATUM["North American Datum 1983 (EPSG ID 6269)",
            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["Albers Conical Equal Area"],
        PARAMETER["Latitude_Of_Origin",45,
            ANGLEUNIT["Decimal Degree",0.0174532925199433]],
        PARAMETER["Central_Meridian",-126,
            ANGLEUNIT["Decimal Degree",0.0174532925199433]],
        PARAMETER["Standard_Parallel_1",50,
            ANGLEUNIT["Decimal Degree",0.0174532925199433]],
        PARAMETER["Standard_Parallel_2",58.5,
            ANGLEUNIT["Decimal Degree",0.0174532925199433]],
        PARAMETER["False_Easting",1000000,
            LENGTHUNIT["Meter",1]],
        PARAMETER["False_Northing",0,
            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:3005: 25 %

We get a single candidate match to EPSG:3005, but at only a 25% percent confidence. QGIS uses a 70% minimum confidence when matching. From the proj docs:

70% means that CRS are equivalent), but the names do not match at all.
25% means that the CRS are not equivalent, but there is some similarity in the names.

So a 25% match is not a close match at all, hence why QGIS rejects this.

Indeed, in this case, the WKT definition from Oracle differs from PROJ's definition of EPSG:3005. Let's compare:

Oracle definition:

PROJCS["NAD83 / BC Albers", GEOGCS [ "NAD83", DATUM ["North American Datum 1983 (EPSG ID 6269)", SPHEROID ["GRS 1980 (EPSG ID 7019)", 6378137.0, 298.257222101]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.0174532925199433]], PROJECTION ["Albers Conical Equal Area"], PARAMETER ["Latitude_Of_Origin", 45.0], PARAMETER ["Central_Meridian", -126.0], PARAMETER ["Standard_Parallel_1", 50.0], PARAMETER ["Standard_Parallel_2", 58.5], PARAMETER ["False_Easting", 1000000.0], PARAMETER ["False_Northing", 0.0], UNIT ["Meter", 1.0]]

Definition of EPSG:3005 from proj (using projinfo EPSG:3005):

PROJCRS["NAD83 / BC Albers",
    BASEGEOGCRS["NAD83",
        DATUM["North American Datum 1983",
            ELLIPSOID["GRS 1980",6378137,298.257222101,
                LENGTHUNIT["metre",1]]],
        PRIMEM["Greenwich",0,
            ANGLEUNIT["degree",0.0174532925199433]],
        ID["EPSG",4269]],
    CONVERSION["British Columbia Albers",
        METHOD["Albers Equal Area",
            ID["EPSG",9822]],
        PARAMETER["Latitude of false origin",45,
            ANGLEUNIT["degree",0.0174532925199433],
            ID["EPSG",8821]],
        PARAMETER["Longitude of false origin",-126,
            ANGLEUNIT["degree",0.0174532925199433],
            ID["EPSG",8822]],
        PARAMETER["Latitude of 1st standard parallel",50,
            ANGLEUNIT["degree",0.0174532925199433],
            ID["EPSG",8823]],
        PARAMETER["Latitude of 2nd standard parallel",58.5,
            ANGLEUNIT["degree",0.0174532925199433],
            ID["EPSG",8824]],
        PARAMETER["Easting at false origin",1000000,
            LENGTHUNIT["metre",1],
            ID["EPSG",8826]],
        PARAMETER["Northing at false origin",0,
            LENGTHUNIT["metre",1],
            ID["EPSG",8827]]],
    CS[Cartesian,2],
        AXIS["(E)",east,
            ORDER[1],
            LENGTHUNIT["metre",1]],
        AXIS["(N)",north,
            ORDER[2],
            LENGTHUNIT["metre",1]],
    USAGE[
        SCOPE["unknown"],
        AREA["Canada - British Columbia"],
        BBOX[48.25,-139.04,60.01,-114.08]],
    ID["EPSG",3005]]

It's quite different, in fundamental ways.

So the fix here would be:

1. Speak to your oracle rep and request that they fix the incorrect authority definition in mdsys.cs_srs
2. Pressure your oracle sales rep to get Oracle to fund improvements in the proj library to handle Oracle's "special" variants of WKT

[wburt]

Thank you @nyalldawson we will work on updating (1). Judging from the Ministry name that definition may align with the conception of QGIS.

[wburt]

@nyalldawson Additional info - our mdsys.cs_srs view has 778 records / 5962 where AUTH_NAME = 'EPSG'. 300+ unique AUTH_NAME values -most attributing a government authority, Oracle or even ' Various oil company records'. Also mdsys.cs_srs is a legacy view that seems possibly derived from SDO_COORD_REF_SYSTEM table that seems to have better info but I don't think is supported by really old versions.

[mdunhamwilkie]

CS_SRS is a view that is defined as "select .. from SDO_CS_SRS" (actually a 1-1 mapping). It does have an "instead" trigger that propagates any updates/insertions/deletions to CS_SRS to SDO_COORD_REF_SYS, which modern versions of Oracle use. The SDO_CS_SRS and CS_SRS are considered legacy table/view, there for compatibility with older versions of Oracle (e.g., version 8). The Oracle docs are clear that these are not to be updated by users. In fact there is an update trigger on SDO_CS_SRS that calls "mdsys.mdprvt_srid.sdo_invalidate_srid_metadata(:old.srid);" - doesn't sound like something you want to have called!

There is a stored procedure SDO_CS.UPDATE_WKTS_FOR_EPSG_CRS(srid) that is supposed to update the SRTEXT for the specified srid. But it's not clear where it gets the new wkt from or where it writes it to. It looks like that stored procedure is called if you want to propagate changes made to SDO_COORD_REF_SYS back to CS_SRS. See https://docs.oracle.com/database/121/SPATL/legacy-tables-and-views.htm#GUID-8056B5FE-E78D-4847-8DC2-F7AD64D1354B.

It does appear that there is an updateable view SDO_COORD_REF_SYSTEM that propagates changes to the table SDO_COORD_REF_SYS. It looks like the INFORMATION_SOURCE column in this table/view gets mapped back to AUTH_NAME in CS_SRS. I will try updating the INFORMATION_SOURCE for srid 3005 to 'EPSG' - this should result in the AUTH_NAME for CS_SRS srid 3005 being set to the correct value.