Go to content Go to navigation and search

Home

Current Oracle Spatial Blog Articles


Search

Browse

RSS / Atom

Email me

textpattern

Creative Commons License
All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

Sorting SDO_GEOMETRY data using the ORDER BY clause of a SELECT statement

Saturday May 12 2012 at 12:12

Keywordsorder by sort sdo_geometry select
Summary

This article gives one worked example of how to create a custom sdo_geometry type that can be used in the Order By clause of the Select statement.

It is often observed that you cannot use an sdo_geometry object in an ORDER BY clause of a SELECT statement.

This is demonstrated below.

Test Data

  1. DROP TABLE rPoints;
  2. -- Result
  3. TABLE RPOINTS dropped.
  4. CREATE TABLE rPoints AS
  5. SELECT ROW_NUMBER() OVER (partition BY 1 ORDER BY rownum) AS point_id,
  6.        sdo_geometry(2001,NULL,
  7.                     sdo_point_type(round(dbms_random.VALUE(10000,19999),3),
  8.                                    round(dbms_random.VALUE(20000,29999),3),
  9.                                    NULL),NULL,NULL) AS pointGeom
  10.   FROM dual
  11. CONNECT BY LEVEL < 10;
  12. -- Result
  13. TABLE RPOINTS created.
  14. -- Now ensure we have two geometries that share the same X but different Y
  15. -- Duplicate a single point's x but with different Y
  16. INSERT INTO rPoints (point_id,pointGeom)
  17. SELECT a.point_id + 1,
  18.        sdo_geometry(a.pointGeom.sdo_gtype,
  19.                     a.pointGeom.sdo_gtype,
  20.                     sdo_point_type(a.pointGeom.sdo_point.x,
  21.                                    a.pointGeom.sdo_point.y - 50,
  22.                                    NULL),
  23.                     NULL,NULL)
  24.   FROM rPoints a
  25.  WHERE a.point_id = (SELECT MAX(point_id) FROM rPoints);
  26. -- Result
  27. 1 Point inserted
  28. commit;
  29. -- Result
  30. committed
  31. -- show points in created order...
  32. SELECT a.point_id, a.pointGeom
  33.   FROM rPoints a;
  34. -- Result
  35. POINT_ID POINTGEOM
  36. -------- --------------------------------------------------------------------------
  37. 1        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(14252.91,29835.248,NULL),NULL,NULL)
  38. 2        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(16440.85,26061.832,NULL),NULL,NULL)
  39. 3        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(11697.227,28402.344,NULL),NULL,NULL)
  40. 4        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(18393.804,27436.371,NULL),NULL,NULL)
  41. 5        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(15213.122,20520.035,NULL),NULL,NULL)
  42. 6        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(16536.188,28199.532,NULL),NULL,NULL)
  43. 7        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(12554.798,27858.246,NULL),NULL,NULL)
  44. 8        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(16104.656,29251.359,NULL),NULL,NULL)
  45. 9        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(17592.891,21333.784,NULL),NULL,NULL)
  46. 10       SDO_GEOMETRY(2001,2001,SDO_POINT_TYPE(17592.891,21283.784,NULL),NULL,NULL)
  47.  10 ROWS selected

Now, if we try and sort this data we get:

  1. SELECT a.point_id, a.pointGeom
  2.   FROM rPoints a
  3.  ORDER BY a.pointGeom;
  4. -- Result
  5. Error starting at line 68 IN command:
  6. SELECT a.point_id, a.pointGeom
  7.   FROM rPoints a
  8.  ORDER BY a.pointGeom
  9. -- Result
  10. Error at Command Line:68 COLUMN:19
  11. Error report:
  12. SQL Error: ORA-22901: cannot compare VARRAY OR LOB attributes OF an object TYPE
  13. 22901. 00000 -  "cannot compare nested table or VARRAY or LOB attributes of an object type"
  14. *Cause:    Comparison OF nested TABLE OR VARRAY OR LOB attributes OF an
  15.            object TYPE was attempted IN the absence OF a MAP OR ORDER
  16.            method.
  17. *Action:   define a MAP OR ORDER method FOR the object TYPE.

If you look at the SDO_GEOMETRY type you will see that it does not have a MAP or ORDER method defined.

  1. DESCRIBE mdsys.sdo_geometry
  2. -- Result
  3. USER TYPE definition
  4. ---------------------------------------------------------------
  5. TYPE SDO_GEOMETRY          AS OBJECT (
  6.            SDO_GTYPE       NUMBER,
  7.            SDO_SRID        NUMBER,
  8.            SDO_POINT       SDO_POINT_TYPE,
  9.            SDO_ELEM_INFO   SDO_ELEM_INFO_ARRAY,
  10.            SDO_ORDINATES   SDO_ORDINATE_ARRAY,
  11.            MEMBER FUNCTION  GET_GTYPE
  12.            RETURN NUMBER DETERMINISTIC,
  13.            MEMBER FUNCTION  GET_DIMS
  14.            RETURN NUMBER DETERMINISTIC,
  15.            MEMBER FUNCTION  GET_LRS_DIM
  16.            RETURN NUMBER DETERMINISTIC)
  17.   ALTER TYPE SDO_GEOMETRY
  18.   ADD MEMBER FUNCTION GET_WKB RETURN BLOB DETERMINISTIC,
  19.   ADD MEMBER FUNCTION GET_WKT RETURN CLOB DETERMINISTIC,
  20.   ADD MEMBER FUNCTION ST_CoordDim RETURN SMALLINT DETERMINISTIC,
  21.   ADD MEMBER FUNCTION ST_IsValid RETURN INTEGER DETERMINISTIC,
  22.   ADD CONSTRUCTOR FUNCTION SDO_GEOMETRY(wkt IN CLOB,
  23.             srid IN INTEGER DEFAULT NULL) RETURN SELF AS RESULT,
  24.   ADD CONSTRUCTOR FUNCTION SDO_GEOMETRY(wkt IN VARCHAR2,
  25.             srid IN INTEGER DEFAULT NULL) RETURN SELF AS RESULT,
  26.   ADD CONSTRUCTOR FUNCTION SDO_GEOMETRY(wkb IN BLOB,
  27.             srid IN INTEGER DEFAULT NULL) RETURN SELF AS RESULT
  28.   CASCADE;

Now, we can’t really add a MAP/ORDER method to Oracle’s data type. But we can create a minimalistic custom object type to do so.

  1. CREATE OR REPLACE TYPE T_Geometry AS OBJECT (
  2.    geometry  mdsys.sdo_geometry,
  3.    tolerance NUMBER,
  4.    centroid  INTEGER,   -- sdo_centroid is slower for polygons than using first coordinate - 0/direct; 1/Centroid
  5.    ORDER member FUNCTION orderBy(p_compare_geom IN T_Geometry)
  6.    RETURN NUMBER
  7. );
  8. /
  9. SHOW errors
  10. CREATE OR REPLACE
  11. TYPE BODY T_Geometry
  12. AS
  13.    ORDER Member FUNCTION orderBy(p_compare_geom IN T_Geometry)
  14.    RETURN NUMBER
  15.    IS
  16.       v_geom         sdo_geometry;
  17.       v_compare_geom sdo_geometry;
  18.       PROCEDURE getSortPoint
  19.       AS
  20.       BEGIN
  21.         -- Get centroid only for polygons. Lines and Points use direct coordinate access
  22.         IF ( SELF.centroid <> 0 AND SELF.geometry.get_type() IN (3,7) THEN
  23.           v_geom         := sdo_geom.sdo_centroid(SELF.geometry,SELF.tolerance);
  24.           v_compare_geom := sdo_geom.sdo_centroid(p_compare_geom.geometry,SELF.tolerance);
  25.         ELSE
  26.           IF ( SELF.geometry.sdo_point IS NOT NULL ) THEN
  27.              v_geom := sdo_geometry(2001,NULL,
  28.                                     sdo_point_type(SELF.geometry.sdo_point.x,
  29.                                                    SELF.geometry.sdo_point.y,
  30.                                                    NULL),
  31.                                     NULL,NULL);
  32.           ELSE
  33.              v_geom := sdo_geometry(2001,NULL,
  34.                                     sdo_point_type(SELF.geometry.sdo_ordinates(1),
  35.                                                    SELF.geometry.sdo_ordinates(2),
  36.                                                    NULL),
  37.                                     NULL,NULL);
  38.           END IF;
  39.           IF ( p_compare_geom.geometry.sdo_point IS NOT NULL ) THEN
  40.              v_compare_geom := sdo_geometry(2001,NULL,
  41.                                     sdo_point_type(p_compare_geom.geometry.sdo_point.x,
  42.                                                    p_compare_geom.geometry.sdo_point.y,
  43.                                                    NULL),
  44.                                     NULL,NULL);
  45.           ELSE
  46.              v_compare_geom := sdo_geometry(2001,NULL,
  47.                                     sdo_point_type(p_compare_geom.geometry.sdo_ordinates(1),
  48.                                                    p_compare_geom.geometry.sdo_ordinates(2),
  49.                                                    NULL),
  50.                                     NULL,NULL);
  51.           END IF;
  52.         END IF;
  53.      END getSortPoint;
  54.    BEGIN
  55.       IF (SELF.geometry IS NULL) THEN
  56.          RETURN -1;
  57.       elsif (p_compare_geom IS NULL) THEN
  58.          RETURN 1;
  59.       END IF;
  60.       getSortPoint;
  61.       IF ( v_geom.sdo_point.x < v_compare_geom.sdo_point.x ) THEN
  62.          RETURN -1;  -- any negative number will do
  63.       ELSIF ( v_geom.sdo_point.x > v_compare_geom.sdo_point.x ) THEN
  64.          RETURN 1;   -- any positive number will do
  65.       ELSIF ( v_geom.sdo_point.y < v_compare_geom.sdo_point.y ) THEN
  66.          RETURN -1;  -- any negative number will do
  67.       ELSIF ( v_geom.sdo_point.y > v_compare_geom.sdo_point.y ) THEN
  68.          RETURN 1;   -- any positive number will do
  69.       ELSE
  70.          RETURN 0;
  71.       END IF;
  72.    END;
  73. END;
  74. /
  75. SHOW errors

We can use this type to sort the data in X,Y order as follows:

  1. SELECT a.point_id, a.pointGeom
  2.   FROM rPoints a
  3.   ORDER BY t_geometry(a.pointGeom,0.0005,0);
  4. -- Result
  5. POINT_ID POINTGEOM
  6. -------- --------------------------------------------------------------------------
  7. 3        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(11697.227,28402.344,NULL),NULL,NULL)
  8. 7        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(12554.798,27858.246,NULL),NULL,NULL)
  9. 1        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(14252.91,29835.248,NULL),NULL,NULL)
  10. 5        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(15213.122,20520.035,NULL),NULL,NULL)
  11. 8        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(16104.656,29251.359,NULL),NULL,NULL)
  12. 2        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(16440.85,26061.832,NULL),NULL,NULL)
  13. 6        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(16536.188,28199.532,NULL),NULL,NULL)
  14. 10       SDO_GEOMETRY(2001,2001,SDO_POINT_TYPE(17592.891,21283.784,NULL),NULL,NULL)
  15. 9        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(17592.891,21333.784,NULL),NULL,NULL)
  16. 4        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(18393.804,27436.371,NULL),NULL,NULL)
  17. .
  18.  10 ROWS selected

Note how point_ids 9 and 10 are correctly sorted on Y.

There are many methods for spatial sorting other than the simple one shown above (which is not that optimised for performance). One would be via a space curve value such as a Morton Key. Perhaps another time…

I hope this helps someone out there.

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter

Comment