Top 5 Recent Articles
ARTICLES CATEGORIES
- Algorithms (22)
- All (399)
- Biography (1)
- Blog (44)
- Business Requirements (1)
- Commentary (1)
- Conversion (2)
- Customers (2)
- Data Models (1)
- Education (2)
- GeoRaptor (13)
- GPS (1)
- Image Processing (2)
- Import Export (8)
- Licensing (2)
- LiDAR (1)
- Linear Referencing (4)
- Manifold GIS (3)
- Mapping (1)
- MySQL Spatial (7)
- Networking and Routing (including Optimization) (5)
- Open Source (18)
- Oracle Spatial and Locator (194)
- Partitioning (1)
- PostGIS (36)
- Projections (1)
- Published Articles (1)
- qGIS (1)
- Recommendations (1)
- Services (1)
- Software Change Log (1)
- Source Code (37)
- Space Curves (9)
- Spatial Database Functions (109)
- Spatial DB comparison (1)
- Spatial XML Processing (11)
- SQL Server Spatial (92)
- Standards (3)
- Stored Procedure (17)
- Tessellation or Gridding (10)
- Tools (2)
- Topological Relationships (1)
- Training (2)
- Triangulation (2)
Some SDO_GEOMETRY/DIMINFO handling functions
While at my current contract employer, two functions were required to handle the conversion of DIMINFO SDO_LB/SDO_UB ranges to optimised rectangles (SDO_GEOMETRY) and the reverse.
Note: GeoRaptor provides such capability for selected DIMINFO objects or to convert a map extent to its equivalent DIMINFO structure.
The functions, written in PL/SQL are:
- ST_Diminfo2Rectangle
- ST_Geom2Diminfo
The definition of ST_Diminfo2Rectangle is as follows:
CREATE OR REPLACE FUNCTION ST_Diminfo2Rectangle( p_dim_array IN mdsys.sdo_dim_array, p_srid IN INTEGER DEFAULT NULL) RETURN mdsys.sdo_geometry deterministic AS v_ords mdsys.sdo_ordinate_array := NEW mdsys.sdo_ordinate_array(NULL); v_ord pls_integer; v_dims pls_integer; v_out_geom mdsys.sdo_geometry; BEGIN IF ( p_dim_array IS NULL ) THEN RETURN NULL; END IF; -- Has p_dim_array sufficient elements IF ( p_dim_array.COUNT < 2 ) THEN RETURN NULL; -- Or SDO_GEOMETRY(NULL,NULL...)? END IF; -- Convert -- Assume first two sdo_dim_elements are X and Y (or Long/Lat -- ie don't bother checking dim name -- v_dims := CASE WHEN p_dim_array.COUNT > 4 THEN 4 ELSE p_dim_array.COUNT END; v_ords.DELETE; v_ords.EXTEND(v_dims * 2); v_ords(1) := p_dim_array(1).sdo_lb; -- X v_ords(2) := p_dim_array(2).sdo_lb; -- Y v_ord := 3; IF ( p_dim_array.COUNT > 2 ) THEN v_ords(v_ord) := p_dim_array(3).sdo_lb; v_ord := v_ord + 1; IF ( p_dim_array.COUNT > 3 ) THEN v_ords(v_ord) := p_dim_array(4).sdo_lb; v_ord := v_ord + 1; END IF; END IF; v_ords(v_ord) := p_dim_array(1).sdo_ub; -- X v_ord := v_ord + 1; v_ords(v_ord) := p_dim_array(1).sdo_ub; -- Y v_ord := v_ord + 1; IF ( p_dim_array.COUNT > 2 ) THEN v_ords(v_ord) := p_dim_array(3).sdo_ub; v_ord := v_ord + 1; IF ( p_dim_array.COUNT > 3 ) THEN v_ords(v_ord) := p_dim_array(4).sdo_ub; END IF; END IF; RETURN mdsys.sdo_geometry(v_dims * 1000 + 3, p_srid, NULL, sdo_elem_info_array(1,1003,3), v_ords); END ST_Diminfo2Rectangle; / SHOW ERRORS
Testing ST_Diminfo2Rectangle generates the following:
SELECT TABLE_NAME, column_name, ST_Diminfo2Rectangle(diminfo,srid) AS dGeom FROM user_sdo_geom_metadata; -- Results TABLE_NAME COLUMN_NAME DGEOM ------------- ----------- --------------------------------------------------------------------------------------- WW_ADMIN_AREA GEOM SDO_GEOMETRY(2003,82469,NULL, SDO_ELEM_INFO_ARRAY(1,1003,3), SDO_ORDINATE_ARRAY(401464.379,6383805.94,476727.39089109,476727.39089109))
The definition of ST_Geom2DimInfo is as follows:
CREATE OR REPLACE
FUNCTION St_Geom2Diminfo(P_Geom IN Sdo_Geometry,
P_Srid IN NUMBER DEFAULT NULL,
P_Tolerance IN NUMBER DEFAULT 0.005,
p_is_geodetic IN Varchar2 DEFAULT 'N')
RETURN Mdsys.Sdo_Dim_Array Deterministic
AS
V_Dims Pls_Integer;
v_tolerance NUMBER := Nvl(P_Tolerance,0.005);
v_x_dimname varchar2(5) := CASE WHEN UPPER(NVL(p_is_geodetic,'Y')) IN ('Y','TRUE') THEN 'LONG' ELSE 'X' END;
v_y_dimname varchar2(5) := CASE WHEN UPPER(NVL(p_is_geodetic,'Y')) IN ('Y','TRUE') THEN 'LAT' ELSE 'Y' END;
BEGIN
IF (P_Geom IS NULL OR P_Geom.Sdo_Ordinates IS NULL) THEN
RETURN NULL;
END IF;
V_Dims := P_Geom.Get_Dims();
RETURN
CASE WHEN V_Dims = 2
THEN Mdsys.Sdo_Dim_Array(
Mdsys.Sdo_Dim_Element(V_X_Dimname, P_Geom.Sdo_Ordinates(1), P_Geom.Sdo_Ordinates(v_dims+1), v_tolerance),
Mdsys.Sdo_Dim_Element(V_Y_Dimname, P_Geom.Sdo_Ordinates(2), P_Geom.Sdo_Ordinates(v_dims+2), v_tolerance))
WHEN V_Dims = 3
THEN Mdsys.Sdo_Dim_Array(
Mdsys.Sdo_Dim_Element(V_X_Dimname, P_Geom.Sdo_Ordinates(1), P_Geom.Sdo_Ordinates(v_dims+1), v_tolerance),
Mdsys.Sdo_Dim_Element(V_Y_Dimname, P_Geom.Sdo_Ordinates(2), P_Geom.Sdo_Ordinates(v_dims+2), v_tolerance),
Mdsys.Sdo_Dim_Element(CASE WHEN P_Geom.Get_Lrs_Dim() IN (0,4) THEN 'Z' ELSE 'M' END,
P_Geom.Sdo_Ordinates(3), P_Geom.Sdo_Ordinates(v_dims+3), v_tolerance))
WHEN V_Dims = 4
THEN Mdsys.Sdo_Dim_Array(
Mdsys.Sdo_Dim_Element(V_X_Dimname, P_Geom.Sdo_Ordinates(1), P_Geom.Sdo_Ordinates(v_dims+1), v_tolerance),
Mdsys.Sdo_Dim_Element(V_Y_Dimname, P_Geom.Sdo_Ordinates(2), P_Geom.Sdo_Ordinates(v_dims+2), v_tolerance),
Mdsys.Sdo_Dim_Element(CASE WHEN P_Geom.Get_Lrs_Dim() IN (0,4) THEN 'Z' ELSE 'M' END,
P_Geom.Sdo_Ordinates(3), P_Geom.Sdo_Ordinates(v_dims+3), v_tolerance),
Mdsys.Sdo_Dim_Element(CASE WHEN P_Geom.Get_Lrs_Dim() = 4 THEN 'M' ELSE 'Z' END,
P_Geom.Sdo_Ordinates(4), P_Geom.Sdo_Ordinates(v_dims+4), v_tolerance))
ELSE NULL
END;
END St_Geom2Diminfo;
/
SHOW ERRORS
Testing ST_Geom2DimInfo generates the following:
--
-- Firstly a generic geometry object
--
SELECT ST_Geom2Diminfo(
SDO_GEOMETRY(3003,NULL,NULL,
SDO_ELEM_INFO_ARRAY(1,1003,3),
SDO_ORDINATE_ARRAY(-0.8736,-0.933335,0.303673, 0.855034,0.571309,0.972036)),
3112,0.005,'y') AS diminfo
FROM dual;
--
-- Output modified
--
DIMINFO
--------------------------------------------------------------
SDO_DIM_ARRAY(SDO_DIM_ELEMENT('LONG',-0.8736,0.855034,0.005),
SDO_DIM_ELEMENT('LAT',-0.933335,0.571309,0.005),
SDO_DIM_ELEMENT('Z',0.303673,0.972036,0.005))
--
-- Secondly, a practical example of how to convert an RTree's sdo_root_mbr into a DIMINFO.
--
SELECT sdo_root_mbr, srid,
st_geom2diminfo(sdo_root_mbr,asgm.srid,0.005,a.SDO_INDEX_GEODETIC) AS diminfo
FROM MDSYS.SDO_INDEX_METADATA_TABLE a
INNER JOIN
all_indexes ai
ON ( ai.owner = a.sdo_index_owner
AND ai.index_name = a.sdo_index_name)
INNER JOIN
all_sdo_geom_metadata asgm
ON (asgm.owner = ai.owner AND asgm.TABLE_NAME = ai.TABLE_NAME AND asgm.column_name = REPLACE(a.sdo_column_name,'"',''))
WHERE sdo_root_mbr IS NOT NULL
AND a.sdo_root_mbr.sdo_gtype <> 2000
AND a.sdo_root_mbr IS NOT NULL;
--
-- Output has been modified.
--
SDO_ROOT_MBR
------------------------------------------------------------------------------------------------------------------------------------------------
SRID
----
DIMINFO
------------------------------------------------------------------------------------------------------------------------------------------------
SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),
SDO_ORDINATE_ARRAY(-13874494.858,2820483.008,-7452778.538,6337725.438))
3785 SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X',-13874494.858,-7452778.538,0.005),
SDO_DIM_ELEMENT('Y',2820483.008,6337725.438,0.005))
SDO_GEOMETRY(3008,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1007,3),
SDO_ORDINATE_ARRAY(-0.432523148154909,-0.902161835066299,0.414984208015312,0.277693714719153,-0.544627601772466,0.759107954517766))
4326 SDO_DIM_ARRAY(SDO_DIM_ELEMENT('LONG',-0.432523148154909,0.277693714719153,0.005),
SDO_DIM_ELEMENT('LAT',-0.902161835066299,-0.544627601772466,0.005),
SDO_DIM_ELEMENT('Z',0.414984208015312,0.759107954517766,0.005))
SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),
SDO_ORDINATE_ARRAY(5979762.10717428,2085798.82445402,6024890.06350611,2130875.57353918))
2872 SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X',5979762.10717428,6024890.06350611,0.005),SDO_DIM_ELEMENT('Y',2085798.82445402,2130875.57353918,0.005))
SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),
SDO_ORDINATE_ARRAY(401099.41,6383793.51,476711.23,6483905.25))
82469 SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X',401099.41,476711.23,0.005),
SDO_DIM_ELEMENT('Y',6383793.51,6483905.25,0.005))
4 ROWS selected
I hope these are of use to someone.