Post
Ask Question

Spatial Queries In Entity Framework Core

Spatial data, also known as geospatial data, is information about a physical object that can be represented by numerical values in a geographic coordinate system. Many applications nowadays make heavy use of spatial data for analysis and to make important business decisions. Consider the examples of online taxi apps, like Uber or Ola. Their whole business model is running based on the location-based data.
 
A spatial database is a database that is optimized for storing and querying data that represents objects defined in a geometric space. Most spatial databases allow the representation of simple geometric objects, such as points, lines, and polygons. Microsoft introduced two spatial data types with SQL Server 2008: geometry and geography. Similarly, databases like PostgreSQL, MySQL also provide spatial data types.
 
With the release of Entity Framework Core 2.2, Microsoft brought support for spatial types and queries in EF Core. With the introduction of spatial types, we could do queries like whether a location falls in a certain area, or queries based on the distance between two points, etc.
 
EF Core supports mapping to spatial data types using the NetTopologySuite spatial library.
 
We will create a sample ASP.NET Core MVC application to learn how to use spatial types in EF Core. Note that we need EF Core version 2.2 and above to support spatial types. This MVC application will collect a latitude and longitude as user input and list a set of tourist attraction places in the ascending order of distance from the input location. I will be using Visual Studio Code and DotNet CLI for development.
  • Create a directory SpatialSample. Open Visual Studio Code and run the command dotnet new mvc --name SpatialSample in the terminal. This shall create an ASP.NET Core MVC application for us.

  • In order to use spatial data with EF Core, we need to install the appropriate supporting NetTopologySuite package from Nuget based on the SQL provider we use. Since I am using SQL Server as my database I need to add the Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuite package. Run the command dotnet add package Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuite --version 2.2.4 in the terminal to add the package.
Now, let's add our tourist attraction entity. Create a folder Entities and add a class TouristAttractions.cs.
  1. using System.ComponentModel.DataAnnotations.Schema;  
  2. using GeoAPI.Geometries;  
  3.   
  4. namespace SpatialSample.Entities  
  5. {  
  6.     public class TouristAttraction  
  7.     {  
  8.         public int Id { get; set; }  
  9.         public string Name { get; set; }  
  10.   
  11.         [Column(TypeName = "geometry")]  
  12.         public IPoint Location { get; set; }  
  13.     }  
  14. }  
Observe the Location property in the entity. It is of type IPoint. It is a NetTopologySuite type which is used to represent a location as a point. Note that I have configured the Location properties column type as geometry. This is because, in SQL Server, spatial properties are mapped to geography type by default.
 
Now, let's add our DbContext class. Create a class called SpatialDbContext.cs.
  1. using GeoAPI.Geometries;  
  2. using Microsoft.EntityFrameworkCore;  
  3. using NetTopologySuite;  
  4. using SpatialSample.Entities;  
  5.   
  6. namespace SpatialSample  
  7. {  
  8.     public class SpatialDbContext : DbContext  
  9.     {  
  10.         public DbSet<TouristAttraction> TouristAttractions { get; set; }  
  11.   
  12.         public SpatialDbContext(DbContextOptions options) : base(options) { }  
  13.     }  
  14. }  
Now, in the Startup.cs class, we need to register our DbContext. Since we are using NetTopologySuite for mapping spatial data, we need to use the UseNetTopologySuite method on the provider’s DbContext options builder. So, in the ConfigureServices method, add the following code.
  1. services.AddDbContext<SpatialDbContext>(opts =>  
  2. {  
  3.     opts.UseSqlServer(  
  4.         "<Connection string goes here>",  
  5.         x => x.UseNetTopologySuite()  
  6.     );  
  7. });  
We need to add the migration to create our database. Run the command dotnet ef migrations add AddTouristAttractionTable. After the migrations are created, run the command dotnet ef database update to apply the migrations on the database.
 
We need to add some seed data for the application. We will add some tourist attraction destinations in the database. We can override the OnModelCreating method of DbContext class to add seed data.
  1. protected override void OnModelCreating(ModelBuilder modelBuilder)  
  2. {  
  3.     var geometryFactory = NtsGeometryServices.Instance.CreateGeometryFactory(srid: 4326);  
  4.   
  5.     modelBuilder.Entity<TouristAttraction>()  
  6.         .HasData(  
  7.             new TouristAttraction  
  8.             {  
  9.                 Id = 1,  
  10.                 Name = "Taj Mahal",  
  11.                 Location = geometryFactory.CreatePoint(new Coordinate(27.175015, 78.042155))  
  12.             },  
  13.             new TouristAttraction  
  14.             {  
  15.                 Id = 2,  
  16.                 Name = "The Golden Temple of Amritsar",  
  17.                 Location = geometryFactory.CreatePoint(new Coordinate(31.619980, 74.876485))  
  18.             },  
  19.             new TouristAttraction  
  20.             {  
  21.                 Id = 3,  
  22.                 Name = "The Red Fort, New Delhi",  
  23.                 Location = geometryFactory.CreatePoint(new Coordinate(28.656159, 77.241020))  
  24.             },  
  25.             new TouristAttraction  
  26.             {  
  27.                 Id = 4,  
  28.                 Name = "The Gateway of India, Mumbai",  
  29.                 Location = geometryFactory.CreatePoint(new Coordinate(18.921984, 72.834654))  
  30.             },  
  31.             new TouristAttraction  
  32.             {  
  33.                 Id = 5,  
  34.                 Name = "Mysore Palace",  
  35.                 Location = geometryFactory.CreatePoint(new Coordinate(12.305025, 76.655753))  
  36.             },  
  37.             new TouristAttraction  
  38.             {  
  39.                 Id = 6,  
  40.                 Name = "Qutb Minar",  
  41.                 Location = geometryFactory.CreatePoint(new Coordinate(28.524475, 77.185521))  
  42.             }  
  43.         );  
  44. }  
We need to create a geometry object from latitude and longitude of a location to save in our database. For creating geometries we can create a geometry factory using CreateGeometryFactory method of NetTopologySuite. Then we use the CreatePoint method of the geometry factory to create a point from a set of coordinates. Note that CreateGeometryFactory method accepts a parameter called srid. An SRID or spatial reference identifier is a unique identifier associated with a specific coordinate system. 4326 is a commonly used SRID.
 
After adding the seed data, we need to create another migration as data seeding has become part of EF migrations in .NET Core. Run the command dotnet ef migrations add AddSeedData. After the migrations are created run the command dotnet ef database update to apply the migrations on the database.
 
We can verify by checking the TouristAttractions table in the database. 
 
Spatial Queries in Entity Framework Core
 
We need to create the web part of our application next. We shall create View Model classes first. We create a View Model to bind the user input and a view model to bind the list of location information based on the user search.
  1. namespace SpatialSample.Models  
  2. {  
  3.     public class TouristAttractionViewModel  
  4.     {  
  5.         public double Latitude { get; set; }  
  6.         public double Longitude { get; set; }  
  7.         public string Name { get; set; }  
  8.         public double Distance { get; set; }  
  9.     }  
  10.   
  11.     public class SearchInputModel  
  12.     {  
  13.         public double Latitude { get; set; }  
  14.         public double Longitude { get; set; }  
  15.     }  
  16.   
  17.     public class IndexPageViewModel  
  18.     {  
  19.         public SearchInputModel SearchInput { get; set; }  
  20.         public List<TouristAttractionViewModel> TouristAttractions { get; set; }  
  21.     }  
  22. }  
Now, let's create the view for user interaction. In the Index.cshtml file in the Views\Home folder, replace the existing code with the following code.
  1. @{  
  2.     ViewData["Title"] = "Spatial Sample";  
  3. }  
  4.   
  5. @model IndexPageViewModel    
  6.   
  7. <form     
  8.     asp-controller="Home"     
  9.     asp-action="Search"    
  10.     method="post"     
  11.     class="form-horizontal"     
  12.     role="form">    
  13.     
  14.     <div class="form-group">    
  15.         <label for="Title">Latitude</label>    
  16.         <input     
  17.             class="form-control"     
  18.             placeholder="Enter Latitude"    
  19.             asp-for="SearchInput.Latitude">       
  20.     </div>   
  21.     <div class="form-group">    
  22.     <label for="Title">Longitude</label>    
  23.     <input     
  24.         class="form-control"     
  25.         placeholder="Enter Longitude"    
  26.         asp-for="SearchInput.Longitude">       
  27.     </div>   
  28.       
  29.     <button type="submit" class="btn btn-primary">Submit</button>    
  30. </form>    
  31.   
  32. @{  
  33.     if(Model?.TouristAttractions?.Any() == true)  
  34.     {  
  35.         <br>          
  36.         <br>  
  37.           
  38.         <table class="table table-bordered table-responsive table-hover">    
  39.         <tr>    
  40.             <th>Name</th>    
  41.             <th>Distance</th>    
  42.             <th>Coordinates</th>    
  43.         </tr>    
  44.         @foreach (var t in Model.TouristAttractions)    
  45.         {    
  46.             <tr>    
  47.                 <td>@t.Name</td>    
  48.                 <td>@t.Distance</td>    
  49.                 <td>@t.Latitude, @t.Longitude</td>     
  50.             </tr>    
  51.         }    
  52.         </table>  
  53.     }  
  54.     else  
  55.     {  
  56.         <br>          
  57.         <br>  
  58.         <div class="alert alert-warning" role="alert">    
  59.             <strong>Please enter Latitude and Longitude of the location</strong>     
  60.         </div>   
  61.     }  
  62. }  
The View contains a form that accepts a latitude and longitude value from the user and submits the input to a controller action. Let's create the controller next. Replace the code in the HomeController.cs with the following.
  1. using System;  
  2. using System.Linq;  
  3. using Microsoft.AspNetCore.Mvc;  
  4. using NetTopologySuite.Geometries;  
  5. using SpatialSample.Models;  
  6.   
  7. namespace SpatialSample.Controllers  
  8. {  
  9.     public class HomeController : Controller  
  10.     {  
  11.         private readonly SpatialDbContext _spatialDbContext;  
  12.   
  13.         public HomeController(SpatialDbContext spatialDbContext)  
  14.         {  
  15.             this._spatialDbContext = spatialDbContext;  
  16.         }  
  17.   
  18.         public IActionResult Index()  
  19.         {  
  20.             return View();  
  21.         }  
  22.   
  23.         [HttpPost]  
  24.         public IActionResult Search([FromForm] IndexPageViewModel indexModel)  
  25.         {  
  26.             var indexViewModel = new IndexPageViewModel  
  27.             {  
  28.                 SearchInput = indexModel.SearchInput  
  29.             };  
  30.   
  31.             // Convert the input latitude and longitude to a Point  
  32.             var location = new Point(indexModel.SearchInput.Latitude, indexModel.SearchInput.Longitude) { SRID = 4326 };  
  33.   
  34.             // Fetch the tourist attractions and their  
  35.             // distances from the input location   
  36.             // using spatial queries.  
  37.             var touristAttractions = _spatialDbContext  
  38.                 .TouristAttractions  
  39.                 .Select(t => new { Place = t, Distance = t.Location.Distance(location) })  
  40.                 .ToList();  
  41.   
  42.             // Ordering the result in the ascending order of distance  
  43.             indexViewModel.TouristAttractions = touristAttractions  
  44.                 .OrderBy(x => x.Distance)  
  45.                 .Select(t => new TouristAttractionViewModel  
  46.                 {  
  47.                     Distance = Math.Round(t.Distance, 6),  
  48.                     Latitude = t.Place.Location.X,  
  49.                     Longitude = t.Place.Location.Y,  
  50.                     Name = t.Place.Name  
  51.                 }).ToList();  
  52.   
  53.             return View("Index", indexViewModel);  
  54.         }  
  55.     }  
  56. }  
The latitude and longitude submitted by the user are passed to the Search action. The latitude and longitude are first converted to a point object and are passed to the spatial query to be run by EF Core. The query will compare every location in the database and return the distance of each with the input location. Then we order the result set in ascending order of the distances and returns the result to the view.
NB
The distance calculated y NetTopologySuite is not the actual distance in kilometers between two locations. The distance is calculated based on a Cartesian plane between two coordinates. If we need to get the actual distance between two locations in kilometers we need to perform some advanced spatial queries and transforms.
 
Now run the application using the command dotnet run. The application will be loaded in the browser with a form to accept a latitude and longitude value. Enter any coordinates and click the Submit button. Let's try New Delhi (28.613939, 77.209023). We shall get a screen like below.
 
Spatial Queries in Entity Framework Core
 

Summary

 
In this article, we have looked at the spatial types and queries support in Entity Framework core. For more information on the spatial support in EF Core, check out this link.


Similar Articles