4 Replies Latest reply on Dec 19, 2012 9:37 AM by 783276

    Correct 3D SRID for Belgium in Oracle 11G

      We've recently migrated from Oracle 10G to 11G.

      In 10G we've always used the Oracle SRID 327680 for our sdo_geometries (Belgian coördinate system) for 2d and 3d geometries.

      Validation of the geometries in 11G produces a nice new and long list of errors for our 3d geometries : ora-54668 (a 2D SRID cannot be used with a 3D geometry)

      The proposed solution is to use an appropriate 3D SRID.

      A query on the mdsys.cs_srs produces no wktext3d value for srid=327680, while there is a value for srid=31370.

      My questions are:
      - Is the missing wktext3d for SRID=327680 an oversight of Oracle ?
      - If not :
      - do I need to convert all my geometry tables to an other SRID? Or can I leave them untouched as everything seems to work fine.
      - what is the appropriate 3d SRID?
      - can I use the same SRID for 2d and 3d geometries?

      Thanks in advance for your time
        • 1. Re: Correct 3D SRID for Belgium in Oracle 11G
          Luc Van Linden

          To start with, you should change your srid anyway as the 327680 has a wrong central meridian parameter in its WKT for the projection:
          PARAMETER ["Central_Meridian", 4.356940]

          This should be PARAMETER ["Central_Meridian", 4.3674866666666667], which is used correctly in the SRID 31370.
          The 31370 is the commonly used SRID for the projected Belgian coord ref system Lambert 1972.

          The problem here is indeed that with R11 the 3D support has forced a more rigorous validation.

          I am personally not so happy with this for the same reason you run into, as this will create additional, mostly unnecessary (when storing 2.5 d data) data management. Obviously this can be subject to discussion.

          If one is working in multiple vendor eco-system, my experience is that you should avoid, or try to avoid, making or forcing people to make user or system defined SRID/SRS definitions (and consequentially other SRID ID's).

          Even if the different system might tend to threat an EPSG:31370 slightly differently, the systems "understand" or can work with the data directly, as the ESPG:31370 is commonly used, without the need for SRID /EPSG code overrides or user defined coordsys definitions maintenance in all systems.
          Also some systems will store the data in 3d, either real Z values or 0 Z values.

          Therefore, I believe it would much easier that, one should be able to set a flag to define a table to be used with a Projected srid in an 10g version for treating the Z values, without the need for custom or compound SRIDs. This obviously with the consequence of some limitations on the availability of 3D functionality offered in R11. Something like the dim=2 parameter in the index creation statement.

          Just as a side note, I have at the moment a similar discussion with one of the main vendors having wrong parameters for the EPSG:31370 in their read-only definition library. Again forced to make a user-defined definition with the correct parameters, but unable to deploy this system in a WMS/WFS context from other type of clients or other coord ref systems. Soit.

          So in your case you will have to question if you really need your 3D information. I suppose you do as you said “our 2D and 3D data).
          I your data is true 3D, you will have to create and assign a compound SRID:


          If your data is 2.5 D, you should still be able to build an index using 2 dimensions. If you are using other functions (validation, CRS transform…) you will have to apply the function make_2D, or use other mechanism like views etc, to have the Z value ignored. Obviously this comes with some trade offs you will have to make related to your overall environments, use-cases and requirements, against the creation, usage or maintenance of a user-defined compound SRID for Oracle (and potentially other systems).

          I hope Mike Horhammer of Oracle can shine his light on this ;-).

          Kind regards
          Luc Van Linden
          • 2. Re: Correct 3D SRID for Belgium in Oracle 11G
            Which database version do you have for 11g now?
            Please see the following flag10g parameter to validate 3D geometries in 10g style (which exists since database version
                 theGeometry IN SDO_GEOMETRY,
                 theDimInfo IN SDO_DIM_ARRAY,
                 conditional IN VARCHAR2 DEFAULT 'TRUE' ,
                 flag10g IN VARCHAR2 DEFAULT 'FALSE'
                 ) RETURN VARCHAR2;
                 theGeometry IN SDO_GEOMETRY,
                 tolerance IN NUMBER,
                 conditional IN VARCHAR2 DEFAULT 'TRUE' ,
                 flag10g IN VARCHAR2 DEFAULT 'FALSE'
                 ) RETURN VARCHAR2;
            Here, flag10g parameter is explained as follows:
            Oracle Database 10g compatibility flag. A string value of TRUE causes only validation checks specific to two-dimensional geometries to be performed, and no 3D-specific validation checks to be performed. A string value of FALSE (the default) performs all validation checks that are relevant for the geometry. (See the Usage Notes for more information about the flag10g parameter.)

            Hope this helps,
            Please let us know if you have further questions.

            • 3. Re: Correct 3D SRID for Belgium in Oracle 11G
              Luc Van Linden
              Hi Baris

              This is good news, the 10G validation flag, at least for those on


              • 4. Re: Correct 3D SRID for Belgium in Oracle 11G
                Thank you all for the valuable feedback.

                Problem solved.