I received a comment the other day from a user of my PL/SQL packages about how he had extracted the GEOM.isCompound() function from the GEOM package, and re-written it in pure PL/SQL getting rid of the SQL pivot that that I use.

That’s the sort of behaviour I encourage.

The user’s comment was this:

This [i.e., his rewrite without the SQL] runs about 20-30 times faster and answers the same question.

And this is about what I would have expected!

But this comment reminded me about that article I have been meaning to write (for the past few years) on how to examine the performance differences between two implementations of a function in PL/SQL. I used the technique in my article on a fast method for creating multi-point geometries but I never got around to the sort of article that follows. At the end of the article I will discuss the SQL vs pure programmatic approach: there is no right answer, just some observations from someone who has written the odd PL/SQL function over the years.

The original GEOM.isCompound() function was extracted for the purpose of this article. With a slight rename, here it is:

create or replace
Function is_Compound(p_elem_info in mdsys.sdo_elem_info_array)
  return boolean
Is
  v_compound_element_count number := 0;
Begin
  SELECT count(*) as c_element_count
    INTO v_compound_element_count
    FROM (SELECT e.id,
                 e.etype,
                 e.offset,
                 e.interpretation
            FROM (SELECT trunc((rownum - 1) / 3,0) as id,
                         sum(case when mod(rownum,3) = 1 then sei.column_value else null end) as offset,
                         sum(case when mod(rownum,3) = 2 then sei.column_value else null end) as etype,
                         sum(case when mod(rownum,3) = 0 then sei.column_value else null end) as interpretation
                    FROM TABLE(p_elem_info) sei
                   GROUP BY trunc((rownum - 1) / 3,0)
                  ) e
         ) i
   WHERE ( i.etype = 2 AND i.interpretation = 2 )
      OR ( i.etype in (1003,2003) AND i.interpretation IN (2,4) );
  Return v_compound_element_count > 0;
End is_Compound;

The commentator’s function is as follows:

create or replace
function has_compound_curves ( geom_in in sdo_geometry) 
  return integer 
as
  etype number;
  interpret number;
begin
  for i in geom_in.sdo_elem_info.first .. geom_in.sdo_elem_info.last loop
    case
      when mod(i,3) = 1 then null; -- continue;
      when mod(i,3) = 2 then etype := geom_in.sdo_elem_info(i);
      when mod(i,3) = 0 
      then
        interpret := geom_in.sdo_elem_info(i);
        if((etype = 2 and interpret = 2) or (etype in (1003,2003) and interpret in (2,4))) then
          return 1;
        end if;
    end case;
  end loop;
  return 0;
end has_compound_curves;

Now, let’s compare the two using Tom Kyte’s runstats_pkg package.

set serveroutput on size unlimited
declare 
  v_OK   integer;
  v_geom mdsys.sdo_geometry := MDSYS.SDO_GEOMETRY(2006,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1,5,2,1,9,2,1,17,4,2,17,2,1,23,2,2),MDSYS.SDO_ORDINATE_ARRAY(50,15,55,15,60,15,65,15,10,25,20,30,25,25,30,30,110,178,110,175,120,175,120,178,115,180,110,178));
begin
 runstats_pkg.rs_start;
 v_OK := has_compound_curves(v_geom);
 runstats_pkg.rs_middle;
 v_OK := case when is_compound(v_geom.sdo_elem_info) = TRUE then 1 else 0 end;
 runstats_pkg.rs_stop;
end;
/
 
anonymous block completed
Run1 ran in 0 hsecs
Run2 ran in 1 hsecs
Run 1 ran in 0 % of the time
...

There appears to be little difference. So, I’ve short-circuited any more testing as a single execution of each function occurs in less than 1/100th of a second and so cannot be reasonably compared. The only way to compare the algorithms is to write a procedure that executes each 1000 times as follows:

create or replace
procedure test_compound(p_geom in mdsys.sdo_geometry,
                        p_is   in integer)
As
  v_OK          integer;
  v_is_compound boolean := case when p_is > 0 then true else false end;
Begin
  For i IN 1..1000 LOOP
    If ( v_is_compound ) Then
      v_OK := case when is_compound(p_geom.sdo_elem_info) then 1 else 0 end;
    Else
      v_OK := has_compound_curves(p_geom);
    End If;
  End Loop;
end test_compound;

Which we would execute using the runstats_pkg as follows:

set serveroutput on size unlimited;
declare 
  v_geom mdsys.sdo_geometry := MDSYS.SDO_GEOMETRY(2006,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1,5,2,1,9,2,1,17,4,2,17,2,1,23,2,2),MDSYS.SDO_ORDINATE_ARRAY(50,15,55,15,60,15,65,15,10,25,20,30,25,25,30,30,110,178,110,175,120,175,120,178,115,180,110,178));
begin
 runstats_pkg.rs_start;
 test_compound(v_geom,0);
 runstats_pkg.rs_middle;
 test_compound(v_geom,1);
 runstats_pkg.rs_stop(10);
end;
 
anonymous block completed
Run1 ran in 1 hsecs
Run2 ran in 91 hsecs
Run 1 ran in 1.1 % of the time
 
Name                                  Run1        Run2        Diff
LATCH.cache buffers chains             121         105         -16
STAT...undo change vector size       2,072       2,140          68
STAT...recursive cpu usage               0          91          91
STAT...CPU used by this sessio           1          93          92
STAT...redo size                     2,720       2,860         140
STAT...execute count                     1       1,001       1,000
STAT...calls to get snapshot s           1       1,001       1,000
STAT...recursive calls                   1       1,003       1,002
LATCH.shared pool                        0       1,028       1,028
LATCH.SQL memory manager worka           6       2,006       2,000
LATCH.library cache lock                 0       2,003       2,003
LATCH.library cache pin                  4       2,007       2,003
LATCH.library cache                      5       3,017       3,012
LATCH.row cache objects                 12      15,024      15,012
STAT...session pga memory           65,536           0     -65,536
Run1 latches total versus runs -- difference AND pct
        Run1        Run2        Diff          Pct
         158      25,210      25,052           1%

This tells me that the pure PL/SQL implementation runs in 1.1% of the time of the original implementation based on SQL. That is a huge difference between the two which is expected and appears to be in agreement with my commentator.

Can we modify is_Compound and close the performance gap?

Let’s modify is_Compound by:

making it return an integer and
removing one of the SELECT in-line views that is not needed.

create or replace
Function is_Compound(p_elem_info in mdsys.sdo_elem_info_array)
  return integer
Is
  v_compound_element_count number := 0;
Begin
  SELECT count(*) as c_element_count
    INTO v_compound_element_count
    FROM (SELECT trunc((rownum - 1) / 3,0) as id,
                 sum(case when mod(rownum,3) = 1 then sei.column_value else null end) as offset,
                 sum(case when mod(rownum,3) = 2 then sei.column_value else null end) as etype,
                 sum(case when mod(rownum,3) = 0 then sei.column_value else null end) as interpretation
            FROM TABLE(p_elem_info) sei
           GROUP BY trunc((rownum - 1) / 3,0)
         ) e
   WHERE ( e.etype = 2 AND e.interpretation = 2 )
      OR ( e.etype in (1003,2003) AND e.interpretation IN (2,4) );
  Return v_compound_element_count;
End is_Compound;
/

And then run it again against has_compound_curves() function. Note that first we need to modify the test_compound procedure to handle the changed return type by removing the “case when is_compound….end” construct and replacing it by a simple call to is_compound.

The new results are:

 
anonymous block completed
Run1 ran in 3 hsecs
Run2 ran in 93 hsecs
Run 1 ran in 3.23 % of the time
 	
Name                                  Run1        Run2        Diff
LATCH.channel operations paren           0          14          14
LATCH.checkpoint queue latch             0          16          16
LATCH.cache buffers chains             121          96         -25
STAT...undo change vector size       2,072       2,140          68
STAT...CPU used by this sessio           5          94          89
STAT...recursive cpu usage               2          91          89
STAT...redo size                     2,720       2,860         140
STAT...calls to get snapshot s           1       1,001       1,000
STAT...execute count                     1       1,001       1,000
STAT...recursive calls                   1       1,003       1,002
LATCH.shared pool                        0       1,028       1,028
LATCH.library cache pin                  4       2,007       2,003
LATCH.library cache lock                 0       2,003       2,003
LATCH.SQL memory manager worka           6       2,073       2,067
LATCH.library cache                      5       3,018       3,013
LATCH.row cache objects                 12      15,027      15,015
 
Run1 latches total versus runs -- difference AND pct
        Run1        Run2        Diff          Pct
         160      25,310      25,150           1%

A very, very slight improvement.

Let’s try modifying the function by adding in an “and rownum < 2” predicate to “short circuit” the query once we have found a compound element. Also, let’s add in a test to short-circuit when we find a compound element (4,1005,2005) and not just a potential sub-element of one.

create or replace
Function is_Compound(p_elem_info in mdsys.sdo_elem_info_array)
  return integer
Is
  v_compound_element_count number := 0;
Begin
  SELECT count(*) as c_element_count
    INTO v_compound_element_count
    FROM (SELECT trunc((rownum - 1) / 3,0) as id,
                 sum(case when mod(rownum,3) = 1 then sei.column_value else null end) as offset,
                 sum(case when mod(rownum,3) = 2 then sei.column_value else null end) as etype,
                 sum(case when mod(rownum,3) = 0 then sei.column_value else null end) as interpretation
            FROM TABLE(p_elem_info) sei
           GROUP BY trunc((rownum - 1) / 3,0)
         ) e
   WHERE ( e.etype = 2 AND e.interpretation = 2 )
      OR ( e.etype in (1003,2003) AND e.interpretation IN (2,4) )
     AND rownum < 2;
  Return v_compound_element_count;
End is_Compound;

Now, one last final run against the new has_compound_curves PL/SQL function….

anonymous block completed
Run1 ran in 3 hsecs
Run2 ran in 90 hsecs
Run 1 ran in 3.33 % of the time
 	
Name                                  Run1        Run2        Diff
LATCH.cache buffers chains             128         112         -16
STAT...undo change vector size       2,140       2,212          72
STAT...CPU used by this sessio           5          90          85
STAT...recursive cpu usage               2          90          88
STAT...redo size                     2,860       3,004         144
STAT...calls to get snapshot s           1       1,001       1,000
STAT...execute count                     1       1,001       1,000
STAT...recursive calls                   1       1,003       1,002
LATCH.shared pool                        0       1,029       1,029
LATCH.SQL memory manager worka           6       2,006       2,000
LATCH.library cache lock                 1       2,003       2,002
LATCH.library cache pin                  4       2,007       2,003
LATCH.library cache                      5       3,017       3,012
LATCH.row cache objects                 12      15,024      15,012
 
Run1 latches total versus runs -- difference AND pct
        Run1        Run2        Diff          Pct
         180      25,246      25,066           1%

Again, not much change.

Let’s try reordering the geometry so the compound element is first…

select sdo_aggr_union(sdoaggrtype(geom,0.05)) as geom from (
select rin,geom from (
select rownum as rin,e.geometry as geom
from table(geom.extractelementspiped(MDSYS.SDO_GEOMETRY(2006,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1,5,2,1,9,2,1,17,4,2,17,2,1,23,2,2),MDSYS.SDO_ORDINATE_ARRAY(50,15,55,15,60,15,65,15,10,25,20,30,25,25,30,30,110,178,110,175,120,175,120,178,115,180,110,178)),0) ) e
) order by rin desc );
 
GEOM
-----------------------------------------
MDSYS.SDO_GEOMETRY(2006,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,4,2,1,2,2,5,2,1,13,2,1,17,2,1,21,2,1),MDSYS.SDO_ORDINATE_ARRAY(120,178,115,180,110,178,110,175,120,175,120,178,60,15,65,15,50,15,55,15,10,25,20,30,25,25,30,30))
 
1 row selected

And run the new geometry against the new has_compound_curves PL/SQL function….

set serveroutput on size unlimited;
declare 
  v_geom mdsys.sdo_geometry := MDSYS.SDO_GEOMETRY(2006,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,4,2,1,2,2,5,2,1,13,2,1,17,2,1,21,2,1),MDSYS.SDO_ORDINATE_ARRAY(120,178,115,180,110,178,110,175,120,175,120,178,60,15,65,15,50,15,55,15,10,25,20,30,25,25,30,30));
begin
 runstats_pkg.rs_start;
 test_compound(v_geom,0);
 runstats_pkg.rs_middle;
 test_compound(v_geom,1);
 runstats_pkg.rs_stop(10);
end;
/
 
anonymous block completed
Run1 ran in 2 hsecs
Run2 ran in 92 hsecs
Run 1 ran in 2.17 % of the time
 
Name                                  Run1        Run2        Diff
STAT...session logical reads            26          38          12
LATCH.cache buffers chains             137         105         -32
STAT...recursive cpu usage               0          91          91
STAT...CPU used by this sessio           2          93          91
STAT...undo change vector size       2,076       2,204         128
STAT...redo size                     2,724       2,996         272
STAT...recursive calls                   1       1,001       1,000
LATCH.shared pool                        0       1,000       1,000
STAT...calls to get snapshot s           1       1,001       1,000
STAT...execute count                     1       1,001       1,000
LATCH.library cache pin                  4       2,003       1,999
LATCH.library cache lock                 0       2,000       2,000
LATCH.SQL memory manager worka           6       2,006       2,000
LATCH.library cache                      4       3,003       2,999
LATCH.row cache objects                 12      15,012      15,000
 
Run1 latches total versus runs -- difference AND pct
        Run1        Run2        Diff          Pct
         190      25,152      24,962           1%

Reorganising the SDO_GEOMETRY had little effect on the SQL algorithm.

Even though I always suspected the issue was and is the overhead of setting up multiple SQL calls, I still want to complete the last of the modifications to the SQL function.

create or replace
Function is_Compound(p_elem_info in mdsys.sdo_elem_info_array)
  return integer
Is
  v_compound_element_count integer := 0;
Begin
  SELECT 1 as c_element_count
    INTO v_compound_element_count
    FROM (SELECT trunc((rownum - 1) / 3,0) as id,
                 sum(case when mod(rownum,3) = 1 then sei.column_value else null end) as offset,
                 sum(case when mod(rownum,3) = 2 then sei.column_value else null end) as etype,
                 sum(case when mod(rownum,3) = 0 then sei.column_value else null end) as interpretation
            FROM TABLE(p_elem_info) sei
           GROUP BY trunc((rownum - 1) / 3,0)
         ) e
   WHERE ( ( e.etype = 2 AND e.interpretation = 2 /* circular arc*/ )
        OR ( e.etype in (4,1005,2005) )
        OR ( e.etype in (1003,2003) AND e.interpretation IN (2/*circular arc*/,4/*circle*/) )
         )
     AND rownum < 2;
  Return 1;
  Exception
    When NO_DATA_FOUND Then
      Return 0;
End is_Compound;

Since I have decided to handle etype 4,1005 and 2005 elements, I should change the has_compound_curves() function. Since I am changing it I will change the function to have it accept and mdsys.sdo_elem_info_array as does the is_compound
() function.

create or replace
function has_compound_curves ( elem_in in mdsys.sdo_elem_info_array) 
  return integer 
as
  etype number;
  interpret number;
begin
  for i in elem_in.first .. elem_in.last loop
    case
      when mod(i,3) = 1 then null; -- continue;
      when mod(i,3) = 2 then etype := elem_in(i);
      when mod(i,3) = 0 
      then
        interpret := elem_in(i);
        if( (etype = 2 and interpret = 2) 
            or 
            ( etype in (4,1005,2005) )
            or 
            (etype in (1003,2003) and interpret in (2,4))
          ) then
          return 1;
        end if;
    end case;
  end loop;
  return 0;
end has_compound_curves;
 
create or replace
procedure test_compound(p_elem in mdsys.sdo_elem_info_array,
                        p_is   in integer)
As
  v_OK          integer;
  v_is_compound boolean := case when p_is > 0 then true else false end;
Begin
  For i IN 1..1000 LOOP
    If ( v_is_compound ) Then
      v_OK := is_compound(p_elem);
    Else
      v_OK := has_compound_curves(p_elem);
    End If;
  End Loop;
end test_compound;

Testing reveals:

set serveroutput on size unlimited;
declare 
  v_geom mdsys.sdo_geometry := MDSYS.SDO_GEOMETRY(2006,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,4,2,1,2,2,5,2,1,13,2,1,17,2,1,21,2,1),MDSYS.SDO_ORDINATE_ARRAY(120,178,115,180,110,178,110,175,120,175,120,178,60,15,65,15,50,15,55,15,10,25,20,30,25,25,30,30));
begin
 runstats_pkg.rs_start;
 test_compound(v_geom.sdo_elem_info,0);
 runstats_pkg.rs_middle;
 test_compound(v_geom.sdo_elem_info,1);
 runstats_pkg.rs_stop(10);
end;
/
anonymous block completed
Run1 ran in 0 hsecs
Run2 ran in 90 hsecs
Run 1 ran in 0 % of the time
 
Name                                  Run1        Run2        Diff
LATCH.cache buffers chains             112         126          14
LATCH.channel operations paren           0          14          14
LATCH.checkpoint queue latch             0          16          16
STAT...undo change vector size       2,076       2,144          68
STAT...recursive cpu usage               2          92          90
STAT...CPU used by this sessio           2          92          90
STAT...redo size                     2,724       2,924         200
STAT...calls to get snapshot s           1       1,001       1,000
STAT...execute count                     1       1,001       1,000
STAT...recursive calls                   1       1,003       1,002
LATCH.shared pool                        0       1,029       1,029
LATCH.library cache lock                 8       2,003       1,995
LATCH.library cache pin                 11       2,007       1,996
LATCH.SQL memory manager worka           6       2,073       2,067
LATCH.library cache                     20       3,018       2,998
LATCH.row cache objects                 12      15,024      15,012
 
Run1 latches total versus runs -- difference AND pct
        Run1        Run2        Diff          Pct
         178      25,343      25,165           1%

Conclusion

It does not appear to matter how much effort we put in to making the SQL version of is_compound() more efficient, the non-SQL version will beat us every time. The performance difference is enormous with the gap, after having been close a small amount, immediately widened by removing of the need to “dereference” the sdo_elem_info_array (this reminds me of the performance difference I extracted from my VB6 library when I removed as much “dereferencing” of OraObjects in runtime).

Discussion

First off, even when you use SQL in a function, it doesn’t hurt to do some
profiling and improvement of its performance. In this case what I thought
was a pretty simple, fast piece of SQL, turned out to be capable of being
improved.

Secondly, why do I use SQL when I could use pure programmatic PL/SQL? I started out using programmatic SQL (as Pro Oracle Spatial uses) but I generally found:

The code harder to write when one tries to handle 2, 3 and 4D data in the algorithm;
The time to complete a standard compile-debug-test cycle longer and more complex than using pure SQL;
The code required more documentation.

The thing I liked about using SQL is that it is:

Easier and faster executing a single loop of the standard compile-debug-test cycle;
Declarative and capable of being given to others for executing using a variety of tools (SQLPlus, SQL Developer, TOAD etc);
Self-documenting because of its declarative nature;
Finally, I have always assumed that SQL execution is the Sine qua non (without which there is nothing) of being an Oracle database!

However easier an algorithm is to write in SQL (and not all are), when one has it running correctly (the goal of all programming), if the algorithm is, as is said in project management, “on the critical path”, then it has to be converted to straight PL/SQL (as my commentator has done).

Of course I always meant to test that my SQL approach is NOT orders of magnitude slower than non-SQL approaches. Sadly, my simple testing above shows that that is not the case (well, at least in this case). Obviously the efficiency of one approach versus another depends on how it is used and the sensitivity of the process to the processing time of the function. But since a user has discovered this then, perhaps, now is the time to swap out the SQL algorithm for a much faster PL/SQL based on i.e., his!

Thanks very much to him for bringing my attention to the speed issues of the original isCompound().

I hope others find this article useful.

Implementing the BEST VicGrid Projection in Oracle 10gR2

· 93 days ago by Simon Greener

There is a Lambert Conformal Conic projection used by Computer Aided Dispatch (CAD) in Victoria, Australia that is called BEST VicGrid.

It is not available as part of the standard Oracle install (most Australian projections in are provided to Oracle by Michael Stoke’s Navigate company in Sydney), and so has to be created. Here is how to create and test it.

I have given the new projection the SRID 82476.

DELETE FROM SDO_COORD_REF_SYSTEM 
 WHERE srid = 82476;
COMMIT;
 
SELECT * 
  FROM cs_srs
 WHERE srid = 82476;
 
Prompt Now insert our Best VicGrid....
INSERT INTO SDO_COORD_REF_SYSTEM (
        SRID,
        COORD_REF_SYS_NAME,
        COORD_REF_SYS_KIND,
        COORD_SYS_ID,
        DATUM_ID,
        GEOG_CRS_DATUM_ID,
        SOURCE_GEOG_SRID,
        PROJECTION_CONV_ID,
        CMPD_HORIZ_SRID,
        CMPD_VERT_SRID,
        INFORMATION_SOURCE,
        DATA_SOURCE,
        IS_LEGACY,
        LEGACY_CODE,
        LEGACY_WKTEXT,
        LEGACY_CS_BOUNDS,
        IS_VALID,
        SUPPORTS_SDO_GEOMETRY
)
  VALUES (
        82476,                /* SRID */
        'BEST VICGRID94 Conformal Projection (Australia GDA94)', /* COORD_REF_SYS_NAME */
        'PROJECTED',          /* COORD_REF_SYS_KIND */
        4400,                 /* COORD_SYS_ID */
        NULL,                 /* DATUM_ID -- 'tis NULL because is projected See select * from sdo_coord_ref_sys where GEOG_CRS_DATUM_ID = 20005;*/
        20005,                /* GEOG_CRS_DATUM_ID */
        2000012,              /* SOURCE_GEOG_SRID */
        NULL,                 /* PROJECTION_CONV_ID  - Same as VICGRID94 ie NULL */
        NULL,                 /* CMPD_HORIZ_SRID */
        NULL,                 /* CMPD_VERT_SRID */
        'SpatialDB Advisor',  /* INFORMATION_SOURCE */
	null,                 /* DATA_SOURCE */
        'FALSE',              /* IS_LEGACY */
        NULL,                 /* LEGACY_CODE */
        'PROJCS["BEST VICGRID94 Conformal Projection (Australia GDA94)",GEOGCS [ "Geodetic Datum of Australia 1994", DATUM ["GDA 94", SPHEROID ["GRS 80", 6378137.000000, 298.257222101]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994330]], PROJECTION ["Lambert Conformal Conic"], PARAMETER ["Standard_Parallel_1", -18.000000], PARAMETER ["Standard_Parallel_2", -36.000000],PARAMETER ["Central_Meridian", 135.000000], PARAMETER ["Latitude_Of_Origin", -27.000000], PARAMETER ["False_Easting", 2500000.00000], PARAMETER ["False_Northing", 2500000.00000], UNIT ["Meter", 1.000000000000]]', 
	                      /* LEGACY_WKTEXT */
        NULL,                 /* LEGACY_CS_BOUNDS */
        'FALSE',              /* EPSG record for the coordinate reference system is NOT completely defined */
        'TRUE')               /* SUPPORTS_SDO_GEOMETRY has to be TRUE for PROJECTED CS */
/
COMMIT;

Now let’s conduct some tests. For this I will compare Oracle against the only GIS I own, Manifold GIS.

Prompt Conduct tests....
-- What projections are used in the test?
 
SELECT CS_NAME,SRID,AUTH_SRID,AUTH_NAME,WKTEXT
  FROM cs_srs
 WHERE srid IN (82472,82473,82476,81938,82469);
 
CS_NAME                                                SRID   AUTH_SRID          AUTH_NAME WKTEXT
------------------------------------------------- --------- ----------- ------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
AMG Zone 56 (AGD 66)                                  81938       81938             Oracle PROJCS["AMG Zone 56 (AGD 66)", GEOGCS [ "Australian Geodetic 1966", DATUM ["Australian Geodetic 1966", SPHEROID ["Australian", 6378160, 298.25]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994330]], PROJECTION ["Transverse Mercator"], PARAMETER ["Scale_Factor", 0.999600], PARAMETER ["Central_Meridian", 153.000000], PARAMETER ["False_Easting", 500000.000000], PARAMETER ["False_Northing", 10000000.000000], UNIT ["Meter", 1.000000000000]] 
MGA94 Zone 56                                         82469       82469           Navigate PROJCS["MGA94 Zone 56", GEOGCS [ "Geodetic Datum of Australia 1994", DATUM ["GDA 94", SPHEROID ["GRS 80", 6378137.000000, 298.257222101]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994330]], PROJECTION ["Transverse Mercator"], PARAMETER ["Scale_Factor", 0.999600], PARAMETER ["Central_Meridian", 153.000000], PARAMETER ["False_Easting", 500000.000000], PARAMETER ["False_Northing", 10000000.000000], UNIT ["Meter", 1.000000000000]]
VICGRID66                                             82472       82472           Navigate PROJCS["VICGRID66 Conformal Projection (Australia AGD66)", GEOGCS [ "AGD 66 VIC NSW", DATUM ["AGD 66 VIC NSW", SPHEROID ["Australian", 6378160, 298.25],-119.35,-48.3,139.48,-.42,-.26,-.44,-.61], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994330]], PROJECTION ["Lambert Conformal Conic"], PARAMETER ["Standard_Parallel_1", -36.000000], PARAMETER ["Standard_Parallel_2", -38.000000], PARAMETER ["Central_Meridian", 145.000000], PARAMETER ["Latitude_Of_Origin", -37.000000], PARAMETER ["False_Easting", 2500000.00000], PARAMETER ["False_Northing", 4500000.00000], UNIT ["Meter", 1.000000000000]]
VICGRID94                                             82473       82473           Navigate PROJCS["VICGRID94 Conformal Projection (Australia GDA94)", GEOGCS [ "Geodetic Datum of Australia 1994", DATUM ["GDA 94", SPHEROID ["GRS 80", 6378137.000000, 298.257222101]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994330]], PROJECTION ["Lambert Conformal Conic"], PARAMETER ["Standard_Parallel_1", -36.000000], PARAMETER ["Standard_Parallel_2", -38.000000], PARAMETER ["Central_Meridian", 145.000000], PARAMETER ["Latitude_Of_Origin", -37.000000], PARAMETER ["False_Easting", 2500000.00000], PARAMETER ["False_Northing", 2500000.00000], UNIT ["Meter", 1.000000000000]]
BEST VICGRID94 Conformal Projection (Australia GDA94) 82476       82476  SpatialDB Advisor PROJCS["BEST VICGRID94 Conformal Projection (Australia GDA94)",GEOGCS [ "Geodetic Datum of Australia 1994", DATUM ["GDA 94", SPHEROID ["GRS 80", 6378137.000000, 298.257222101]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994330]], PROJECTION ["Lambert Conformal Conic"], PARAMETER ["Standard_Parallel_1", -18.000000], PARAMETER ["Standard_Parallel_2", -36.000000],PARAMETER ["Central_Meridian", 135.000000], PARAMETER ["Latitude_Of_Origin", -27.000000], PARAMETER ["False_Easting", 2500000.00000], PARAMETER ["False_Northing", 2500000.00000], UNIT ["Meter", 1.000000000000]]
 
5 rows selected
 
Prompt Test Oracle against Manifold GIS ...
Test Oracle against Manifold GIS ...
 
/* Here is the custom preset projection used in Manifold GIS
<xml>
 <preset>
  <name>BEST VICGRID94</name>
  <category>National Grids*Australia</category>
  <system>Lambert Conformal Conic</system>
  <datum>Australian Geocentric 1994 (GDA94)</datum>
  <centerLat>-27.0</centerLat> 
  <centerLon>135.0</centerLon> 
  <firstStdLat>-18.0</firstStdLat> 
  <secondStdLat>-36.0</secondStdLat> 
  <falseEasting>2500000.0</falseEasting> 
  <falseNorthing>2500000.0</falseNorthing> 
 </preset>
</xml>
*/
 
SELECT round(a.geom.sdo_point.x,6) as OX,
       round(a.geom.sdo_point.y,6) as OY,
       round(a.manifoldx,6) as Mx,
       round(a.manifoldy,6) as My
  FROM (SELECT SDO_CS.TRANSFORM(MDSYS.SDO_GEOMETRY(2001,82469,SDO_POINT_TYPE(300000,7800000,NULL),NULL,NULL), 82476) as geom,
               4172735.1951413983 as ManifoldX,
               3173070.9409930659 as ManifoldY
          FROM DUAL
       ) a;
 
OX                     OY                     MX                     MY
---------------------- ---------------------- ---------------------- ---------------------- 
4172735.195141         3173070.940993         4172735.195141         3173070.940993
 
1 rows selected

I hope this article is of use to the odd Victorian Oracle user!

Making Sdo Geometry Metadata Update Generic Code

· 93 days ago by Simon Greener

As a part of my last article on tracking down and correcting ORA-13011 errors when using SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT() , I showed how to update the diminfo structure associated with a table’s geometry column in its USER_SDO_GEOM_METADATA entry.

Here was the code in that article.

UPDATE user_sdo_geom_metadata 
   SET diminfo = ( SELECT MDSYS.SDO_DIM_ARRAY( 
                                MDSYS.SDO_DIM_ELEMENT('X', minx, maxx, 0.05), 
                                MDSYS.SDO_DIM_ELEMENT('Y', miny, maxy, 0.05)) as diminfo 
                     FROM ( SELECT TRUNC( MIN( v.x ) - 1,0) as minx,
                                   ROUND( MAX( v.x ) + 1,0) as maxx,
                                   TRUNC( MIN( v.y ) - 1,0) as miny,
                                   ROUND( MAX( v.y ) + 1,0) as maxy
                              FROM (SELECT SDO_AGGR_MBR(a.geometry) as mbr
                                      FROM rectangles a) b,
                                           TABLE(mdsys.sdo_util.getvertices(b.mbr)) v
                           )
                 )
 WHERE table_name = 'RECTANGLES'
   AND column_name = 'GEOMETRY';

Now, that code is pretty neat, but it has in it a few things that need changing every time you want to use it for another table’s geometry column, or you want to change the MBR shrinkage/expansion factor (ie – 1/+ 1), or you can’t remember the sdo_tolerance values (in the above, 0.05).

What we need to do is encapsulate this in a PL/SQL Procedure that you can call anytime you need to. Here is one such implementation that is, I believe, generic enough to do all that is required.

   /** function    UpdateSdoMetadata
   *   description Updates 2D spatial extent of DIMINFO associated with table/column in all_sdo_geom_metadata
   *   param       p_table_name  The object containing the spatal data.
   *   param       p_column_name The sdo_geometry column to be analyzed.
   *   param       p_mbr_factor  Expansion/Shrinkage amount for MBR of current data.
   *   param       p_commit      Whether to commit the update.
   */
Create Or Replace
   Procedure UpdateSdoMetadata( p_table_name  in varchar2,
                                p_column_name in varchar2,
                                p_mbr_factor  in number,
                                p_commit      in boolean := false )
   As
     v_mbr_factor number := case when p_mbr_factor is null then 0 else p_mbr_factor end;
     v_diminfo    mdsys.sdo_dim_array;
   Begin
     -- Check if something to process
     If ( p_table_name is null or p_column_name is null ) Then
       Return;
     End If;
 
     -- Get existing record (checks if one even exists)
     --
     SELECT diminfo
       INTO v_diminfo
       FROM user_sdo_geom_metadata
      WHERE table_name  = UPPER(p_table_name)
        AND column_name = UPPER(p_column_name);
 
      -- Update the diminfo with the MBR of the existing data
      EXECUTE IMMEDIATE 'SELECT MDSYS.SDO_DIM_ARRAY( 
                                MDSYS.SDO_DIM_ELEMENT(''X'', minx, maxx, :1), 
                                MDSYS.SDO_DIM_ELEMENT(''Y'', miny, maxy, :2)) as diminfo 
                     FROM ( SELECT TRUNC( MIN( v.x ) - :3,0) as minx,
                                   ROUND( MAX( v.x ) + :4,0) as maxx,
                                   TRUNC( MIN( v.y ) - :5,0) as miny,
                                   ROUND( MAX( v.y ) + :6,0) as maxy
                              FROM (SELECT SDO_AGGR_MBR(a.' || p_column_name || ') as mbr
                                      FROM ' || p_table_name || ' a) b,
                                           TABLE(mdsys.sdo_util.getvertices(b.mbr)) v
                           )'
                 INTO v_diminfo
                USING v_diminfo(1).sdo_tolerance, 
                      v_diminfo(2).sdo_tolerance,
                      v_mbr_factor,v_mbr_factor,v_mbr_factor,v_mbr_factor;
                       
     -- Now update the existing record
     --
     UPDATE user_sdo_geom_metadata 
        SET diminfo     = v_diminfo
      WHERE table_name  = UPPER(p_table_name)
        AND column_name = UPPER(p_column_name);
 
    -- Commit if requested
    If ( p_commit ) Then
      commit;
    End If;
 
    Return;
 
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
         raise_application_error(-20000, 'No SDO_METADATA record exists for ' || p_table_name || '.' || p_column_name || '. Run MetadataAnalayzer');
   End UpdateSdoMetadata;

I’ve placed this in the TOOLS package in my free PL/SQL packages.

I hope the code is useful to someone.

ORA-13011 errors when using SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT()

· 96 days ago by Simon Greener

I was sent a request for help the other day from some I know overseas:

Do you know the true meaning and impact of ‘ORA-13011 value is out of range’? I have a dataset with a lot of very (almost impossible) precise data coming from Bentley microstation. And we have a ‘businessrule’ that the default tolerance in all the tables is 0.005. Any suggestion how to react on a ORA-13011 error when using sdo_geom.validate_layer_with_context

What was not said till a later email was that (my italics highlight what I suspected):

We use FME to transform … DGN-files to Oracle Spatial. An existing table is truncated and refilled with geometries. Because the tables already exist, they are already registered in de user_metadata and FME is not updating it.

Answer

Let’s construct an example that you can all try that shows this problem.

First, let’s create a 10,000 rectangular polygons inside this area.

               +------------------------+ 608222 5497524
               |                        |
               |                        |
               |                        |
               |                        |
               |                        |
               |                        |
294829 5162028 +------------------------+

DROP TABLE rectangles;
  DROP TABLE rectangles succeeded.
 
CREATE TABLE rectangles ( 
  rect_id    Integer primary key,
  geometry   mdsys.sdo_geometry
);
  CREATE TABLE succeeded.
 
INSERT /*+APPEND*/ INTO rectangles (rect_id,geometry)
SELECT rownum as rect_id,
       mdsys.sdo_geometry(2003,28355,NULL,mdsys.sdo_elem_info_array(1,1003,3),
                   MDSYS.SDO_ORDINATE_ARRAY(ROUND(w.x - (w.rectWidth/2),w.rndf), ROUND(w.y - (w.rectHeight/2),w.rndf),
                                            ROUND(w.x + (w.rectWidth/2),w.rndf), ROUND(w.y + (w.rectHeight/2),w.rndf) )
                         ) as geometry
 FROM ( SELECT ROUND(dbms_random.value(w.x - ( w.WinWidth  / 2 ), w.x + ( w.WinWidth  / 2 )),w.rndf) as x,
               ROUND(dbms_random.value(w.y - ( w.WinHeight / 2 ), w.y + ( w.WinHeight / 2 )),w.rndf) as y,
               dbms_random.value(50, 1000)  as rectWidth, 
               dbms_random.value(50, 1000)  as rectHeight,
               w.rndf
          FROM ( SELECT ( 294000  + 609000  ) / 2    as X,
                        ( 5162000 + 5498000 ) / 2    as y,
                        ( 5498000 - 5162000 )        as WinHeight,
                        ( 609000  - 294000  )        as WinWidth,
                        10000                        as RectCount,
                        ROUND(log(10,1/0.05))+1      as rndf
                   FROM DUAL 
               ) w,
               TABLE(CAST(MULTISET(select level from dual connect by level <= w.RectCount) as SYS.UTL_NLA_ARRAY_INT)) s
      ) w;
  10000 rows inserted
 
ALTER TABLE rectangles LOGGING;
  ALTER TABLE rectangles succeeded.
 
COMMIT;
  commited

Let’s summary what was inserted into the table…

SELECT count(*) || ' rectangles were stored' As Result
  FROM rectangles;
 
RESULT                                                          
--------------------------------------------------------------- 
10000 rectangles were stored

Now, let’s create an Oracle Metadata entry that is DELIBERATELY too small.

DELETE FROM user_sdo_geom_metadata WHERE table_name = 'RECTANGLES';
1 rows deleted
 
COMMIT;
commited
 
INSERT INTO user_sdo_geom_metadata (  
   table_name, 
   column_name, 
   diminfo, 
   srid 
) VALUES (
   'RECTANGLES',
   'GEOMETRY', 
   MDSYS.SDO_DIM_ARRAY( 
         MDSYS.SDO_DIM_ELEMENT(
               'X', 
               ( ( 294000  + 609000  ) / 2 ) - 1000,
               ( ( 294000  + 609000  ) / 2 ) + 1000,
               0.05), 
         MDSYS.SDO_DIM_ELEMENT(
               'Y', 
               ( ( 5162000 + 5498000 ) / 2 ) - 1000,
               ( ( 5162000 + 5498000 ) / 2 ) + 1000,
               0.05)
   ), 
   28355
);
1 rows inserted
 
COMMIT;
commited

Now, let’s run SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT;

DROP   TABLE rectangles_v;
  DROP TABLE rectangles_v succeeded.
 
CREATE TABLE rectangles_v (sdo_rowid ROWID, result varchar2(1000));
  CREATE TABLE succeeded.
 
EXECUTE SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT('RECTANGLES','GEOMETRY','RECTANGLES_V');
  anonymous block completed
 
SELECT result, count(*) as rcount
  FROM rectangles_v
 GROUP BY result;
 
RESULT                    RCOUNT                 
------------------------- -----
13011                     10000                  
Rows Processed <10000>    1                      
 
2 rows selected

This indicates that of the 10,000 rectangles processed all are invalid (see following) as “The result table contains one row for each invalid geometry”.

As a check, let’s run SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT() on a 10% sample of all the rectangles in the table:

SELECT DISTINCT SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(a.geometry,b.diminfo)
  FROM RECTANGLES SAMPLE (10) a,
       user_sdo_geom_metadata b
 WHERE b.table_name  = 'RECTANGLES' 
   AND b.column_name = 'GEOMETRY';
 
SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(A.GEOMETRY,B.DIMINFO)
-------------------------------------------------------------
13011
 
1 rows selected

Notice how, in both cases, we get the Oracle error 13011 which is:

ORA-13011: value is out of range
    Cause: A specified dimension value is outside the range defined for that dimension.
    Action: Make sure that all values to be encoded are within the defined dimension range.

Now, let’s change the user_sdo_geom_metadata to be correct by calculating it from the actual data….

UPDATE user_sdo_geom_metadata 
   SET diminfo = ( SELECT MDSYS.SDO_DIM_ARRAY( 
                                MDSYS.SDO_DIM_ELEMENT('X', minx, maxx, 0.05), 
                                MDSYS.SDO_DIM_ELEMENT('Y', miny, maxy, 0.05)) as diminfo 
                     FROM ( SELECT TRUNC( MIN( v.x ) - 1,0) as minx,
                                   ROUND( MAX( v.x ) + 1,0) as maxx,
                                   TRUNC( MIN( v.y ) - 1,0) as miny,
                                   ROUND( MAX( v.y ) + 1,0) as maxy
                              FROM (SELECT SDO_AGGR_MBR(a.geometry) as mbr
                                      FROM rectangles a) b,
                                           TABLE(mdsys.sdo_util.getvertices(b.mbr)) v
                           )
                 )
 WHERE table_name = 'RECTANGLES'
   AND column_name = 'GEOMETRY';
 
1 rows updated
 
COMMIT;
commited

Now, let’s re-run the validate_layer_with_context….

DROP   TABLE rectangles_v;
 DROP TABLE rectangles_v succeeded.
 
CREATE TABLE rectangles_v (sdo_rowid ROWID, result varchar2(1000));
 CREATE TABLE succeeded.
 
EXECUTE SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT('RECTANGLES','GEOMETRY','RECTANGLES_V');
 anonymous block completed
 
SELECT result, count(*) as rcount
  FROM rectangles_v
 GROUP BY result;
 
RESULT                    RCOUNT
------------------------- ------
Rows Processed <10000>    1
 
2 rows selected

SDO_GEOM.VALIDATE_LEYER_WITH_CONTEXT does not write a row if a geometry is valid. There are no rows in the table other than the row that is written indicating how many rows have been processed.

This can be checked by running SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT() against the whole layer in an ordinary SQL statement:

SELECT DISTINCT SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(a.geometry,b.diminfo)
  FROM RECTANGLES a,
       user_sdo_geom_metadata b
 WHERE b.table_name  = 'RECTANGLES' 
   AND b.column_name = 'GEOMETRY';
 
SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(A.GEOMETRY,B.DIMINFO)
-------------------------------------------------------------
TRUE
 
1 rows selected

I hope this is of help to someone.

>>Tips and Tricks