Here at AU 2008 in Las Vegas, I was having a dicussion about Oracle Spatial with Lance Maidlow of Landor Investments Ltd. We were discussing converting a Line in Oracle and using LRS function to get the CENTROID on the line (geographic center of the object).

Well he suggested that rather than getting the start and end point of the line with LRS and dividing by 2 – which may not land on the line, that I should use the SDO_LRS.LOCATE_PT function that will find a point on a line based on how far down the line you want to go.

So.. you get the length of the line, divide by 2 and then use the SDO_LRS.LOCATE_PT to get the middle point on the line…oh and convert it to LL if you need. See below:

select sdo_cs.transform(SDO_LRS.CONVERT_TO_STD_GEOM(SDO_LRS.LOCATE_PT(SDO_LRS.CONVERT_TO_LRS_GEOM(GEOM, 3), SDO_GEOM.SDO_LENGTH(GEOM,3)/2)),8307).SDO_POINT.X as LON,

sdo_cs.transform(SDO_LRS.CONVERT_TO_STD_GEOM(SDO_LRS.LOCATE_PT(SDO_LRS.CONVERT_TO_LRS_GEOM(GEOM, 3), SDO_GEOM.SDO_LENGTH(GEOM,3)/2)),8307).SDO_POINT.Y as LAT

from DUBLIN_CALIFORNIA_CA83IIIF.sewer_pipe

I was lazy and set the toleraces to 3 rather than query the SDO_METADATA…

Thanks Lance!

gordon

43.547982
-80.250765

### Like this:

Like Loading...

*Related*

Thank you so much.

I was lazier than you, and did not want to find the code by myself.

If we do not want to do an SRID transform, and want to get directly the geometry as a point, we can use the “raw” formula :

select SDO_LRS.CONVERT_TO_STD_GEOM(SDO_LRS.LOCATE_PT(SDO_LRS.CONVERT_TO_LRS_GEOM(GEOM, 3), SDO_GEOM.SDO_LENGTH(GEOM,3)/2))

from DUBLIN_CALIFORNIA_CA83IIIF.sewer_pipe

Be sure to replace the “GEOM” words by the name of your geometric field.

Have fun !