Oracle Spatial Extension in AutoCAD Map 3D dead and gone

The Oracle Spatial Extension was a tool that enabled AutoCAD Map 3D to push your DWG directly in Oracle.  Your Block Definitions, Block Attributes, Object Data and Link Templates were all maintained in Oracle and you could round trip back and forth from Oracle to DWG and back again with a READ/WRITE button.

This extension was added in AutoCAD Map 2000- but only for 32bit.
Those days are past, although if you install 32bit version of AutoCAD Map 3D 2015 you might still get it to run.

Yet I doubt you’ve been able to buy a 32bit computer in any store in the past 5 years.

I talk about it in detail in my AUGI post here:   https://www.augi.com/library/highs-and-lows-of-moving-dwg-into-a-database

Luckily there is FDO (Feature Data Objects).  This is a generic data connection tool found in products such as AutoCAD Map 3D, InfraWorks, AutoCAD Civil 3D, Autodesk Infrastructure Map Server AND MapGuide.

You can connect to more than just Oracle such as SDF, SHP, SQL Server Spatial, ArcSDE, and almost anything that Safe Software’s FME can connect to with the FDO Provider for FME (http://www.safe.com/solutions/for-applications/autodesk/autodesk-autocad-map-3d/fme-fdo-provider/)

Now there are a few organizations out there that still store their data in OSE (ask them and they don’t want to move since the DWG round tripping to Oracle and back is so seamless).  But, now we are in the 64bit world, we may have to move forward.

In this document that I wrote for Autodesk a number of years ago, I talk about moving from Oracle OSE to FDO using AutoCAD Map 3D.  The information is still relevant for all those OSE users:

Moving from OSE to FDO with AutoCAD Map 3D

Although the document moves OSE data to Oracle, there is no reason you can’t move OSE data from Oracle to any FDO data source.  I have recently used this technique to move OSE data in Oracle to SQL Server Spatial 2012.

Advertisement

DWG to FDO: Moving Blocks to SDF (with Attributes)

I have had a lot of questions lately on how to move complex DWG files with Rotated Blocks with Attributes to FDO.

(Feature Data Objects include storage such as Oracle Spatial, SQL Server Spatial, SHP, SDF, mySQL, etc.)

Moving from DWG to Universal Spatial Storage (USS) is key for any organization wanting to share data or maintain a “Truth” to their assets.

I have created a video that shows how to take Blocks with 3 Attributes and export them to a SDF file – which can then be put in the SQL Server or Oracle etc.

I use the existing block as a symbol with FDO, so please keep your individual DWG block libraries on your system.  For example, I have a folder with all my utility blocks – each block exploded in an individual DWG file.

GIS Data Needs a Database for a Home

There are many GIS formats out there, current SAFE Software translates over 250 GIS/CAD/Spatial formats.  The one thing in common is that the data can be accessed much easier if it was in a database.

The issue is maintaining the data once it’s there.  I really like the Oracle Spatial Extension that is part of AutoCAD Map 3D.  This has been around for over 10 years and works great.  It stores the AutoCAD blocks and its attributes, the Object Data and Link Template (all data in the DWG file) as columns in an Oracle table.  You can then render that data in other GIS that can read Oracle spatial data (such as MapGuide) exactly like it was in the DWG.  In fact, since the Oracle keeps the DWG file verbatim, you can read and write to Oracle and never lose the AutoCAD features you are used to.

This MapGuide rendered data including Polylines, Blocks and Symbols is maintained in AutoCAD Map and stored in Oracle.

Now, there is newer technology called FDO (feature data objects) that all the Autodesk (and other Open Source GISs) data is built on.  I really love FDO but the bridge between the DWG (object data, blocks, link templates) and the data sources (such as SQL Server 2008 spatial and Oracle spatial) is not complete for round-tripping.  I would love to see the mapping seen in the old Oracle Spatial Extension for AutoCAD Map moved over into the FDO world.

Now that would help GIS data find a home!

Using Map 3D 2010 with Oracle? Better use 10G client!

Hi All, here’s one that stumped me (I should have read the documentation!).

If you want to connect AutoCAD Map 3D 2010 to Oracle with FDO, remember to use the 10G client and not the 9i one. Even if you are connecting to a 9i database.

That one stumped me for a while.

gordon

PHP and AJAX Caching Bug

Wow, this one “bugged” me for days. 

I have a PHP form that gets data from Oracle.  When I click the submit button, I want another page to process some script to keep a many-to-many table up to data.

For example:

<input type=submit onClick=submitDistricts($PUBID)>

The Javascript calls a GET

function submitDistricts(PUBID)
{
var xmlHttp = getXMLHttp();
    xmlHttp.onreadystatechange = function()
  {
    if(xmlHttp.readyState == 4)
    {
      HandleDistrictResponse(xmlHttp.responseText);
    }
  }
 xmlHttp.open(“GET”, “updateDistricts.php?PUBID=”+PUBID);
  xmlHttp.send(null);
}

And this works pretty well.  Except that the call gets cached and I can only submit it once.  YIKES!!

How did I fix it?  Well ensure the GET is always unique.

Change the line from:

xmlHttp.open(“GET”, “updateDistricts.php?PUBID=”+PUBID);

TO:

xmlHttp.open(“GET”, “updateDistricts.php?PUBID=”+PUBID + ‘&’ + Math.random());

That’s it. Now my AJAX call to the database is always unique and has no caching…

That was a weird one!

Another Tricky Trigger

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!

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…

First Post..

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…