Entity Framework Core 3.1: Spatial Queries – Nearby Places

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:

public class Restaurant
{
public int Id { get; set; }
public string Name { get; set; }
public string City { get; set; }
public Point Location { get; set; }
}

view raw
restaurant.cs
hosted with ❤ by GitHub

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:

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:

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)");
}
}

view raw
Program.cs
hosted with ❤ by GitHub

Here we are hardcoding the user’s location, however, in real life, this value will come from the user’s device.

Regards!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s