Another Tricky Trigger

Ok, I really struggled with this one. I have a CLOB field in my database that has all the LAT/LON coordinate pairs stored.  I wanted to harvest this – with a trigger- to populate my SDO_GEOM field in Oracle.  The trick is, you can’t just EVALUATE the column.  You have to build a SQL statement and then insert it into the column in the trigger: – this example is for POLYLINES…

CREATE OR REPLACE TRIGGER CRAZYTABLE
BEFORE INSERT OR UPDATE
OF FEATURE_TYPE
ON CRAZYTABLE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
myCoords CLOB;
mySQL VARCHAR2(4000);
myGEOM SDO_GEOMETRY;

BEGIN
   myCoords := :OLD.COORDS;

        mySQL := ’select MDSYS.SDO_GEOMETRY(3002, 8307, null, MDSYS.SDO_ELEM_INFO_ARRAY(1, 2, 1),MDSYS.SDO_ORDINATE_ARRAY(‘  || myCoords || ‘)) from dual’;
        
         EXECUTE IMMEDIATE mySQL into myGEOM;
         
      :NEW.GEOMCOL := myGEOM;    

END;
/

This works like a charm.  whew!

Trigger for Line Centroids

Ok, this was a cool one.  I needed to get the Latitude and Longitude of the centroid of a line (the current coordinate system in UTM83-11)  in Oracle.

I needed this as a trigger, to keep it up to date.

So…first convert the geometry in the table into LL
sdo_cs.transform(GEOM,8307)

Then convert that line geometry into a LRS geometery (Linear Reference System)
SDO_LRS.CONVERT_TO_LRS_GEOM(sdo_cs.transform(GEOM,8307), 3))

Ok now that the geometry is in LRS, we can use LRS functions, such as getting the start point of the line.
SDO_LRS.GEOM_SEGMENT_START_PT(SDO_LRS.CONVERT_TO_LRS_GEOM(sdo_cs.transform(GEOM,8307), 3)))

Then convert that start point back into a standard geometry object.
SDO_LRS.CONVERT_TO_STD_GEOM(SDO_LRS.GEOM_SEGMENT_START_PT(SDO_LRS.CONVERT_TO_LRS_GEOM(sdo_cs.transform(GEOM,8307), 3))))

Finally, once we have a start (or end point) we can get the X and Y.
SDO_LRS.CONVERT_TO_STD_GEOM(SDO_LRS.GEOM_SEGMENT_START_PT(SDO_LRS.CONVERT_TO_LRS_GEOM(sdo_cs.transform(GEOM,8307), 3)))).SDO_POINT.X

Now just get the average of the two start point and end point and that is your line centroid.

   CREATE OR REPLACE TRIGGER “WATER_LINE”
BEFORE INSERT OR UPDATE
ON WATER_LINE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
 
      if :OLD.GEOM IS NOT NULL THEN
  :NEW.LONGITUDE :=  (SDO_LRS.CONVERT_TO_STD_GEOM(SDO_LRS.GEOM_SEGMENT_START_PT(SDO_LRS.CONVERT_TO_LRS_GEOM(sdo_cs.transform(:OLD.GEOM,8307), 3))).SDO_POINT.X +  SDO_LRS.CONVERT_TO_STD_GEOM(SDO_LRS.GEOM_SEGMENT_END_PT(SDO_LRS.CONVERT_TO_LRS_GEOM(sdo_cs.transform(:OLD.GEOM,8307), 3))).SDO_POINT.X)/2;
 

:NEW.LATITUDE := (SDO_LRS.CONVERT_TO_STD_GEOM(SDO_LRS.GEOM_SEGMENT_START_PT(SDO_LRS.CONVERT_TO_LRS_GEOM(sdo_cs.transform(:OLD.GEOM,8307), 3))).SDO_POINT.Y +  SDO_LRS.CONVERT_TO_STD_GEOM(SDO_LRS.GEOM_SEGMENT_END_PT(SDO_LRS.CONVERT_TO_LRS_GEOM(sdo_cs.transform(:OLD.GEOM,8307), 3))).SDO_POINT.Y)/2;
      END IF;

END;
/

I love this stuff. 

Oh, I started to use CONVEXHULL but I found that it has an issue with perfectly vertical and horizontal lines…

First Post..

Ok my first post will be regarding Oracle SDO_GEOM objects.  I commonly use the AutoCAD Map OSE (Oracle Spatial Extension) rather than the FDO connector to Oracle because it retains the Blocks, Layers, Link Templates and Object Data.  But… unless you store your closed polylines as MPOLYGONS, you only get polylines in Oracle.  If you were to use FDO to look at the data (Map or MapGuide) all you would get is polylnes…

So.. I decided to create a view of the data that dynamically converts the closed Polylines into Polygons with an oracle spatial/locator SQL command.

create or replace view vwMYPARCELS as
select ENTITYID, PARCEL_ID,
mdsys.sdo_geometry(3003, NULL, NULL,
mdsys.sdo_elem_info_array( 1, 3, 1),
c.geometry.SDO_ORDINATES) as GEOMETRY
from
PARCELS c

That’s it.  Very cool.  For performance sake you could make a Materialized View with it…