There Be Dragons – Uncharted Internet Mapping

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…