## Top 5 Recent Articles

##### ARTICLES CATEGORIES

- Biography (1)
- Blog (41)
- Changes (1)
- Customers (1)
- Data Models (1)
- Education (2)
- General Software (21)
- Georaptor Blog (5)
- Image Catalog (2)
- Licensing (1)
- ManifoldGIS (3)
- MySQL Blog (4)
- MySQL Spatial (3)
- Networking and Routing (including Optimization) (3)
- Oracle Spatial (171)
- Philosophy (1)
- PostGIS (30)
- Press Releases (1)
- Source code (24)
- Space Curves (1)
- Spatial DB comparison (1)
- SQL (1)
- SQL Server Blog (58)
- SQL Server Spatial (General) (15)
- SQL Server Spatial (LRS) (37)
- Stored Procedure (2)
- Training (1)
- XML (5)

# Alternative to my SQL based GetNumRings function

When creating a new PL/SQL procedure or function, where possible, I write the algorithm in SQL first. Once the SQL is done, it then becomes comparatively easy to encapsulate the SQL in a PL/SQL procedure or function for generic use.

This declarative approach is wonderful as the SQL statement can be developed independently of any programming language or framework, understood by SQL literate people who may not necessarily understand programming, and can shared with others by pasting it into an email to a colleague.

However, the SQL approach comes with a price: it is far slower than its more direct, programmatic version. This is due to the issues relating to SQL optimisation and the orchestrating of the execution between Oracle’s SQL and PL/SQL engines.

Recently I have had need to take previous, SQL based, functions and convert them to their programmatic equivalent. The GetNumRings function I previously blogged on is one I have re-written to be faster.

Here is the new version of the function.

create or replace Function GetNumRings( p_geometry in mdsys.sdo_geometry, p_ring_type in integer default 0 /* 0 = ALL; 1 = OUTER; 2 = INNER */ ) Return Number Is v_ring_count number := 0; v_ring_type number := p_ring_type; v_elements number; v_etype pls_integer; Begin If ( p_geometry is null ) Then return 0; End If; If ( p_geometry.sdo_elem_info is null ) Then return 0; End If; If ( v_ring_type not in (0,1,2) ) Then v_ring_type := 0; End If; v_elements := ( ( p_geometry.sdo_elem_info.COUNT / 3 ) - 1 ); <<element_extraction>> for v_i IN 0 .. v_elements LOOP v_etype := p_geometry.sdo_elem_info(v_i * 3 + 2); If ( v_etype in (1003,1005,2003,2005) and 0 = v_ring_type ) OR ( v_etype in (1003,1005) and 1 = v_ring_type ) OR ( v_etype in (2003,2005) and 2 = v_ring_type ) Then v_ring_count := v_ring_count + 1; end If; end loop element_extraction; Return v_ring_count; End GetNumRings;

I have not yet run Oracle’s DBMS_PROFILER over the two functions but will do so when I get time (or someone out there reminds me of this).

I did conduct a comparison test with the previous version. The old version is called GetNumRingsSQL just for convenience.

select a.id, getnumringsSQL(a.geometry) as old_getnumrings, getnumrings(b.geometry) as new_getnumrings from Oracle_Test_Geometries a inner join Oracle_Test_Geometries b on ( b.id = a.id) where a.geometry is not null and a.geometry.sdo_elem_info is not null and getnumringsSQL(a.geometry) <> getnumrings(b.geometry) order by 1 ; ID OLD_GETNUMRINGS NEW_GETNUMRINGS ---------------------- ---------------------- ----------------------

No rows… which is the right answer. In vestigating further.

select a.id, getnumringsSQL(a.geometry) as old_getnumrings, getnumrings(b.geometry) as new_getnumrings from Oracle_Test_Geometries a inner join Oracle_Test_Geometries b on ( b.id = a.id) where a.geometry is not null and a.geometry.sdo_elem_info is not null and getnumringsSQL(a.geometry) <> 0 order by 1 ; ID OLD_GETNUMRINGS NEW_GETNUMRINGS -- --------------- --------------- 11 1 1 12 1 1 13 1 1 14 1 1 15 1 1 23 2 2 24 2 2 25 2 2 26 2 2 27 2 2 28 2 2 29 1 1 30 1 1 31 1 1 32 3 3 35 1 1 36 2 2 37 1 1 39 2 2 50 2 2 51 2 2 52 2 2 54 1 1 55 1 1 59 1 1 65 1 1 66 1 1 67 1 1 141 2 2 201 1 1 202 9 9 203 12 12 204 3 3 205 1 1 34 rows selected

I hope this helps someone.

## Documentation

- MySQL Spatial General Function Documentation
- Oracle Spatial Exporter Package Documentation
- Oracle Spatial Object Function Documentation
- Oracle Spatial Object Function Documentation (Multi Page Version)
- PostGIS pl/pgSQL Function Documentation
- SC4O Oracle Java Topology Suite (Stored Procedures) Package Documentation
- SQL Server Spatial General TSQL Function Documentation
- SQL Server Spatial LRS TSQL Function Documentation