How to update the geometry (X,Y) in one point based feature class with the geometry from another point feature class.
It's easy to extract the XY ordinates from the input geometry but writing to the output geometry is problematic. The reason is that the entire geometry must be updated; you cannot just update values in the array. This means you need to reconstruct the new geometry based on XY array values from the source while preserving the rotation values. I do not see an easy way to do this via SQL, it would have to be done via a procedure or script.
Here is a simple function that will merge the XYZ values from v_ptgeom2 into the XYZ of v_ptgeom1 while retaining all other v_ptgeom1 values and return an sdo_geometry as a result. There is no error checking and 3D points are assumed
CREATE OR REPLACE
FUNCTION MergePtGeoms( v_ptgeom1 in MDSYS.SDO_GEOMETRY, v_ptgeom2 in MDSYS.SDO_GEOMETRY) RETURN MDSYS.SDO_GEOMETRY DETERMINISTIC IS
c_cmdname CONSTANT VARCHAR2(30):='MergePtGeoms';
--
v_mergePtGeom SDO_GEOMETRY:=SDO_GEOMETRY(3001,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(),MDSYS.SDO_ORDINATE_ARRAY());
-- Other variables
v_numelems INTEGER;
v_numords INTEGER; -- Num of input ordinates
--
BEGIN
v_mergePtGeom.SDO_GTYPE := v_ptgeom1.SDO_GTYPE;
v_mergePtGeom.SDO_SRID := v_ptgeom1.SDO_SRID;
v_numords := v_ptgeom1.sdo_ordinates.count; -- Number of ordinates to process
v_numelems := v_ptgeom1.sdo_elem_info.count;
v_mergePtGeom.SDO_ELEM_INFO.EXTEND( v_numelems );
FOR i IN 1..v_numelems LOOP
v_mergePtGeom.SDO_ELEM_INFO(i) := v_ptgeom1.SDO_ELEM_INFO(i);
END LOOP;
v_mergePtGeom.SDO_ORDINATES.EXTEND(v_numords); -- Initialize Ordinate Array by number of pts
FOR i IN 1..v_numords LOOP
IF i = 1 then
v_mergePtGeom.SDO_ORDINATES(1) := v_ptgeom2.SDO_ORDINATES(1);
ELSIF i = 2 then
v_mergePtGeom.SDO_ORDINATES(2) := v_ptgeom2.SDO_ORDINATES(2);
ELSIF i = 3 then
v_mergePtGeom.SDO_ORDINATES(3) := v_ptgeom2.SDO_ORDINATES(3);
ELSE
v_mergePtGeom.SDO_ORDINATES(i) := v_ptgeom1.SDO_ORDINATES(i);
END IF;
END LOOP;
RETURN v_mergePtGeom;
EXCEPTION
WHEN OTHERS THEN
NULL;
END MergePtGeoms;
/
select a.geometry, b.geometry, mergeptgeoms(a.geometry, b.geometry) MergedGeom from POINTTAB1 a, POINTTAB2 b where a.id=b.id;
a: SDO_GEOMETRY(3001, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1, 4, 1, 0), SDO_ORDINATE_ARRAY(1605684.26, 582424.397, 0, .998607256, -.05275935, 0))
b: SDO_GEOMETRY(3001, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1, 4, 1, 0), SDO_ORDINATE_ARRAY(1605679.31, 582424.66, 0, 1, 1.8806E-08, 0))
MergedGeom: SDO_GEOMETRY(3001, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1, 4, 1, 0), SDO_ORDINATE_ARRAY(1605679.31, 582424.66, 0, .998607256, -.05275935, 0))
For an update that merges reddot XYZ into blackdot XYZ:
UPDATE blacksdot SET geometry = (SELECT mergeptgeoms(A.geometry, B.geometry) FROM blackdot A, reddot b WHERE A.ID=B.ID);
Test it first!