# Sorting SDO_GEOMETRY data using the ORDER BY clause of a 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

``` DROP TABLE rPoints;
-- Result
TABLE RPOINTS dropped.
CREATE TABLE rPoints AS
SELECT ROW_NUMBER() OVER (partition BY 1 ORDER BY rownum) AS point_id,
sdo_geometry(2001,NULL,
sdo_point_type(round(dbms_random.VALUE(10000,19999),3),
round(dbms_random.VALUE(20000,29999),3),
NULL),NULL,NULL) AS pointGeom
FROM dual
CONNECT BY LEVEL < 10;
-- Result
TABLE RPOINTS created.
-- Now ensure we have two geometries that share the same X but different Y
-- Duplicate a single point's x but with different Y
INSERT INTO rPoints (point_id,pointGeom)
SELECT a.point_id + 1,
sdo_geometry(a.pointGeom.sdo_gtype,
a.pointGeom.sdo_gtype,
sdo_point_type(a.pointGeom.sdo_point.x,
a.pointGeom.sdo_point.y - 50,
NULL),
NULL,NULL)
FROM rPoints a
WHERE a.point_id = (SELECT MAX(point_id) FROM rPoints);
-- Result
1 Point inserted
commit;
-- Result
committed
-- show points in created order...
SELECT a.point_id, a.pointGeom
FROM rPoints a;
-- Result
POINT_ID POINTGEOM
-------- --------------------------------------------------------------------------
1        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(14252.91,29835.248,NULL),NULL,NULL)
2        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(16440.85,26061.832,NULL),NULL,NULL)
3        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(11697.227,28402.344,NULL),NULL,NULL)
4        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(18393.804,27436.371,NULL),NULL,NULL)
5        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(15213.122,20520.035,NULL),NULL,NULL)
6        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(16536.188,28199.532,NULL),NULL,NULL)
7        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(12554.798,27858.246,NULL),NULL,NULL)
8        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(16104.656,29251.359,NULL),NULL,NULL)
9        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(17592.891,21333.784,NULL),NULL,NULL)
10       SDO_GEOMETRY(2001,2001,SDO_POINT_TYPE(17592.891,21283.784,NULL),NULL,NULL)
10 ROWS selected
```

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

``` SELECT a.point_id, a.pointGeom
FROM rPoints a
ORDER BY a.pointGeom;
-- Result
Error starting at line 68 IN command:
SELECT a.point_id, a.pointGeom
FROM rPoints a
ORDER BY a.pointGeom
-- Result
Error at Command Line:68 COLUMN:19
Error report:
SQL Error: ORA-22901: cannot compare VARRAY OR LOB attributes OF an object TYPE
22901. 00000 -  "cannot compare nested table or VARRAY or LOB attributes of an object type"
*Cause:    Comparison OF nested TABLE OR VARRAY OR LOB attributes OF an
object TYPE was attempted IN the absence OF a MAP OR ORDER
method.
*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.

``` DESCRIBE mdsys.sdo_geometry
-- Result
USER TYPE definition
---------------------------------------------------------------
TYPE SDO_GEOMETRY          AS OBJECT (
SDO_GTYPE       NUMBER,
SDO_SRID        NUMBER,
SDO_POINT       SDO_POINT_TYPE,
SDO_ELEM_INFO   SDO_ELEM_INFO_ARRAY,
SDO_ORDINATES   SDO_ORDINATE_ARRAY,
MEMBER FUNCTION  GET_GTYPE
RETURN NUMBER DETERMINISTIC,
MEMBER FUNCTION  GET_DIMS
RETURN NUMBER DETERMINISTIC,
MEMBER FUNCTION  GET_LRS_DIM
RETURN NUMBER DETERMINISTIC)
ALTER TYPE SDO_GEOMETRY
ADD MEMBER FUNCTION GET_WKB RETURN BLOB DETERMINISTIC,
ADD MEMBER FUNCTION GET_WKT RETURN CLOB DETERMINISTIC,
ADD MEMBER FUNCTION ST_CoordDim RETURN SMALLINT DETERMINISTIC,
ADD MEMBER FUNCTION ST_IsValid RETURN INTEGER DETERMINISTIC,
ADD CONSTRUCTOR FUNCTION SDO_GEOMETRY(wkt IN CLOB,
srid IN INTEGER DEFAULT NULL) RETURN SELF AS RESULT,
ADD CONSTRUCTOR FUNCTION SDO_GEOMETRY(wkt IN VARCHAR2,
srid IN INTEGER DEFAULT NULL) RETURN SELF AS RESULT,
ADD CONSTRUCTOR FUNCTION SDO_GEOMETRY(wkb IN BLOB,
srid IN INTEGER DEFAULT NULL) RETURN SELF AS RESULT
```

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.

``` CREATE OR REPLACE TYPE T_Geometry AS OBJECT (
geometry  mdsys.sdo_geometry,
tolerance NUMBER,
centroid  INTEGER,   -- sdo_centroid is slower for polygons than using first coordinate - 0/direct; 1/Centroid
ORDER member FUNCTION orderBy(p_compare_geom IN T_Geometry)
RETURN NUMBER
);
/
SHOW errors
CREATE OR REPLACE
TYPE BODY T_Geometry
AS
ORDER Member FUNCTION orderBy(p_compare_geom IN T_Geometry)
RETURN NUMBER
IS
v_geom         sdo_geometry;
v_compare_geom sdo_geometry;
PROCEDURE getSortPoint
AS
BEGIN
-- Get centroid only for polygons. Lines and Points use direct coordinate access
IF ( SELF.centroid <> 0 AND SELF.geometry.get_gtype() IN (3,7) ) THEN
v_geom         := sdo_geom.sdo_centroid(SELF.geometry,SELF.tolerance);
v_compare_geom := sdo_geom.sdo_centroid(p_compare_geom.geometry,SELF.tolerance);
ELSE
IF ( SELF.geometry.sdo_point IS NOT NULL ) THEN
v_geom := sdo_geometry(2001,NULL,
sdo_point_type(SELF.geometry.sdo_point.x,
SELF.geometry.sdo_point.y,
NULL),
NULL,NULL);
ELSE
v_geom := sdo_geometry(2001,NULL,
sdo_point_type(SELF.geometry.sdo_ordinates(1),
SELF.geometry.sdo_ordinates(2),
NULL),
NULL,NULL);
END IF;
IF ( p_compare_geom.geometry.sdo_point IS NOT NULL ) THEN
v_compare_geom := sdo_geometry(2001,NULL,
sdo_point_type(p_compare_geom.geometry.sdo_point.x,
p_compare_geom.geometry.sdo_point.y,
NULL),
NULL,NULL);
ELSE
v_compare_geom := sdo_geometry(2001,NULL,
sdo_point_type(p_compare_geom.geometry.sdo_ordinates(1),
p_compare_geom.geometry.sdo_ordinates(2),
NULL),
NULL,NULL);
END IF;
END IF;
END getSortPoint;
BEGIN
IF (SELF.geometry IS NULL) THEN
RETURN -1;
elsif (p_compare_geom IS NULL) THEN
RETURN 1;
END IF;
getSortPoint;
IF ( v_geom.sdo_point.x < v_compare_geom.sdo_point.x ) THEN
RETURN -1;  -- any negative number will do
ELSIF ( v_geom.sdo_point.x > v_compare_geom.sdo_point.x ) THEN
RETURN 1;   -- any positive number will do
ELSIF ( v_geom.sdo_point.y < v_compare_geom.sdo_point.y ) THEN
RETURN -1;  -- any negative number will do
ELSIF ( v_geom.sdo_point.y > v_compare_geom.sdo_point.y ) THEN
RETURN 1;   -- any positive number will do
ELSE
RETURN 0;
END IF;
END;
END;
/
SHOW errors
```

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

``` SELECT a.point_id, a.pointGeom
FROM rPoints a
ORDER BY t_geometry(a.pointGeom,0.0005,0);
-- Result
POINT_ID POINTGEOM
-------- --------------------------------------------------------------------------
3        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(11697.227,28402.344,NULL),NULL,NULL)
7        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(12554.798,27858.246,NULL),NULL,NULL)
1        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(14252.91,29835.248,NULL),NULL,NULL)
5        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(15213.122,20520.035,NULL),NULL,NULL)
8        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(16104.656,29251.359,NULL),NULL,NULL)
2        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(16440.85,26061.832,NULL),NULL,NULL)
6        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(16536.188,28199.532,NULL),NULL,NULL)
10       SDO_GEOMETRY(2001,2001,SDO_POINT_TYPE(17592.891,21283.784,NULL),NULL,NULL)
9        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(17592.891,21333.784,NULL),NULL,NULL)
4        SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(18393.804,27436.371,NULL),NULL,NULL)
.
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.