GeoMedia Q&A

[#1]

Question

How to update the geometry (X,Y) in one point based feature class with the geometry from another point feature class.

Answer

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!