This discussion is archived
8 Replies Latest reply: Mar 11, 2013 2:45 PM by rpitts RSS

SDO_CS.VALIDATE_WKT: Nearly all coodinate systems fail the test

878029 Newbie
Currently Being Moderated
Test query:

select * from CS_SRS where SDO_CS.VALIDATE_WKT(srid)='TRUE';

This query yields only 13 rows of 4457 total. The CS_SRS table consists of Oracle data only, and nothing else.




I found this problem in a customer production database running "Oracle Database 11g Release 11.2.0.3.0 - 64bit Production".

I have compared the WKTEXTs with another database (running 11.2.0.3.0 - 64bit), and they are equal and they validate fine in this other database.

So it seems that it is something wrong with the SDO_CS.VALIDATE_WKT function itself.



What can I do about this problem?






Valid WKTEXT

5800     Astra Minas Grid     LOCAL_CS [ "Astra Minas Grid", LOCAL_DATUM ["Local Datum", 0], UNIT ["metre", 1], AXIS ["X", NORTH], AXIS["Y", WEST]]
5801     Barcelona Grid B1     LOCAL_CS [ "Barcelona Grid B1", LOCAL_DATUM ["Local Datum", 0], UNIT ["metre", 1], AXIS ["N", NORTH], AXIS["E", EAST]]
5802     Barcelona Grid B2     LOCAL_CS [ "Barcelona Grid B2", LOCAL_DATUM ["Local Datum", 0], UNIT ["metre", 1], AXIS ["N", NORTH], AXIS["E", EAST]]
5803     Maturin Grid     LOCAL_CS [ "Maturin Grid", LOCAL_DATUM ["Local Datum", 0], UNIT ["metre", 1], AXIS ["N", NORTH], AXIS["E", EAST]]
5808     Maracaibo Cross Grid M4     LOCAL_CS [ "Maracaibo Cross Grid M4", LOCAL_DATUM ["Local Datum", 0], UNIT ["metre", 1], AXIS ["N", NORTH], AXIS["E", EAST]]
5809     Maracaibo Cross Grid M5     LOCAL_CS [ "Maracaibo Cross Grid M5", LOCAL_DATUM ["Local Datum", 0], UNIT ["metre", 1], AXIS ["N", NORTH], AXIS["E", EAST]]
5810     La Rosa Grid     LOCAL_CS [ "La Rosa Grid", LOCAL_DATUM ["Local Datum", 0], UNIT ["metre", 1], AXIS ["N", NORTH], AXIS["E", EAST]]
5811     Mene Grande     LOCAL_CS [ "Mene Grande", LOCAL_DATUM ["Local Datum", 0], UNIT ["metre", 1], AXIS ["N", NORTH], AXIS["E", EAST]]
5812     El Cubo     LOCAL_CS [ "El Cubo", LOCAL_DATUM ["Local Datum", 0], UNIT ["metre", 1], AXIS ["N", NORTH], AXIS["E", EAST]]
5813     Dabajuro     LOCAL_CS [ "Dabajuro", LOCAL_DATUM ["Local Datum", 0], UNIT ["metre", 1], AXIS ["N", NORTH], AXIS["E", EAST]]
5814     Tucupita     LOCAL_CS [ "Tucupita", LOCAL_DATUM ["Local Datum", 0], UNIT ["metre", 1], AXIS ["N", NORTH], AXIS["E", EAST]]
5815     Santa Maria de Ipire     LOCAL_CS [ "Santa Maria de Ipire", LOCAL_DATUM ["Local Datum", 0], UNIT ["metre", 1], AXIS ["N", NORTH], AXIS["E", EAST]]
5816     Barinas west base     LOCAL_CS [ "Barinas west base", LOCAL_DATUM ["Local Datum", 0], UNIT ["metre", 1], AXIS ["N", NORTH], AXIS["E", EAST]]
  • 1. Re: SDO_CS.VALIDATE_WKT: Nearly all coodinate systems fail the test
    878029 Newbie
    Currently Being Moderated
    Here is an example of a WKTEXT that fails with FALSE(163)

    PROJCS["ETRS89 / UTM zone 32N", GEOGCS [ "ETRS89", DATUM ["European Terrestrial Reference System 1989 (EPSG ID 6258)", SPHEROID ["GRS 1980 (EPSG ID 7019)", 6378137.0, 298.257222101]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.0174532925199433]], PROJECTION ["Transverse Mercator"], PARAMETER ["Latitude_Of_Origin", 0.0], PARAMETER ["Central_Meridian", 9.0], PARAMETER ["Scale_Factor", 0.9996], PARAMETER ["False_Easting", 500000.0], PARAMETER ["False_Northing", 0.0], UNIT ["Meter", 1.0]]
  • 2. Re: SDO_CS.VALIDATE_WKT: Nearly all coodinate systems fail the test
    Simon Greener Journeyer
    Currently Being Moderated
    I use 11.2.0.1 and running this stops at 100 with the following error....
    select rownum,srid,cs_name,auth_name 
      from CS_SRS 
     where SDO_CS.VALIDATE_WKT(srid)='TRUE';
    1....
    2....
           100       2141 NAD83(CSRS98) / MTM zone 4                                                       Topographic Mapping Section; Quebec Ministry of Natural Resources.                                                                                                                                                                                               
    
    Error starting at line 1 in command:
    select rownum,srid,cs_name,auth_name
     from CS_SRS where SDO_CS.VALIDATE_WKT(srid)='TRUE'
    Error report:
    SQL Error: ORA-13249: Stmt-Execute Failure:  select CONVERSION_FACTOR from MDSYS.SDO_DIST_UNITS    WHERE UNIT_NAME = 'Clarke's foot' 
    ORA-29400: data cartridge error
    ORA-01756: quoted string not properly terminated
    ORA-06512: at "MDSYS.SDO_CS", line 510
    ORA-06512: at "MDSYS.SDO_CS", line 586
    13249. 00000 -  "%s"
    *Cause:    An internal error was encountered in the extensible spatial index
               component. The text of the message is obtained from some
               other server component.
    *Action:   Contact Oracle Support Services with the exact error text.
    Pretty obvious error. Probably fixed (use bind variable) in a later release.

    The more important question you don't offer is why you are doing this?

    Most users require only one or two SRIDs for their data unless you are working for a data company that supplies data worldwide for any projection.

    If only one or two SRID test those to see if they are valid: don't bother with the rest as it is Oracle's problem to ensure the whole set of SRIDS are valid.

    regards
    Simon
  • 3. Re: SDO_CS.VALIDATE_WKT: Nearly all coodinate systems fail the test
    _jum Journeyer
    Currently Being Moderated
    If you necessarily have to check all systems, do the test in the LOOP and add an EXCEPTION:
    SET SERVEROUTPUT ON SIZE UNLIMITED;
    
    DECLARE
      cur_cs SYS_REFCURSOR;
      dat_cs CS_SRS%ROWTYPE; 
      i_ok   BOOLEAN;
      
    BEGIN
    
      -- EXCEPTION with 2314 and 24100 
      OPEN cur_cs 
       FOR SELECT * 
      FROM CS_SRS
      -- WHERE srid not IN (2314, 24100)
      ORDER BY srid;
    
    
      LOOP
        FETCH cur_cs INTO dat_cs;
        EXIT WHEN cur_cs%NOTFOUND;
        BEGIN 
          i_ok := SDO_CS.VALIDATE_WKT(dat_cs.srid)='TRUE';
          IF NOT i_ok THEN
            dbms_output.put_line('FALSE: '||dat_cs.srid);
          END IF;
        EXCEPTION
          WHEN OTHERS THEN
            dbms_output.put_line('EXC_SR:'||dat_cs.srid);
            dbms_output.put_line('EXC_CS:'||substr(dat_cs.cs_name,1,20));
        END;
    
      END LOOP;
      
      CLOSE cur_cs;
    
    END;
    
    ...
    FALSE: 2291
    FALSE: 2292
    EXC_SR:2314
    EXC_CS:Trinidad 1903 / Trin
    FALSE: 2953
    FALSE: 2954
    ...
    FALSE: 22780
    EXC_SR:24100
    EXC_CS:Jamaica 1875 / Jamai
    FALSE: 28991
    ...
    You get the FALSE srid and these, which raise an EXCEPTION.

    Edited by: _jum on 08.03.2013 14:34
    If I read your answer to Simon Greener now - may be I misread your question ?!
  • 4. Re: SDO_CS.VALIDATE_WKT: Nearly all coodinate systems fail the test
    878029 Newbie
    Currently Being Moderated
    You have right, I certainly do not need all coordinate systems. But the ones I do will not validate, and their WKTEXT are exactly equal to the ones in another database.

    Summing up
    - Same database versions (Oracle Database 11g Release 11.2.0.3.0 - 64bit Production)
    - Same WKTEXT for same SRID
    - Valid in one database, invalid in the other


    Example WKTEXT for SRID: 25832

    PROJCS["ETRS89 / UTM zone 32N", GEOGCS [ "ETRS89", DATUM ["European Terrestrial Reference System 1989 (EPSG ID 6258)", SPHEROID ["GRS 1980 (EPSG ID 7019)", 6378137.0, 298.257222101]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.0174532925199433]], PROJECTION ["Transverse Mercator"], PARAMETER ["Latitude_Of_Origin", 0.0], PARAMETER ["Central_Meridian", 9.0], PARAMETER ["Scale_Factor", 0.9996], PARAMETER ["False_Easting", 500000.0], PARAMETER ["False_Northing", 0.0], UNIT ["Meter", 1.0]]



    So my conclusion is that it is the VALIDATE_WKT function itself (or SDO_CS package) that has faults.
  • 5. Re: SDO_CS.VALIDATE_WKT: Nearly all coodinate systems fail the test
    B Hall Explorer
    Currently Being Moderated
    I'd start by re-loading the spatial code into the database.

    Probably best to de-install and then re-install to make sure everything is clean. Look in the manual under Oracle Locator (not real intuitive right?) - B.1 Installing and Deinstalling Locator or Spatial Manually

    http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/sdo_locator.htm#i632018

    Bryan
  • 6. Re: SDO_CS.VALIDATE_WKT: Nearly all coodinate systems fail the test
    Paul Dziemiela Journeyer
    Currently Being Moderated
    Hi folks,

    I know it's a detail but Simon's observation about the apostrophe crashing the function is salient and exists in my fully patched production system. Has anyone reported this bug? if not I can do so.

    Cheers,

    Paul
  • 7. Re: SDO_CS.VALIDATE_WKT: Nearly all coodinate systems fail the test
    rpitts Newbie
    Currently Being Moderated
    You dont say what a fully patched production system is 11.2.0.3.x ? (opatch lsinventory -detail please when you open the SR)

    Just to remind us.....

    This function returns the string 'TRUE' if the WKT description is valid. If the WKT description is invalid, this function returns a string in the format 'FALSE (<position-number>)', where <position-number> is the number of the character position in the WKT description where the first error occurs.

    The WKT description is checked to see if it satisfies the requirements described in Section 6.8.1.1.

    Examples

    The following example validates the WKT description of the coordinate system associated with SRID 81989000. The results show that the cause of the invalidity (or the first cause of the invalidity) starts at character position 181 in the WKT description. (SRID 81989000 is not associated with any established coordinate system. Rather, it is for a deliberately invalid coordinate system that was inserted into a test version of the MDSYS.CS_SRS table, and it is not included in the MDSYS.CS_SRS table that is shipped with Oracle Spatial.)

    SELECT SDO_CS.VALIDATE_WKT(81989000) FROM DUAL;

    SDO_CS.VALIDATE_WKT(81989000)
    --------------------------------------------------------------------------------
    FALSE (181)

    The crash by the apostrophe can be raised as an issue....
  • 8. Re: SDO_CS.VALIDATE_WKT: Nearly all coodinate systems fail the test
    rpitts Newbie
    Currently Being Moderated
    Sounds like this database is suspect, are both Db's out of the same home?

    Take care with a drop of MDSYS, I would suggest just a reload of the co-ordinate systems
    Was the database upgraded from 10.2 or older?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points