24
Nov
08

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…

Advertisements

0 Responses to “Trigger for Line Centroids”



  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Gordon Luckett

Arrow Geomatics Inc's Gordon Luckett

Contact

gordon dot luckett at arrowgeo dot com 1-519-837-9500 (Arrow Geomatics Inc.)

Checkout MapGuide Guy’s Youtube Channel

gordonluckett@twitter


%d bloggers like this: