Top 5 Recent Articles
ARTICLES CATEGORIES
- Algorithms (20)
- All (400)
- Biography (1)
- Blog (45)
- Business Requirements (1)
- Commentary (1)
- 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) (4)
- Open Source (18)
- Oracle Spatial and Locator (193)
- Partitioning (1)
- PostGIS (34)
- Projections (1)
- Published Articles (1)
- qGIS (1)
- Recommendations (1)
- Services (1)
- Software Change Log (1)
- Source Code (35)
- Space Curves (9)
- Spatial Database Functions (108)
- Spatial DB comparison (1)
- Spatial XML Processing (11)
- SQL Server Spatial (92)
- Standards (3)
- Stored Procedure (15)
- Tessellation or Gridding (10)
- Tools (2)
- Topological Relationships (1)
- Training (2)
- Triangulation (2)
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 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.
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.