03
Dec
08

Get Line Centroids – still on the LINE!

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

Advertisements

1 Response to “Get Line Centroids – still on the LINE!”


  1. 1 recher
    March 9, 2015 at 4:41 pm

    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 !


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: