Spatial data is the data about the location of places. In our case, we are interested in the location of places on our planet earth. When we talk about spatial queries, we mean queries we can make to databases that contain spatial data.
Some examples of a space queries would be: What are the restaurants closest to my current location? What tourist attractions are located within 3 miles from my current location?
Starting with Entity Framework Core 2.2, and with the help of the NetTopologySuite library, we can make space queries.
In this post we will see how to use EF Core 3.1 to perform spatial queries to a SQL Server database to determine which restaurants are closest to our current location.
This is a step by step video tutorial on the subject:
Now a summary of the video:
Github Repo: https://github.com/gavilanch/EFCoreSpatialQueries
We install the following libraries:
Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuite
Microsoft.EntityFrameworkCore.Tools (optional: only needed if you are going to use Visual Studio on Windows)
Microsoft.Extensions.Logging.Console (optional: I use this one to display the queries generated by EF Core in the console)
We create the Restaurant model, and in it we use the Point data type, which comes from NetTopologySuite.Geometries, this to be able to save the geographic location of a restaurant:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| public class Restaurant | |
| { | |
| public int Id { get; set; } | |
| public string Name { get; set; } | |
| public string City { get; set; } | |
| public Point Location { get; set; } | |
| } |
Then, we create a class that inherits from DbContext, and we configure it to use SQL Server. In addition, we configure the NetTopologySuite to work with EF Core. Below, we also set up test data for our Restaurant entity:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| public class ApplicationDbContext: DbContext | |
| { | |
| protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) | |
| { | |
| optionsBuilder.UseSqlServer("Data Source=.;Initial Catalog=EFCoreSpatial;Integrated Security=True", | |
| x => x.UseNetTopologySuite()) | |
| .UseLoggerFactory(MyLoggerFactory); | |
| base.OnConfiguring(optionsBuilder); | |
| } | |
| public static readonly ILoggerFactory MyLoggerFactory = LoggerFactory.Create(builder => | |
| { | |
| builder | |
| .AddFilter((category, level) => | |
| category == DbLoggerCategory.Database.Command.Name | |
| && level == LogLevel.Information) | |
| .AddConsole(); | |
| }); | |
| protected override void OnModelCreating(ModelBuilder modelBuilder) | |
| { | |
| var geometryFactory = NtsGeometryServices.Instance.CreateGeometryFactory(srid: 4326); | |
| modelBuilder.Entity<Restaurant>() | |
| .HasData( | |
| new List<Restaurant>() | |
| { | |
| new Restaurant(){Id = 1, Name = "Agora", City = "Santo Domingo", Location = geometryFactory.CreatePoint(new Coordinate(–69.9388777, 18.4839233))}, | |
| new Restaurant(){Id = 2, Name = "Sambil", City = "Santo Domingo", Location = geometryFactory.CreatePoint(new Coordinate(–69.9118804, 18.4826214))}, | |
| new Restaurant(){Id = 3, Name = "Adrian Tropical", City = "Santo Domingo", Location = geometryFactory.CreatePoint(new Coordinate(–69.9334673, 18.4718075))}, | |
| new Restaurant(){Id = 4, Name = "Restaurante El Cardenal", City = "Mexito City", Location = geometryFactory.CreatePoint(new Coordinate(–99.1353659,19.4336164))} | |
| }); | |
| base.OnModelCreating(modelBuilder); | |
| } | |
| public DbSet<Restaurant> Restaurants { get; set; } | |
| } |
Note that in the OnModelCreating we use the geometryFactory with srid equal to 4326 (WGS 84). This is the standard in cartography and GPS systems. Therefore, for locations on our planet, it is the most used.
Then, we can add the migration and send the changes to the database.
Finally, we can make queries to our Restaurants table. For example, let’s say we want to query all the restaurants whose distance to us is less than 2 km (2,000 meters). For this, we can say:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| var geometryFactory = NtsGeometryServices.Instance.CreateGeometryFactory(srid: 4326); | |
| var myLocation = geometryFactory.CreatePoint(new Coordinate(–69.938951, 18.481188)); | |
| using (var context = new ApplicationDbContext()) | |
| { | |
| var restaurants = context.Restaurants | |
| .OrderBy(x => x.Location.Distance(myLocation)) | |
| .Where(x => x.Location.IsWithinDistance(myLocation, 2000)) | |
| .Select(x => new { x.Name, x.City, Distance = x.Location.Distance(myLocation) }) | |
| .ToList(); | |
| Console.WriteLine("———–"); | |
| foreach (var restaurant in restaurants) | |
| { | |
| Console.WriteLine($"{restaurant.Name} from {restaurant.City} ({restaurant.Distance.ToString("N0")} meters away)"); | |
| } | |
| } |
Here we are hardcoding the user’s location, however, in real life, this value will come from the user’s device.
Regards!
Felipe Gavilán 