New PL/SQL Packages – Rotate oriented point

I was asked if I would enhance my pl/sql Rotate function so that it handles oriented points. (Note: Oracle’s sdo_util.getVertices() does not support the oriented point.)

After a bit of coding and modification of GeoRaptor so that I can visualise the result, I have finally finished the code and uploading it to my website. The GeoRaptor changes should be available in the next release.

Here is an example of using the GEOM.ROTATE function to rotate an oriented point and visualising the result in GeoRaptor.


In answer to Jacob’s question, here is a function that will find the angle from the point to its virtual end point.

 CREATE OR REPLACE FUNCTION oriented_point_angle(p_oriented_point IN mdsys.sdo_geometry) RETURN NUMBER deterministic
   v_vertices mdsys.vertex_set_type;
   v_dims     NUMBER;
   IF (p_oriented_point IS NULL) THEN
   END IF;
   IF ( NOT codesys.geom.isOrientedPoint(p_oriented_point.sdo_elem_info)) THEN
   END IF;
   v_vertices := mdsys.sdo_util.getVertices(p_oriented_point);
   IF (v_vertices IS NULL OR v_vertices.COUNT != 2) THEN
   END IF;
   v_dims := p_oriented_point.get_dims();
   RETURN codesys.cogo.bearing(v_vertices(1).x,
                                v_vertices(1).x + v_vertices(2).x,
                                v_vertices(1).y + v_vertices(2).y);
 END oriented_point_angle;
 SHOW errors
 -- Testing it with a single oriented point we get:
 SELECT codesys.cogo.dd2dms(
                       oriented_point_angle(mdsys.sdo_geometry(2001,25832,NULL,mdsys.sdo_elem_info_array(1,1,1,3,1,0),mdsys.sdo_ordinate_array(575163.89,6228535.08,0.908093,0.404309))))) AS angle   FROM dual;
 -- Result
 -- Testing it with a number of oriented points we get:
 WITH oriented_point AS (
 SELECT 1 AS id,  MDSYS.SDO_GEOMETRY(3001,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1,1, 4,1,0),MDSYS.SDO_ORDINATE_ARRAY(42781396.2001512,457837132.60015,0.0, 0.8661869519,0.499720086,0.0)) AS geom FROM dual UNION ALL SELECT 2 AS id,  MDSYS.SDO_GEOMETRY(3001,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1,1, 4,1,0),MDSYS.SDO_ORDINATE_ARRAY(42792668.4002372,457751297.79978,0.0, 0.9011907292,-0.4334227378,0.0)) AS geom FROM dual UNION ALL SELECT 3 AS id,  MDSYS.SDO_GEOMETRY(3001,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1,1, 4,1,0),MDSYS.SDO_ORDINATE_ARRAY(42667160.399947,457538468.700236,0.0, 0.4280264799,0.9037661935,0.0)) AS geom FROM dual UNION ALL SELECT 4 AS id,  MDSYS.SDO_GEOMETRY(3001,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1,1, 4,1,0),MDSYS.SDO_ORDINATE_ARRAY(42718894.599856,457647416.600053,0.0, 0.4281589757,0.9037034312,0.0)) AS geom FROM dual UNION ALL SELECT 5 AS id,  MDSYS.SDO_GEOMETRY(3001,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1,1, 4,1,0),MDSYS.SDO_ORDINATE_ARRAY(42611295.9003171,457662210.400047,0.0, 0.8992037046,-0.4375302249,0.0)) AS geom FROM dual UNION ALL SELECT 6 AS id,  MDSYS.SDO_GEOMETRY(3001,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1,1, 4,1,0),MDSYS.SDO_ORDINATE_ARRAY(42689117.6003236,457624436.599882,0.0, 0.8991318247,-0.4376779202,0.0)) AS geom FROM dual UNION ALL SELECT 7 AS id,  MDSYS.SDO_GEOMETRY(3001,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1,1, 4,1,0),MDSYS.SDO_ORDINATE_ARRAY(42578371.9002364,457584580.400213,0.0, -0.4581473224,0.8888762743,0.0)) AS geom FROM dual UNION ALL SELECT 8 AS id,  MDSYS.SDO_GEOMETRY(3001,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1,1, 4,1,0),MDSYS.SDO_ORDINATE_ARRAY(42576686.8003421,457648016.299926,0.0, 0.0476250064,0.9988652856,0.0)) AS geom FROM dual UNION ALL SELECT 9 AS id,  MDSYS.SDO_GEOMETRY(3001,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1,1, 4,1,0),MDSYS.SDO_ORDINATE_ARRAY(42689623.3000174,457533376.699752,0.0, 0.4320776143,0.9018364237,0.0)) AS geom FROM dual UNION ALL SELECT 10 AS id, MDSYS.SDO_GEOMETRY(3001,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1,1, 4,1,0),MDSYS.SDO_ORDINATE_ARRAY(42738282.7998623,457636926.800073,0.0, 0.4321094461,0.9018211722,0.0)) AS geom FROM dual )
                       oriented_point_angle(op.geom))) AS angle
   FROM oriented_point op;
 -- ---------------
  1 60^1'6.662"
 2 115^41'5.901"
 3 25^20'32.57"
  4 25^21'2.811"
 5 115^56'47.06"
 6 115^57'20.941"
  7 332^43'56.561"
 8 2^43'47.08"
 9 25^35'58.14"
 10 25^36'5.42"
 10 rows selected

The function has been added to the GEOM package available for download from this site.

I hope this is of use.

Leave a Reply

Your email address will not be published. Required fields are marked *