Working With Spatial Data In Oracle And ASP.NET 5

Spatial data, often known as geospatial data, is information about a physical item represented numerically in a geographic coordinate system. The article briefly explains how to deal with geographical data in Oracle and ASP.NET 5.

Pre-requisites

To be able to work with the code examples demonstrated in this article, you should have the following installed in your system,

Visual Studio 2019 Community Edition
.NET 5
Oracle database
dotConnect for Oracle

You can download .NET 5.0 runtime from here.

You can download Visual Studio 2019 from here.

You can download Oracle Express Edition from here.

You can download a copy of dotConnect for Oracle from here.

An Introduction to Spatial Data

Spatial data refers to any data linked to a particular place or geographical region, either directly or indirectly. Often referred to as geospatial or geographic information, it is data that may represent a physical object in a geographical coordinate system quantitatively.

A road map is a typical example of spatial data that depicts geographic information. Road maps are two-dimensional objects that include points, lines, and polygons representing cities, highways, and political borders such as states or provinces.

Types of Spatial Data

There are many different forms of spatial data, but the two most common categories are geometric data and geographic data.

Geometric data is a kind of spatial data represented as a two-dimensional flat surface on a computer screen. A typical example of geometric data is floor plans. Google Maps is a navigational tool that relies on geometric data to give precise directions to its users. It is one of the most basic examples of geographical data in operation.

Geographic data is information plotted on a map of a sphere often represented as the planet earth. Geographic data emphasizes the connection between a specific object or location and its latitude and longitude coordinates. A global positioning system (GPS) is a well-known example of geographic data.

What is a Spatial Database?

A spatial database is a kind of database designed for accessing and storing data that represents objects specified in a geometric space. Spatial databases can represent basic geometric objects such as points, lines, polygons, and more complex geometric objects.

Oracle's support for dealing with spatial data makes it simple for developers and analysts to get started with location intelligence analytics and mapping services without any prior experience or training. GIS experts may use it to implement sophisticated geospatial applications effectively.

Support for Spatial Data in EF Core

Spatial data is used to depict the actual position of objects. Many databases include built-in support for this kind of data, allowing it to be indexed and queried alongside other data types. Typical scenarios include searching for items within a certain distance of a place and choosing the object whose border contains a specific location.

dotConnect for Oracle enables you to specifically select the Oracle spatial engine for the Oracle Database server that the application connects to. The following code snippet illustrates how you can specify Oracle spatial engine in your code:

var config = OracleEntityProviderConfig.Instance;
config.SpatialOptions.OracleSpatialEngine = OracleSpatialEngine.OracleSpatial11g;

Entity Framework Core provides support for spatial data types. The mapping of EF Core data types to spatial data types is supported via the NetTopologySuite spatial library.

Create a new ASP.NET Core 5.0 Project in Visual Studio 2019

Assuming that the necessary software has been installed on your computer, follow the steps outlined below to create a new ASP.NET Core Web API project.

First off, open the Visual Studio 2019 IDE
Next, click "Create a new project" once the IDE has loaded
In the "Create a new project" screen, select “ASP.NET Core Web API” as the project template.
Click the "Next" button
Specify the project name and location - where it should be stored in your system
Optionally, click the "Place solution and project in the same directory" checkbox.
Next, click the "Create" button
In the "Create a new ASP.NET Core Web Application" dialog window that is shown next, select "API" as the project template.
In the “Additional Information” screen, .NET 5.0 is the framework version.
You should disable the "Configure for HTTPS" and "Enable Docker Support" options by disabling the respective checkboxes.
Since we'll not be using authentication in this example, specify authentication as "No Authentication".
Finally, click on the "Create" button to finish the process.

Install the Required NuGet Packages

To work with the code examples given in this article, you should install Devart.Data.Oracle.EFCore.NetTopologySuite in your project. There are two ways you can accomplish this. The first one is installing the package from the NuGet Package Manager inside the Visual Studio IDE. The other option is to install it from the Package Manager Console by typing the following command in there:

Install-Package Devart.Data.Oracle.EFCore.NetTopologySuite

Create the Database Table(s)

Assuming you’ve installed Oracle on your computer, you can use the following syntax for creating a new table in Oracle. Note how schema names and constraints are specified.

CREATE TABLE city (
    id number(9) PRIMARY KEY NOT NULL,
    name varchar2(50) NOT NULL,
    point SDO_GEOMETRY,   
    area BINARY_DOUBLE NOT NULL,
    CONSTRAINT cities_pk PRIMARY KEY (id) 
);

Next, populate this table with some meaningful data as shown below,

INSERT INTO City Values(
    1, 'Boston', SDO_GEOMETRY(2001, 8307,SDO_POINT_TYPE ( -71.0598, 42.3584,NULL), NULL, NULL), 0)
);
INSERT INTO City Values(
    2, 'Dallas', SDO_GEOMETRY(2001, 8307,SDO_POINT_TYPE (-96.8005, 32.7801,NULL), NULL, NULL), 0)
);

We’ll use this table in the sections that follow.

Creating an OracleConnection

First off, you should provide the Oracle database credentials in your application to establish a connection to the database. You can save this information inside of a connection string and make it configurable.

The connection string contains information such as the name of the database server, user Id, password, etc. You can create an OracleConnection in two distinct ways: during the design and run phases. You may build an OracleConnection during the design phase by using the Visual Studio Integrated Development Environment Toolbox.

To create an instance of OracleConnection at run-time, you can use the following code snippet,

OracleConnection oracleConnection = new OracleConnection();
oracleConnection.Server = "DemoXServer";
oracleConnection.UserId = "joydip";
oracleConnection.Password = "mypass1@3";

You should include the following namespace in your program,

using Devart.Data.Oracle;

NetTopologySuite Configuration

You should use the UseNetTopologySuite() method of the DbContext options builder and enable the ability to map properties to spatial data types to use the NetTopologySuite library in your application. The following code snippet illustrates how this can be achieved,

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
        optionsBuilder.UseOracle(@ "User Id=joydip;Password=mypass1@3;
            Data Source = Ora;
            ",x => x.UseNetTopologySuite());
        }

Calculate the Distance Between Two Places

Consider the City class given below,

public class City {
    public int Id {
        get;
        set;
    }
    public Point Geometry {
        get;
        set;
    }
    public string Name {
        get;
        set;
    }
    public double Area {
        get;
        set;
    }
}

We’ll need a DbContext class to connect to and work with Oracle. It should be noted that with the created table in Oracle, it is also possible to generate both the City class and MyDbContext via EntityDeveloper ORM, included in the provider. The following code snippet illustrates how you can create a custom DbContext class,

public class MyDbContext: DbContext {
        public IQueryable < City > Cities {
            get;
            internal set;
        }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
            optionsBuilder.UseOracle(@ "User Id=joydip;Password=mypass1@3;
                Data Source = Ora;
                ",x => x.UseNetTopologySuite());
            }
        }

You can take advantage of the following LINQ to Entities query using Oracle Spatial to calculate the distance between two cities,

var query = from sourceCity in ctx.Cities
where sourceCity.Name == sourceCityName
let secondCity = ctx.Cities.Where(destinationCity => destinationCity.Name == destinationCityName).Select(secondCity => secondCity.Geometry).FirstOrDefault()
select new {
    FirstCity = sourceCity.Geometry,
    SecondCity = secondCity,
    Distance = sourceCity.Geometry.Distance(secondCity)
};

The following code snippet shows how you can display the name and area of cities in descending order of area values,

var query = ctx.Cities.Select(city => new {
    Name = city.Name,
    Area = city.Geometry.X * city.Geometry.Y;
}).OrderByDescending(x => x.Area);

Summary

Many applications now make extensive use of geographical data for analysis and the formulation of critical business decisions. Take, for example, internet taxi applications such as Uber or Ola. Their whole business strategy is built on location-based data. Note that for the sake of simplicity, I’ve demonstrated how to work with Spatial data in Oracle using dotConnetc for Oracle and ASP.NET 5 with simple code examples. Once you’ve got a good grasp of the concepts, you can build your own application that leverages the concepts covered in this article.