Top 5 Recent Articles

ARTICLES CATEGORIES

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.

UPDATE

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
 AS
   v_vertices mdsys.vertex_set_type;
   v_dims     NUMBER;
 BEGIN
   IF (p_oriented_point IS NULL) THEN
     RETURN NULL;
   END IF;
   IF ( NOT codesys.geom.isOrientedPoint(p_oriented_point.sdo_elem_info)) THEN
     RETURN NULL;
   END IF;
   v_vertices := mdsys.sdo_util.getVertices(p_oriented_point);
   IF (v_vertices IS NULL OR v_vertices.COUNT != 2) THEN
     RETURN NULL;
   END IF;
   v_dims := p_oriented_point.get_dims();
   RETURN codesys.cogo.bearing(v_vertices(1).x,
                                v_vertices(1).y,
                                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(
              codesys.cogo.degrees(
                       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
 --
 ANGLE
 ---------
 66^0'.01"
 '
 --
 -- 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 )
 SELECT op.id,
        codesys.cogo.dd2dms(
              codesys.cogo.degrees(
                       oriented_point_angle(op.geom))) AS angle
   FROM oriented_point op;
 --
 ID ANGLE
 -- ---------------
  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.