Reading Oracle Spatial data into .NET

Recently, I was tasked with pulling geo-spatial data from Oracle into an application.  While Microsoft provides support for SQL Server 2008’s SqlGeometry and SqlGeography data types in the Microsoft.SqlServer.Types namespace, there is no equivalent for Oracle’s SDO_Geometry data type.  We’ll need some custom code to pull this information out of Oracle.

What is Geospatial Data?

Both SQL Server and Oracle provide native data types, SqlGeometry and SDO_Geometry respectively to represent spatial data.  These special fields can be used to describe a single point, or collection of points to form a line, multi-line, polygon, etc. The database technology indexes this data to create a “spatial index”, and provides native SQL functions to query for intersecting and nearby points.  The result is an extremely powerful solution that serves as the basis for next-generation location-aware technologies.

Oracle stores spatial data as a Character Large Object (CLOB), and to read it, we’ll need to parse that data into a structure we can use.

User Defined types with Oracle Data Provider for .NET

Fortunately, parsing the CLOB is really quite easy as the Oracle Data Provider for .NET supports a plug-in architecture that let you define your own .NET user-defined types.  I found a great starter example in the oracle forums that included user-defined types for SDO_Geometry and SDO_Point.  A near identical example can be found as part of the Topology Framework .NET, which cites Dennis Jonio as the author.

Summarized, the classes appear as:

[OracleCustomTypeMappingAttribute("MDSYS.SDO_POINT_TYPE")]
public class SdoPoint : OracleCustomTypeBase<SdoPoint>
{
    public decimal? X { get; set; }
    public decimal? Y { get; set; }
    public decimal? Z { get; set; }

    /* ... details omitted for clarity  */
}

[OracleCustomTypeMappingAttribute("MDSYS.SDO_GEOMETRY")]
public class SdoGeometry : OracleCustomTypeBase<SdoGeometry>
{
    public decimal? Sdo_Gtype { get; set; }
    public decimal? Sdo_Srid { get; set; }
    public SdoPoint Point { get; set; }
    public decimal[] ElemAray { get; set; }
    public decimal[] OrdinatesArray { get; set; }
    
    /* ... details omitted for clarity  */
}

With these custom user-defined types, spatial data can be read from the database just like any other data type.

[Test]
public void CanFetchSdoGeometry()
{
    string oracleConnection = 
            "Data Source=" +
                "(DESCRIPTION=" +
                    "(ADDRESS_LIST=" +
                        "(ADDRESS=" + 
                            "(PROTOCOL=TCP)" + 
                            "(HOST=SERVER1)" + 
                            "(PORT=1600) +
                        ")" +
                    ")"
                    "(CONNECT_DATA=" + 
                        "(SERVER=DEDICATED)" +
                        "(SERVICE_NAME=INSTANCE1)"
                    ")" +
                ");" +
                "User Id=username;" +
                "Password=password;";

    using (OracleConnection cnn = new OracleConnection(oracleConnection))
    {
        cnn.Open();

        string sql = "SELECT * FROM TABLE1";

        OracleCommand cmd = new OracleCmd(sql, cnn);
        cmd.CommandType = System.Data.CommandType.Text;

        OracleDataReader reader = cmd.ExecuteReader();
        
        while(reader.Read())
        {
            if (!reader.IsDBNull(1))
            {
                SdoGeometry geometry = reader.GetValue(1) as SdoGeometry;
                Assert.IsNotNull(geometry);
            }
        }
    }
}

Using SdoGeometry in your Application

Now that we have our SdoGeometry type, how do we use it?  The oracle documentation provides us with some details which we can use to decipher the SDO_GEOMETRY object type.

Recall that our SdoGeometry object has five properties: Sdo_Gtype, Sdo_Srid, Point, ElemArray and OrdinatesArray.

In the most basic scenario when your geometry object refers to a single point, all the information you need is in the Point property.  In all other scenarios, Point will be null and you’ll have to parse the values out of the Ordinates array.

Although the Geometry Type (sdo_gtype) property is represented as an integer, it uses a numbering format to convey the shape-type and structure of the ordinates array.  The four digits of the sdo_gtype represent dltt, where:

• First digit “d” represents how many dimensions are used to represent each point, and consequently, how many bytes in the ordinates array are used to describe a point. Possible values are 2, 3 or 4.
• The second digit, “l” refers to the LRS measurement when there are more than 2 dimensions.  This is typically zero for the default.
• The last two digits refer to the shape.

Since the algorithm to convert from UTM to Latitude/Longitude depends on your Projection and Datum, I don’t want to mislead anyone with the wrong algorithm.  A very detailed breakdown of the formulas are listed here, including an Excel Spreadsheet that breaks down the formula.  Here’s an example:

public Shape ToShape(SdoGeometry geometry)
{
    Shape shape = new Shape();

    if (geometry.Point != null)
    {
        LatLong point = ReadPoint(geometry.Point);
        shape.Add(point);
        shape.shapeType = Constants.ShapeType.Point;
    }
    else
    {

        // GTYPE is represented as dltt where:
        //      d  = number of dimensions to the data
        //      l  = LRS measure value (default is zero)
        //      tt = shape type 
        string gType = geometry.Sdo_Gtype.Value.ToString();
        int dimensions = Convert.ToInt32(gType.Substring(0, 1));
        int lrsMeasureValue = Convert.ToInt32(gType.Substring(1, 1));
        int shapeType = Convert.ToInt32(gType.Substring(3));

        // convert tt value to a custom enum
        shape.shapeType = GetShapeType(shapeType);

        LatLongs points = ReadPoints(geometry.OrdinatesArray, dimensions);
        shape.Points.Add(points);
    }

    return shape;
}

private LatLongs ReadPoints(decimal[] ordinates, int dimensions)
{
    LatLongs points = new LatLongs();
    for (int pIndex = 0; pIndex < ordinates.Length; pIndex += dimensions)
    {
        double lat = (double)ordinates[pIndex];
        double lng = (double)ordinates[pIndex + 1];

        LatLong latLong = ToLatLong(lat, lng);
        points.Add(latLong);
    }
    return points;
}