There Be Dragons - Uncharted Internet Mapping | Gordon Luckett's Tips and Tricks

Archive Page 8

Dec

Get Line Centroids – still on the LINE!

Categories: Uncategorized
Tags: centroid, line, Linear referencing system, LRS, polyline

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

Nov

Unsupported Autodesk FDO Provider to Oracle with XE – works…er..

By Gordon 1 Comment

Categories: Uncategorized
Tags: autodesk, Express, fdo, mapguide, XE

Ok, I managed to get Autodesk FDO Provider for Oracle working with Autodesk MapGuide Enterprise 2009 with Oracle XE. The trick was simple.

Just make sure that the SCHEMA, the USERS, the TABLES and all the COLUMNS are in upper case. That’s it. I was stuggling with theming, connecting, etc with Oracle XE and Autodesk FDO Provider for Oracle. I had reverted to the SL-KING Provider for Oracle (due to the fact that the Autodesk FDO Provider for Oracle does not offically supports XE and I couldn’t get it working).

So I created a new users, loaded it up with tables, including a geometry column called GEOMCOL all in upper case, added an entry to the SDO_GEOM_METADATA table, a spatial Index, and voila! I have the Autodesk FDO Provider for Oracle working “tickety-boo” with XE.

Yay!

Nov

Another Tricky Trigger

By Gordon Leave a Comment

Categories: Uncategorized
Tags: clob, digitize, oracle, sdo_geom

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!

Nov

Trigger for Line Centroids

By Gordon Leave a Comment

Categories: Uncategorized
Tags: coordinate system, oracle, sdo_geom, trigger

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…

Nov

First Post..

By Gordon Leave a Comment

Categories: Uncategorized
Tags: autodesk, fdo, mapguide, oracle

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…

There Be Dragons – Uncharted Internet Mapping

Archive Page 8

Get Line Centroids – still on the LINE!

Unsupported Autodesk FDO Provider to Oracle with XE – works…er..

Another Tricky Trigger

Trigger for Line Centroids

First Post..

Gordon Luckett

Contact

Checkout MapGuide Guy’s Youtube Channel

Recent Posts